Through all my years of programming PHP I have been pretty good about escaping variables when they are included in a database query. The first method I learned was simply using addslashes(), I was unaware that MySQL and Postgresql have their own PHP functions to escape strings for a while. Using addslashes() has recently came back to bite me. Postgresql started throwing errors on what I thought were perfectly good queries. This caused me a lot of stress and panic as clients are e-mailing me with screenshots of screens full of errors. In the error is what looks like perfectly good SQL, for example something like this:
SELECT first_name, last_name, address,phone FROM people WHERE last_name=’O\’Reilly’
The apostrophe looks properly escaped. I was still using addslashes() even after I learned about Postgresql and MySQL’s escape functions because it had been working so well. So remembering those functions I changed my code from using addslashes() to using pg_escape_string() and mysqli_real_escape_string().
$q = sprintf(“SELECT first_name, last_name, address,phone FROM people WHERE last_name=’%s’), addslashes($last_name));
$q = sprintf(“SELECT first_name, last_name, address,phone FROM people WHERE last_name=’%s’), pg_escape_string($last_name));
It’s an easy change to make, a simple Find/Replace can fix it reliably and quickly. With the applications where I have used MVC it’s an even easier since I generally only have to change one line of code in the base model class. Honestly since the change has worked flawlessly I haven’t even checked to see what the different in the output between addslashes() and pg_escape_string() is.
I don’t think I’ve seen this issue with MySQL but it is consistently a problem on some of my older PHP applications that use Postgresql.