Properly escaping MySQL queries in PHP

I'm on various boards and such and from time to time people run into issues where they're trying to insert something into MySQL via a raw query and they inevitably run into that pesky apostrophe and the query dies.

Then almost always someone comes along to tell them that they need to use addslashes().

This is wrong.

Ideally you really want to use prepared statements (mysqli and PDO extensions), but let's assume for now you're throwing caution to the wind and you're going to do it the old fashioned way.

If you're using the mysql extention, you should use mysql_real_escape_string() around all of your variables which are not cast as integers. But actually, you shouldn't be using this function because mysql_* is deprecated, way deprecated. Instead you should be using…

mysqli which is faster, better, sexier, everything you want in a wom… extension. In this case we have the more logical name mysqli_escape_string() or you can use the back-to-goofiness-again method in the mysqli class $mysqli::real_escape_string() and it works the same way.

One issue is that with both of the above functions you have to actually be connected to the server to use them, that's because it escapes based on your connection chartype and some other stuff.

However assuming you're not too worried about potential unicode issues (I've yet to have any, supporting Serbian and Hungarian) you can always make your own function to escape based on what MySQL requires:

But there's always a potential danger in doing things yourself and I actually don't have proof the above is faster than the connection required escape functions, so just use prepared statements ideally.

Leave a Reply