skip to content

In PHP, what changes when PDO::ATTR_EMULATE_PREPARES is on versus off, and why do many MySQL projects turn it off?

level: seniorimportance: should knowfreq 42%

answer

  1. client-side quoting vs server-side parameters
  2. pdo_mysql emulates by default, pdo_pgsql does not
  3. errors surface at execute(), not prepare()
  4. LIMIT '10' under emulation
  5. charset from the DSN, not SET NAMES

basics

~20 s

With emulation on, PDO quotes each value into the SQL and sends one query; with it off, the driver sends template and values separately. pdo_mysql emulates by default; turning it off gives server-checked templates and typed parameters.

solid answer

~50 s

`PDO::ATTR_EMULATE_PREPARES` decides who combines template and values. **On**, PDO parses the template, quotes each bound value with the driver's quoting routine and sends a finished SQL string: one round trip, but `prepare()` never contacts the server, so SQL errors appear only at `execute()`, and string-bound values arrive as quoted literals, which is why `LIMIT ?` fails unless bound with `PARAM_INT`. **Off**, the driver uses the server's native prepared statements: the template is checked at `prepare()` and values travel as separate typed parameters. `pdo_mysql` emulates by default and `pdo_pgsql` does not. MySQL projects often switch emulation off for server-side checking and value handling that does not depend on client-side quoting; the costs are an extra round trip for one-shot queries and no reuse of a `:name` marker. With emulation left on, the charset must come from the DSN so quoting is correct.

code

php · 15 lines
php
<?php
declare(strict_types=1);

$pdo = new PDO('mysql:host=db;dbname=shop;charset=utf8mb4', $user, $password, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,   // native prepares: server checks the template
]);

var_dump($pdo->getAttribute(PDO::ATTR_EMULATE_PREPARES));   // bool(false)

try {
    $stmt = $pdo->prepare('SELEC id FROM products');         // fails here when native
} catch (PDOException $e) {
    echo $e->getCode(), "\n";                                 // 42000
}

go deeper

for a junior

Know that PDO can either emulate prepared statements itself or use the database's native ones, and that MySQL's driver emulates by default.

for a middle

Explain what differs between the modes: error timing, LIMIT quoting, named-marker reuse and round trips.

for a senior

Choose the setting deliberately for MySQL, set the charset in the DSN, and account for infrastructure that cannot hold server-side prepared statements.

for a principal

Standardise the emulation setting across services and databases so query behaviour and error handling do not differ by driver default.

## What emulation means A **native** prepared statement is a feature of the database protocol: the client sends the SQL template, the server parses it and returns a handle, and each execution sends only the parameter values. An **emulated** prepared statement is done entirely inside PDO: PDO finds the markers, quotes each value with the driver's quoting routine, substitutes them, and sends one ordinary query. `PDO::ATTR_EMULATE_PREPARES` (a bool, typed as such since PHP 8.4) chooses between them. It is set in the constructor options or later with `$pdo->setAttribute()`, and read back with `getAttribute()`. | Driver | Default | |---|---| | `pdo_mysql` | emulation **on** | | `pdo_pgsql` | emulation **off** (native) | ## What changes | Aspect | Emulated (on) | Native (off) | |---|---|---| | who combines SQL and values | PDO, by quoting values client-side | the server, values sent separately | | round trips for a one-off query | one | prepare, then execute | | when a syntax error appears | at `execute()`; `prepare()` does not contact the server | at `prepare()` | | `LIMIT ?` bound as a string | becomes `LIMIT '10'` and is rejected | the value travels as a parameter | | same `:name` used twice | allowed | not allowed; use two names | | quoting depends on | the connection charset known to the client | nothing client-side | Two rows need explanation. - **Error timing.** With emulation, `$pdo->prepare('SELEC ...')` succeeds and returns a statement, because nothing has been sent; the `PDOException` comes from `execute()`. Code that expects prepare-time failures behaves differently. - **Charset.** Emulated quoting escapes values using the character set the client library believes the connection uses. That is the charset given in the DSN (`charset=utf8mb4`). Changing it later with a `SET NAMES` query changes the server's view but not the client's escaping, which is why the charset belongs in the DSN. ## Since PHP 8.1: result types Before PHP 8.1, MySQL results under emulation came back as strings, while native prepares returned ints and floats. Since 8.1, `pdo_mysql` returns native PHP ints and floats in both modes, and `PDO::ATTR_STRINGIFY_FETCHES` restores the old string behaviour. That removed a classic difference in `===` comparisons between the two settings. ## Since PHP 8.4: the SQL parser Emulation, and the rewriting between `?` and `:name` styles, depends on PDO scanning the SQL for markers. Before 8.4 the scanner did not understand driver-specific quoting, such as backslash-escaped quotes in MySQL strings, which could misplace markers. Since 8.4 each driver supplies its own parser, so markers inside literals and comments are ignored reliably. ## Why many MySQL projects turn it off 1. **The server validates the template at `prepare()`,** so errors surface where the statement is built. 2. **Typed parameters.** Integer positions such as `LIMIT` and `OFFSET` work without remembering `PARAM_INT` on every call. 3. **No dependence on client-side quoting.** Values never pass through PDO's escaping, so the charset caveat and parser edge cases do not apply. Reasons to keep it on, or to decide per statement: - **Round trips.** A statement executed once pays for a separate prepare step when native. - **Named-marker reuse.** Queries that repeat `:term` in several places need renaming first. - **Infrastructure.** Some connection-pooling setups do not carry server-side prepared statements across the connections they multiplex, so native prepares can fail there. ## Checking the effective setting - `$pdo->getAttribute(PDO::ATTR_EMULATE_PREPARES)` returns the current value for the connection. - `$stmt->debugDumpParams()` prints a `Sent SQL` section with the values substituted only when the statement was emulated, which makes the mode visible from a single statement. - A `prepare()` that succeeds on obviously broken SQL is itself a sign that emulation is on. ## A practical setting For a MySQL-backed product search, set `PDO::ATTR_EMULATE_PREPARES => false` in the constructor options, set the charset in the DSN anyway, and keep typed `bindValue()` for integers so the code stays correct if the setting is ever flipped back.

  • Why does $pdo->prepare() with a typo in the SQL succeed on a default MySQL connection?
    `pdo_mysql` emulates prepares by default, and an emulated `prepare()` does not contact the server, so nothing checks the template yet. The syntax error arrives as a `PDOException` from `execute()`. With `ATTR_EMULATE_PREPARES => false`, the server parses the template at `prepare()` and the error is raised there.
  • Does turning emulation off change the PHP types of fetched MySQL values in PHP 8.5?
    Not any more. Since PHP 8.1, `pdo_mysql` returns ints and floats as native PHP types with emulated prepares too, matching native ones. Before 8.1 emulated results were strings. `PDO::ATTR_STRINGIFY_FETCHES` turns everything back into strings if old code depends on that.

Emulation is a clerk who fills in a paper form for you and hands over the finished sheet; native prepares hand the office a blank form and the answers on a separate card. The first saves a trip to the counter, but mistakes on the form are only spotted when the sheet is handed in.

saying these in an interview costs you the question

  • PDO always uses the server's native prepared statements for MySQL
  • An emulated prepare() already validates the SQL on the server
  • Emulated prepares interpolate values without any quoting
  • Running SET NAMES after connecting fixes the charset used for emulated quoting
  • Native prepares allow the same named placeholder several times