Emulated vs Native

Emulated Versus Native Prepared Statements

pdo_mysql emulates prepared statements by default: prepare() only stores the SQL, and execute() quotes each value into the text and sends one ordinary query. With emulation off, the SQL travels once with its ? markers and the values follow separately in MySQL 524 's binary protocol (the server-side statements of Subsection 3.16.8).

One statement, two wire protocols

The listing runs the same statements on two connections, one per mode, then reads each session's Com_stmt_prepare counter of statements the server itself prepared:

Four behaviors that change with ATTR_EMULATE_PREPARESPHP

<?php
$emulated = require 'db.php';
$emulated->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$native = require 'db.php';                  // a second connection, emulation off
$tests = [
  'LIMIT bound as a string' => fn($pdo) => $pdo->prepare('SELECT 1 FROM products LIMIT ?')
                                               ->execute(['2']),
  'one :name used twice' => fn($pdo) => $pdo->prepare('SELECT 1 FROM products WHERE sku = :s
                                            OR title = :s')->execute(['s' => 'x']),
  'typo caught by prepare' => fn($pdo) => $pdo->prepare('SELEC 1 FROM products') && false,
  'server-side prepares' => fn($pdo) => $pdo->query("SHOW SESSION STATUS
                                            LIKE 'Com_stmt_prepare'")->fetch()['Value'],
];
printf("%-24s %-9s %s\n", '', 'emulated', 'native');
foreach ($tests as $name => $test) {
  $r = [];
  foreach ([$emulated, $native] as $pdo) {
    try { $r[] = var_export($test($pdo), true); }
    catch (PDOException $e) { $r[] = $e->getCode(); }
  }
  printf("%-24s %-9s %s\n", $name, ...$r);
}

Output

                         emulated  native
LIMIT bound as a string  42000     true
one :name used twice     true      HY093
typo caught by prepare   false     42000
server-side prepares     '0'       '4'

Emulation turned LIMIT ? into LIMIT '2', a syntax error, allowed a named marker twice, and deferred the typo to execute(). Natively even query() is prepared on the server, hence four. Emulation saves a round trip per statement and quotes safely while the character set is in the DSN, but native mode reports errors where they happen and never mixes data into SQL, so db.php uses it.

MENU

Emulated vs Native