Is emulated SQL query preparation less secure?

Question

I have been looking at PDO attributes, specifically at resources (some are linked at the end of this post) saying that emulated query preparation is less secure than using real prepared statements, such that you should disable emulation with this:

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

But I don't see any examples on why, and the argument comes down to "it may be possible, so do it this way."

Is there any known SQL injection that has happened or other proof of concept to show that this is the case, that one way is more secure than the other?

---

Related from Stack Overflow:

• How can I prevent SQL injection in PHP?
• PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?

_{PDO can be used to connect to more than one type of database, so this question is not restricted to MySQL.}

Answer 1

The only currently known SQL injection vulnerability in PDO parameterized prepared statements using the default of emulating prepared statements is if the client's and server's locale is out of sync.

Since locale is negotiated during the initial connection setup, and persists for that connection even if the server's default is changed, in order to get the locale out of sync, you need to have a successful Man-in-the-Middle attack. And if you do have a MitM attack, then the emulated prepared statements mode is the least of your worries, as the attacker can send any arbitrary commands and responses, without needing to go through the trouble of SQL injections.

While theoretically, there may be a slight decrease in security from using emulated prepared statements, there is no practical difference since exploiting the emulated mode requires pwning the connection anyways.

Still, there is very little practical benefit from leaving the emulated mode on, and if you ever get to a point where that tradeoff matters, your site will have already faced several other scaling issues, and you'll know how to do the necessary research to make the best choice for your system. It won't hurt to turn emulated mode off, in the sake of security... but it won't help either, and anyone who tells you one way or another without a thorough understanding is engaging in cargo cult programming.

Also, if you're still paranoid (which the people here who answer security questions tend to see as the only sane state of mind), know that the PDO emulation of prepared statements only applies to the MySQL client engine. Other database client engines in PDO, such as PostgreSQL, never emulate prepared statements.