PHP magic_quotes vs mysql_escape_string vs mysql_real_escape_string

PHP is one of the more popular programming languages on the web today. Many sites, and web applications use PHP because of it’s very easy to use with popular database software like MySQL. Because of this, PHP can also be abused, and cause security issues with your site via a SQL injection. To try to prevent SQL injections, the functions magic_quotes, mysql_escape_string, and mysql_real_escape_string have been integrated into PHP. Which one should you use?

First, to understand the three functions, you have to understand how they work. Magic_quotes basically runs all get, post, and request variables through add_slashes function. The add_slashes function itself, adds a backslash before a single quote (‘), double quote(“), backslash (\), and Null byte. If magic_quote is turned off, you can get the same effect by running your varible through the add_slashes function.

Mysql_escape_string and mysql_real_escape_string are very similar. The only differences are that you can use mysql_escape_string while not connected to a database, and it doesn’t respect the current charset setting. PHP manual at php.org, suggest that you should use mysql_real_escape_string anytime you want to insert information in to a database. Both of these functions escapes backslashes (\), new rows (\r), new lines (\n), single (‘) and double (“) quotes. They also escape the characters \x00, and \x1a.

How are all three functions used? Well magic_quotes is passive, meaning that it is an option that is turned on in your php config or htaccess file by adding magic_quotes_gpc = On. Mysql_escape_string and mysql_real_escape_string must be used along with a variable, like all other functions. If you were to use the mysql_real_escape_string, your code will look like this mysql_real_escape_string($var).

So which one is more secure? Well magic_quote is considered deprecated as of PHP 5.3.0 and has been removed in version 6.0. PHP.org even goes as far as saying “Relying on this feature is highly discouraged.”. The reason being that by using magic_quote, someone can still execute a SQL Injection. For most PHP programmers, they all consider the use of magic_quote as a horrible way to code. The idea is that programmers who use it are either inexperienced, lazy, or don’t care about security. On top of this, not all data that is passed onto other pages via GET/POST/REQUEST are used for database insertion. You may have a script that ask for a user’s name, which has a single quote in it. When displaying thie varable on the screen now, it will have a backslash before the quote. So magic_quote cause issues with your site’s output that is unintended.

Overall, if you are working with databases, you should use mysql_real_escape_string. This will ensure that your code is safe from SQL injections. Take the time to write the extra code, because if you don’t, the future may cause you more of a headache.

Leave a comment