How to fix SQL injection vulnerabilities

Here is an example of vulnerable code in which the user-supplied input is directly used in a SQL query:

Name: < input type = "text" name = " Name" />

< input type = " submit " value = " Add Comment" />

$query = "SELECT * FROM users WHERE username = '{$_POST['username']}";
$result = mysql_query($query);
?>

The script will work normally when the username doesn't contain any malicious characters. In other words, when submitting a non-malicious username (steve) the query becomes:

$query = "SELECT * FROM users WHERE username = 'steve'";

However, a malicious SQL injection query will result in the following attempt:

$query = "SELECT * FROM users WHERE username = '' or '1=1'";

As the "or" condition is always true, the mysql_query function returns records from the database. A similar example, using AND and a SQL command to generate a specific error message, is shown in the URL below in Figure 1.


Figure 1. Error message displaying the MS SQL server version.

It is obvious that these error messages help an attacker to get a hold of the information which they are looking for (such as the database name, table name, usernames, password hashes etc). Thus displaying customized error messages may be a good workaround for this problem, however, there is another attack technique known as Blind SQL Injection where the attacker is still able to perform a SQL injection even when the application does not reveal any database server error message containing useful information for the attacker.

Fix:

1. Avoid connecting to the database as a superuser or as the database owner. Always use customized database users with the bare minimum required privileges required to perform the assigned task.
2. If the PHP magic_quotes_gpc function is on, then all the POST, GET, COOKIE data is escaped automatically.
3. PHP has two functions for MySQL that sanitize user input: addslashes (an older approach) and mysql_real_escape_string (the recommended method). This function comes from PHP >= 4.3.0, so you should check first if this function exists and that you're running the latest version of PHP 4 or 5. MySQL_real_escape_string prepends backslashes to the following characters: \x00, \n, \r, \, ', "and \x1a.

References for standard SQL Injection:

1. Steve's SQL Injection attack examples
2. SQL Injection Whitepaper (PDF)
3. Advanced SQL Injection paper

Blind SQL Injection:

1. SPI Dynamics blind SQL Injection paper (PDF)
2. iMperva blind SQL Injection article