Mark’s Site

SQL Injection

How SQL Injection Occurs

The first thing you need to know about websites is you can NEVER trust that the user’s input will be as you expect. Most of you know this and design your forms to only allow certain input. You add both client-side and server-side validation to fields to ensure only integers are accepted on certain fields, emails are correct and so forth. However, while this ensure your application is robust, it does not ensure protection against injection attacks, because a hacker will examine your pages to find a field that allows input of any character (most likely, a general textarea control).

Once a hacker finds a page with one such field and finds that there’s no validation on it, it’s party time.

The Hack Occurs How?

Take for example, an application that has a contacts table and a users table. Our application is written to include a standard contact form, with two fields, email_address and message.

You should always have validation on the email_address field so that you get a valid email address to which you can later reply to. However, most programmers leave no validation whatsoever on the message field, and this is an entry for an attack.

Let’s say my query for the contact form’s input is as follows:

<?php
$var_strSql = "INSERT INTO contacts ( ";
$var_strSql .= "email_address ";
$var_strSql .= ",message) ";
$var_strSql .= "VALUES (";
$var_strSql .= "'".$var_email_address."' ";
$var_strSql .= ",'".$var_message."');";
?>

As there is no validation on the message field, let me give you an example of how an attack will occur (square brackets indicate start and end of input):

Example Input:

$var_email_address = [example@emailaddress.com]
$var_message = ['); DELETE FROM users;]

SQL will see/execute what?:

INSERT INTO contacts (
email_address
,message)
VALUES (
'example@emailaddress.com'
,''); DELETE FROM users;';

Consequence:
All information deleted from our users table

Assumptions:
This assumes our SQL permissions allow DELETE permissions, and from my experience they probably do, because other aspects of our systems require delete permissions, so most programmers tend to have one SQL user with the standard set of permissions: INSERT, UPDATE, SELECT and DELETE.)

The Bad News

For those of you who have stringent permissions setup and do not have DELETE permissions allowed, you are not safe because a hacker can still run whatever INSERT statements they want (and if they have UPDATE privilieges, they can corrupt your data), which can results in some even more disturbing news than a simple DELETE.

The Even More Bad News

Unfortunately, I need to inform you that a hacker does not just have the ability to write a few extra INSERT/DELETE/UPDATE statements in these situations. SQL is a programming language on its own, allowing SQL to be written to include loops, allowing hackers to input loops of INSERT statements, flooding your SQL table and consequently crashing your server.

For example:
Example Input:

var_email_address = [example@emailaddress.com]
var_message = [''; DECLARE @COUNT INTEGER;
SET @COUNT=1000;
WHILE @COUNT > 0
BEGIN
INSERT INTO contacts (email_address, message)
VALUES (''test'', ''some text'');
@COUNT = @COUNT - 1;
END]

SQL will see/execute what?:
SQL will execute this as 1000 INSERT statements.

Now imagine the effect on your server if we set the value of @Count to a hundred thousand Fun stuff huh?

The Good News

The good news is there are safeguards. You just need to be aware of them and then you can write secure code. If you think the answer is as simple as a single quote replacement function, think again The fact is hacking attempts can occur on plain integer fields where no quotes are present to BLOB/VARCHAR fields where character encoding can be used to represent a single quote differently to how humans would interpret it. To find out the answers, stay tuned.