Lesson 1 of 5

PDO, Queries and Transactions

"Trust, but verify." — Suzanne Massie to Ronald Reagan, quoting a Russian proverb (1984)

An ORM is a convenience over a driver. Eloquent's User::where('email', $email)->first() ends as a prepared statement sent over PDO, and every property Eloquent has — that your data is safe from injection, that a failed batch leaves nothing half-written, that a column comes back as an integer — is a property of that layer rather than of the ORM.

This chapter is the layer underneath. It is where SQL injection is prevented or not, where transactions succeed or silently stay open, and where the types your models expose are decided. Everything in the rest of this Part sits on it.

The query and the data are different channels

PHP — Try it Yourself

Loading editor…

Output

Click Run to execute the code.

The demonstration is worth running rather than reading about. The concatenated query returned every row in the table; the prepared statement returned none, because it looked for a user whose email address is literally ' OR '1'='1.

The distinction underneath is the one to hold on to. Escaping asks "which characters in this string are dangerous, and how do I neutralise them?" — a question whose answer depends on the character set, the SQL mode, and the quoting context, and which has been got wrong repeatedly by competent people. Binding does not ask it. The SQL is parsed before the value exists, and the value travels in a different field of the protocol, so there is no moment at which it could become syntax.

That is why "use prepared statements" is advice of a different kind from "escape your input". It removes the question rather than answering it.

An analogy

Think of dictating a letter to an assistant, where part of the letter is a quotation from somebody else. If you simply read the whole thing aloud, your assistant has to work out from tone and context which words are your instructions — "new paragraph", "sign it yours sincerely" — and which are part of the quotation. Most of the time they get it right. Then you quote someone who happened to say "new paragraph" in the passage, and the letter comes back mangled. The fix is not to speak more carefully. It is to hand the quotation over on a separate sheet of paper and say "insert the attached text here" — because now there is no interpretation to get wrong, and it does not matter what the quotation contains. Escaping is speaking carefully. Binding is the separate sheet. And the reason the second one is robust is not that it is more rigorous but that it makes the ambiguity structurally impossible rather than reliably avoided.

What you can bind, and what you cannot

PHP — Try it Yourself

Loading editor…

Output

Click Run to execute the code.

A placeholder stands for a value, never for syntax. That follows directly from the previous section: the database parses the statement at prepare() time, before any value exists, so a table name or a sort direction cannot be supplied later — there would be nothing to parse.

The consequence is that dynamic sorting, dynamic column selection and dynamic table names cannot be made safe by binding. They need an allowlist, and it is worth noticing what an allowlist is not: it does not clean the hostile value. '; DROP TABLE users --' was not sanitised; it simply was not in the list, so a known-safe default was used instead. That is the same shape as the URL-scheme allowlist from Part IV, and it is more robust than filtering for the same reason.

IN is the case that catches people, because it looks like a value and is actually structure. The number of placeholders comes from your array's length; the values inside them are still bound.

A little history

SQL injection was named and described publicly by Jeff Forristal, writing as "rain.forest.puppy", in Phrack 54 in December 1998 — and the technique was already old by then. PHP's relationship with it is unusually instructive because the language tried three answers in sequence and the first two made things worse. Magic quotes, on by default from PHP 2 until its removal in 5.4, escaped all input automatically; it produced double-escaped databases, could be turned off by configuration you did not control, and taught a generation that input arrived "already safe". Then came mysql_real_escape_string(), which is correct when used correctly and has two conditions most callers did not know about: it must be given the connection handle so it knows the character set, and it only protects a value already inside quotes — WHERE id = $id with no quotes is injectable no matter how well the value was escaped. The famous failure was the GBK character-set attack of 2006, where a multibyte encoding let an attacker construct a byte sequence that survived escaping and became a quote. PDO arrived in PHP 5.1 in 2005 with prepared statements, the mysql_* functions were deprecated in 5.5 and removed in 7.0, and the profession settled on the answer that removes the question instead of answering it. Worth noting that PDO's ATTR_EMULATE_PREPARES, historically on by default for MySQL, quietly reintroduced client-side escaping — real prepared statements only arrived for most PHP developers when that default changed.

Transactions, and the way they are left open

PHP — Try it Yourself

Loading editor…

Output

Click Run to execute the code.

The middle block is the bug. A beginTransaction() followed by a throw leaves the transaction open: nothing is committed, but the connection is holding locks and every subsequent query on it runs inside a transaction that is going to be rolled back. Under PHP-FPM the connection closes at the end of the request and the transaction is rolled back by the server, which hides the problem — exactly the way the shared-nothing model hid the singleton bug in Part V. On a long-lived worker, or a connection returned to a pool, it does not close.

The closure wrapper is the fix, and it is worth recognising as the same structural move this volume keeps arriving at. DB::transaction(fn () => ...) is this function. It makes the rollback impossible to forget rather than easy to remember, and it survives an early return inside the callback in a way that a hand-written commit() at the bottom of a method does not.

From my notebook

One of my probes for this chapter died with exit 255 and no error, and finding out why taught me something I thought I already knew. I had written $result = @$pdo->query('SELECT * FROM does_not_exist'); inside a loop that set each of the three PDO error modes in turn, expecting the @ to let me inspect the return value in every mode. In ERRMODE_EXCEPTION it threw, uncaught, and killed the script — because @ suppresses diagnostics, and an exception is not a diagnostic. I know that rule. I have written it down in this very volume, in Part III, from the other direction: check() cannot catch a warning because a warning is not Throwable. The two facts are the same boundary seen from either side, and I had filed them separately, which is why knowing one did not stop me making the other mistake. What makes it worth recording is the failure mode rather than the fact. The script produced partial output and exit 255, with the fatal error going to a channel I was not reading, so my first hypothesis was a memory limit and my second was something exotic about repeated in-memory SQLite connections. Both were wrong and both were more interesting than the truth. The cheap move — bisect the probe until the failing line is isolated — would have found it in two minutes, and I reached for it third.

Nesting that is not nesting, and errors that are not errors

PHP — Try it Yourself

Loading editor…

Output

Click Run to execute the code.

Two things here are worth keeping.

PDO has one transaction, not a stack. A second beginTransaction() throws. Frameworks that appear to support nesting maintain a depth counter: the outermost call issues a real BEGIN, inner ones issue SAVEPOINT, and an inner rollback returns only to its savepoint. The consequence that surprises people is correct and follows directly — an inner transaction that "succeeded" is still undone if the outer one fails, because it was never independently committed.

The error modes are the falsy-return pattern one last time. ERRMODE_SILENT was the default until PHP 8.0, which meant a failing query returned false and execution continued, so a typo in a column name produced a blank page rather than an error. ERRMODE_EXCEPTION is now the default, and is the only mode worth using.

The big question

If prepared statements make injection structurally impossible, why does SQL injection still appear in breach reports every year? The uncomfortable answer is that the protection is real and its coverage is partial, and almost every modern injection lives in the gap. Four gaps recur. The first is the structural positions this chapter measured — table names, column names, sort directions, IN lists — which cannot be bound at all, so any dynamic sorting or filtering feature is hand-built and only as safe as its allowlist. The second is query builders used as string builders: whereRaw(), selectRaw(), orderByRaw() and DB::raw() all exist for good reasons and all take a string, and a value interpolated into one is injectable exactly as it was in 1998. The third is second-order injection, where a value is bound safely on the way in, stored, and later read back out and concatenated into another query — at which point it is no longer "user input" to anyone reading the code, because it came from your own database. The fourth is emulated prepares, where the driver does client-side escaping rather than sending a real prepared statement, which reintroduces the character-set assumptions binding was supposed to eliminate. What unites all four is that the developer believed they were using the safe path and were not, which suggests the useful habit is not "always use prepared statements" — everyone agrees with that already — but a narrower question asked at review: which parts of this SQL string came from a variable, and for each one, is it a bound value or is it text? That question has an answer you can check. The general instruction does not.

What comes back

PHP — Try it Yourself

Loading editor…

Output

Click Run to execute the code.

Three defaults here are worth changing on every connection you open. FETCH_BOTH returns every value twice, once by name and once by index, which doubles memory for no benefit — set ATTR_DEFAULT_FETCH_MODE to FETCH_ASSOC. ERRMODE should be EXCEPTION, which it now is by default. And ATTR_EMULATE_PREPARES should be off, which makes the driver send real prepared statements and return native types.

That last one connects to the types table. Here age came back as an int and rate as a float. Under MySQL with emulated prepares, every column arrives as a string — so $user['age'] === 36 is false, silently, and the bug appears when someone changes a connection setting rather than when someone writes the comparison.

rowCount() deserves its reputation. After an UPDATE it reports matched rows on some drivers and changed rows on others; after a SELECT it is simply unreliable and returned 0 here. Count the rows you fetched.

In the real world

The database incident that costs the most time is the connection left holding an open transaction. It usually starts as a job or a command that begins a transaction, hits an exception partway, and has no rollback on the failure path — which under PHP-FPM is invisible, because the process ends and the connection closes. Then the same code runs on a queue worker, which is a long-lived process reusing one connection across thousands of jobs, and the failure changes character completely: the transaction stays open, the rows it touched stay locked, and every subsequent job on that worker executes inside a doomed transaction whose writes will be discarded. What makes it hard to diagnose is that the symptoms appear everywhere except the code that caused them — other jobs timing out on lock waits, writes that "succeeded" and are not in the database, a connection count climbing on the server. The diagnostic that cuts through it is to ask the database rather than the application: most engines expose the currently open transactions and the locks they hold, and the offending connection will be visible with an open transaction and an idle age measured in minutes. The prevention is structural and cheap — never call beginTransaction() directly, use the closure form so the rollback cannot be skipped, and if you must call it directly then the catch that rolls back is not optional. This is the same lesson as the middleware chapter's finally, arriving at a layer where the cost of forgetting is measured in locked rows.

Common Mistakes

Concatenating any value into SQL. A prepared statement is not stricter escaping; the SQL is parsed before the value exists, so the value can never become syntax.
Trying to bind a table name, column name or sort direction. Those positions are parsed at prepare() time. They need an allowlist with a safe default, not a placeholder.
Binding a single placeholder for an IN list. The number of markers is structure and comes from your array's length; the values inside them are still bound.
Using whereRaw() or DB::raw() with an interpolated variable. These take strings, and a variable inside one is injectable exactly as it was before prepared statements existed.
Calling beginTransaction() without a rollback on the failure path. The transaction stays open, holding locks, and every later query on that connection runs inside it.
Assuming PDO supports nested transactions. A second beginTransaction() throws. Frameworks fake nesting with savepoints, so an inner "commit" is still undone if the outer transaction fails.
Leaving ATTR_EMULATE_PREPARES on. The driver escapes client-side instead of sending a real prepared statement, and every column comes back as a string.
Leaving the default fetch mode at FETCH_BOTH. Every value is returned twice, by name and by index, doubling result memory for nothing.
Using rowCount() after a SELECT. It is unreliable and returned 0 here. Count what you actually fetched.
Testing fetch() against null. It returns false when there are no rows, so a === null check never fires.

Interview Corner

Interview Corner — the same idea at three levels. Answer yours; recognise the one above it.

Warm-up

Why does a prepared statement prevent SQL injection?

Model answer — Because the query and the data travel separately. prepare() sends the SQL text with placeholders and the database parses and plans it at that moment; execute() then sends only values, in a different part of the protocol. Since parsing already happened, there is no point at which a value can be interpreted as syntax — so a value like ' OR '1'='1 is searched for literally and matches nothing. It is importantly different from escaping, which tries to neutralise dangerous characters inside a single string and therefore depends on the character set and quoting context being right.

Intermediate

A feature lets users sort a table by any column. How do you make it safe?

Model answer — Not with a placeholder, because a column name and a sort direction are syntax rather than values — the database cannot even parse ORDER BY ?, since parsing happens before any value is bound. The safe construction is an allowlist: compare the requested column against a hard-coded list of permitted columns and the direction against ASC/DESC, and fall back to a known-safe default when either does not match. The important property is that the hostile value is never cleaned or transformed — it simply fails to appear in the list, so nothing is riding on a filter being comprehensive.

Advanced

A queue worker starts timing out on lock waits after an unrelated deploy. Where do you look?

Model answer — A transaction left open by an exception. If code calls beginTransaction() and throws before committing, with no rollback on the failure path, the transaction stays open — nothing is committed, but the connection holds its locks and every later query on it runs inside a doomed transaction. Under PHP-FPM this is invisible because the process ends and the connection closes each request; on a long-lived worker reusing one connection across thousands of jobs, it persists and poisons everything after it. I would confirm it by asking the database which transactions are currently open and how long they have been idle, rather than reading application code, since the symptoms appear in jobs unrelated to the cause. The fix is to use the closure form of the transaction helper so the rollback is structural rather than remembered.

micro1Practical: prepared statements and why they work.GoogleDeepest — what binding cannot cover, and the remaining gaps.MetaAllowlists for structural positions.AmazonTransaction lifetime on long-lived workers.

Hands-on

Model the driver rules this chapter measured. Implement isBindable, values against syntax; inPlaceholders, the IN construction; safeOrderBy, the allowlist; transactionState, what a throw leaves behind; and emptyResult, what each fetch returns. A stub scores zero, so a passing run means the model is real.

PHP — Try it Yourself

Loading editor…

Output

Click Run to execute the code.

Summary

Prepared statements are not escaping. prepare() parses the SQL before any value exists, and execute() sends values separately, so a value can never become syntax.
A concatenated query with ' OR '1'='1 returned every row; the same value bound returned none, because it was searched for literally.
Only values can be bound. Table names, column names, sort directions and IN lists are syntax and cannot be parsed later.
Structural positions need an allowlist, which does not clean the hostile value — it simply is not in the list, so a safe default is used.
An IN clause needs one placeholder per value, generated from the array's length; the values themselves are still bound.
A throw after beginTransaction() leaves the transaction open, holding locks, with every later query on that connection inside it.
The closure form makes rollback structural — it is what DB::transaction() is, and it survives an early return.
PDO has one transaction, not a stack. Nesting is faked with savepoints, so an inner commit is still undone if the outer transaction fails.
ERRMODE_EXCEPTION has been the default only since PHP 8.0; before that a failed query returned false and execution continued.
FETCH_BOTH is the default and duplicates every value, lastInsertId() always returns a string, rowCount() is unreliable after a SELECT, and fetch() returns false — not null — when there are no rows.

Next: models and Active Record — what an Eloquent model actually is, and the query each method call produces.

Editorial reflection

What strikes me about this layer is that its most important idea is a negative one. Prepared statements are valuable not because they do something clever with your data but because they make an entire question stop being askable: there is no longer any sense in which a value could be interpreted as syntax, so no amount of ingenuity about encodings, character sets or quoting can recover the attack. The industry spent roughly fifteen years trying to answer the question well — magic quotes, escaping functions with subtle preconditions, filters with ever-longer lists of dangerous characters — and every answer was defeated by someone who found a case the answer had not anticipated. The thing that worked was changing the shape of the problem. I keep meeting this pattern in the volume and it has started to feel like the most transferable thing in it: the strongest fixes are the ones that remove a category of mistake rather than defending against it. Escaping defends; binding removes. A finally defends; a closure wrapper removes. Careful escaping in templates defends; auto-escaping removes. And each time, the removing version is slightly less flexible and enormously more reliable, which is a trade that looks bad in the abstract and good in every incident report. The corollary is worth stating too, because this chapter contains it: where the removal does not reach — raw expressions, dynamic column names, second-order values read back from your own database — you are back to defending, and that is exactly where the remaining injections live.

Enrol to track progress

Q&A · 0

Enrol to ask questions and join the discussion.

No questions yet — be the first to ask.