3 Replies - 12067 Views - Last Post: 23 September 2013 - 09:52 AM

#1 Atli  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

PDO_MySQL always emulates prepared statements...

Posted 16 September 2012 - 05:01 AM

So I've been looking up stuff on prepared statements and persistent connections this morning, and I came across Request #54638 in the PHP bug tracker. It states:

Quote

The PDO_MySQL driver defaults emulate_prepare to 1, which forces all prepared
queries to be emulated by the driver. This means that even though the client
library (mysqlnd or libmysql) may support prepared statements, PDO will never
really use them.


It goes on to say that this was originally done like this because MySQL 5.1.16 and earlier don't cache prepared statements and this works around that. I can sort of understand that... But it's been like five and a half years since that was fixed, and we are still defaulting to this!

Does anybody know what kind of performance and security implications this has? The main reason I use prepared statement in the first place is to avoid any possibility of SQL injection that might occur due to a bug in the escape code. That assurance is - theoretically at least - lost when this is enabled. I'd also imagine that the otherwise unnecessary escaping + injecting + parsing that occurs could have some small performance penalty. (Which I'm planing to test later.)


What I find most interesting about this is the lack of documentation. While the constant that can be used to disable this behavior (PDO::ATTR_EMULATE_PREPARES) is listed in the PDO predefined constants list, it is neither explained there nor in the PDO_MySQL page. I didn't even know, nor suspect, that this was an issue before I accidentally stumbled upon that feature request.

Is This A Good Question/Topic? 4
  • +

Replies To: PDO_MySQL always emulates prepared statements...

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: PDO_MySQL always emulates prepared statements...

Posted 22 January 2013 - 04:33 PM

To be honest, prepared statements as a performance enhancement is a bit of a joke. Unless you're running a query over and over in the same session, you're not going to get any performance improvement, as the only corner you're cutting is the creation of the execution plan, which is stored for that DB connection only. The only time you're going to benefit from this across sessions is if you use persistent connections, which are a complete pain in the rear-end, and also put the onus of DB connection management on PHP, which is a potential nightmare scenario (I brought down my live server doing this).

Something to keep in mind is that MySQL and PostgreSQL do not cache execution plans globally. MSSQL does (I believe Oracle does as well), but even MSSQL is dodgy about it. Caching execution plans of stored procedures works perfectly, but caching EPs of ad-hoc queries is flaky, with EPs sometimes being generated multiple times for the exact same query text.

If you're looking for performance enhancement in your queries, there are two server-side options you can take:
  • Retrieve all the data you need up front (this can have negative consequences if your dataset is particularly large)
  • Store the retrieved data in a PHP cache (memcache, singleton property, etc) and check to see if the data is already stored before going to the DB again (this can have negative consequences if your data changes state during the same session, though you can of course invalidate your cache if that is an issue)

This post has been edited by e_i_pi: 22 January 2013 - 04:34 PM

Was This Post Helpful? 2
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: PDO_MySQL always emulates prepared statements...

Posted 20 August 2013 - 04:26 PM

Found an article that may be of interest here, though it is ostensibly about the application pgBouncer, a connection pooler for PostgreSQL:

What is the point of bouncing?

About half the way down it talks about PDO::ATTR_EMULATE_PREPARES, and why you should set it to TRUE. In a nutshell, here's the use case of why not emulating is bad:

  • PHP creates a connection to PostgreSQL via pgBouncer (Conn#1)
  • PHP uses Conn#1 to prepare a statement (Stmt#1)
  • PHP creates another connection to PostgreSQL via pgBouncer as Conn#1 is in use (Conn#2)
  • PHP uses Conn#2 to prepare another statement (Stmt#2)
  • PHP finishes it's scripts, so both connections go back into the pool for later use

Now, here's where it gets interesting. There is no way to check in a connection whether or not a statement has been prepared already. Because of this, one of two things can happen:

  • PHP wants to execute Stmt#1, but has to assume that it isn't yet prepared. PHP picks up a connection via pgBouncer, and is assigned Conn#1. Upon trying to prepare Stmt#1 again, the error prepared statement "Stmt#1" already exists occurs, OR
  • PHP wants to execute Stmt#1, and assumes that it is already prepared. PHP picks up a connection via pgBouncer, and is assigned Conn#2. Upon trying to execute the unprepared Stmt#1, the error prepared statement "Stmt#1" does not exist occurs

So you can see why emulation has it's advantages when it comes to connection pooling, at least with connection pooling to PostgreSQL via pgBouncer. I would imagine the same problem exists with other connection poolers using prepared statements. There are two workarounds to this issue, at least in PostgreSQL:

  • PDO::ATTR_EMULATE_PREPARES = TRUE
  • Use stored procedures (aka Functions in PostgreSQL), which is advantageous in that execution plans tend to be globally cached1, and are safe from SQL injection as they are parameterised2


1Execution plans for stored procedures are globally cached in MSSQL3, cached by connection in PostgreSQL4, not sure about MySQL or Oracle
2Unless you do something silly, like accept a user input text parameter, build that into an SQL string, and execute that string via an EXEC() function
3MSSQL Execution Plan Caching and Reuse
4PL/pgSQL Under the Hood - 39.10.2 Plan Caching

This post has been edited by e_i_pi: 20 August 2013 - 04:34 PM

Was This Post Helpful? 1
  • +
  • -

#4 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3541
  • View blog
  • Posts: 10,250
  • Joined: 08-June 10

Re: PDO_MySQL always emulates prepared statements...

Posted 23 September 2013 - 09:52 AM

I might note that PDO::ATTR_EMULATE_PREPARES is not necessarily supported in PDO_MySQL/*/. I recently tested an XAMPP install for all PDO::ATTR_* I could find, and more than half of them (including PDO::ATTR_EMULATE_PREPARES) wasn’t supported at all.


* - might have something to do with the new mysqlnd (MySQL Native Driver) library used now.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1