Preventing SQL Injection in PHP

Do you remember this great xkcd comic ?

Let’s break this down and see the problem. Consider the following form of a MySQL query in PHP :

mysql_query("INSERT INTO Students VALUES ('" + FNMName.Text + "', " + LName.Text + ")";

Substituting the above value of name for FNMName.Text and an imaginary surname for LName.Text results in not one, but two queries :

INSERT INTO Students VALUES ('Robert'); DROP TABLE Students;--', 'FooBar')

And there goes the table. This technique of attacking data-driven applications by injection malicious SQL code is known as SQL Injection. Well-written applications that employ proper checks can easily defend themselves against this method. Here are some ways in which apps written in PHP can check SQL Injection.

Prepared Statements and Parameterized Queries

Let’s discuss this method using the two common interfaces that PHP provides for database connections : PDO and MySQLi.

PDO

$stmt = $pdo->prepare('SELECT * FROM Students WHERE name = :name'); $stmt->execute(array('name' => $name));

foreach ($stmt as $row) {
// do something with $row

}

$stmt = $databaseCnn->prepare('SELECT * FROM Students WHERE name = ?'); $stmt->bind_param('s', $name);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {

// do something with $row

}

While using PDO, the database connection must be set up correctly so that PDO knows that real parameterized queries have to be used, and not emulated ones. Something like this:

$databaseCnn = new PDO('mysql:dbname=test;host=127.0.0.1;charset=utf8', 'uname', 'passwd');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

What happens with prepared statements is that the database server parses and compiles them before executing. The `?` and the `:name` parameters tell the server where the query should be filtered.

When the query is executed, the server combines the query with the search params. The method prevents Injection attacks by treating the query separately (as a query and not as a string) from the params which are treated like strings (or numbers, for optimization purposes).

Hence, passing a name above like `”Robert’); DROP TABLE Students;–“` will just result in one query instead of two which searches for a student with a rather weird name.

MySQLi (for MySQL)

SELECT * FROM Students WHERE name = “Robert’); DROP TABLE Students;–“;

An additional benefit of using parameterized queries is that when executing multiple queries of the same kind, minor speed gains can be attained because the server compiles and parses the query once only.

Escaping Literals

Escaping the special characters in user-entered parameters is another technique to prevent Injection attacks. In comparison to prepared statements, this method requires much fewer changes.

// setup connection

$unsafe_variable = $_POST["user-input"];
$safe_variable = mysql_real_escape_string($unsafe_variable);

mysql_query("Select * FROM Students (name) VALUES ('" . $safe_variable . "')");

The `mysql_real_escape_string` function call its MySQL equivalent function `mysql_real_escape_string` which prepends backslashes to certain special characters.

Note : `mysql_real_esape_string` is deprecated. Please refer the PHP documentation page for alternatives.

Possible attack

Consider :

$query = "SELECT * FROM Students WHERE id = " . $safe_variable; // safe variable as defined above

Since the value expected for id is an integer, it is not surrounded by quotes. But, Injection here is still possible because a parameter of the following kind will pass through the escape check :

`UNION SELECT password FROM users`

In such cases, it’ll be essential to conduct checks validating that the param contains only digits.

Whitelisting

Whitelisting deals with dynamic queries, i.e., queries whose structure changes with user input. Consider the following example in which the user generates a dynamic query by deciding how the results should be ordered :

$orders = array("name","price","qty"); //field names
$key = array_search($_GET['sort'],$orders)); // see if we have such a name; $orderby = $orders[$key]; // FALSE evals to 0
$query = "SELECT * FROM `table` ORDER BY $orderby";

The above two methods can also be used to protect identifiers. They can be escaped to prevent attacks or placeholders can be devised for identifiers in the same manner as placeholders were put in place for column values. We might call these identifier placeholders.

Hacks based on Input Types

Integer Input

This basically implies that the programmer makes sure that the user-entered input really is an integer. Here’s a neat way to do it :

$query = sprintf("SELECT 1,2,3 FROM table WHERE 4 = %u", $input); Other than Integer

Just hex the parameter to escape it. The relevant PHP function here is bin2hex() . However, all such params have to be prepended with 0x or the MySQL function UNHEX be used.

`SELECT * FROM Students WHERE name = ‘Robert’`

becomes

`SELECT * FROM Students WHERE name = 0x526f62657274 — UNHEX(‘526f62657274’)`

While UNHEX is robust and works like magic, 0x only works on certain data types and gives an error if an empty string is passed to it.

Last, but not the least, make sure before proceeding with any method that the user input hasn’t been mutated with `magic_quotes` or similar methods. The input will need to be sanitized using `stripslashes` etc.