Prepared Statements and Binding

The login query is one line of PHP, and it is the whole vulnerability:

$sql = "SELECT * FROM users WHERE email = '$email' AND password = '$hash'";
$pdo->query($sql);

Send admin@shop.com' -- as the email and the comment swallows the password check. Send ' OR '1'='1 and the WHERE clause becomes email = '' OR '1'='1', which is true for every row — the first of which is usually an administrator. The value was read as SQL because it arrived in the same string as the SQL. Escaping by hand (addslashes, string concatenation with quotes) makes the boundary a convention your code has to keep forever, and one missed input breaks it.

A prepared statement moves the boundary from convention to protocol. You submit the query shape first, the server parses it, and the values travel afterward as data:

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND active = :active');
$stmt->execute([
    ':email'  => $input,          // "x' OR '1'='1" — just a value, never parsed
    ':active' => true,
]);
$user = $stmt->fetch();

The SQL text is fixed at prepare() time. Nothing you pass to execute() can add a clause, close a quote, or comment anything out — by the time the value exists, the structure has already been decided. That is the entire defense, and it holds for inputs you have never seen before.

PDO gives you two placeholder spellings:

// named: keys in the execute array, readable in the SQL
$stmt = $pdo->prepare('SELECT id, email FROM users WHERE id = :id');
$stmt->execute([':id' => 42]);

// positional: bound by 1-based index, order-sensitive
$stmt = $pdo->prepare('SELECT id, email FROM users WHERE id = ?');
$stmt->execute([42]);

Pick one style per statement — mixing named and positional placeholders in a single statement is rejected with SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters.

How the two halves travel depends on PDO::ATTR_EMULATE_PREPARES:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

With emulation off, the server parses the structure once and values are delivered out-of-band; a quoting bug in the client could not alter the statement even if one existed. With emulation on — PDO's default for pdo_mysql — PDO quotes each value and sends finished query text, which saves a round trip and makes LIMIT :offset, :count work, but your safety now rests on that quoting layer. Interviewers ask about this setting precisely because the default is the less safe of the two, and because "prepared statements" means something different depending on it.

Two failures are worth naming while the shape is fresh: passing a key that is not in the statement (a :userId versus a written :userid — placeholder names are case-sensitive) fails with SQLSTATE[HY093]: Invalid parameter number: parameter was not defined, and binding fewer parameters than the statement contains fails the same way. HY093 is always a shape problem, never a data problem.

Prepared statements are the fix for values. The next chapter is about the values themselves — what type a parameter carries, and how one prepared statement runs a thousand times.

execute([':id' => 42]) is the convenient shorthand, and it hides a decision: that array is bound as strings. For most queries MySQL and PostgreSQL coerce the text and nobody notices. The noticing starts when the type is the point.

$stmt = $pdo->prepare('UPDATE accounts SET balance = balance - :amount WHERE id = :id');
$stmt->bindValue(':amount', $amount, PDO::PARAM_INT);   // a number, sent as a number
$stmt->bindValue(':id', $accountId, PDO::PARAM_INT);
$stmt->execute();

Binding with an explicit type makes the parameter's meaning part of the message rather than something the server guesses from the text. PDO's type constants:

The PARAM_NULL row is the one that catches people: SQL NULL is not an empty string, and a column being cleared must be bound as null so the server receives an actual NULL rather than ''. Passing null in the execute() array is convenient, but bindValue(':deleted_at', null, PDO::PARAM_NULL) states the intent where a reviewer can see it.

Once bound, a statement is reusable — which is what makes bulk work fast:

$insert = $pdo->prepare('INSERT INTO order_items (order_id, sku, qty) VALUES (:oid, :sku, :qty)');
$insert->bindValue(':oid', $orderId, PDO::PARAM_INT);

foreach ($lines as $line) {
    $insert->bindValue(':sku', $line->sku);
    $insert->bindValue(':qty', $line->qty, PDO::PARAM_INT);
    $insert->execute();          // new values, same parsed statement
}

Each execute() sends the already-parsed statement with the current values; no re-parse, and no client-side string building for a thousand rows. Values you bound stay bound until you overwrite them, so binding the invariant (:oid) once outside the loop and the per-row values inside it is the pattern. On drivers that buffer results, finish fetching (or call closeCursor()) before the next execute() if the statement also returns rows.

Binding does have a grammar limit, and it is the most common misuse in real code:

// NOT valid: a placeholder cannot stand in for an identifier
$stmt = $pdo->prepare('SELECT * FROM products ORDER BY :sort');   // sorts by the literal text ':sort'

// valid: placeholders hold values, identifiers come from your own vocabulary
$columns = ['price' => 'price', 'newest' => 'created_at', 'name' => 'name'];
$sort = $columns[$_GET['sort'] ?? 'name'] ?? 'name';             // whitelist, not pass-through
$stmt = $pdo->prepare("SELECT * FROM products ORDER BY $sort DESC");

If that surprises you: ORDER BY 'price' orders by a constant string, so the query "works" and returns rows in a nonsense order — a silent wrong-answer bug rather than an error. Table names, column names, and sort direction are structure, and structure is chosen by your code from an allow-list, never supplied by the request.

One more shape problem appears the moment you filter on a list: IN (...) needs one placeholder per element, and the count has to match the array you pass.

$placeholders = implode(', ', array_map(fn($i) => ":p$i", range(0, count($ids) - 1)));
$stmt = $pdo->prepare("SELECT * FROM users WHERE id IN ($placeholders)");
$stmt->execute(array_combine(
    array_map(fn($i) => ":p$i", range(0, count($ids) - 1)),
    $ids
));

Generate the placeholders, generate the matching keys, keep the two counts equal — and a mismatch announces itself as HY093, the parameter-shape error from the previous chapter.

Even a perfectly bound statement cannot protect everything you pass it, and knowing exactly where the protection stops is the difference between "we use prepared statements" and "we do not have SQL injection."

Prepared statements defend values. The failures that survive them share one cause: something the server treats as syntax was allowed to travel as data — or something that is data behaves like syntax anyway.

Identifiers are not values. The previous chapter showed ORDER BY :sort silently sorting by a literal string; the same applies to table names, column lists, and GROUP BY. There is no placeholder for them, so the defense is a vocabulary your code owns:

const SORTABLE = ['price_asc' => 'price ASC', 'price_desc' => 'price DESC', 'newest' => 'created_at DESC'];
$order = SORTABLE[$_GET['sort'] ?? 'newest'] ?? SORTABLE['newest']; // key is looked up, never concatenated raw
$stmt = $pdo->prepare("SELECT * FROM products ORDER BY $order");

The request supplies a key; the SQL only ever contains values from the constant. That is the shape of every safe dynamic query — a search builder that takes $_GET['order'] straight into SQL has identical risk to string concatenation, and a code review should treat the two the same way.

LIKE patterns are data with syntax. A user searching for 100% means the percent sign literally, but in a pattern % matches everything, so :q bound to 100% returns rows the user did not ask for — a wrong-answer bug, not an injection:

$needle = str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $raw); // escape pattern metacharacters
$stmt = $pdo->prepare('SELECT * FROM products WHERE name LIKE :q');
$stmt->execute([':q' => "%$needle%"]);

Escaping for LIKE is data hygiene: you are neutralizing characters that would otherwise act as wildcards inside the value.

Emulation decides who does the quoting. With PDO::ATTR_EMULATE_PREPARES on — the pdo_mysql default — PDO interpolates quoted values into finished query text, so client-side escaping is the security layer. With it off, the server parses structure first and receives values afterward. Two consequences follow: turning it off is a genuine hardening step (and makes LIMIT bind cleanly), and one assumption the community held for years — that the connection charset does not matter because prepared statements bypass escaping — is only true with emulation off. If you keep emulation on, the DSN's charset=utf8mb4 from Connecting with PDO is load-bearing: a wrong charset is how escaping historically mis-parsed multi-byte input. Related and often confused: stacked statements. Multi-statement queries are disabled by default in pdo_mysql; if a codebase enables PDO::MYSQL_ATTR_MULTI_STATEMENTS, every other defense has to hold, because a statement separator that reaches the server executes a second query.

One statement, many executions, one contract. A prepared statement reuses its parsed form across execute() calls, so parameters from the previous run are replaced for keys you pass again — but a placeholder you forget to rebind keeps the old value, which is how a loop writes the same row a thousand times. Rebind every per-row value each iteration.

None of this changes the workflow — prepare once, execute with data, never concatenate a value into SQL:

$stmt = $pdo->prepare('SELECT id, name FROM users WHERE email = :email AND status = :status');
$stmt->execute([':email' => $input, ':status' => 'active']);   // structure fixed, data passed
$users = $stmt->fetchAll();

What changes is knowing the edges: identifiers need a whitelist, LIKE needs escaped wildcards, the connection charset and emulation mode are part of the security story, and HY093 errors are always about parameter shape. Carry that into Performing CRUD Queries, where fetch(), fetchAll(), and rowCount() decide what you do with the results these statements return.