mysql_real_escape_string() Requires DB Connection

You probably won’t have noticed much if anything different about Soylent Red in the last day or so—I think the new URLs are the most visible change—but I did make quite a significant code change yesterday evening. The diff weighed in at over 2000 lines in fact. All of the code worked on my local testing setup, but I had a problem as soon as I tried to post a new entry on the live site. Only the date, the tags and the comment status (open or closed) were saved.

After a bit of wrangling—I had to check in a new copy of the code every time I wanted to test something—I discovered that the problem lay in the use of the mysql_real_escape_string() function, which is used to protect against injection attacks when putting text in a database. It was news to me that this PHP function actually calls an SQL function to do the work for it, so it requires an existing database connection.

After my big code changes there was one part of my code where I happened to try to use the function to create my query before the code to establish a connection to the database server. In this event PHP will try to connect as if calling mysql_connect() with no arguments, and failing that it does the only thing it can do: it throws a warning while returning an empty string. I have my live site set not to display errors, so it looked to be silently failing. Meanwhile my local testing setup never came across the warning because it is always possible to establish a connection (to a server on localhost with no username or password).

I love these bugs that lead to new learning.