What is SQL Injection

SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.

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.

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

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.

// Example #1 (using old mysql library)
$q=$_GET["q"];
$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 http://www.w3schools.com/PHP/php_ajax_database.asp )

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']);
$hStmt->execute();

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.