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. If these users are presented with a login box somewhere on your site, your php user verification query might be something like this:
SELECT * FROM `tbl_Users` WHERE `username`='”.$_POST[‘username’].”‘ AND `password`=’”.md5($_POST[‘password’]).”‘”The problem is that unscrupulous users (read: bad ones) could enter this into your form:
username: no_onepassword: ‘ OR ”=”Which would make your query look something like this:
SELECT * FROM `tbl_Users` WHERE `username`=’no_one’ AND `password`=” OR ”=”Which, if you read that correctly, would allow that user access to whatever it was you wanted hidden by logging them in. There are a multitude of other ways this can be dangerous, but this is by far the easiest example. Even more unscrupulous users (read: the real jackasses) could send in multiple queries including DELETE queries.
In which case, when you wake up the morning after the attack you are most likely to be heard saying:”Hey, where did all my users go?”
Wow. Okay so I’ve got a friend… and his website isn’t secure. What can I do to help him out?
The good news is that with a few easy 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’therebecomes
username: hey\’thereThis 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 change of a MySQL injection attack.
#3: Always use proper MySQL syntax, including “`” and “‘” characters.
If your queries look something like this:
SELECT * FROM tbl_Users WHERE username=$value; Rewrite it so that it looks more like this:
$value = mysql_real_escape_string($value);mysql_query(SELECT * FROM `tbl_Users` WHERE `username`='”.$value.”‘”); Proper MySQL syntax requires that all table and field names are surrounded by the reverse apostraphe (”`”) and values surrounded with single quotes / apostraphe (”‘”).
I hope this gives you a better indication of what you can do to help secure your websites. Keep in mind that this is in no way a complete list. Be ever vigilant in your efforts to prevent attacks of any kind on your code. Leave a comment or two if this helped you at all or if you have different suggestions on how to secure your code from injection attacks!
