Prepared Statements and Transactions

The moment a query includes a value from a user — a search term, an ID from the URL, a form field — you must use a prepared statement. It's the only reliable defence against SQL injection, one of the most damaging web vulnerabilities. This lesson also covers transactions, which make a group of changes succeed or fail together, and a simple repository class.

SQL injection

Here's what happens when user input is pasted into SQL:

PHP setup.php

<?php

$pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE, name TEXT, role TEXT)");
$pdo->exec("INSERT INTO users (email, name, role) VALUES
    ('ana@example.com', 'Ana', 'admin'), ('ben@example.com', 'Ben', 'member'), ('cleo@example.com', 'Cleo', 'member')");
return $pdo;

PHP main.php

<?php

$pdo = require __DIR__ . '/setup.php';

function findUserUnsafe(PDO $pdo, string $email): array
{
    // NEVER do this
    return $pdo->query("SELECT name, role FROM users WHERE email = '$email'")->fetchAll();
}

print_r(findUserUnsafe($pdo, "ben@example.com"));
print_r(findUserUnsafe($pdo, "' OR '1'='1"));

Output

Array
(
    [0] => Array
        (
            [name] => Ben
            [role] => member
        )

)
Array
(
    [0] => Array
        (
            [name] => Ana
            [role] => admin
        )

    [1] => Array
        (
            [name] => Ben
            [role] => member
        )

    [2] => Array
        (
            [name] => Cleo
            [role] => member
        )

)

The second "email" turned the query into WHERE email = '' OR '1'='1', which matches every row. With other inputs, an attacker could bypass logins, read any table, or delete data.

Prepared statements

A prepared statement sends the SQL and the values to the database separately. Placeholders mark where values go, and the database never interprets the values as SQL, whatever they contain:

PHP main.php

<?php

$pdo = require __DIR__ . '/setup.php';

function findUser(PDO $pdo, string $email): ?array
{
    $stmt = $pdo->prepare('SELECT name, role FROM users WHERE email = :email');
    $stmt->execute(['email' => $email]);
    return $stmt->fetch() ?: null;
}

var_dump(findUser($pdo, "ben@example.com"));
var_dump(findUser($pdo, "' OR '1'='1"));

Output

array(2) {
  ["name"]=>
  string(3) "Ben"
  ["role"]=>
  string(6) "member"
}
NULL

The attack string is simply treated as an (unusual) email address that doesn't exist. fetch() returns false when there are no rows, converted to null here.

There are two placeholder styles: named (:email) with an associative array, or positional (?) with a list:

PHP main.php

<?php

$pdo = require __DIR__ . '/setup.php';

$stmt = $pdo->prepare('SELECT name FROM users WHERE role = ? AND id > ? ORDER BY name');
$stmt->execute(['member', 1]);
echo implode(', ', $stmt->fetchAll(PDO::FETCH_COLUMN)), "\n";

Output

Ben, Cleo

Inserting and reusing statements

A prepared statement can be executed many times with different values — efficient for bulk inserts:

PHP main.php

<?php

$pdo = require __DIR__ . '/setup.php';

$insert = $pdo->prepare('INSERT INTO users (email, name, role) VALUES (:email, :name, :role)');
$newUsers = [
    ['email' => 'dev@example.com', 'name' => 'Dev', 'role' => 'member'],
    ['email' => "o'brien@example.com", 'name' => "Seán O'Brien", 'role' => 'member'],
];

foreach ($newUsers as $user) {
    $insert->execute($user);
    echo "Added {$user['name']} with id ", $pdo->lastInsertId(), "\n";
}

try {
    $insert->execute(['email' => 'ana@example.com', 'name' => 'Imposter', 'role' => 'admin']);
} catch (PDOException $e) {
    echo "Rejected: ", $e->getMessage(), "\n";
}

Output

Added Dev with id 4
Added Seán O'Brien with id 5
Rejected: SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: users.email

Names with apostrophes, which break naive string-built SQL, just work. The UNIQUE constraint stopped a duplicate email.

What can't be a placeholder

Placeholders stand for values only — not table names, column names, ORDER BY directions or SQL keywords. When those depend on input, choose from a fixed list you control:

PHP main.php

<?php

$pdo = require __DIR__ . '/setup.php';

function listUsers(PDO $pdo, string $sort, string $direction): array
{
    $columns = ['name' => 'name', 'email' => 'email', 'newest' => 'id'];
    $column = $columns[$sort] ?? 'name';
    $dir = strtolower($direction) === 'desc' ? 'DESC' : 'ASC';
    return $pdo->query("SELECT name FROM users ORDER BY $column $dir")->fetchAll(PDO::FETCH_COLUMN);
}

echo implode(', ', listUsers($pdo, 'newest', 'desc')), "\n";
echo implode(', ', listUsers($pdo, 'name; DROP TABLE users', 'asc')), "\n";

Output

Cleo, Ben, Ana
Ana, Ben, Cleo

For IN (…) lists, generate one placeholder per value: implode(',', array_fill(0, count($ids), '?')).

Transactions

Some operations involve several statements that must all succeed or all fail — moving money between accounts, placing an order and reducing stock. A transaction groups them: commit() makes the changes permanent, rollBack() undoes them all:

PHP transfer.php

<?php

$pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
$pdo->exec("CREATE TABLE accounts (id INTEGER PRIMARY KEY, owner TEXT, balance INTEGER CHECK (balance >= 0))");
$pdo->exec("INSERT INTO accounts (owner, balance) VALUES ('Ana', 10000), ('Ben', 2500)");

function transfer(PDO $pdo, int $from, int $to, int $cents): void
{
    $pdo->beginTransaction();
    try {
        $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')->execute([$cents, $to]);
        $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')->execute([$cents, $from]);
        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

function balances(PDO $pdo): string
{
    return implode(', ', array_map(fn($r) => "{$r['owner']} " . $r['balance'] / 100,
        $pdo->query('SELECT owner, balance FROM accounts')->fetchAll()));
}

transfer($pdo, 1, 2, 3000);
echo balances($pdo), "\n";

try {
    transfer($pdo, 2, 1, 999999);     // more than Ben has
} catch (PDOException $e) {
    echo "Transfer failed: ", $e->getMessage(), "\n";
}
echo balances($pdo), "\n";

Output

Ana 70, Ben 55
Transfer failed: SQLSTATE[23000]: Integrity constraint violation: 19 CHECK constraint failed: balance >= 0
Ana 70, Ben 55

In the failed transfer, the first UPDATE had already added the money to Ana's account when the second one failed. The rollback undid it, so no money appeared out of nowhere.

A repository class

Rather than scattering SQL through your application, collect each table's queries in a class. The rest of the code calls meaningful methods, and the SQL lives in one place:

PHP main.php

<?php

final readonly class User
{
    public function __construct(public int $id, public string $email, public string $name, public string $role) {}
}

final class UserRepository
{
    public function __construct(private PDO $pdo) {}

    public function find(int $id): ?User
    {
        $stmt = $this->pdo->prepare('SELECT * FROM users WHERE id = ?');
        $stmt->execute([$id]);
        $row = $stmt->fetch();
        return $row ? new User((int) $row['id'], $row['email'], $row['name'], $row['role']) : null;
    }

    /** @return list<User> */
    public function byRole(string $role): array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM users WHERE role = ? ORDER BY name');
        $stmt->execute([$role]);
        return array_map(fn($r) => new User((int) $r['id'], $r['email'], $r['name'], $r['role']), $stmt->fetchAll());
    }
}

$users = new UserRepository(require __DIR__ . '/setup.php');
echo $users->find(1)?->name, "\n";
var_dump($users->find(99));
echo implode(', ', array_map(fn(User $u) => $u->email, $users->byRole('member'))), "\n";

Output

Ana
NULL
ben@example.com, cleo@example.com

Larger projects use an ORM such as Doctrine or Laravel's Eloquent, or a query builder, which generate this kind of code for you — always with prepared statements underneath.

Try it yourself

Write a search function that finds products whose name contains a term, using a prepared statement with LIKE.

<?php

$pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
$pdo->exec("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL)");
$pdo->exec("INSERT INTO products (name, price) VALUES ('Desk lamp', 34.5), ('Standing desk', 399), ('Lamp shade', 12)");

function search(PDO $pdo, string $term): array
{
    $stmt = $pdo->prepare('SELECT name, price FROM products WHERE name LIKE :pattern ORDER BY price');
    $stmt->execute(['pattern' => '%' . $term . '%']);
    return $stmt->fetchAll();
}

foreach (['lamp', 'desk', "'; DROP TABLE products; --"] as $term) {
    $names = array_column(search($pdo, $term), 'name');
    echo str_pad($term, 28), count($names), " match(es): ", implode(', ', $names), "\n";
}

Edit the code, copy it into main.php, then run it with php main.php (PHP 8.5). How to install PHP

Output

lamp                        2 match(es): Lamp shade, Desk lamp
desk                        2 match(es): Desk lamp, Standing desk
'; DROP TABLE products; --  0 match(es):

PreviousDatabases with PDO NextWeb Security