Php Prepared Statements Turn Emulation Off

Question

Are there any side effects to turning off emulation when using prepared statements with pdo? I'm using a select * and limiting the results which needs to be handled as an int and not a string. I can do one of two things.

$conn->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

Or to bind these variables explicitly with param type:

$stm = $pdo->prepare('SELECT * FROM table LIMIT ?, ?');
$stm->bindParam(1, $limit_from,PDO::PARAM_INT);
$stm->bindParam(2, $per_page,PDO::PARAM_INT);
$stm->execute();
$data = $stm->fetchAll();

Any pros or cons? Obviously turning emulation off would save a lot of binding.

Answer 1

Prepared statements are a feature of the low level database driver. The database accepts the query structure first and receives the variable parameters separately. Again, this is a feature actually supported by the database itself.

"Emulated prepares" means that you use the same API on the PHP-side, with separate prepare() and bind/execute calls, but that the PDO driver is just internally escaping and concatenating the strings, sending a good old long SQL string to the database. The database doesn't get to use its native parameterized query feature.

Turning emulated prepares off forces PDO to use the database's native parameterized query feature. You should only turn/leave emulated prepares on if your database (-driver) doesn't support native parameterized queries. Emulated prepares are only there to support old database (-drivers), it does not change how you bind parameters in your PHP code.

Emulated prepares may expose security flaws under certain circumstances, just as all client-side escaping and concatenation may. If the query and data remain separated all the way to the database, those flaws aren't possible.