Digital Oasis

Every website has ‘em. Forms. Places for users to enter data into your website. Whether it be a search box, a “Contact Us” form, or variables in the website address, at some point in the flow of your script these suckers are going to touch your database

Oh, that’s no problem — We’ll just take what they type in and run a query in MySQL on it!

WHOA, there! Are you sure you want to do that? Any input from a user should be treated like a nuclear fuel rod. You can handle it, but you’ve got to make sure you do it right. You wouldn’t just pick it up with your bare hands, would you?

Why? Just what are MySQL Injection attacks anyway?

Lets say your database has a table inside called ‘tbl_Users’. Inside ‘tbl_Users’ are a list of your users, which all have usernames, passwords, first names, last names, addresses, etc.

Let’s pretend you’ve got a website and in that website you have an area where users have to log in to gain access to a restricted area. After the user types in their username and password your site will check the database to verify the username and password are correct. If it is correct, your site will provide them access.

The query below is an extremely simplified version of what may be running on your site, though I have seen examples of this before.

SELECT * FROM `tbl_Users` WHERE `username`='".$_POST['username']."' AND `password`='".$_POST['password']."'"

Ugh. I feel dirty just writing that.

There are numerous problems in this example that relate not only to MySQL but to general security hazards. Better solutions would be to verify the username and password separately to make logging login attempts easy and also adding a “salt” to the password.

Though this article deals directly with MySQL injection hazards, it is advisable that you do your research when it comes to login forms and security. There are other hazards out there!

The direct MySQL injection threat is that unscrupulous users (read: bad ones) could enter this into your form:

username: no_one

password: ' OR ''=''

Which would make your query look something like this:

SELECT * FROM `tbl_Users` WHERE `username`='no_one' AND `password`='' OR ''=''

This query would allow that user access to restricted page by logging them in. There are a multitude of other ways this can be dangerous, but this is by far the easiest example.

You may be safe from query stacking, though. MySQL will not allow two queries to be executed in a single function call. To make it simple, consider this example, which will cause MySQL to throw an error:

username: no_one

password: ' OR ''=''; DELETE * FROM `tbl_Users`;

Okay so I’ve got this friend… and his website isn’t secure. What can I do to help him out?

The good news is that with a few precautions, your “friend’s” website will be pretty secure against these types of attacks. I say pretty secure because there is no way to prevent every attack. We can only do our best to increase security to a point to take every realistic precaution to prevent these attacks.

#1: Escape your variables!

Using the php function ‘mysql_real_escape_string’ you can “escape” the single quote character from user input. This is probably the easiest method to prevent MySQL injection attacks. It works by adding a backslash (“\”) before each quote that the user enters into their input. So, to use our example from before:

username: hey'there

becomes

username: hey\'there

This effectively stops MySQL injection in its tracks since it not only escapes the single quote (“‘”) character but also all other characters that the baddies can use to hijack your queries.

If you’ve got an array of data coming in, you can use this neat function that I found on the PHP mysql_real_escape_string page (code by “brian dot folts at gmail dot com”). It escapes all of the values in your array with ease.

To escape an array, use this function:

function mysql_real_escape_array($t){
return array_map("mysql_real_escape_string",$t);
}

Then you can call that function easily by passing your array to it:

$your_array = mysql_real_escape_array($your_array);

#2: Check the variable type of your input.

This is done by using the php functions “is_numeric()“, “is_string()“, “is_float()“, and “is_int()” to determine if the input the user is sending in is the same type that you were asking for. It’s not perfect, but if you were asking for a number and they sent in a word you know to discard it straight away and return an error thereby entirely avoiding any chance of a MySQL injection attack.

#3: Use Prepared Statements.

Consider switching from using mysql_xxx commands in php to MySQL Improved (mysqli). A great document by Zak Greant and Georg Richter cover a lot of the basics and reasoning behind this switch.

All of these things put together will help make your site better equipped to handle malicious injection attacks. I hope this gives you a better indication of what you can do to help secure your websites. I want to personally thank Sven Arild Helleland and exsecror.pip.verisignlabs.com who smacked me upside the head a few times while reviewing this article and helped to steer me in the right direction. Thanks a ton!

Leave a comment or two if this helped you at all or if you have different suggestions on how to secure your code from MySQL injection attacks!