There's something I have recently noticed among PHP "security experts" (people who have had their software criticized at some point or other for poor security, and Googled "php security"). For some reason, some PHP developers have this idea that mysql_escape_string or mysql_real_escape_string is the answer to all of their problems. Wrong, wrong, wrong, wrong, wrong, wrong, WRONG. In some cases, they're right - doing a mysql_real_escape_string prevents quotation marks and other undesired characters from entering into SQL statements. However, when your input is an integer, those quotation marks are not required. Here's an example of a string being filtered.
$query = "SELECT name,interests FROM users WHERE username = '" . mysql_real_escape_string($_GET['name']) . "'";
Entering 'union all select email,password from users/* from users won't do anything because the required quotation is escaped, and you'll just get a syntax error. Your query looks like this:
SELECT name,interests FROM users WHERE username = '\'union all select email,password from users/*'
But what happens in this scenario?
$query = "SELECT name,interests FROM users WHERE id = " . mysql_real_escape_string($_GET['query']);
Well, the quotation is still escaped, but in this case we don't need it. The query "0 union all select email,password from users where id=1/*" is still perfectly valid because there are no quotation marks around it. The server sees this, a perfectly legal statement:
SELECT name,interests FROM users WHERE id = 0 union all select email,password from users where id=1/*
Ultimately, the best solution to this problem is type checking. This can be done with intval, is_numeric, etc. If it is determined that the data is the correct type, THEN it should be filtered with functions like mysql_real_escape_string when appropriate.
