mysql_real_escape more than once

Question

I was just wondering whether it makes a difference if I mysql_real_escape data more than once?

So if I escaped data in one part of my website, and then again in another part of code. Would this be a problem? Or make a difference?

Answer 1

Yes, it will be an over-escapement problem. This is the same for any escaping, regardless of what exactly it does. For instance, if you'd escape double quotes in string following common rule:

bla "foo"

after one escaping becomes

bla \"foo\"

after two becomes

bla \\\"foo\\\"

and so on. Number of "unescapements" must exactly match number of "escapements". You could see manifestations of this problem on some sites that over-escape some characters in text fields, so that simple apostrophe becomes \' on output.

Answer 2

The right place for mysql_real_escape is right before you send the query to save the data. Every other instance anywhere else in the script is a major design flaw.

That should preferably in an own db-class of course.

Answer 3

Of course, data would be double-escaped.

You should not use mysql_real_escape() at all, parameterized queries via mysqli have been sticking around long enough.

Answer 4

Yes. You'd get extra unnecessary backslashes.

Answer 5

Yes, it would be a problem.

For example:
if a is "Joe's House", the first call will produce "Joe\'s House" and the second one will produce "Joe\\\'s House", saving the backslash in the database.

This is similar to the problem that arises when the web server has the magic quotes enabled and you use mysql_real_escape_string on input from the client. This is solved by:

if (! get_magic_quotes_gpc()) {
    $value = mysql_real_escape_string($_GET["value"]);
} else {
    $value = mysql_real_escape_string(stripslashes($_GET["value"])); 
}

(For the latter example see http://www.php.net/get_magic_quotes_gpc )

[I edited the answer to reflect corrections in the comments below]

Answer 6

It is not possible to distinguish between an escaped and an unescaped string, because the thing which looks like an escaped string was the intended unescaped string. Therefore, trying to escape again, would escape the escaping - and the escaped-once text will be what MySQL reads.

Therefore, you should never escape more than once.

However, a better solution is to use paramterized queries, since then you don't need to escape at all.