Preventing SQL injection with prepared statements

Submitted by olaf on 2016-10-15

When talking about web security in general, and SQL injection in particular, a common advice is to use prepared statements. For example

$dbh = new PDO($dsn, $username, $password);
$sql = "update order_details set amount = " . $_POST['amount'] .
       " where order_id = " . $_POST[order_id];
$result = $dbh->exec($sql);

is an invitation to break into the system. But simply suggesting prepared statements might fail too, if not applied properly. I just saw the equivalent of (in Java)

$dbh = new PDO($dsn, $username, $password);
$sql = "update order_details set amount = " . $_POST['amount'] .
       " where order_id = " . $_POST[order_id];
$stmt = $dbh->prepare($sql);
$result = $stmt->execute();

which is equally fatal from a security point of view. To do this properly, you must use prepared statements and placeholders for any data coming from a client, e.g. using question marks

$dbh = new PDO($dsn, $username, $password);
$sql = "update order_details set amount = ? where order_id = ?";
$stmt = $dbh->prepare($sql);
$result = $stmt->execute(array($_POST['amount'], $_POST[order_id]));

or using the appropriate PDOStatement::bindParam or PDOStatement::bindValue.

Post a comment

All comments are held for moderation; Markdown and basic HTML formatting accepted. If you want to stay anonymous, leave name, e-mail and website empty.