Deprecated Warning: This answer's sample code (like the question's sample code) uses PHP's
MySQLextension, 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.)
IMPORTANT
The best way to prevent SQL Injection is to use Prepared Statements instead of escaping, as the accepted answer demonstrates.
There are libraries such as Aura.Sql and EasyDB that allow developers to use prepared statements easier. To learn more about why prepared statements are better at stopping SQL injection, refer to this
mysql_real_escape_string()bypass and recently fixed Unicode SQL Injection vulnerabilities in WordPress.
Injection prevention - mysql_real_escape_string()
PHP has a specially-made function to prevent these attacks. All you need to do is use the mouthful of a function, mysql_real_escape_string.
mysql_real_escape_string takes a string that is going to be used in a MySQL query and return the same string with all SQL injection attempts safely escaped. Basically, it will replace those troublesome quotes(') a user might enter with a MySQL-safe substitute, an escaped quote '.
NOTE: you must be connected to the database to use this function!
// Connect to MySQL
$name_bad = "' OR 1'";
$name_bad = mysql_real_escape_string($name_bad);
$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
echo "Escaped Bad Injection: <br />" . $query_bad . "<br />";
$name_evil = "'; DELETE FROM customers WHERE 1 or username = '";
$name_evil = mysql_real_escape_string($name_evil);
$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
echo "Escaped Evil Injection: <br />" . $query_evil;
You can find more details in MySQL - SQL Injection Prevention.
