{"id":426,"date":"2012-03-09T10:20:56","date_gmt":"2012-03-09T15:20:56","guid":{"rendered":"http:\/\/www.jetmore.org\/john\/blog\/?p=426"},"modified":"2012-03-30T14:09:33","modified_gmt":"2012-03-30T18:09:33","slug":"commented-sql-named-parametes-and-phps-pdo-module","status":"publish","type":"post","link":"https:\/\/www.jetmore.org\/john\/blog\/2012\/03\/commented-sql-named-parametes-and-phps-pdo-module\/","title":{"rendered":"Commented SQL, Named Parameters, and PHP’s PDO module"},"content":{"rendered":"

I can’t include code in this post because I don’t own it, but I think I can discuss the concept, which I found really interesting.<\/p>\n

A coworker had the idea to extend the PDO<\/a> prepare()<\/code><\/a> method to aid in debugging. I’m not sure if he implemented something he found on the net or it was an original idea, but I really like the results. It prepends, to every query executed, an SQL comment (ala “\/* comment *\/<\/code>“) including the username of the logged in user and the URL of the current request. This usually makes no difference, but if you’re in a really tough spot in debugging you can turn on mysql general_log, run the session that is having problems, and easily see every query that the was executed on behalf of the user having problems.<\/p>\n

<\/p>\n

This has been working for over a year, and through three software releases.<\/p>\n

Yesterday a support engineer brought me a problem involving a little-used corner of our software. This particular function would ultimately make a GET with a piece of (properly encoded) arbitrary text (something along the lines of “append this string to this log”, something so simple there wasn’t any reason not to make it a GET).<\/p>\n

The engineer found that on some strings we would get PHP errors. Specifically “SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters<\/code>“. I dug in and found two things. First, the text in question that was causing this looked something like “foo_:_bar<\/code>“. The colon was the key. Second, the actual query that was blowing up did not have the URL in it, it was a very innocent query looking up session data. It did have one positional parameter in it (‘?’). I temporarily rewrote the query to embed the single param instead, and the error changed to “SQLSTATE[HY093]: Invalid parameter number: no parameters were bound<\/code>“.<\/p>\n

Clearly the PDO parser thought there was a named param in my query, and just as clearly I could see that there wasn’t. I then found the debugDumpParams()<\/code><\/a> function, and prepare thought my query looked something like this:<\/p>\n

\r\n\/* https:\/\/server\/path\/to\/code\/arbitrary_foo_:_bar *\/\r\nSELECT count(*)\r\n  FROM Foo\r\n<\/pre>\n
Ah.  That’d explain it then.<\/p>\n
The solution was very simple, just wrap the URL in parent::quote()<\/code> in the extended prepare()<\/code> function.  It was very interesting to me though that PDO still scans SQL comments and at first glance I thought it was a bug.  I decided that this was actually a feature though.  It allows you to comment out portions of your SQL without having to alter the execute()<\/code> to match.<\/p>\n
One open question I have through all of this is “what is the canonical definition of what a named parameter looks like and where it can  be used?”  I can’t find this documented anywhere.  If you know please let me know.  It is not as simple as saying “a colon causes this problem”, because it doesn’t.  Here’s my empirical findings:<\/p>\n