Why wouldn't I disable PDO::MYSQL_ATTR_DIRECT_QUERY?

Question

I stumbled upon the (imho rather poorly documented) fact that by default PHP PDO has the flag MYSQL_ATTR_DIRECT_QUERY enabled for its MySQL driver.

This means rather than actually use prepared statements, it emulates the behaviour of prepared statements. This means it replaces the placeholders client-side with escaped values and just sends the full query to the database as-is.

There used to be a good reason to do this, as under older versions of MySQL prepared statements would bypass the query cache. But this hasn't been the case for a while now. There's still a slight performance advantage, as it reduces the number of roundtrips from your app to the database, but I'm not sure that's worth it?

The obvious downside to using this method is that we're still relying on client-side escaping, which is usually a bad idea. I've run into weird issues with mysqli_real_escape_string in the past where invalid characters were allowed into a query due to some character set misconfiguration. I'd rather not have something like that happen again.

I'm only finding half-truths and superficial comments on this issue (e.g. 'yeah, you can enable that' or 'it'll cause "issues"'). Looking for a real reason why I wouldn't switch this off? Is using actual prepared statements in MySQL/PDO in anyway incompatible with emulated prepared statements?

Part of the reason why I'm asking is because we use PHPActiverecord, which relies on PDO. It doesn't ship with tests and I don't want it to suddenly break in production because switching off emulated prepared statements subtly changes behaviour in certain edge cases or something.

(as a side-note, before anyone brings it up: checking PDO::ATTR_EMULATE_PREPARES won't work as it's not actually (fully) implemented for the MySQL driver, you have to check PDO::MYSQL_ATTR_DIRECT_QUERY instead. Yeah, that one took me a while.)

To clarify: I am wondering if there is a good reason to NOT switch off this behaviour. Not reasons why I shouldn't be caring in the first place.

Answer 1

The question is based on a invalid assumption: that emulate prepares are not fully supported. (They are fully supported).

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

Proof in the source code: connection handling and Attribute Getter Code and Attribute Setter Code.

The setter code is the most telling. 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 absolutely turn off emulated prepares, see this answer.