PDO’s MySQL driver emulates prepared statements by default. prepare() does not talk to the server; PDO keeps the string, and execute() substitutes the values into it with its own quoting and sends one complete query. The safety usually attributed to prepared statements is, in that mode, PHP’s quoting function and nothing else.
$pdo = new PDO('mysql:host=localhost;dbname=catalogue;charset=utf8', $user, $pass, array(
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
));
// works with emulation on, syntax error with it off:
// execute() binds every value as a string, and LIMIT will not take '20'
$stmt = $pdo->prepare('SELECT sku, price FROM products ORDER BY sku LIMIT ?');
$stmt->execute(array($limit));
// bind it with a type, or keep it out of the statement entirely
$stmt->bindValue(1, (int) $limit, PDO::PARAM_INT);The charset in the DSN matters more than it looks while emulation is on, because PDO quotes according to the connection charset it believes is in use — set later with a SET NAMES query, the driver never learns about it and quotes for the wrong encoding, which is the mechanism behind the multi-byte injection cases. Turning emulation off hands the parameterising to the server and the question disappears. What you give up is convenience: execute() with an array binds everything as PDO::PARAM_STR, so LIMIT ? and OFFSET ? need explicit bindValue() calls with PARAM_INT. Server-side prepares also cost an extra round trip, so a statement prepared once and executed once is now slower — and one prepared once and executed a thousand times is faster.