8 March 2007
Nuances with mysql_real_escape_string
I discovered a strange bug today. In a DataAdapter class accessed from two distinct parts of a site, there is a SQL INSERT query and a SQL UPDATE query. The public side uses only the INSERT, the admin side uses only the update. Both are nearly identical. If I called the code from the public site, it ran fine. If I called it from the admin side, mysql_real_escape_string returned an empty string as the escaped value.
I wondered what was going on. I found a guy in the comments with the same problem. His explanation was that you had to have the appropriate version MySQL Library. I ran mysql_get_client_info above each instance and each was the same.
I then read the documentation for mysql_escape_string. That reference explains the difference between the two functions: mysql_real_escape_string will take a connection as its second argument to handle any quirks for particular encodings. On the public site, there is only one database connection to access the appropriate database. Parts of the admin, however, access different databases. I can only conclude that because there were multiple live database connections, mysql_real_escape_string couldn’t know which one to use and failed.
This is one of those rare cases where keeping a reference to the database connection is necessary (if you have multiple connections, anyway). Granted, if you have multiple connections, keeping references to each is important anyway, but at least to me, mysql_real_escape_string doesn’t seem like the kind of routine which would require an explicit connection.