PHP / Intermediate: objects, persistence, and tests / Lesson 10

PDO, prepared SQL, and transactions

Bind data separately from SQL structure and verify durable invariants. All database demonstrations create their own in-memory SQLite database and never read configured application connections.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Bind values and allowlist structural SQL choices.
Use explicit error and fetch contracts.
Verify rollback and distinguish engine-specific behavior.

PDO is an interface with driver-specific behavior

PDO provides a common API for database access, but drivers and engines still differ in SQL syntax, transaction rules, type handling, and prepared-statement behavior. Check the required extension and choose explicit error and fetch modes. Keep credentials and application DSNs out of examples and public error messages. sqlite::memory: creates an isolated database for the connection used here; it is not a test of MySQL or a production schema. A passing in-memory example establishes the illustrated PDO interaction and local invariant under that driver, not every concurrency or collation behavior of another engine.

Parameters are values, not SQL identifiers

Prepare a statement with placeholders and pass values through execute or explicit binding. This separates data from SQL parsing for the supported driver path. Placeholders cannot generally stand for a table name, column name, sort direction, or an arbitrary SQL fragment. Choose those structural elements from an application-owned allowlist instead. Do not manually quote an already bound value, and do not assume all drivers accept repeated named placeholders in the same way. The example uses a single named value and an identifier chosen entirely by the program, with a quote inside the synthetic title preserved as ordinary data.

php Bound text in a private in-memory database

<?php
declare(strict_types=1);
$db = new PDO("sqlite::memory:", null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$db->exec("CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL)");
$insert = $db->prepare("INSERT INTO courses (title) VALUES (:title)");
$insert->execute(["title" => "Ada's PHP"]);
$read = $db->prepare("SELECT title FROM courses WHERE id = :id");
$read->execute(["id" => 1]);
echo $read->fetchColumn(), PHP_EOL;

Expected output

Ada's PHP

The quote remains part of the stored title. No SQL is constructed from that title, and no external database is contacted.

A transaction protects a group of related writes

When an operation changes several records, define which outcomes must commit together. Begin a transaction, perform the related writes, commit on success, and roll back on failure if the transaction remains active. A catch should not assume every operation began a transaction or that every engine treats DDL transactionally. Use database constraints for durable rules such as uniqueness and foreign keys where appropriate. A friendly application check can still race with another writer. Test the final state after a controlled failure point, not just the fact that rollback was called.

Inject failure after work has begun

The second demonstration seeds balance ten, changes it within a transaction, then throws a controlled failure before commit. After rollback, the balance is still ten. Throwing before the update would not establish rollback of partial work. The small case has one connection and no contention; a real reservation invariant needs targeted tests on the intended engine and isolation level. Keep transaction ownership clear so nested helpers do not commit work their caller expected to control. Avoid performing irreversible external actions inside a transaction and assuming a database rollback can undo them.

php Rollback verified through final state

<?php
declare(strict_types=1);
$db = new PDO("sqlite::memory:", null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$db->exec("CREATE TABLE balance (amount INTEGER NOT NULL)");
$db->exec("INSERT INTO balance VALUES (10)");
try {
    $db->beginTransaction();
    $db->exec("UPDATE balance SET amount = 3");
    throw new RuntimeException("controlled failure");
} catch (RuntimeException $error) {
    if ($db->inTransaction()) { $db->rollBack(); }
}
echo $db->query("SELECT amount FROM balance")->fetchColumn(), PHP_EOL;

Expected output

The update occurred before failure and was not committed. The evidence is limited to this isolated SQLite transaction.

Result shape, encoding, and query cost remain explicit

Choose a fetch representation and distinguish no row from a valid row containing null. Validate values crossing from database types into domain types rather than assuming every driver returns identical scalar representations. Parameter binding prevents one class of injection mistakes but does not enforce authorization, output escaping, or efficient query plans. Limit result sizes and avoid fetching an unbounded dataset just to show one page. For text corruption, trace connection encoding and stored bytes on the intended engine; do not repeatedly convert strings until they look right. Record engine-specific assumptions alongside the SQL and expected outcomes.

Common errors and fixes

A placeholder is used for an ORDER BY column

Why it happens: Bound values are confused with SQL structure.
How to fix it: Select a column from a fixed allowlist and bind only values.
Prevent it next time: Review dynamic query structure separately from parameter data.

Rollback is claimed without checking state

Why it happens: The test observes a method call instead of persisted effects.
How to fix it: Fail after a write and inspect the final invariant.
Prevent it next time: Record the injection point and transaction owner.

SQLite success is claimed for another engine

Why it happens: Driver and isolation differences are ignored.
How to fix it: Add controlled target-engine checks for relevant risks.
Prevent it next time: Label engine-specific evidence and limitations.

Check your understanding

1. A user selects sort order. Can you bind a column name as :column?

Show worked solution

Not as a general identifier substitution. Map the user’s choice to an application-owned column/direction allowlist, then build that bounded structure and bind actual values separately. Unknown choices should be rejected or use a documented safe default, not inserted as raw SQL.

2. What proves a partial update was rolled back?