Loading…

Ready?

Click the button below to find out more about how Brema Web Solutions can help your business succeed!
Go!

PHP, addslashes, Postgresql, and MySQL

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().

Old Code:

$q = sprintf(“SELECT first_name, last_name, address,phone FROM people WHERE last_name=’%s’), addslashes($last_name));

New Code:

$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.