PHP what happens when a string is double times mysqli_real_escape_string

Question

I'm using mysqli.

When I echo mysqli_real_escape_string($db,mysqli_real_escape_string($db,'"'));

which one of those will be the output: 1. \" 2. \\\" ?

Is there a safe way to check whether a string has been already escaped?

Unfortunately, I cannot test at present as I cannot access MySQL for 24 hours.

Answer 1

The output is \\\" (your second example).

I don't think you can reliably say whether a string has already been escaped or not, you should organize your code in a way that you only can call mysqli_real_escape_string() once.

Answer 2

The resulting string will be \\\" (escaping each of the two characters).

If by "safe" you mean "foolproof", then no. Even if you check that the relevant characters are escaped, you can't know if it was supposed to be \", and strings don't provide any hidden flags that could be set to mark it as having been escaped.

However, you shouldn't manually be calling mysqli_real_escape_string twice. Only do it at the moment you need it.

The main source of "double escaping" is when you're doing the right* thing by escaping, but neither turn off magic quotes or remove them if they're on.

*Well, as close as you can get without using parameterized queries.

Answer 3

You can't tell if a string has already been escaped, because a string that has been escaped could also have been entered a user (and therefore be unescaped).

Answer 4

The second one will be returned. " will first be converted to \" (escaping ") and that will then be converted to \\\" (escaping \ and ").

In general you do have to do it on your own. Although there is the “feature” Magic Quotes that does add backslashes to the character ', ", \ and the NULL character in your incoming data, Magic Quotes are a horrible invention and will be removed in PHP 6. Futhermore they can be enabled on one server and disable on another. So it’s not even reliable.

But more important is that they are not suitable for MySQL as in MySQL you not just have to take care of these meta characters only but of other character too (see string syntax in MySQL for more information).

Answer 5

To add some extra information.

Escaping is a way to add extra information to a sequence of symbols without introducing extra symbols. In order to do this, at least one symbol gets to function as the 'escape' symbol. This means the original role is lost and needs to be added using an escape sequence.

Example:

We have a language with 3 symbols: a, b and c. In which the symbols have the following functions:

Language 1
a has the function A
b has the function B
c has the function C

We now need to introduce the functions D and E, but we are not able to add extra symbols.

So we can do this using an escape symbol:

Language 2
a, is now the escape symbol.
aa has the function A
ab had the function D
ac has the function E
b  has the function B
c  has the function C

The sequence aabcabac, if interpreted using language 1 reads AABCABAC. But interpreted using language 2 reads ABCDE.

The problem with this is that, most of the time, you are not able to determine the language used from the symbols. This meta information needs to be provided before the data is processed.

The botom line is that you have no safe way to determine if a string is escaped enough.

Answer 6

Calling mysqli_real_escape_string() multiple times does not make the query less safe. It does however corrupt your data. A good example is Conan O'Brien.

If you do the following:

$name=mysqli_real_escape_string($db,mysqli_real_escape_string($db,"Conan O'Brien"));
//At this point $name is Conan O\\\'Brien
mysql_query("insert into table (name)values('$name')")
//In the database the name will be stored as: Conan O\'Brien  Which is corrupt.   

To deal with this corruption you can do the following:

funciton strip_all($var){
    $len=strlen($var);
    $ret=stripslashes($var);
    while($len!=strlen($ret)){
        $len=strlen($ret);
        $ret=stripslashes($ret);
    }
    return ret;
}
//$name is unkown somthing like: Conan O\\\\\\\'Brien
$name=strip_all($name);
//$name is: Conan O'Brien
$name=mysqli_real_escape_string($db,$name);
//$name is: Conan O\'Brien,  which is properly escaped.
mysql_query("insert into table (name)values('$name')")
//In the database the value is Conan O'Brien,  mysql will eat the back slash. 

The best approach is to keep track where your escaping is coming from. In this case we account if magic_quotes is disabled or enabled, and then we use mysqli_real_escape_string() which provides better security because it escapes more characters.

if(get_magic_quotes_gpc())
{
   $name=stripslashes($_GET[name]);
}
$name=mysqli_real_escape_string($db,$_GET[name]);
//At this point $name is Conan O\'Brien
mysql_query("insert into table (name)values('$name')")