Prepared Statements using PHP or Fix SQL Injection.

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:

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

Vulnerable Usage

<?PHP
    // 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.

<?PHP
    // 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.