Recently I have been updating one of my older sites to a completely new code base, and with that new code I’ve switched all old MySQL calls from the old mysql_* functions to PDO. I’ve used PDO for quite a while in all of my more recent projects, but the code base on this particular project was about 10 years old, so it was in dire need of an upgrade.
As I began testing my code, I noticed that one of my data calls was failing – returning a “false” and nothing more. Taking the query and the bound parameters, I put it directly into MySQL and got a result set without any errors, so I knew the problem was somewhere in my code, rather than in the data query. But I wasn’t getting any errors other than that the execute method was returning “false.”
A quick look at my database connection class, and I realized that I hadn’t added in any PDO attributes for error reporting. I quickly added in the following to the connection (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION) and tried my query again. The response was that not all variables were bound in the query, which at first made me wonder, since I had bound all the variables.
Only I hadn’t. After looking at my database connection class in further detail, I had left myself a note saying that I had switched PDO::ATTR_EMULATE_PREPARES to ‘false,’ and linked to the documentation, which clearly states that when you turn off emulation, you need to pass in a unique parameter marker for each variable you pass to the database in the prepared query. In my case, I was using the same parameter marker twice in the query because I had a UNION. Quick modification of the query and I was back in the running. Personal code documentation – and actually reading the notes I left for myself – FTW (even if it was almost a year after the fact…).
Why switch emulation to ‘false’?
The main reason for this is that having the database engine do the prepare instead of PDO is that the query and the actual data are sent separately, which increases security. This means when the parameters are passed to the query, attempts to inject SQL into them are blocked, since MySQL prepared statements are limited to a single query. That means that a true prepared statement would fail when passed a second query in a parameter.
The main argument against using the database engine for the prepare vs PDO is the two trips to the server – one for the prepare, and another for the parameters to get passed – but I think the added security is worth it. Also, at least in the case of MySQL, query caching has not been an issue since version 5.1.