How To Fix SQL Injection: PHP

How To Fix SQL Injection:

Prepared Statements

PHP coders should use the PDO module if possible as it supports prepared statements across various databases. MySQL users should in particular avoid the old "mysql" module which does not support prepared statements. As of PHP 5, mysqli is available and it supports prepared statements.

Secure Usage
$oDB=new PDO('... your connection details... ');
$hStmt=$oDB->prepare("select name, age from users where userid=:userid");

This code is not vulnerable to SQL injection because it correctly uses parameterized queries. By utilizing the PHP PDO module and binding variables to the prepared statements, SQL injection can easily be prevented.

Vulnerable Usage
// Example #1 (using old mysql library)
$con = mysql_connect('localhost', 'peter', 'abc123');
mysql_select_db("ajax_demo", $con);
$sql="SELECT * FROM user WHERE id = '".$q."'";
$result = mysql_query($sql);

(code copied from )

This code is vulnerable to SQL injection. It uses the old mysql library, which does not support prepared statements. However, the vulnerability could still be avoided by either properly escaping or validating the user input.

// Example #2 (incorrectly preparing a statement with PDO)
$oDB=new PDO('... your connection details...');
$hStmt=$oDB->prepare("select name, age from users where userid=".$_GET['userid']);

The second vulnerable example looks just like the secure one above. But instead of properly binding the user data, it assembles dynamic SQL and prepared it after adding user data.


Johannes Ullrich, Ph.D.