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.

Leckie

How exactly did you imagine "turning emulation off would save a lot of binding"? – Eggshaped

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.

Eggshaped

yes, it's change how to bind as example in question. if not turning off you should bind seperatly. – Gamic

"You should only turn/leave emulated prepares on if your database (-driver) doesn't support native parameterized queries", i think this is wrong, disabling pdo emulate prepared statements add a layer of security by letting mysql filter prepared values not pdo itself. michaelseiler.net/2016/07/04/… – Masjid

@Masjid Uhm, yes, that’s exactly what I’m saying as well. – Eggshaped

Answer 2

Nope, there are no pros or cons worth mentioning.

Obviously turning emulation off would save a lot of binding.

Not that much. You can use binding only for such cases with LIMIT and continue using lazy binding in execute() for all other cases even with emulation turned on.

Jerkin