Why wouldn't I disable MYSQL_ATTR_DIRECT_QUERY?
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.)
No comments:
Post a Comment