MySQL 8.0, PDO::ATTR_EMULATE_PREPARES, and the int-vs-string trap

A queue worker on PHP 8.2 / MySQL 8.0.36 started silently matching zero rows on a lookup that had passed staging. The table was small — about 120k rows in orders, indexed on (tenant_id, external_id). The query came from a repository method that built it with a bound parameter:

$stmt = $pdo->prepare(
    'SELECT id, status FROM orders WHERE tenant_id = ? AND external_id = ?'
);
$stmt->execute([$tenantId, $externalId]);

On staging $externalId was a string. In the worker it arrived from a decoded JSON payload and was sometimes an integer. When it was an integer, the query returned nothing, even though external_id is VARCHAR(64) and the value existed as a string.

Check what the connection is actually doing before touching the query:

var_dump($pdo->getAttribute(PDO::ATTR_EMULATE_PREPARES));
var_dump($pdo->getAttribute(PDO::ATTR_STRINGIFY_FETCHES));

If emulation is on, PDO does not send a server-side prepared statement. It interpolates the values client-side into a string and sends a plain query. The integer goes out as 42, unquoted. MySQL then compares VARCHAR to a number and applies numeric coercion on the column: external_id values like '0042', '42 ', or any non-numeric string stop matching the way the index expects. With emulation off, the parameter is sent typed in the binary protocol and bound as the string you passed.

My fix was two lines at connection setup, not a rewrite of the repository:

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

And I cast at the boundary: (string) $externalId in the DTO hydration, so a numeric JSON value cannot leak into a string column. The cost is real: emulation off means one round trip per statement on first prepare for the connection, and some hosts with old libmysqlclient or odd proxy setups will break on named parameters used twice in one statement. MySQL 8.0 with mysqlnd handles it fine; MariaDB 10.4 behind a connection pooler is the case I’d test first.

Where this fails: if the column is genuinely numeric and you pass strings, you get the mirror bug — MySQL coerces the string to a number, leading zeros disappear, and '007' matches 7. Turning emulation off does not protect you from a schema that disagrees with your PHP types; it only stops PDO from silently rewriting the comparison. Also, ATTR_EMULATE_PREPARES is per-connection — a long-lived worker that reconnects mid-job can end up on a fresh handle with different settings if the factory does not reapply them.

What does your connection factory do on reconnect, and do you assert the emulation flag in a test or only in the DSN string?