mysql_real_escape_string

Description

This function will escape special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query(). If you wish to insert binary data you must use this function.

mysql_real_escape_string() calls MySQL's library function mysql_escape_string, which prepends backslashes to the following characters: NULL, \x00, \n, \r, \, ', " and \x1a.

Example 1. Simple mysql_real_escape_string() example <?php // Connect $link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')     OR die(mysql_error()); // Query $query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",             mysql_real_escape_string($user),             mysql_real_escape_string($password)); ?>

You must always (with few exceptions) use this function to make your data safe before sending a query to MySQL. If you have magic_quotes_gpc enabled, and you are working with data from user input, you must first stripslashes() your data. If your data are form other sources and you have magic_quotes_runtime enabled, you also have to stripslashes() your data. If you don't do so, you leave yourself open to SQL Injection Attacks. Here's an example:

Example 2. An example SQL Injection Attack <?php // Query database to check if there are any matching users $query = "SELECT * FROM users WHERE user='{$_POST['username']}' AND password='{$_POST['password']}'"; mysql_query($query); // We didn't check $_POST['password'], it could be anything the user wanted! For example: $_POST['username'] = 'aidan'; $_POST['password'] = "' OR 1=1"; // This means the query sent to MySQL would be: echo $query; ?> The query sent to MySQL: SELECT * FROM users WHERE name='aidan' AND password='' OR 1=1 This would allow anyone to log in without a valid password! Using mysql_real_escape_string() around each variable prevents this. <?php /** * Quote a variable to make it safe */ function quote_smart($value) {     // Stripslashes if we need to     if (get_magic_quotes_gpc()) {         $value = stripslashes($value);     }     // Quote it if it's not an integer     if (!is_int($value)) {         $value = "'" . mysql_real_escape_string($value) . "'";     }     return $value; } // Connect $link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')     OR die(mysql_error()); // Make a safe query $query = sprintf("SELECT * FROM users WHERE user=%s AND password=%s",             quote_smart($_POST['username']),             quote_smart($_POST['password'])); mysql_query($query); ?> The query will now execute correctly, and Injection attacks will no longer work.

See also mysql_client_encoding(), addslashes(), stripslashes(), the magic_quotes_gpc, and the magic_quotes_runtime directive.