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
[[fig:php-pdo-sqli-flow]]
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.combroke 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; --throughquery()returned 0 rows and the table survived: PDO's SQLitequery()runs only the first statement. Do not rely on that. The same payload style throughexec()did run both statements; ourUPDATE users SET name = '…'withx' WHERE 1=0; DROP TABLE posts; --deleted thepoststable. 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:
-
Character set. Escaping is only safe if PHP knows the real connection charset. Put
charset=utf8mb4in the DSN. RunningSET NAMESafterwards changes the server's charset without telling PDO, which is the precondition of the old multibyte escaping attacks. - 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.
-
Multiple statements are allowed by default with emulation (see
Pdo\Mysql::ATTR_MULTI_STATEMENTS). -
Errors arrive later. A syntax error appears at
execute(), not atprepare().
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:
| Mode | Example result | Use for |
|---|---|---|
FETCH_ASSOC |
[id => 1, email => alice@…, role => admin] |
most queries, JSON |
FETCH_NUM |
[0 => 1, 1 => alice@…, 2 => admin] |
[$a, $b] = $row |
FETCH_COLUMN |
[alice@…, bob@…, carol@…] |
one column as a list |
FETCH_KEY_PAIR |
[1 => alice@…, 2 => bob@…, 3 => carol@…] |
<select> options, lookups |
FETCH_GROUP | FETCH_COLUMN |
[admin => [alice@…], member => [bob@…, carol@…]] |
grouping without a loop |
FETCH_UNIQUE | FETCH_ASSOC |
[1 => [email, role], 2 => …] |
index rows by id |
FETCH_CLASS, UserRow::class |
UserRow {id: 1, email: …, role: …} |
typed objects (lecture 3) |
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 int14for the connection error. Use$e->errorInfo[0] === '23000'(or the driver codeerrorInfo[1] === 19) to detect "email already registered", notgetCode() === 23000. -
Too few parameters did not throw on SQLite. The missing
?was bound asNULL; we confirmed withSELECT ?, ?given one value, which returned"1"andNULL. The query silently matched nothing. MySQL raisesSQLSTATE[HY093]: Invalid parameter numberhere, so code tested only on SQLite can fail in production. - A misspelled named parameter (
uidfor:id) gave the unhelpfulcolumn 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:
| Mode | Run 1 | Run 2 | Run 3 | Rows/s (median) |
|---|---|---|---|---|
| Autocommit (default) | 2,601.2 ms | 2,604.6 ms | 2,568.5 ms | ~3,840 |
Autocommit, journal_mode=WAL, synchronous=NORMAL
|
79.1 ms | 85.3 ms | 84.6 ms | ~118,000 |
| One transaction | 4.8 ms | 5.1 ms | 4.9 ms | ~2,000,000 |
[[fig:php-pdo-tx]]
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
| Situation | Do this |
|---|---|
| Any value from outside (GET, POST, JSON, files, headers) |
? or :name placeholder |
| Table/column name, sort direction | allow-list in PHP |
IN (...) with an array |
one ? per value, array_values, handle []
|
LIKE search |
placeholder + likeEscape() + ESCAPE '\'
|
| More than a handful of writes | one transaction |
| One write per web request on SQLite | WAL mode |
| New MySQL code |
charset=utf8mb4, emulation off, multi-statements off |
| Legacy code you cannot restructure today | escaping with the right charset, then plan the migration (see the audit post) |
In production: a security patch that silently emptied 12 pages
Our legacy PHP pages were patched for SQL injection with escaping (real_escape_string on input, htmlspecialchars on output), as described in auditing legacy PHP for SQLi and XSS. On 19 November 2026, while redesigning those pages, we found that one patched file had stopped working altogether: the archive page of each of the 12 star signs showed a title and nothing else. The patched lines read:
$cate1_raw = isset($cate1_sql) ? $cate1_sql : ''; // $cate1_sql is not defined yet
$cate1_sql = mysqli_real_escape_string($Conn, $cate1_raw);
They never look at $_GET['cate1'], so every request searched for an empty string. The query was perfectly safe, and returned nothing: no error, no warning in the log, HTTP 200. The fix was one line, with the escaping unchanged:
$cate1_raw = (string)($_GET['cate1'] ?? '');
What we took from it:
-
A security fix is a behavior change. Test it with real inputs, not only with an attack string. Comparing the page before and after the patch with a valid
cate1would have caught this on day one. - "Safe and empty" fails silently. A page that answers 200 with zero rows needs a check of its own: count the rows and alert on zero.
-
Prepared statements make this kind of mix-up harder. The value goes into
execute()right next to the query that uses it, so there is no separate "cleaned" variable to get wrong.
The same week we wrote a one-off PHP script to fill missing data in a production table, and it followed this lecture: prepared statements only; a dry-run mode that first checked all 125 target rows (exactly one row each, current values all zero, numbers as expected); a transaction that rolls back if any row fails; and a secret token. The dry run passed 125/125, the real run updated 125 rows, and the script was deleted right after.
Honest limits / version notes
- Run on PHP 8.5.6 CLI with SQLite 3.53.4 only.
ERRMODE_EXCEPTIONas the default dates from PHP 8.0, driver subclasses (Pdo\Sqlite,PDO::connect) from 8.4, and the deprecation ofPDO::MYSQL_ATTR_*from 8.5 (we saw the Deprecated message). - MySQL behaviour (emulated prepares,
HY093,IN ()errors, string casting inIN) 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_FULLFSYNCby 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
Originally published at tlaboratory.net. Every output in this post was copied from a real terminal run.
Top comments (0)