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.

Answer 1

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.

Answer 2

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.)