PHP SQL Injection

SQL Injection ?

SQL Injection is a technique which enables an attacker to execute unauthorized SQL commands by taking advantage of unsanitized input opportunities in Web applications building dynamic SQL queries.

This is due to the fact that PHP combines the strings of the MYSQL query you gave it with the variable before it so that someone could add a second query or otherwise change your MYSQL query to give them information or access that they should not have.

The most common of these unsanitized input includes unescaped quotes in SQL statements.

1. Injection of a Second Query

1.1 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 which 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. While it may not seem dangerous, other more malicious code could be added in place, especially in DELETE or UPDATE queries which modify the tables.

Also, another line of SQL code can be added to
SELECT * FROM `users` WHERE username = ''
by adding a quote and a semicolon to the end so that the line would become
SELECT * FROM `users` WHERE username = ''; '
This could easily become:
SELECT * FROM `users` WHERE username = '';
DELETE * FROM `forum` WHERE title != ''

1.2 The Solution
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 8 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 restricions set on client-side code.

2. Use of mysql_real_escape_sring()
PHP provides you with a function to deal with user input in MySQL, and that is mysql_real_escape_string(string unescaped_string[, resource link_identifier]). This script escapes all potentially dangerous characters in the string provided and returns the escaped string such that is is now safe to put into a MySQL query.

(The original function, myqsl_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=mysql_query('SELECT * FROM users WHERE username="'.$_GET['username'].'"');

This could be escaped as follows:

$result=mysql_query('SELECT * FROM users WHERE username="'.mysql_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 mysql_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

3. For more information
PHP Manual: SQL Injection