In my first blog post, I talked a bit about the ability to use client side software to edit forms and submit data that might not be expected from a server side script. Well now I am back, and like the kool-aid dude, I am bursting through your screens with knowledge; OH YEAH!

Okay, now that we have the comedy out of the way, time to get to the important stuff.  This post is going to be all about SQL injection attacks. Of all the possible exploits that can be done against a server, this is certainly in the top 5 most scary in my book (yes, I check under my bed for the SQL injection monster).

What is SQL Injection:

SQL Injection is a term that is used to refer to a situation in which user input is placed into a database query in such a way that allows for unintended manipulation of data. Most commonly this occurs when user input is not properly validated and scrubbed.

Example:

Let us assume that you start with the following  SQL statement:

$statement = "SELECT * FROM users WHERE name = '" . $_REQUEST[‘userName’] . "';";

Okay, pretty simple. You basically want to select the user with the name that was submitted to the form field userName… but here is how it can be used against you by a bored high school kid that has read a little bit about hacking the interwebs.

He first tries to see if your script is vulnerable by typing the following into the userName field:

a' or 't'='t

Now your statement looks exactly like this to the database engine:

SELECT * FROM users WHERE name = 'a' or 't'='t’;

This doesn’t seem that bad, but if this were in the context of an authentication script, it would always result in true and the young high school hacker has essentially bypassed your login script. (What is even worse, is that most of the time when we create user tables the first entry is the administrator which means that this individual would most likely be logged in as an admin)

Now what if this enterprising high school student decided that he didn’t just want to log in, but wanted to ruin your day… he could simply write the following into the userName field.

a';DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't

Making the query look like:

SELECT * FROM users WHERE name = ' a';DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't’

At this point you have lost your entire users table and praying that there is a backup of the database somewhere.

How to prevent it:

In most cases you want to do 2 things.

1)   validate user input before you even attempt to put it in the database. This means that if you are expecting a number, write a function that validates that the user entered a number. This helps ensure that your database isn’t filled with random data in something like a zip code field.

2)   Put user data through a scrubbing function. In most cases the following scrubbing function should be adequate.

function cleanQuery($string) {
if(get_magic_quotes_gpc()) {  // prevents duplicate backslashes
$string = stripslashes($string);
}
if (phpversion() >= '4.3.0') { //PHP is always version 5 on RadioTown
$string = mysql_real_escape_string($string);
} else {
$string = mysql_escape_string($string);
}
return $string;
}