PDO, Prepared Statements and Transactions

A checkout request writes an order row, decrements stock and inserts a payment record. Halfway through, one statement throws. The handler catches the exception, logs it, returns a 500, and the next request on that connection fails with There is already an active transaction. The first request did not corrupt the data, but it left a transaction open, and everything the pool hands that connection to afterwards inherits it.

Two things are being decided in that story. The first is how values get into SQL, which is where injection lives and where half the folklore about prepared statements is wrong. The second is the lifetime of a transaction, which is a boundary your code owns and the database cannot guess. PDO gives you one object for both, and its defaults are better than most people assume and worse than its documentation used to say.

Everything below ran on PHP 8.5.6 against MySQL 9.6.0 over TCP, with the orders table from the previous module's CSV but seeded with five rows.

ERRMODE_EXCEPTION is already the default, and that is not the reason to set it

The advice to pass PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION is usually justified with a claim that has been false since PHP 8.0: that PDO is silent unless you ask it not to be. It is not. Ask the connection what it is doing before you set anything.

<?php
declare(strict_types=1);

$pdo = new PDO('mysql:host=127.0.0.1;port=3306;dbname=m6lab;charset=utf8mb4', 'root', '');

printf("ATTR_ERRMODE           = %d\n", $pdo->getAttribute(PDO::ATTR_ERRMODE));
printf("ATTR_EMULATE_PREPARES  = %s\n", var_export($pdo->getAttribute(PDO::ATTR_EMULATE_PREPARES), true));
printf("ATTR_DEFAULT_FETCH_MODE= %d\n", $pdo->getAttribute(PDO::ATTR_DEFAULT_FETCH_MODE));
printf("ATTR_STRINGIFY_FETCHES = %s\n", var_export($pdo->getAttribute(PDO::ATTR_STRINGIFY_FETCHES), true));
printf("driver = %s, server = %s\n",
    $pdo->getAttribute(PDO::ATTR_DRIVER_NAME),
    $pdo->getAttribute(PDO::ATTR_SERVER_VERSION));

ATTR_ERRMODE           = 2
ATTR_EMULATE_PREPARES  = true
ATTR_DEFAULT_FETCH_MODE= 4
ATTR_STRINGIFY_FETCHES = false
driver = mysql, server = 9.6.0

2 is PDO::ERRMODE_EXCEPTION. The mode that throws is what a fresh PDO gives you, and the manual says so plainly: exception mode has been the default since PHP 8.0.0. So why set it? Because an attribute can be changed at runtime, by a framework bootstrap, by a connection that came out of a pool, or by whoever edited the config last. Setting it in the options array records the contract at the point where the connection is created and makes it visible to the next reader. That is a documentation argument, not a behavioural one, and it is worth being honest about which you are making.

The two options that do change behaviour on this stack are PDO::ATTR_EMULATE_PREPARES, which is still true by default, and the charset parameter in the DSN, which decides what the server thinks the bytes on the wire mean. Leave charset out and you are relying on the server's default character_set_client; on MySQL 9.6 here that happened to be utf8mb4, and on another server it will not be. Connecting a latin1 client to a utf8mb4 column double-encodes every accented character, which is the kind of bug that survives testing because the test data is ASCII.

A prepared statement separates the query from the values, and that is all it does

Concatenating a value into SQL fails because the value is parsed as SQL. It is worth running that failure rather than describing it.

<?php
declare(strict_types=1);

$pdo = new PDO($dsn, 'root', '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

$attack = "' OR '1'='1";

$interpolated = "SELECT reference, customer FROM orders WHERE reference = '{$attack}'";
printf("interpolated: %d rows\n", count($pdo->query($interpolated)->fetchAll()));

$statement = $pdo->prepare('SELECT reference, customer FROM orders WHERE reference = ?');
$statement->execute([$attack]);
printf("prepared:     %d rows\n", count($statement->fetchAll()));

interpolated: 5 rows
prepared:     0 rows

The interpolated query returned the whole table, because '1'='1' is a true condition and the parser had no way to know that the text came from a user. The prepared version sent the same characters as a value and found no order with that reference, which is correct.

Now the part that gets oversold. The placeholder is a value slot. It is not a slot for a table name, a column name, an ORDER BY direction, or a LIMIT count that the driver decides to quote as a string. Ask for the ordering column to be bound and you get no error at all, because the statement is valid SQL:

<?php
$statement = $pdo->prepare('SELECT reference FROM orders ORDER BY ? LIMIT 2');
$statement->execute(['total_cents']);
echo implode(', ', array_column($statement->fetchAll(), 'reference')), "\n";

ORD-1001, ORD-1002

Those are the first two rows by primary key. The bound string was a constant expression in the order clause, so every row compared equal and the engine kept insertion order. The correct move for an identifier is an allow-list: map a request parameter to one of a fixed set of column names in PHP, then interpolate the name you chose. You are still building a string, but the string no longer contains anything the caller supplied.

ATTR_EMULATE_PREPARES decides who does the substitution

With emulation on, PDO builds the final SQL string locally and quotes each value, then sends one ordinary query. With it off, the placeholder goes to the server as a real prepared statement and the values travel in a separate protocol message. The difference is invisible until it is not: the emulated path quotes every bound value as a string literal unless you bind a type, and the server never sees a placeholder at all.

<?php
foreach ([true => 'emulated', false => 'native'] as $flag => $label) {
    $pdo = new PDO($dsn, 'root', '', [
        PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => (bool) $flag,
    ]);

    $statement = $pdo->prepare('SELECT reference FROM orders ORDER BY id LIMIT ?');
    try {
        $statement->execute([2]);
        printf("%-9s execute([2])             -> %s\n", $label,
            implode(', ', array_column($statement->fetchAll(), 'reference')));
    } catch (PDOException $e) {
        printf("%-9s execute([2])             -> %s\n", $label, $e->getMessage());
    }

    $statement = $pdo->prepare('SELECT reference FROM orders ORDER BY id LIMIT ?');
    $statement->bindValue(1, 2, PDO::PARAM_INT);
    $statement->execute();
    printf("%-9s bindValue(1, 2, PARAM_INT) -> %s\n", $label,
        implode(', ', array_column($statement->fetchAll(), 'reference')));
}

emulated  execute([2])             -> SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'' at line 1
emulated  bindValue(1, 2, PARAM_INT) -> ORD-1001, ORD-1002
native    execute([2])             -> ORD-1001, ORD-1002
native    bindValue(1, 2, PARAM_INT) -> ORD-1001, ORD-1002

Read the first line carefully: near ''2''. The emulated driver substituted the string '2', and MySQL does not accept a quoted string in LIMIT. The same placeholder works once you tell PDO the value is an integer.

Named placeholders behave differently across the two modes as well. Binding the same name twice in one statement is rejected by native prepares and accepted by emulation:

<?php
$statement = $pdo->prepare('SELECT :x AS a, :x AS b');
$statement->execute(['x' => 7]);

emulated  -> {"a":"7","b":"7"}
native    -> SQLSTATE[HY093]: Invalid parameter number

So a query that repeats a named placeholder works in development with emulation on and throws in production with it off. That is the strongest practical argument for turning emulation off in development too: you find out on your machine, where the error message is right in front of you.

beginTransaction is a claim about the code between commit and rollBack

A transaction is the database's promise that a group of writes becomes visible together or not at all. PDO exposes it as three methods and one question you can ask at any point: inTransaction(). Here is the shape that survives failure, run against a table with a unique index on reference.

<?php
declare(strict_types=1);

$pdo->beginTransaction();
echo "beginTransaction() -> inTransaction() = ", var_export($pdo->inTransaction(), true), "\n";

$insert = $pdo->prepare(
    'INSERT INTO orders (reference, customer, total_cents, currency, note, placed_at)
     VALUES (:reference, :customer, :total_cents, :currency, NULL, NOW())'
);

try {
    $insert->execute(['reference' => 'P' . bin2hex(random_bytes(4)),
                      'customer' => 'Rollback Probe', 'total_cents' => 4999, 'currency' => 'EUR']);
    printf("first insert ok, probe rows inside the transaction = %d\n", $probeRows());

    // This reference already exists, so the write fails the way a real conflict does.
    $insert->execute(['reference' => 'ORD-1001',
                      'customer' => 'Rollback Probe', 'total_cents' => 1234, 'currency' => 'EUR']);
    $pdo->commit();
} catch (PDOException $e) {
    printf("caught %s\n  message = %s\n", $e::class, $e->getMessage());
    printf("inTransaction() while unwinding = %s\n", var_export($pdo->inTransaction(), true));
    $pdo->rollBack();
    printf("rollBack() called, inTransaction() = %s\n", var_export($pdo->inTransaction(), true));
}

printf("probe rows after the rollback = %d\n", $probeRows());

probe rows before: 0
beginTransaction() -> inTransaction() = true
first insert ok, probe rows inside the transaction = 1
caught PDOException
  message = SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'ORD-1001' for key 'orders.uniq_reference'
  errorInfo = ["23000",1062,"Duplicate entry 'ORD-1001' for key 'orders.uniq_reference'"]
inTransaction() while unwinding = true
rollBack() called, inTransaction() = false
probe rows after the rollback = 0

Two details in that output matter more than the happy path. The first insert was visible on this connection while the transaction was open, and it is gone afterwards: uncommitted work is real to you and invisible to everyone else. And inTransaction() was still true inside the catch. An exception does not close a transaction, it only interrupts your function. If the handler returns without calling rollBack(), the connection keeps the transaction open and the next user of that connection inherits the mess.

That is the trap in the opening story, and the fix is to make the rollback unconditional on the failure path rather than remembering it at each catch site.

<?php
$pdo->beginTransaction();

try {
    $orders->insert($customer, $cents, $currency, $note);
    $pdo->commit();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $e;
}

The inTransaction() guard matters because a failed commit() may or may not have left the transaction open, depending on the driver and the failure, and calling rollBack() with nothing open throws There is no active transaction. That second exception would replace the one you were trying to report.

Nesting is not supported either. A second beginTransaction() while one is open throws There is already an active transaction, which is the database telling you that savepoints are a different feature. If two layers both want a transaction, one of them has to own it.

DDL ends the transaction for you, and MySQL will not tell you

A schema change inside a transaction does not join it. MySQL commits the open transaction first, so the writes you thought were pending are permanent and the rollBack() afterwards throws because there is nothing to roll back.

<?php
$pdo->beginTransaction();
$pdo->exec("INSERT INTO accounts VALUES (5, 'eve', 250)");
printf("inTransaction() before the DDL: %s\n", var_export($pdo->inTransaction(), true));

$pdo->exec('CREATE TABLE IF NOT EXISTS scratch (id INT)');
printf("inTransaction() after CREATE TABLE: %s\n", var_export($pdo->inTransaction(), true));

try {
    $pdo->rollBack();
} catch (PDOException $e) {
    printf("rollBack() after the DDL: %s\n", $e->getMessage());
}

printf("row 5 survived the rollback: %s\n", var_export(
    $pdo->query('SELECT owner FROM accounts WHERE id = 5')->fetchColumn(), true));

inTransaction() before the DDL: true
inTransaction() after CREATE TABLE: false
rollBack() after the DDL: There is no active transaction
row 5 survived the rollback: 'eve'

PDO reports this honestly through inTransaction(), which is why the guard above is not only defensive politeness. Keep migrations out of request handlers, and if a request must touch the schema, treat every write before it as already committed.

<?php
declare(strict_types=1);

final class InsufficientFunds extends RuntimeException
{
}

function transfer(PDO $pdo, int $from, int $to, int $cents): void
{
    if ($cents < 1) {
        throw new InvalidArgumentException('transfer() needs a positive amount');
    }

    $pdo->beginTransaction();

    try {
        $debit = $pdo->prepare('UPDATE accounts SET balance_cents = balance_cents - :cents WHERE id = :id');
        $debit->execute(['cents' => $cents, 'id' => $from]);

        if ($debit->rowCount() !== 1) {
            throw new InsufficientFunds("account {$from} does not exist");
        }

        $balance = (int) $pdo->query("SELECT balance_cents FROM accounts WHERE id = {$from}")->fetchColumn();

        if ($balance < 0) {
            throw new InsufficientFunds("account {$from} has no room for {$cents} cents");
        }

        $credit = $pdo->prepare('UPDATE accounts SET balance_cents = balance_cents + :cents WHERE id = :id');
        $credit->execute(['cents' => $cents, 'id' => $to]);

        if ($credit->rowCount() !== 1) {
            throw new RuntimeException("account {$to} does not exist");
        }

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

        throw $e;
    }
}