PHP – Securing your Web Application : SQL Injection

The second most common web application vulnerability is SQL injection, an attack very similar to XSS. The difference is that SQL injection vulnerabilities exist wherever you use un-escaped data in an SQL query. (If these names were more consistent, XSS would probably be called HTML injection.)

The following example demonstrates an SQL injection vulnerability:

$hash = hash($_POST['password']);

$sql = "SELECT count(*) FROM users
		WHERE username = '{$_POST['username']}' AND password = '{$hash}'";

$result = mysql_query($sql);

The problem is that without escaping the username, its value can manipulate the format of the SQL query. Because this particular vulnerability is so common, many attackers try usernames such as the following when trying to log in to a target site:

chris' --

I often joke that this is my favorite username, because it allows access to the account with the username chris’ without me having to know that account’s password. After interpolation, the SQL query becomes:

SELECT count(*)
FROM users
WHERE username = 'chris' --'
AND password = '...'";

Because two consecutive hyphens (–) indicate the beginning of an SQL comment, this query is identical to:

SELECT count(*)
FROM users
WHERE username = 'chris'

If the code containing this snippet of code assumes a successful login when $result is nonzero, this SQL injection would allow an attacker to log in to any account without having to know or guess the password.

Safeguarding your applications against SQL injection is primarily accomplished by escaping output:

$mysql = array();
$hash = hash($_POST['password']);

$mysql['username'] = mysql_real_escape_string($clean['username']);
$sql = "SELECT count(*) FROM users
        WHERE username = '{$mysql['username']}' AND password = '{$hash}'";

$result = mysql_query($sql);

However, this only assures that the data you escape is interpreted as data. You still need to filter data, because characters like the percent sign (%) have a special meaning in SQL but they don’t need to be escaped.

The best protection against SQL injection is the use of bound parameters. The following example demonstrates the use of bound parameters with PHP’s PDO extension and an Oracle database:

$sql = $db->prepare("SELECT count(*) FROM users
		WHERE username = :username AND password = :hash");

$sql->bindParam(":username", $clean['username'], PDO::PARAM_STRING, 32);
$sql->bindParam(":hash", hash($_POST['password']), PDO::PARAM_STRING, 32);

Because bound parameters ensure that the data never enters a context where it can be considered anything but data (i.e., it’s never misinterpreted), no escaping of the username and password is necessary.
Here is the list of of Article in this Series:

Please share the article if you like let your friends learn PHP Security. Please comment any suggestion or queries.


Thanks Kevin Tatroe, Peter MacIntyre and Rasmus Lerdorf. Special Thanks to O’Relly.