In this tutorial I am going to explain what MySQL injection is and how important it is to prevent this attack on your database(s). It is vital that all security measures are in place before you make your website live because the effects can be devastating especially if you store sensitive data. Lets start with the basics and learn what MySQL injection is.
What is MySQL injection?
“MySQL injection is an attack technique used to exploit applications that construct SQL statements from user-supplied input.” [Cyferweb.com] This statement for me sums it up perfectly. It is a way of exploiting the background SQL statement by entering specific logic to a form (or other means of user input) and then sending their own commands to control your database. This could be as simple as gaining access to an admin/user area to even deleting an entire database. When sensitive data is stored it becomes even more vital to prevent this attack else you risk losing this information into the wrong hands.
How often does this attack occur?
It is diffult to estimate just how many websites are suffering from these attacks but it is really dependant on the level of coding. Poor coders who don’t consider the risks of MySQL injection run the risk of losing their entire database or more importantly – a client’s database. PHP developers that put the effort it to prevent such attacks are those considered much more advanced and in the long run saving lots of important data.
Can you show me an example of MySQL injection?
MySQL injection is really very easy and anyone can do it. Take the following example: a user supplies a username and password to enter the administration section of a website. A poorly written login script will query the database directly for either the username, password or both without stripping/escaping each string first. Check out the code below:
<?php
$query = mysql_query("SELECT id FROM users WHERE username = '".$username."' AND password = '".$password."'");
?>
As you can see the database is queried for a record matching the username AND the password. Now, if I was to enter my password as ‘ OR 1=1′ then this would cause the query to match 1 to 1, which is of course true – 1 does equal 1. This will result in a positive login and the hacker gaining access to a restricted area of the website. This is a very simple example of how MySQL injection can occur.
How do I prevent MySQL injection?
All you need to do to prevent these attacks is to ensure that you escape each item being passed into the query. Normally you wouldn’t query the users table for both the username and password but for the sake of this tutorial I will keep the query the same. The best way of going around it would be to query for the username (escaped) whilst returning the password from the table, then matching the string passed through the form with the password that is returned. That way you can match hash/salts for encrypted passwords.
I normally always use mysql_real_escape_string() to escape my user-inputted data because it escapes any special characters (such as ‘ and “ which are the prime cause of MySQL injection). Therefore your new query would look like this:
<?php
$query = mysql_query("SELECT id FROM users WHERE username = '".mysql_real_escape_string($username)."' AND password = '".mysql_real_escape_string($password)."'");
?>
The password is now escaped meaning that if that same injection string was to be passed through the form, it would actually look for a password matching that instead of manipulating the query. The hacker would have no way (using MySQL injection anyway) of altering or even deleting your database.