# PHP Programming/SQL Injection Attacks
## The Problem

Consider the following SQL query in PHP:
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.
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:
If $_POST['id'] contained 23 OR 1=1 then the resulting query would be:
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:
This could be escaped as follows:
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:
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.
The query method, also do the same as prepare/execute,
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:
Similarly, you could use the built-in PDO Class in PHP5.

## References

* ↑ http://pear.php.net/package/DB
* ↑ Official documentation for Mysqli extension, php.net.
* ↑ Prepared Statements in PHP and MySQLi, Matt Bango.
* ↑ http://php.net/manual/en/book.pdo.php

## 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