PHP mysql_real_escape_string() function

PHP MySQL functions

Definition and usage

The mysql_real_escape_string() function escapes special characters in strings used in SQL statements.

The following characters are affected:

x00nr'"x1a

If successful, the function returns the escaped string. If failed, returns false.

Grammar

mysql_real_escape_string(string,connection)

Copy after login

参数	描述
string	必需。规定要转义的字符串。
connection	可选。规定 MySQL 连接。如果未规定，则使用上一个连接。

Description

This function escapes special characters in string and takes into account the current character set of the connection, so it is safe to use mysql_query().

Tips and Notes

Tip: You can use this function to prevent database attacks.

Example

Example 1

<?php
$con = mysql_connect("localhost", "hello", "321");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// 获得用户名和密码的代码

// 转义用户名和密码，以便在 SQL 中使用
$user = <code>mysql_real_escape_string($user)</code>;
$pwd = <code>mysql_real_escape_string($pwd)</code>;

$sql = "SELECT * FROM users WHERE
user='" . $user . "' AND password='" . $pwd . "'"

// 更多代码

mysql_close($con);
?>

Copy after login

Example 2

Database attack. This example shows what happens if we don't 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);

// 不检查用户名和密码
// 可以是用户输入的任何内容，比如：
$_POST['user'] = 'john';
$_POST['pwd'] = "' OR ''='";

// 一些代码...

mysql_close($con);
?>

Copy after login

Then the SQL query will look like this:

SELECT * FROM users
WHERE user='john' AND password='' OR ''=''

Copy after login

This means that any user can log in without entering a valid password.

Example 3

Correct ways to prevent database attacks:

<?php
function check_input($value)
{
// 去除斜杠
if (get_magic_quotes_gpc())
  {
  $value = <code>stripslashes($value)</code>;
  }
// 如果不是数字则加引号
if (!is_numeric($value))
  {
  $value = "'" . <code>mysql_real_escape_string($value)</code> . "'";
  }
return $value;
}

$con = mysql_connect("localhost", "hello", "321");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// 进行安全的 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);
?>

Copy after login