PHP: Escape String Literals for SQL, mysqli::real_escape_string and PDO to Prevent SQL Injection Attacks

• Tweet

• Tweet

To successfully run a query with text data containing single quotes ‘ as well as other SQL reserved punctuations, AND to prevent SQL injections, you will always want to escape the text values before using them in a SQL query.

In PHP 4.0, we are stuck with mysql_real_escape_string. With PHP 5.0, mysqli:prepare and mysqli::real_escape_string are better choices:

$city_name = "Xi'an"; // one of the top travel destinations of China
$mysqli -> query("SELECT * FROM `cities` WHERE `name` = '" . $mysqli -> real_escape_string($city_name) . "'");

If the string value $city_name is not escaped, the SQL query would look like "SELECT * FROM `cities` WHERE `name` = 'Xi'an'" which is grammatically erroneous and will be rejected by the SQL engine because of the unpaired single quotes at the end. Without escaping of the value, malicious users can make up their own WHERE clauses to append to yours to do much more to your database than you want.

Other than these, some would prefer the PDO class to prepare a SQL query, inject literal string values and then execute it. It takes a real OOP approach to the problem and is much more intuitive in accomplishing the job, yet slightly inferior in efficiency to the mysqli real_escape_string function.