Deprecated Warning: This answer's sample code (like the question's sample code) uses PHP's
MySQL
extension, which was deprecated in PHP 5.5.0 and removed entirely in PHP 7.0.0.
Security Warning: This answer is not in line with security best practices. Escaping is inadequate to prevent SQL injection, use prepared statements instead. Use the strategy outlined below at your own risk. (Also,
mysql_real_escape_string()
was removed in PHP 7.)
Parameterized query AND input validation is the way to go. There are many scenarios under which SQL injection may occur, even though mysql_real_escape_string()
has been used.
Those examples are vulnerable to SQL injection:
$offset = isset($_GET['o']) ? $_GET['o'] : 0;
$offset = mysql_real_escape_string($offset);
RunQuery("SELECT userid, username FROM sql_injection_test LIMIT $offset, 10");
or
$order = isset($_GET['o']) ? $_GET['o'] : 'userid';
$order = mysql_real_escape_string($order);
RunQuery("SELECT userid, username FROM sql_injection_test ORDER BY `$order`");
In both cases, you can't use '
to protect the encapsulation.
Source: The Unexpected SQL Injection (When Escaping Is Not Enough)