mysql_real_escape_string
(PHP 4 >= 4.3.0, PHP 5)
mysql_real_escape_string --
Escapes special characters in a string for use in a SQL statement,
taking into account the current charset of the connection.
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
charset 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 of the
same name, which prepends slashes to the following characters:
NULL, \x00, \n,
\r, \, ',
" and \x1a.
You must always (with few exceptions) use this function
to make your data safe before inserting. If you have
magic_quotes_gpc enabled,
you must first stripslashes() your data. If you don't use
this, you'll leave yourself open to SQL Injection Attacks. Here's an example:
Example 1. 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='fred' 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
/**
* Apply stripslashes recursively
*/
function stripslashes_deep($value)
{
$value = is_array($value) ?
array_map('stripslashes_deep', $value) :
stripslashes($value);
return $value;
}
/**
* Quote a variable to make it safe for insertion
*/
function quote_smart($value)
{
// Stripslashes if we need to
if (get_magic_quotes_gpc()) {
$value = stripslashes_deep($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('localhost', 'mysql_user', 'mysql_password')
OR die('Could not connect: ' . 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);
?>
|
Our query is now safe no matter what the user submits!
|
Note:
mysql_real_escape_string() does not escape
% and _. These are wildcards in MySQL
if not bounded by quotes.
See also
mysql_client_encoding(),
addslashes(), and the
magic_quotes_gpc
directive.