Prepared Statements: Why Escaping Is the Wrong Mental Model

SQL injection has been the same bug for twenty-five years and it is still on every vulnerability list, which says something about how the problem is usually taught. The framing is nearly always "escape dangerous input" — and that framing is why the bug survives. Escaping is a filter you can get wrong, forget, or apply in the wrong character set. Parameterization is not a filter. It removes the possibility.

The two mental models

ESCAPING
  Build one string containing code AND data,
  after trying to defang the data.

  "SELECT * FROM users WHERE email = '" . escape($e) . "'"

  The database receives one string and must
  work out which parts are instructions.
  Your escaping is the only thing standing
  between those two categories.

PARAMETERIZATION
  Send the query and the data SEPARATELY.

  "SELECT * FROM users WHERE email = ?"   +   [$e]

  The database parses the query first, with a
  placeholder where the value goes. The value
  is bound afterward as a value. There is no
  parsing step left for it to influence.

That is the whole idea. Once the statement is parsed, a parameter containing ' OR 1=1-- is just a string that no row will match. Not because anything escaped it — because by the time it arrives, the grammar of the query is already fixed.

Connecting properly

Most tutorials show a DSN and stop. Three settings matter and are usually missing:

<?php
$dsn = 'mysql:host=localhost;dbname=app;charset=utf8mb4';

$pdo = new PDO($dsn, $user, $pass, [
    // Errors throw instead of returning false silently.
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,

    // Return associative arrays, not duplicated
    // numeric + named columns.
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,

    // Send the query to the server for real parsing
    // instead of interpolating client-side.
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

charset=utf8mb4 belongs in the DSN, not in a later SET NAMES query. The client library needs to know the encoding to handle values correctly; telling the server after the fact leaves the client with a stale idea of the charset, which historically was the root of some genuinely subtle injection vectors.

ATTR_EMULATE_PREPARES => false is the difference between PDO building the final SQL string itself and PDO handing the query to MySQL to prepare. Emulation is not automatically insecure — PDO's quoting is competent — but real server-side preparation is a stronger guarantee and it makes type handling less surprising.

ERRMODE_EXCEPTION is security-relevant too. With the default, a failing query returns false and execution carries on with a variable nobody checked.

The normal case

$stmt = $pdo->prepare(
    'SELECT id, email FROM users WHERE email = ? AND status = ?'
);
$stmt->execute([$email, 'active']);
$user = $stmt->fetch();

Or with named placeholders, which read better once there are more than about three:

$stmt = $pdo->prepare(
    'UPDATE articles SET title = :title, updated_at = UTC_TIMESTAMP()
      WHERE id = :id'
);
$stmt->execute(['title' => $title, 'id' => $id]);

No escaping, no quoting, no addslashes. If you find yourself reaching for a quoting function, something has gone wrong in the design.

Three places a placeholder cannot help

Placeholders stand in for values. They cannot stand in for parts of the query's structure, and this is where injection still happens in code that otherwise uses PDO correctly.

1. Identifiers

// Does NOT work - and cannot be made to work
$pdo->prepare('SELECT * FROM ? WHERE id = ?');
$pdo->prepare('SELECT * FROM articles ORDER BY ? ?');

Table names, column names, and sort directions are grammar, not data. The only safe approach is a whitelist — map user input to values you wrote yourself:

$sortable = [
    'date'  => 'published_at',
    'title' => 'title',
];

// Unknown input falls back to a default. It never
// reaches the query.
$column = $sortable[$_GET['sort'] ?? ''] ?? 'published_at';
$dir    = ($_GET['dir'] ?? '') === 'asc' ? 'ASC' : 'DESC';

$sql = "SELECT id, title FROM articles
         WHERE status = ?
         ORDER BY $column $dir
         LIMIT 20";

The interpolated values can only ever be strings from the array above. That is what makes it safe — not validation of the input, but the fact that the input is used as a key and never as SQL.

2. IN lists

// Wrong: one placeholder cannot hold three values
$stmt = $pdo->prepare('SELECT * FROM articles WHERE id IN (?)');

// Right: build the placeholder list from the count
$ids = array_map('intval', $ids);
$in  = implode(',', array_fill(0, count($ids), '?'));

$stmt = $pdo->prepare(
    "SELECT id, title FROM articles WHERE id IN ($in)"
);
$stmt->execute($ids);

The count comes from your array; the values are still bound. Guard against an empty array, since IN () is a syntax error — return early rather than building it.

3. LIMIT with an integer

// With emulation off, bind the type explicitly
$stmt = $pdo->prepare('SELECT * FROM articles LIMIT :n');
$stmt->bindValue(':n', $perPage, PDO::PARAM_INT);
$stmt->execute();

Without PARAM_INT the value can be sent as a string, and LIMIT '20' is not valid SQL. Not a security problem — a confusing error at 6pm. Casting the page size to int before it gets anywhere near the query solves it in most codebases.

Second-order injection

The one that gets past code review. Input is safely parameterized on the way in, stored, and then interpolated into a query later — by which point it feels like trusted internal data.

// Insert: safe
$stmt = $pdo->prepare('INSERT INTO users (nickname) VALUES (?)');
$stmt->execute(["o'brien' OR 1=1--"]);

// Later, somewhere else entirely: not safe
$nick = $pdo->query('SELECT nickname FROM users WHERE id = 5')
             ->fetchColumn();
$pdo->query("SELECT * FROM logs WHERE actor = '$nick'");   // boom

The rule that avoids this: parameterize every query, always, regardless of where the value came from. "It is from our own database" is not a security property. Data does not become safe by being stored; it becomes safe by never being parsed as code.

Least privilege as a backstop

Assume, for a moment, that something does get through. What can it reach?

-- Application user: exactly what the app needs
CREATE USER 'app'@'localhost' IDENTIFIED BY '...';
GRANT SELECT, INSERT, UPDATE, DELETE
   ON app_db.* TO 'app'@'localhost';

-- Migrations get their own credentials, used by
-- deploys and never by the running application.
GRANT ALL PRIVILEGES ON app_db.* TO 'migrate'@'localhost';

An application account without DROP cannot drop a table, and one scoped to a single database cannot read another. It costs one GRANT statement and it converts a class of catastrophe into a smaller incident.

Do not leak the error

try {
    $stmt->execute($params);
} catch (PDOException $e) {
    // Details to the log, where you can read them.
    error_log('[db] ' . $e->getMessage());

    // Nothing useful to the visitor.
    http_response_code(500);
    exit('Something went wrong.');
}

A raw PDOException message can contain the query, the table and column names, and sometimes the values. Displaying it hands an attacker your schema and turns blind probing into targeted work. Keep display_errors off in production — which is a configuration question, covered in our piece on secrets and environment config.

Auditing an existing codebase

# string interpolation inside a query - the main smell
grep -rnE '(query|exec)\(\s*"[^"]*\$' src/

# concatenation into SQL
grep -rnE "(SELECT|INSERT|UPDATE|DELETE)[^;]*\.\s*\\\$" src/

# functions that suggest the escaping mental model
grep -rnE 'addslashes|mysql_real_escape_string|->quote\(' src/

Every hit is a place to read carefully. Some will be legitimate whitelisted identifiers like the sort example above; the rest are the bug.

The rules

1. Every value goes in as a bound parameter. No exceptions for "internal" data.
2. charset in the DSN, ERRMODE_EXCEPTION on, EMULATE_PREPARES off.
3. Identifiers and sort directions come from a whitelist, mapped by key.
4. Build IN placeholder lists from the array count.
5. The application's database user has no schema privileges.
6. Database errors go to the log, never to the response.