Saturday, March 14, 2009

Using mysql_real_escape_string() around each variable prevents SQL Injection

Using mysql_real_escape_string() around each variable prevents SQL Injection. This example demonstrates the "best practice" method for querying a database, independent of the Magic Quotes setting.

You can also use mysql_real_escape_string() to filter the smart quotes.

copy to clipboard

if (isset($_POST['product_name']) && isset($_POST['product_description']) && isset($_POST['user_id'])) {
// Connect

$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password');

if(!is_resource($link)) {

echo "Failed to connect to the server\n";
// ... log the error properly

} else {

// Reverse magic_quotes_gpc/magic_quotes_sybase effects on those vars if ON.

if(get_magic_quotes_gpc()) {
$product_name = stripslashes($_POST['product_name']);
$product_description = stripslashes($_POST['product_description']);
} else {
$product_name = $_POST['product_name'];
$product_description = $_POST['product_description'];

// Make a safe query
$query = sprintf("INSERT INTO products (`name`, `description`, `user_id`) VALUES ('%s', '%s', %d)",
mysql_real_escape_string($product_name, $link),
mysql_real_escape_string($product_description, $link),

mysql_query($query, $link);

if (mysql_affected_rows($link) > 0) {
echo "Product inserted\n";
} else {
echo "Fill the form properly\n";

The query will now execute correctly, and SQL Injection attacks will not work.

A MySQL connection is required before using mysql_real_escape_string() otherwise an error of level E_WARNING is generated, and FALSE is returned. If link_identifier isn't defined, the last MySQL connection is used.

If magic_quotes_gpc is enabled, first apply stripslashes() to the data. Using this function on data which has already been escaped will escape the data twice.

If this function is not used to escape data, the query is vulnerable to SQL Injection Attacks.

mysql_real_escape_string() does not escape % and _. These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE.

No comments: