Brendan & Tiffany Sullivan

I’m honestly surprised this hasn’t bit me before now. I was working on some OLD code that didn’t have much of any escaping built into it. Ok, I take that back. It had /no/ validation methods whatsoever. So I’m rewriting a good chunk of it so that doing things like entering a person’s name that happens to have an apostrophe in it actually works and doesn’t break the SQL query.

So, you take a query in php with no validation and add mysql_real_escape_string() to the values you are inserting/updating right?

Well, it turns out it’s not /quite/ that easy if the lovely php magic_quotes_gpc is on. The insert or update will succeed (albeit with nasty escaping characters in your data). But now go try and select row (imagining your searching for a name) using a query such as :

“SELECT * FROM guest WHERE name LIKE ‘%”.mysql_real_escape_string($name).”%’”;

MySQL at least will not find your data. If the name field you were searching for contained “test\’s name” and you searched for “test’s”, the escape function above would insert the backslash and if you viewed your MySQL log, it would appear that MySQL had gone off the deep end. You print your table and clearly see “test\’s name”, but your query (truncated to the relevant portion) ” name LIKE ‘%test\’s%’” returns zero results.

My first thought was to “escape the escape character”. So like a dumbass, I try this mess: addslashes(mysql_real_escape_string($name)). I figured there’s no way that they would make you jump through this many hoops to deal with escaped data, and in that regard at least I was right. It’s much, MUCH easier than all that. (For the record, not even the previous monstrosity worked. Playing with queries manually in MySQL’s CLI, i found if you ran a search ” LIKE ‘%test\\\\’s%’ ” it would FINALLY find it. But that looks terrible, and there’s no way that such a commonly used language would get that way if it required ugly unreadable code like that.

So, the solution I found after plenty of reading:

#1) If you have full access to your server (dedicated, VPS, your home test server), in /etc/php5/apache2/php.ini (for Ubuntu Server at least) change the line:

magic_quotes_gpc = On

to

magic_quotes_gpc = Off

Be sure to then restart your apache service using

sudo /etc/init.d/apache2 restart

#2) if you do not have root access to your server (Shared hosting, etc), you can use a rule in your webroot’s .htaccess. Add the line:

php_flag magic_quotes_gpc off

preferably in the top of the file so it’s nice and easy to find. I believe this will only work on linux apache servers. Windows does not support .htaccess for anything but password authentication I believe. But you should be running your apache server on linux anyhow

So, this was a really newb mistake, but I learned my lesson and figured maybe this could help someone else out.