mysql_real_escape_string() not sanitizing variable

Question

I'm working on an existing website trying to prevent SQL injections. Before $_GET['ID'] was unsanitized.

$ID=mysql_real_escape_string($_GET['ID']);
$sQuery=mysql_query("select * from tbl_mini_website as s1, tbl_actor_merchant as me where s1.MERCHANT_ID=$ID AND s1.MERCHANT_ID=me.MERCHANT_ID");

If I put a ' at the end of the url, with mysql_real_escape_string() I get this from mysql_error():

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\\' AND s1.MERCHANT_ID=me.MERCHANT_ID' at line 1

with out mysql_real_escape_string() I get:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\' AND s1.MERCHANT_ID=me.MERCHANT_ID' at line 1

I'm not sure whats up with it? Any help would be greatly appreciated.

What if you echo the query before perform it?

zerkms
– zerkms

2011-04-07 00:32:37 +00:00
Commented Apr 7, 2011 at 0:32 — zerkms
– zerkms, Commented Apr 7, 2011 at 0:32

alex · Accepted Answer · 2011-04-07 00:49:31Z

6

If it is an id, numerical I assume, why don't you just cast it to an integer?

$ID = (int) $_GET['ID'];

The best advice I can give you is to check out PDO and use bound parameters.

edited Apr 7, 2011 at 0:49

answered Apr 7, 2011 at 0:27

alex

492k205 gold badges890 silver badges992 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dinah Over a year ago

Probably not else the orig solution would work for an integer field. I think Frank is correct to add the quotes.

zerkms Over a year ago

@Dinah: they both correct. Frank Farmer in fact, that mysql_... doesn't add quotes, and alex - that numerics shouldn't be surrounded with quotes but used as numerics (this can cause casting/performance issues)

Frank Farmer · Accepted Answer · 2011-04-07 00:31:08Z

5

mysql_real_escape_string escapes, but doesn't quote.

Try:

$sQuery=mysql_query("select * from tbl_mini_website as s1, tbl_actor_merchant as me where s1.MERCHANT_ID='$ID' AND s1.MERCHANT_ID=me.MERCHANT_ID");

More generally, I tend to wrap both of these in a function, like:

function quoteValue($value) {
    return "'" . mysql_real_escape_string($value) . "'";
}

This is useful, because you may find down the line that you want more refined quoting behavior (especially when it comes to handling Unicode, control characters, etc.)

answered Apr 7, 2011 at 0:31

Frank Farmer

39.6k12 gold badges73 silver badges90 bronze badges

1 Comment

Lienau Over a year ago

Thank you!! I've been working on this for an hour and it was right under my nose.

Phil · Accepted Answer · 2011-04-07 00:33:43Z

2

It's because you're not quoting the variable.

Here's your query given the following inputs

$_GET['ID'] = "1";
$ID=mysql_real_escape_string($_GET['ID']);
SELECT ... where s1.MERCHANT_ID=1 ...

$_GET['ID'] = "1'"
$ID=mysql_real_escape_string($_GET['ID']);
SELECT ... where s1.MERCHANT_ID=1\' ...

$_GET['ID'] = "1'"
SELECT ... where s1.MERCHANT_ID=1' ...

answered Apr 7, 2011 at 0:33

Phil

166k25 gold badges265 silver badges269 bronze badges

Comments

pufos · Accepted Answer · 2011-04-07 01:19:29Z

1

Phil Brown is right, but you shoul forget about old fashioned mysql_real_escape_string or mysql_connect() as they are very old and move to php`s PDO() where you cand use prepared statements, binds, fetch object any many many more functions.

I suggest read PDO documentation at http://php.net/manual/en/book.pdo.php if you want next generation dabatase manipulation and security from SQL Injection .

answered Apr 7, 2011 at 1:19

pufos

2,9509 gold badges36 silver badges38 bronze badges

Collectives™ on Stack Overflow

mysql_real_escape_string() not sanitizing variable

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related