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?

why not to just try it and print the result out? – Your Common Sense May 16 '10 at 13:13 — Your Common Sense, May 16 '10 at 13:13
`mysql_real_escape_string` is not an idempotent function. – Gumbo May 16 '10 at 13:32 — Gumbo, May 16 '10 at 13:32

score 8 · Answer 1 · answered May 16 '10 at 13:16

8

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.

answered May 16 '10 at 13:16

b_i_d

180
5

It's the right place to do it, but not the best way to handle the problem. – Blair McMillan May 16 '10 at 13:42
@Blair care to explain a little? What problem and what's the best? – Your Common Sense May 16 '10 at 13:47
The problem of handling injection. Parametrised queries are a better way of dealing with that. But like I said, your answer gives the right place to do what the OP asked. – Blair McMillan May 17 '10 at 08:13

score 3 · Accepted Answer · answered May 16 '10 at 13:11

3

Yes. You'd get extra unnecessary backslashes.

answered May 16 '10 at 13:11

Vilx-

104,512
87
279
422

Dom De Felice · Answer 3 · 2010-05-16T13:32:14.073

2

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]

edited May 16 '10 at 13:32

answered May 16 '10 at 13:13

Dom De Felice

476
4
10

Magic quotes do not perform the same escaping as mysql(i)_(real)_escape string. The right way to handle magic quotes, if you are unable to disable them, is to remove them with stripslashes, and then apply escaping if necessary (i.e., if you can't use parameterized queries). Ideally, you'd get rid of magic quotes at the very beginning, to avoid having to strip them every time you need to output them in a non-DB context. – Michael Madsen May 16 '10 at 13:16
I won't downvote because of your score, but this approach is wrong. Escaping should be just `$value = mysql_real_escape_string($_GET["value"]);` **unconditional**. And getting rid of magic quotes is another matter that should be done far before query composing, at the very top of the script. – Your Common Sense May 16 '10 at 13:17
I'll have to downvote you if you don't do `$value = mysql_real_escape_string(stripslashes($_GET["value"]));` on the last condition. – Alix Axel May 16 '10 at 13:17
Sorry guys, I didn't know that. And thanks for the information, I will correct my code too :-) – Dom De Felice May 16 '10 at 13:20
@Alix but this approach is wrong too. Just leave database escaping alone. It has nothing to do with magic quotes. Maguc slashes must be just stripped off, for the many reasons. Don't your app consists only of this very line? – Your Common Sense May 16 '10 at 13:30
@Col. Shrapnel: I know it's wrong and I don't expect him to provide a solution for magic quotes in his answer but since he is checking for it the least he can do is provide equally good code. – Alix Axel May 16 '10 at 15:01

Michael Madsen · Answer 4 · 2010-05-16T13:19:22.887

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.

score 1 · Answer 5 · answered May 16 '10 at 13:14

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.

score 0 · Answer 6 · answered May 16 '10 at 13:12

0

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.

answered May 16 '10 at 13:12

Tomalak

332,285
67
532
628

@ whoever downvoted this: An explanation would have been nice. – Tomalak May 16 '10 at 15:09

score 0 · Answer 7 · answered May 16 '10 at 13:15

0

Yes, it makes a difference:

$string = "I'm Chuck!";
mysql_escape_string($string); // I\'m Chuck!
mysql_escape_string(mysql_escape_string($string)); // "I\\\'m Chuck!

answered May 16 '10 at 13:15

Alix Axel

151,645
95
393
500

mysql_real_escape more than once

7 Answers7

Linked