Why mysql_real_escape_string() did not prevent hack?

Question

I've a website that hacked today. Server logs returned something like this as hacker's tries:

www.site.com/notifications.php?PID=7&id=999999.9%20union%20all%20select%20%28select%20distinct%20concat%280x7e%2C0x27%2Cunhex%28Hex%28cast%28schema_name%20as%20char%29%29%29%2C0x27%2C0x7e%29%20from%20%60information_schema%60.schemata%20limit%201%2C1%29%2C0x31303235343830303536%2C0x31303235343830303536%2C0x31303235343830303536--

But I've used mysql_real_escape_string() in my code:

if (isset($_GET['id']) && $_GET['id'] != '') {
   $id = mysql_real_escape_string($_GET['id']); 
} else {
   $id = '';
}

if ($id == '') {
   $stmt = "SELECT * FROM tbln13 ORDER BY id DESC"; 
} else {
   $stmt = "SELECT * FROM tbln13 WHERE id = $id";
}

$NewsResult = mysql_query($stmt) or die (mysql_error());

Why my website could not prevent this attack?

Answer 1

Because escape_string add slashes and such to quotes. You didn't have any quotes in your query, or the string they submitted.

Your query doesn't have a STRING in it, it appears to expect an int. If you expected an integer, you should have verified it was an int, or forced it to an int, before using it in a query. Escaping a value as a string, then using it as an int, won't work.

Switch to prepared statements in MySQLi or PDO.