Description
string
mysql_real_escape_string ( string unescaped_string [, resource link_identifier])
- unescaped_string
The string to escape
- link_identifier (optional)
The mysql connection resource
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.
|
Note:
mysql_real_escape_string() does not escape
% and _. These are wildcards in
MySQL if combined with LIKE, GRANT,
or REVOKE.
See also
mysql_client_encoding(),
addslashes(),
stripslashes(),
the magic_quotes_gpc,
and the
magic_quotes_runtime
directive.
