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  are not escaped, and thus will be concatenated as part of the statement, which can allow malicious behaviour.

Consider what would happen if  was the following:   (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:. The seemingly redundant  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.

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.

For increased security, you might want to abort the script's execution replacing  by   or.

This issue still applies when using checkboxes, radio buttons, select lists, etc. Any browser request(even ) 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. 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  function you still may have SQL injection vectors. For example;  would not protect against an SQL injection vector such as the following:

If  contained   then the resulting query would be:

which is a valid SQL injection vector.

(The original function,, 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, 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  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  attack would not work anyway

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

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

The prepare/execute will automatically call  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  extension. Example :

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