Databases and PDO
PHP talks to a database in a shared-nothing model: a php-fpm worker opens a connection for the duration of one request and drops it when the request ends. Nothing carries over between requests — no pool, no connection cache, no open transaction. That is what makes PHP simple, and it is also what sets three traps interviewers reliably probe: you have as many connections as you have workers, not as many as the database wants; all transaction state lives inside a single request; and reading a row and then writing it is two round trips, with room for a neighbouring worker in between.
The second thread running through the topic is what a prepared statement actually does. The right answer is not "it escapes the input" but "the server parses and plans the statement before it receives a single value, so a parameter physically cannot become syntax". That is also where the senior-level trap grows from: PDO::ATTR_EMULATE_PREPARES is on by default in the MySQL driver, and with it there is no server-side parse before binding at all — PDO assembles the final SQL client-side. And it is where the boundary comes from too: you can bind a value, but never a column name in ORDER BY — the one place a prepared statement does not save you and an allowlist must. The layer-by-layer breakdown is below.
Topic map
- PDO and prepared statements — one API across drivers, the two-phase prepare/execute protocol, emulation mode, and why
PDO::quote()is no substitute for binding. - Transactions — all-or-nothing atomicity,
beginTransaction()/commit()/rollBack()paired with exceptions, and the isolation levels with their anomalies. - Locking — the read-then-write race, a conditional
UPDATEchecked withrowCount(), pessimisticSELECT ... FOR UPDATE, and the optimisticversioncheck. - Indexes — the B-tree as a sorted search structure, the leftmost-prefix rule for composite indexes, reading
EXPLAIN, and what an index costs on write. - Connection management — a connection's life inside one request, the
max_connectionsarithmetic, howATTR_PERSISTENTdiffers from a pool, and whatpgbouncerdoes.
Common Mistakes and Traps
| Mistake | Consequence |
|---|---|
| Saying a prepared statement "escapes the input" | The mechanism is different: the statement is parsed before binding, so a parameter cannot become syntax |
Leaving PDO::ATTR_EMULATE_PREPARES on (the MySQL driver's default) | There is no server-side parse before binding at all — PDO assembles the SQL client-side, and the guarantee degrades to "the escaping was correct" |
Treating PDO::quote() as an equivalent of binding | That is escaping, not a separate data channel; miss one call and you have an injection |
Trying to bind a column name or ASC/DESC in ORDER BY | A placeholder is a slot for a value: you would sort by a constant string. This needs an allowlist |
Not enabling PDO::ERRMODE_EXCEPTION | A failed query silently returns false and the code carries on over an empty result |
| Believing already-executed statements stay committed when the next one fails | Without commit() nothing is committed: rollBack() unwinds the whole transaction |
| Catching an exception inside a transaction and not rethrowing | The caller assumes success while the work was rolled back |
| Treating a transaction as protection against a read-then-write race | Isolation does not stop a neighbour from reading the same row — you need a lock or a conditional UPDATE |
| Writing an absolute value computed in PHP | stock = 0 overwrites someone else's write; a relative UPDATE ... SET stock = stock - 1 WHERE stock >= 1 does not |
Not checking rowCount() after a conditional or optimistic UPDATE | A lost race is silently taken for success |
| Adding one index per column instead of one composite index | The engine picks one index: a composite index built for the query works, a pile of single-column ones mostly does not |
Putting the ORDER BY column before the WHERE columns in a composite index | The leftmost prefix no longer matches the predicate — the index is not used and the filesort stays |
Wrapping a column in a function in WHERE (YEAR(created_at) = 2024) | The index on created_at is not used: the engine must evaluate the function on every row |
Calling PDO::ATTR_PERSISTENT a connection pool | It is one socket pinned to one worker; the total connection count does not drop |
Raising max_connections without comparing it to servers × pm.max_children | PHP's ceiling stays above the database's — Too many connections returns under load |
Interview relevance
Databases are where interviewers check whether you can tell a mechanism from an incantation. A candidate who answers the prepared-statement question with "PDO escapes the data" nominally "knows about security" but will fail every follow-up: why can't you bind ORDER BY, what does emulation change, why does bindValue take a type at all. A candidate who says "the server parses the statement before it ever sees a value" answers all three at once.
Typical checks:
- What
prepare()does on the server and why a parameter cannot become syntax. - What
PDO::ATTR_EMULATE_PREPARESchanges and why you turn it off. - What can be bound and what must go through an allowlist.
- What a transaction guarantees when a statement fails midway, and which anomaly each isolation level removes.
- How optimistic locking differs from
SELECT ... FOR UPDATEand when each is cheaper. - How to prove from
EXPLAINthat a slow endpoint is a missing index. - Why there is no connection pool in php-fpm and what people do about it.
Common wrong answer: "We use PDO, so we have no SQL injection." PDO is an API, not a guarantee: string interpolation inside query() is exactly as injectable through PDO as it was through mysql_*. The second classic failure is "a transaction will protect us from the double sale": a transaction gives you atomicity, not mutual exclusion, and without a row lock or a condition inside the UPDATE, two workers will happily read the same stock level.