PHP PDO and SQLite: SQL Injection, Shown Then Fixed

Series: PHP lecture 4/6 (intermediate). For developers who can write PHP classes and now need a database. By the end you can open a database with PDO, query it with prepared statements, show a teammate a working SQL injection on their own code, and fix it.

This post is for developers who write or review PHP code that talks to a database, especially code that builds SQL with the . operator. We use SQLite, which ships with PHP, so you need no server: every example ran on PHP 8.5.6 CLI with SQLite 3.53.4 on an Apple Silicon Mac (M1 Pro). The injection is real: the payload below returned every row of a table that a login check was meant to protect.

For a production story — 90 injection and output fixes in a ten-year-old PHP backend, proven byte-identical — read our legacy PHP SQLi/XSS audit. This lecture is the foundation under it. Every table here is generic: users and posts.

Connecting with PDO and ERRMODE_EXCEPTION

<?php
declare(strict_types=1);

$file = __DIR__ . '/app.sqlite';
$pdo = new PDO('sqlite:' . $file, options: [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$pdo->exec(<<<SQL
    CREATE TABLE users (
        id    INTEGER PRIMARY KEY,
        email TEXT NOT NULL UNIQUE,
        name  TEXT NOT NULL,
        role  TEXT NOT NULL DEFAULT 'member'
    );
    CREATE TABLE posts (
        id      INTEGER PRIMARY KEY,
        user_id INTEGER NOT NULL REFERENCES users(id),
        title   TEXT NOT NULL,
        body    TEXT NOT NULL
    );
SQL);

$ins = $pdo->prepare('INSERT INTO users (email, name, role) VALUES (:email, :name, :role)');
foreach ([
    ['alice@example.com', 'Alice', 'admin'],
    ['bob@example.com',   'Bob',   'member'],
    ['carol@example.com', 'Carol', 'member'],
] as [$email, $name, $role]) {
    $ins->execute(['email' => $email, 'name' => $name, 'role' => $role]);
}
$p = $pdo->prepare('INSERT INTO posts (user_id, title, body) VALUES (?, ?, ?)');
$p->execute([1, '100% sure', 'Discount 50% off_today']);
$p->execute([1, 'Hello', 'first post']);
$p->execute([2, 'under_score', 'a_b']);
$p->execute([3, 'Points', 'you have 500 points']);
echo "users: ", $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn(), PHP_EOL;   // users: 3

ERRMODE_EXCEPTION has been the default since PHP 8.0 — we confirmed getAttribute(PDO::ATTR_ERRMODE) returns it on 8.5.6 — but set it explicitly anyway: code gets copied onto old servers, and the older default was silent. Here is what silent looks like:

ERRMODE_SILENT:  $pdo->query('SELECT * FROM nope') returns bool(false)
                 errorInfo() = [HY000, 1, no such table: nope]
                 then: Fatal error: Uncaught Error: Call to a member function fetchAll() on false

The real cause (no such table) is in errorInfo(), and the error you actually see is one line later and talks about false. Exceptions put the cause in the message.

PHP 8.4 also added driver subclasses: PDO::connect('sqlite::memory:') returns a Pdo\Sqlite object. In 8.5 the driver-specific constants on PDO are deprecated, which you will see as soon as you touch MySQL settings:

Deprecated: Constant PDO::MYSQL_ATTR_INIT_COMMAND is deprecated since 8.5, use Pdo\Mysql::ATTR_INIT_COMMAND instead in ~/demo/b8_misc.php on line 4

SQL injection, demonstrated on a real query

Here is the pattern from countless tutorials: take the input and glue it into the SQL.

$input = $argv[1] ?? "nobody@example.com' OR '1'='1";   // pretend: $_GET['email']

// VULNERABLE
$sql = "SELECT id, email, role FROM users WHERE email = '" . $input . "'";
echo "SQL: $sql\n";
$rows = $pdo->query($sql)->fetchAll();
echo "concat   -> ", count($rows), " rows\n";
foreach ($rows as $r) echo "  ", implode(' | ', $r), "\n";

// FIX
$st = $pdo->prepare('SELECT id, email, role FROM users WHERE email = ?');
$st->execute([$input]);
echo "prepared -> ", count($st->fetchAll()), " rows\n";

SQL: SELECT id, email, role FROM users WHERE email = 'nobody@example.com' OR '1'='1'
concat   -> 3 rows
  1 | alice@example.com | admin
  2 | bob@example.com | member
  3 | carol@example.com | member
prepared -> 0 rows

The quote in the input closed the string literal, and OR '1'='1' became part of the condition. A query meant to return one user returned all three, including the admin. A UNION payload goes further and reads columns the query never selected:

SQL: SELECT id, email, role FROM users WHERE email = 'x' UNION SELECT id, name || ':' || email, role FROM users --'
concat   -> 3 rows
  1 | Alice:alice@example.com | admin
  ...
prepared -> 0 rows

With prepare(), the SQL text is sent and parsed before the value exists. The value is then bound as data and compared as one whole string, so the quote is just a character in an email address that nobody has.

Two further observations from the same run:

• An innocent name like O'Brien@example.com broke the concatenated query: PDOException: SQLSTATE[HY000]: General error: 1 near "Brien": syntax error. If users with apostrophes get errors, you almost certainly have an injection hole.
• x'; DROP TABLE users; -- through query() returned 0 rows and the table survived: PDO's SQLite query() runs only the first statement. Do not rely on that. The same payload style through exec() did run both statements; our UPDATE users SET name = '…' with x' WHERE 1=0; DROP TABLE posts; -- deleted the posts table. And MySQL with PDO allows multiple statements by default when prepares are emulated.

Common mistake → what actually happens → fix

Mistake: "I escaped it with addslashes, so it is safe." addslashes does not know the connection's character set or the database's quoting rules. SQLite does not use backslash escaping at all: the correct quoting of O'Brien is 'O''Brien', which is exactly what $pdo->quote("O'Brien") returned. We ran the payload x' OR 1=1 -- through addslashes:

SELECT id FROM users WHERE email = 'x\' OR 1=1 --'
  rows: 3

To SQLite, 'x\' is a complete string containing a backslash. The quote that addslashes "escaped" closed the literal, and all three rows came back. Fix: placeholders for values. quote() exists, but treat it as a last resort for code you cannot restructure — which is the situation described in the audit post linked above.

Mistake: binding table or column names. Placeholders hold values only. ORDER BY ? binds a constant, so the rows are not sorted by that column: ORDER BY ? DESC with 'id' returned [1,2,3], no error. Fix: an allow-list: $col = in_array($_GET['sort'], ['id', 'email'], true) ? $_GET['sort'] : 'id';.

Why emulated prepares matter on MySQL (described, not run)

We had no MySQL server for this lecture, so this section is from the PDO documentation and our earlier production work, not from a run here. On SQLite, setAttribute(PDO::ATTR_EMULATE_PREPARES, true) simply returned false: the driver always prepares natively.

PDO_MySQL emulates prepares by default. PHP itself substitutes the escaped values into the SQL text and sends one finished string to the server. Consequences:

1. Character set. Escaping is only safe if PHP knows the real connection charset. Put charset=utf8mb4 in the DSN. Running SET NAMES afterwards changes the server's charset without telling PDO, which is the precondition of the old multibyte escaping attacks.
2. Types. With emulation, numbers usually come back as strings; with native prepares, INT columns come back as PHP ints. That changes your JSON — see prepared statements can change your JSON types for the mysqli version of this.
3. Multiple statements are allowed by default with emulation (see Pdo\Mysql::ATTR_MULTI_STATEMENTS).
4. Errors arrive later. A syntax error appears at execute(), not at prepare().

For new MySQL code: charset=utf8mb4 in the DSN, ATTR_EMULATE_PREPARES => false, and Pdo\Mysql::ATTR_MULTI_STATEMENTS => false. Re-test your JSON output after switching.

Fetch modes

The default fetch mode is FETCH_BOTH, which returns every column twice:

FETCH_BOTH: ["id"=>1, 0=>1, "email"=>"alice@example.com", 1=>"alice@example.com", "role"=>"admin", 2=>"admin"]

That is double the memory and duplicated keys in json_encode. Set FETCH_ASSOC as the default and pick a specialised mode when it fits:

With FETCH_CLASS into a class with typed properties, id arrived as int(1): PDO SQLite returns integer columns as PHP ints. When there is no row, both fetch() and fetchColumn() return false — so check === false. A real value of 0 or an empty string must not be mistaken for "no row".

IN lists and LIKE escaping

The single-placeholder mistake:

$ids = [3, 1, 999];
$st = $pdo->prepare('SELECT id FROM users WHERE id IN (?)');
$st->execute([implode(',', $ids)]);       // -> []

It returned no rows and no error, because the database compared each id with the one string '3,1,999'. (MySQL would cast that string to 3 and return one row — a different wrong answer.) The fix is one placeholder per value:

function inList(PDO $pdo, string $sqlWithIn, array $values): PDOStatement
{
    if ($values === []) {
        throw new InvalidArgumentException('empty IN list: return early (IN () is a syntax error in MySQL)');
    }
    $marks = implode(',', array_fill(0, count($values), '?'));
    $st = $pdo->prepare(str_replace('(:in)', "($marks)", $sqlWithIn));
    $st->execute(array_values($values));
    return $st;
}
$st = inList($pdo, 'SELECT id, email FROM users WHERE id IN (:in) ORDER BY id', $ids);  // -> [1, 3]

array_values() matters: after array_filter() the keys may be [0, 2], and execute([0 => 1, 2 => 3]) on IN (?,?) failed with General error: 25 column index out of range. SQLite accepted an empty IN () in our test and returned no rows, but MySQL rejects it, so handle [] in PHP.

Prepared statements stop injection in LIKE, but % and _ in the user's text are still wildcards:

function likeEscape(string $s, string $esc = '\\'): string
{
    return str_replace([$esc, '%', '_'], [$esc . $esc, $esc . '%', $esc . '_'], $s);
}
$safe = $pdo->prepare("SELECT title FROM posts WHERE body LIKE ? ESCAPE '\\'");
$safe->execute(['%' . likeEscape($q) . '%']);

search 50%  naive=["100% sure","Points"]                            escaped=["100% sure"]
search _    naive=["100% sure","Hello","under_score","Points"]      escaped=["100% sure","under_score"]
search %    naive=["100% sure","Hello","under_score","Points"]      escaped=["100% sure"]

A search for _ matched every post. On a large table, a user typing % forces a full scan of every row, which is a cheap denial of service. Escape the escape character first (the order in str_replace arrays is left to right).

Real PDOException messages

All from one script against the database above:

[typo in table]    SQLSTATE[HY000]: General error: 1 no such table: user
[typo in column]   SQLSTATE[HY000]: General error: 1 no such column: mail
[UNIQUE violation] SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: users.email
[NOT NULL]         SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: users.name
[too many params]  SQLSTATE[HY000]: General error: 25 column index out of range
[missing :name]    SQLSTATE[HY000]: General error: 25 column index out of range
[bad file path]    SQLSTATE[HY000] [14] unable to open database file
[too few params]   no exception

Things to notice:

• getCode() returned the string '23000' for the UNIQUE violation but the int 14 for the connection error. Use $e->errorInfo[0] === '23000' (or the driver code errorInfo[1] === 19) to detect "email already registered", not getCode() === 23000.
• Too few parameters did not throw on SQLite. The missing ? was bound as NULL; we confirmed with SELECT ?, ? given one value, which returned "1" and NULL. The query silently matched nothing. MySQL raises SQLSTATE[HY093]: Invalid parameter number here, so code tested only on SQLite can fail in production.
• A misspelled named parameter (uid for :id) gave the unhelpful column index out of range, not "missing parameter". When you see that message, compare your array keys with the placeholders.

Transactions: 10,000 inserts measured

Each INSERT outside a transaction is its own transaction: SQLite writes a journal and flushes it to disk before returning. Wrapped in one transaction, that happens once.

$st = $pdo->prepare('INSERT INTO posts (user_id, title) VALUES (?, ?)');
$t = hrtime(true);
if ($mode === 'tx') $pdo->beginTransaction();
for ($i = 0; $i < 10_000; $i++) {
    $st->execute([$i % 100, "title $i"]);
}
if ($mode === 'tx') $pdo->commit();
$ms = (hrtime(true) - $t) / 1e6;

Fresh database file per run, PHP 8.5.6 CLI, SQLite 3.53.4 (defaults: journal_mode=delete, synchronous=2/FULL), Apple Silicon Mac (M1 Pro), internal SSD, three runs:

About 530 times faster with one transaction. WAL mode helps a lot when you cannot batch (a web app where each request writes one row), but it does not replace a transaction for bulk work. A transaction is also about correctness — all or nothing:

function transfer(PDO $pdo, callable $work): void
{
    $pdo->beginTransaction();
    try {
        $work($pdo);
        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

before: 4
rolled back: SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: posts.title
after: 4
PDOException: There is already an active transaction
PDOException: There is no active transaction

The first INSERT succeeded, the second failed, and the rollback removed both. The last two lines show that PDO has no nested transactions: a second beginTransaction() throws, and so does commit() after a rollback. If a helper may run inside an outer transaction, check $pdo->inTransaction() or use SAVEPOINT.

Decision table

Honest limits / version notes

• Run on PHP 8.5.6 CLI with SQLite 3.53.4 only. ERRMODE_EXCEPTION as the default dates from PHP 8.0, driver subclasses (Pdo\Sqlite, PDO::connect) from 8.4, and the deprecation of PDO::MYSQL_ATTR_* from 8.5 (we saw the Deprecated message).
• MySQL behaviour (emulated prepares, HY093, IN () errors, string casting in IN) is described from the documentation and earlier production work, not run here.
• The transaction timings are for macOS on an internal SSD. SQLite on macOS does not use F_FULLFSYNC by default, so autocommit here is faster than it would be with full durability. On a shared host's network storage, expect autocommit to be slower still.

Exercise

Write findPosts(PDO $pdo, array $userIds, string $q): array that returns id, title of posts whose author is in $userIds and whose body contains $q literally (so _ means an underscore). Return [] for an empty $userIds without touching the database.

Answer

function findPosts(PDO $pdo, array $userIds, string $q): array
{
    $userIds = array_values(array_map('intval', $userIds));
    if ($userIds === []) {
        return [];
    }
    $marks = implode(',', array_fill(0, count($userIds), '?'));
    $like = '%' . str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $q) . '%';
    $st = $pdo->prepare(
        "SELECT id, title FROM posts WHERE user_id IN ($marks) AND body LIKE ? ESCAPE '\\' ORDER BY id"
    );
    $st->execute([...$userIds, $like]);
    return $st->fetchAll(PDO::FETCH_ASSOC);
}

echo json_encode(findPosts($pdo, [1, 2], '_')), PHP_EOL;
echo json_encode(findPosts($pdo, [], '_')), PHP_EOL;
echo json_encode(findPosts($pdo, [2, 3], '50')), PHP_EOL;

[{"id":1,"title":"100% sure"},{"id":3,"title":"under_score"}]
[]
[{"id":4,"title":"Points"}]

The IN values come first in the array because their ? come first in the SQL. Mixing up that order is a classic silent bug.

Previous: PHP lecture 3 — classes, interfaces and enums · Next: PHP lecture 5 — XSS, CSRF and sessions