PHP mysql_real_escape_string() function
Definition and Usage
The mysql_real_escape_string() function escapes special characters in the string used in SQL statements.
The following characters are affected:
- \x00
- \n
- \r
- \
- '
- "
- \x1a
If successful, the function returns the escaped string. If failed, it returns false.
Syntax
mysql_real_escape_string(string,connection)
| Parameter | Description |
|---|---|
| string | Required. Specify the string to be escaped. |
| connection | Optional. Specify the MySQL connection. If not specified, the previous connection is used. |
Description
This function will string Escape special characters, and take into account the current character set of the connection, so it can be used safely for mysql_query().
Tips and comments
Tip:This function can be used to prevent database attacks.
Instance
Example 1
<?php
$con = mysql_connect("localhost", "hello", "321");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
// Code to get the username and password
// Escape the username and password to use in SQL
$user = mysql_real_escape_string($user);
$pwd = mysql_real_escape_string($pwd);
$sql = "SELECT * FROM users WHERE
user='" . $user . "' AND password='" . $pwd . "'"
// More code
mysql_close($con);
?>
Example 2
Database attack. This example demonstrates what happens if we do not apply the mysql_real_escape_string() function to the username and password:
<?php
$con = mysql_connect("localhost", "hello", "321");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
$sql = "SELECT * FROM users
WHERE user='{$_POST['user']}'
AND password='{$_POST['pwd']}'";
mysql_query($sql);
// No check on username and password
// It can be any content input by the user, like:
$_POST['user'] = 'john';
$_POST['pwd'] = "' OR ''='";
// Some code...
mysql_close($con);
?>
Then the SQL query will become like this:
SELECT * FROM users WHERE user='john' AND password='' OR ''=''
This means that any user can log in without entering a valid password.
Example 3
Best Practices for Preventing Database Attacks:
<?php
function check_input($value)
{
// Remove slashes
if (get_magic_quotes_gpc())
{
$value = stripslashes($value);
}
// If not a number, add quotes
if (!is_numeric($value))
{
$value = "'" . mysql_real_escape_string($value) . "'";
}
return $value;
}
$con = mysql_connect("localhost", "hello", "321");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
// Perform secure SQL
$user = check_input($_POST['user']);
$pwd = check_input($_POST['pwd']);
$sql = "SELECT * FROM users WHERE
user=$user AND password=$pwd";
mysql_query($sql);
mysql_close($con);
?>
