PDO: prepared statements and transactions

Configure PDO the way production does, structurally eliminate SQL injection and make your writes reliable with transactions.

10 min · 3 questions

PDO is the unified database access interface: MySQL, PostgreSQL or SQLite are all driven with the same API. Three settings separate a naive connection from a production one: ERRMODE_EXCEPTION so that every SQL error raises an exception instead of failing silently, FETCH_ASSOC by default for clean results, and EMULATE_PREPARES set to false for prepared statements actually executed by the server. Add charset=utf8mb4 directly in the DSN - it's the only reliable place to declare it.

<?php

declare(strict_types=1);

$dsn = 'mysql:host=localhost;dbname=shop'
    . ';charset=utf8mb4';

$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];

$pdo = new PDO($dsn, 'app_user', 'secret', $options);

The prepared statement is your only structural defense against SQL injection. The principle: the structure of the query, with its :email or ? placeholders, is sent to the server first; the data follows separately and is never interpreted as SQL. Concatenating a variable into a query, even an "escaped" one, remains a professional mistake. Named placeholders make the code readable; positional ones are enough for short queries.

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(
    PDO::ATTR_ERRMODE,
    PDO::ERRMODE_EXCEPTION,
);

$pdo->exec(
    'CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        email TEXT NOT NULL
    )'
);

$insert = $pdo->prepare(
    'INSERT INTO users (email) VALUES (:email)'
);
$insert->execute(['email' => 'lea@example.com']);

$query = $pdo->prepare(
    'SELECT id, email FROM users
     WHERE email = :email'
);
$query->execute(['email' => 'lea@example.com']);

var_dump($query->fetch(PDO::FETCH_ASSOC));

A transaction guarantees atomicity: either all the writes succeed, or none of them do. The canonical pattern: beginTransaction(), the work inside a try, commit() at the end of the block, rollBack() in the catch - then you rethrow the exception, because hiding the failure would be worse than the failure itself. Keep transactions short: every lock held during a network call or a long process degrades concurrency across the whole application.

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(
    PDO::ATTR_ERRMODE,
    PDO::ERRMODE_EXCEPTION,
);

$pdo->exec(
    'CREATE TABLE accounts (
        id INTEGER PRIMARY KEY,
        balance INTEGER NOT NULL
    )'
);
$pdo->exec(
    'INSERT INTO accounts (balance)
     VALUES (100), (20)'
);

$pdo->beginTransaction();

try {
    $debit = $pdo->prepare(
        'UPDATE accounts
         SET balance = balance - :n
         WHERE id = :id'
    );
    $debit->execute(['n' => 50, 'id' => 1]);

    $credit = $pdo->prepare(
        'UPDATE accounts
         SET balance = balance + :n
         WHERE id = :id'
    );
    $credit->execute(['n' => 50, 'id' => 2]);

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

echo 'Transfer done';

Knowledge check

Make sure you remember the key points of this lesson.

1. Why does a prepared statement block SQL injection?
   • It automatically escapes apostrophes in strings
   • The SQL structure and the data travel separately: the data is never interpreted as SQL code
   • It encrypts the query between PHP and the server
2. What can you pass through a :param placeholder in a prepared statement?
   • A value: string, number or null
   • A table or column name
   • A complete ORDER BY clause
3. In the canonical transaction pattern, what do you do in the catch block?
   • commit() to save what succeeded
   • rollBack(), then rethrow the exception
   • Nothing: the transaction expires on its own