Why don't I disable PDO :: MYSQL_ATTR_DIRECT_QUERY?

Question

I came across the (imho pretty poorly documented) fact that by default PHP PDO has the MYSQL_ATTR_DIRECT_QUERY flag for its MySQL driver.

This means that instead of using ready-made instructions, it emulates the behavior of prepared statements. This means that it replaces the client placeholder database with shielded values ​​and simply sends the full query to the database as is.

There used to be a good reason to do this, since in earlier versions of MySQL, prepared statements would bypass the query cache. But this is not so long ago. There is still a slight performance advantage, as it reduces the number of hits from your application to the database, but I'm not sure what it costs?

The obvious disadvantage of using this method is that we still rely on client-side shielding, which is usually a bad idea. I had strange problems with mysqli_real_escape_string in the past when invalid characters were allowed in the query due to incorrect character set configuration. I would rather not repeat something like that.

I only find half-truths and superficial comments on this issue (for example, “yes, you can turn it on” or “it will cause“ problems ”). Looking for the real reason why I would not turn it off? Is the use of the actual prepared statements in MySQL / PDO is in any case incompatible with emulated prepared operations?

Partially I ask because we use PHPActiverecord which relies on PDO. It doesn’t come with tests, and I don’t want it to suddenly break in production, because disabling emulated prepared statements subtly changes the behavior in some cases of the edge or something like that.

(as a side note, before anyone picks it up: checking PDO::ATTR_EMULATE_PREPARES will not work like it is not (fully) implemented for the MySQL driver, instead you should check PDO::MYSQL_ATTR_DIRECT_QUERY . took me for a while.)

To clarify: I wonder if there is a good reason not to disable this behavior. Not the reasons why I should not care first.

Answer 1

The question is based on the wrong assumption: to emulate the preparation is not complete. (They are fully supported).

In fact, MYSQL_ATTR_DIRECT_QUERY is nothing more than an alias for ATTR_EMULATE_PREPARES.

Proof in source code: Connection Handling and Attribute Getter Code and Attribute Adapter Code .

The installer code is the most revealing. Namely:

 390 case PDO_MYSQL_ATTR_DIRECT_QUERY: 391 case PDO_ATTR_EMULATE_PREPARES: 392 ((pdo_mysql_db_handle *)dbh->driver_data)->emulate_prepare = Z_BVAL_P(val); 393 PDO_DBG_RETURN(1); 

For more on why you should completely disable emulation prepare , see this answer .