PHP Programming/SQL Injection Attacks

The Problem

Consider the following SQL query in PHP:

$result=mysql_query('SELECT * FROM users WHERE username="'.$_GET['username'].'"');

The query selects all rows from the users table where the username is equal to the one put in the query string. If you look carefully, you'll realise that the statement is vulnerable to SQL Injection - quotes in $_GET['username'] are not escaped, and thus will be concatenated as part of the statement, which can allow malicious behaviour.

Consider what would happen if $_GET['username'] was the following: " OR 1 OR username = " (a double-quote, followed by a textual " OR 1 OR username = " followed by another double-quote). When concatenated into the original expression, you have a query that looks like this: SELECT * FROM users WHERE username = "" OR 1 OR username = "". The seemingly redundant OR username = " part added is to ensure that the SQL statement evaluates without error. Otherwise, a hanging double quote would be left at the end of the statement.

This selects all rows from the users table.

Solutions

Input Validation

Never trust user provided data, process this data only after validation; as a rule, this is done by pattern matching. In the example below, the username is restricted to alphanumerical chars plus underscore and to a length between eight and 20 chars - modify as needed.

 if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))
   $result = mysql_query("SELECT * FROM users WHERE username='$matches[0]'");
 else // we don't bother querying the database
   echo "username not accepted";

For increased security, you might want to abort the script's execution replacing echo by exit() or die().

This issue still applies when using checkboxes, radio buttons, select lists, etc. Any browser request(even POST) can be replicated through telnet, duplicate sites, javascript, or code (even PHP), so always be cautious of any restrictions set on client-side code.

Escaping Values

PHP provides you with a function to deal with user input in MySQL, and that is mysqli_real_escape_string([mysqli link, ]string unescaped_string). This script escapes all potentially dangerous characters in the string provided and returns the escaped string such that it may be safe to put into a MySQL query. However, if you do not sanitize input prior to passing it to mysqli_real_escape_string() function you still may have SQL injection vectors. For example; mysqli_real_escape_string would not protect against an SQL injection vector such as the following:

  $result = "SELECT fields FROM table WHERE id = ".mysqli_real_escape_string($_POST['id']);

If $_POST['id'] contained 23 OR 1=1 then the resulting query would be:

  SELECT fields FROM table WHERE id = 23 OR 1=1

which is a valid SQL injection vector.

(The original function, mysql_escape_string, did not take the current character set in account for escaping the string, nor accepted the connection argument. It is deprecated since PHP 4.3.0.)

For example, consider one of the examples above:

$result=mysqli_query($link, 'SELECT * FROM users WHERE username="'.$_GET['username'].'"');

This could be escaped as follows:

$result=mysqli_query($link, 'SELECT * FROM users WHERE username="'.mysqli_real_escape_string($_GET['username']).'"');

This way, if the user tried to inject another statement such as a DELETE, it would harmlessly be interpreted as part of the WHERE clause parameter as expected:

SELECT * FROM `users` WHERE username = '\';DELETE FROM `forum` WHERE title != \''

The backslashes added by mysqli_real_escape_string make MySQL interpret them as actual single quote characters rather than as part of the SQL statement.

Note that MySQL does not allow stacking of queries so the ;DELETE FROM table attack would not work anyway

Parameterized Statements

The PEAR's DB package provides a prepare/execute mechanism to do parameterized statements.

require_once("DB.php");
$db = &DB::connect("mysql://user:pass@host/database1");
$p = $db->prepare("SELECT * FROM users WHERE username = ?");
$db->execute( $p, array($_GET['username']) );

The query() method, also do the same as prepare/execute,

$db->query( "SELECT * FROM users WHERE username = ?", array($_GET['username']) );

The prepare/execute will automatically call mysql_real_escape_string() as discussed in the above section.

In PHP version 5 and MySQL version 4.1 and above, it is also possible to use prepared statements through mysqli extension. Example:

$db = new mysqli("localhost", "user", "pass", "database");
$stmt = $db -> prepare("SELECT priv FROM testUsers WHERE username=? AND password=?");
$stmt -> bind_param("ss", $user, $pass);
$stmt -> execute();

Similarly, you could use the built-in PDO Class in PHP5.

For More Information

• PHP Manual: SQL Injection
• How to prevent SQL Injection Attacks
• Preventing SQL Injection in PHP MySQL Insert and Update Queries
• Preventing SQL Injection in PHP MySQL Select Queries