Is it necessary to use mysql_real_escape_string(), when magic_quotes_gpc is on?

Question

To prevent SQL injection, is it necessary to use mysql_real_escape_string(), when magic_quotes_gpc is on?

Answer 1

yes its good practice to run all values that are going into your sql statement through the filter mysql_real_escape_string() its not just quotes that the filter is fixing.

it prevents injection attacks, see the example on the php manual for the method.

http://php.net/manual/en/function.mysql-real-escape-string.php

Answer 2

Looking at the documentation; http://php.net/manual/en/function.mysql-real-escape-string.php

Note: If magic_quotes_gpc is enabled, first apply stripslashes() to the data. Using this function on data which has already been escaped will escape the data twice.

You can check if magic_quotes_gpc is on, see example; http://php.net/manual/en/function.get-magic-quotes-gpc.php

Answer 3

No and yes. If magic_quotes is turned on and mysql_real_escape_string is applied, then some escapes will be doubled, which results things like "It\'s an example." I saw on some forums. For best practices, you should disable magic_quotes and use mysql_real_escape_string all the time, or even better, use a DB abstraction library.

Answer 4

Isn't mysql_real_escape_string best for when inserting data into MySQL? And you should use magic_quotes_gpc for general safety.

Answer 5

For some rare encodings, such as GBk - yes.
But you should revert it not for this reason. Magic quotes should be turned off anyway (and will be in the next PHP version). So, mysql_real_escape_string() is the only escape function is left. Note that it is not sql injection prevention function. Many many people don't understand this point: it's just a part of syntax. It must be used not to "protect" anything, but to assemble syntactically correct SQL query. And must be used every time you build your query, no matter where data come from. Sure it will protect you from SQL injections too, as a side effect.
Of course, mysql_real_escape_string() works only within quoted strings. So, if you do

$num=mysql_real_escape_string($num);
$sql="SELECT INTO table SET data=$num"; /BAD!!!

It will protect nothing. If you going to use numbers unquoted, it must be cast to the proper type obligatory, like this:

$num=intval($num);
$sql="SELECT INTO table SET data=$num"; /GOOD

If you want to get rid of all these complexities, you can use prepared statements, though you will need to switch your mysql driver to mysqli or PDO.

Note that proper syntax nor prepared statements would not help you with query parts other than literals. You can't escape Identifiers or operators. If you happen to use these parts dynamically, they must be hardcoded in your script, like this (for the ORDER BY clause):

$orders=array("name","price","qty");
$key=array_search($_GET['sort'],$orders));
$orderby=$orders[$key];
$query="SELECT * FROM `table` ORDER BY $orderby";

or this (WHERE clause)

$w=array();
if (!empty($_GET['rooms'])) $w[]="rooms='".mysql_real_escape_string($_GET['rooms'])."'";
if (!empty($_GET['space'])) $w[]="space='".mysql_real_escape_string($_GET['space'])."'";
if (!empty($_GET['max_price'])) $w[]="price < '".mysql_real_escape_string($_GET['max_price'])."'";

if (count($w)) $where="WHERE ".implode(' AND ',$w); else $where='';
$query="select * from table $where";