Preventing SQL Injection

It may be hard to understand just how dangerous it is to pass user input unchecked to MySQL. For example, suppose you have a simple piece of code to verify a user, and it looks like this:

$user = $_POST['user'];
$pass = $_POST['pass'];
$query = "SELECT * FROM users WHERE user='$user' AND pass='$pass'";

At first glance, you might think this code is perfectly fine. If the user enters values of fredsmith and mypass for $user and $pass, then the query string, as passed to MySQL, will be as follows:

SELECT * FROM users WHERE user='fredsmith' AND pass='mypass'

This is all well and good, but what if someone enters the following for $user (and doesn’t even enter anything for $pass)? admin’ #

Let’s look at the string that would be sent to MySQL:

SELECT * FROM users WHERE user='admin' #' AND pass=''

Do you see the problem there? In MySQL, the # symbol represents the start of a comment. Therefore the user will be logged in as admin (assuming there is a user admin), without having to enter a password. In the following, the part of the query that will be executed is shown in bold—the rest will be ignored.

SELECT * FROM users WHERE user='admin' #' AND pass=''

But you should count yourself very lucky if that’s all a malicious user does to you. At least you might still be able to go into your application and undo any changes the user makes as admin. But what about the case in which your application code removes a user from the database? The code might look something like this:

$user = $_POST['user'];
$pass = $_POST['pass'];
$query = "DELETE FROM users WHERE user='$user' AND pass='$pass'";

Again, this looks quite normal at first glance, but what if someone entered the following for $user? anything’ OR 1=1 #
This would be interpreted by MySQL as:

DELETE FROM users WHERE user='anything' OR 1=1 #' AND pass=''

Ouch—that SQL query will always be true and therefore you’ve lost your whole users database! So what can you do about this kind of attack?
Well, the first thing is not to rely on PHP’s built-in magic quotes, which automatically escape any characters such as single and double quotes by prefacing them with a backslash (\). Why? Because this feature can be turned off; many programmers do so in order to put their own security code in place. So there is no guarantee that this hasn’t happened on the server you are working on. In fact, the feature was deprecated as of
PHP 5.3.0 and has been removed in PHP 6.0.0.
 
Instead, you should always use the function mysql_real_escape_string for all calls to MySQL. Following Example is a function you can use that will remove any magic quotes added to a user-inputted string and then properly sanitize it for you.

<?php 
function mysql_fix_string($string)
{
	if (get_magic_quotes_gpc()) $string = stripslashes($string);
	return mysql_real_escape_string($string);
}
?>

The get_magic_quotes_gpc function returns TRUE if magic quotes are active. In that case, any slashes that have been added to a string have to be removed or the function mysql_real_eascape_string could end up double-escaping some characters, creating corrupted strings. Following Example illustrates how you would incorporate mysql_fix within your own code.

<?php
$user = mysql_fix_string($_POST['user']);
$pass = mysql_fix_string($_POST['pass']);
$query = "SELECT * FROM users WHERE user='$user' AND pass='$pass'";
function mysql_fix_string($string)
{
	if (get_magic_quotes_gpc()) $string = stripslashes($string);
	return mysql_real_escape_string($string);
}
?>

Remember that you can use mysql_escape_string only when a MySQL database is actively open otherwise, an error will occur.