中文

PDO and Prepared Statements

AI-assistedDrafted with AI assistance and reviewed before publishing; examples are version-pinned. Verify against official docs when in doubt.

PHP 8.4

Building SQL by pasting user input into a string is still the most common security hole in PHP code. PDO prepared statements send the statement and the data separately, which closes the injection path structurally rather than by careful escaping.

Before you start

Enable the driver you need: pdo_mysql for MySQL, pdo_sqlite for SQLite.

php -m | findstr pdo

The examples use SQLite (a file is the database) so you can run them without a server. Switching to MySQL only changes the DSN.

Connecting

<?php
declare(strict_types=1);

$dsn = 'sqlite:' . __DIR__ . '/app.db';

$pdo = new PDO($dsn, null, null, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // throw instead of returning false
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // rows as arrays
    PDO::ATTR_EMULATE_PREPARES   => false,                  // use real server-side prepares
]);

The MySQL form of the DSN:

$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=app;charset=utf8mb4';
$pdo = new PDO($dsn, $user, $password, $options);

Do not skip any of the three options:

  • Exception mode. The default is silent failure: execute() returns false and nothing else happens.
  • Default fetch mode. Otherwise every call repeats fetch(PDO::FETCH_ASSOC).
  • Disable emulated prepares. With emulation, the driver interpolates values into the SQL string itself, so quoting and type handling are the driver’s business — and edge cases behave differently between drivers.

Creating a table and inserting

$pdo->exec(<<<SQL
CREATE TABLE IF NOT EXISTS posts (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    title   TEXT    NOT NULL,
    body    TEXT    NOT NULL,
    status  TEXT    NOT NULL DEFAULT 'draft',
    created TEXT    NOT NULL
)
SQL);

$stmt = $pdo->prepare(
    'INSERT INTO posts (title, body, status, created) VALUES (:title, :body, :status, :created)'
);

$stmt->execute([
    ':title'   => 'Getting started with PDO',
    ':body'    => 'Body text',
    ':status'  => 'published',
    ':created' => date('c'),
]);

$id = (int) $pdo->lastInsertId();

Named parameters read better, but with real (non-emulated) prepares the same name cannot appear twice in one statement — see the mistakes section.

Querying

// many rows
$stmt = $pdo->prepare('SELECT id, title, status FROM posts WHERE status = :status ORDER BY id DESC');
$stmt->execute([':status' => 'published']);
$posts = $stmt->fetchAll();

// one row, or false
$stmt = $pdo->prepare('SELECT * FROM posts WHERE id = :id');
$stmt->execute([':id' => $id]);
$post = $stmt->fetch();

if ($post === false) {
    http_response_code(404);
    exit('Not found');
}

// a single value
$stmt = $pdo->prepare('SELECT COUNT(*) FROM posts WHERE status = :status');
$stmt->execute([':status' => 'published']);
$count = (int) $stmt->fetchColumn();

fetch() returns false when there is no row — not null. Compare with === false rather than a falsy check, so an empty row array can never be confused with “no row”.

Positional parameters

$stmt = $pdo->prepare('SELECT * FROM posts WHERE status = ? AND id > ?');
$stmt->execute(['published', 100]);

Values bind in order, which is easy to get wrong once there are five conditions. Use one style per query — mixing ? and :name is an error, not a convenience.

Binding types

An array passed to execute() binds everything as a string. When the driver needs a real integer, bind explicitly:

$stmt = $pdo->prepare('SELECT * FROM posts WHERE id = :id AND created > :since');
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->bindValue(':since', $since, PDO::PARAM_STR);
$stmt->execute();

LIMIT and OFFSET must be bound as integers on several drivers, otherwise you get a syntax error. This is the classic pagination bug:

$stmt = $pdo->prepare('SELECT * FROM posts ORDER BY id LIMIT :limit OFFSET :offset');
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();

Transactions

try {
    $pdo->beginTransaction();

    $pdo->prepare('UPDATE accounts SET balance = balance - :amt WHERE id = :id')
        ->execute([':amt' => 100, ':id' => 1]);
    $pdo->prepare('UPDATE accounts SET balance = balance + :amt WHERE id = :id')
        ->execute([':amt' => 100, ':id' => 2]);

    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

In real code, check that the debit touched exactly one row. Otherwise an insufficient balance still commits:

$stmt = $pdo->prepare('UPDATE accounts SET balance = balance - :amt WHERE id = :id AND balance >= :amt');
$stmt->execute([':amt' => 100, ':id' => 1]);
if ($stmt->rowCount() !== 1) {
    throw new RuntimeException('Insufficient funds');
}

rowCount() on UPDATE reports affected rows. MySQL by default reports rows that actually changed, so an update that sets the same value reports 0 — use PDO::MYSQL_ATTR_FOUND_ROWS if you need “matched” semantics.

Where placeholders stop working

A placeholder stands in for a value only — never a table name, column name or keyword:

// wrong: this sorts by the literal string "created_at"
$sql = 'SELECT * FROM posts ORDER BY :column';

Sort columns need a whitelist:

$sortable = ['id', 'created', 'title'];
$sort = in_array($_GET['sort'] ?? 'id', $sortable, true) ? $_GET['sort'] : 'id';
$dir  = ($_GET['dir'] ?? 'asc') === 'desc' ? 'DESC' : 'ASC';

$sql = "SELECT * FROM posts ORDER BY {$sort} {$dir} LIMIT 20";
$pdo->query($sql)->fetchAll();

LIKE needs the wildcards added by you, plus escaping of % and _ in the user’s input:

$keyword = str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $q);
$stmt = $pdo->prepare("SELECT * FROM posts WHERE title LIKE :kw ESCAPE '\\'");
$stmt->execute([':kw' => "%{$keyword}%"]);

IN (...) needs one placeholder per element:

$ids = [1, 2, 3];
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare("SELECT * FROM posts WHERE id IN ($placeholders)");
$stmt->execute($ids);

An empty array makes IN () a syntax error — short-circuit to an empty result before building the SQL.

Common mistakes

1. Interpolating SQL

// dangerous
$pdo->query("SELECT * FROM users WHERE name = '$name'");

Placeholders and bindValue are the answer; quote() is a last resort with its own edge cases.

2. Reusing a named parameter

With native prepares this raises HY093:

'SELECT * FROM posts WHERE title = :kw OR body = :kw'

Use two names (:kw_title, :kw_body) or positional parameters.

3. Mojibake from MySQL

The DSN must carry charset=utf8mb4, and tables must be created with utf8mb4. SET NAMES alone is not reliable.

4. Opening a connection inside every function

A request can end up with a dozen connections. Create $pdo once at the entry point and pass it down.

5. Hard-coding credentials

Read them from environment variables, and give the production account only the privileges it needs.

6. Leaking SQL through exception messages

PDOException::getMessage() includes fragments of the statement. Return a generic error to the client and log the detail.

7. Long transactions

Never make HTTP calls, send email or wait for input inside a transaction. Keep the window between beginTransaction and commit as short as possible.

Summary

  • ERRMODE_EXCEPTION plus real prepares is the baseline configuration.
  • Placeholders bind values only; identifiers, ORDER BY and LIMIT need whitelists or integer binding.
  • Named parameters cannot repeat under native prepares; positional ones must line up exactly.
  • Transactions need a row-count check, not just “no exception was thrown”.
  • Sorting, LIKE and IN are the three places you must build SQL yourself.

Next: modelling with classes, enums and types instead of arrays and magic strings.

Tags