Placeholders

Placeholders

Prepared Statements and Placeholders

A prepared statement separates the SQL, which you write, from the values, which arrive later. Mark each value with a positional ? and pass a list to execute(), or with a named :email and pass an array keyed by name. Use one style per statement: mixing them throws HY093. The listing attacks a lookup built by concatenation, the pattern Subsection 3.16.7 dissected in SQL, with an honest address, the classic ' OR '1'='1, and a UNION that asks who the query runs as:

A concatenated query under attack, and the prepared versionPHP

<?php
$pdo = require 'db.php';
$sql = 'SELECT id, name FROM customers WHERE email = ';
$safe = $pdo->prepare($sql . ':email');              // the value travels separately
$inputs = ['ana@example.com', "x' OR '1'='1", "x' UNION SELECT CURRENT_USER(), @@version #"];
foreach ($inputs as $in) {
  $naive = $pdo->query($sql . "'" . $in . "'")->fetchAll(PDO::FETCH_NUM);   // VULNERABLE
  $safe->execute(['email' => $in]);
  printf("%s\n  naive: %d row(s), first %s | safe: %d row(s)\n",
         $in, count($naive), implode(' / ', $naive[0]), count($safe->fetchAll()));
}

Output

ana@example.com
  naive: 1 row(s), first 1 / Ana Souza | safe: 1 row(s)
x' OR '1'='1
  naive: 8 row(s), first 1 / Ana Souza | safe: 0 row(s)
x' UNION SELECT CURRENT_USER(), @@version #
  naive: 1 row(s), first shop_app@localhost / 9.7.2 | safe: 0 row(s)

The concatenated query returned every customer for the second input and, for the third, the database account and exact MySQL 524 version, the first two things an attacker wants. The prepared version looked for an address that literally is x' OR '1'='1 and found none, because the value never became SQL. That defense does not depend on escaping or on knowing which characters are dangerous. Subsection 4.17.2 builds the wider threat model on it.

MENU