mysqli::real_escape_string()

mysqli::escape_string

(PHP 5, PHP 7)

根据当前连接的字符集，对于 SQL 语句中的特殊字符进行转义

说明

面向对象风格
mysqli::escape_string(string $escapestr): string

mysqli::real_escape_string(string $escapestr): string

过程化风格
mysqli_real_escape_string(mysqli$link,string $escapestr): string

此函数用来对字符串中的特殊字符进行转义，以使得这个字符串是一个合法的 SQL 语句。传入的字符串会根据当前连接的字符集进行转义，得到一个编码后的合法的 SQL 语句。

Caution

安全：默认字符集

在调用mysqli_real_escape_string()函数之前，必须先通过调用mysqli_set_charset()函数或者在 MySQL 服务器端设置字符集。更多信息请参考字符集。

参数

$link

仅以过程化样式：由mysqli_connect()或mysqli_init()返回的链接标识。

$escapestr

需要进行转义的字符串。

会被进行转义的字符包括：NUL （ASCII 0），n，r，，'，"和 Control-Z.

返回值

转义后的字符串。

错误／异常

在无效的连接上调用此函数会返回NULL并发出一个E_WARNING级别的错误。

范例

Example #1 mysqli::real_escape_string()例程

面向对象风格

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* 检查连接 */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
$mysqli->query("CREATE TEMPORARY TABLE myCity LIKE City");
$city = "'s Hertogenbosch";
/* 由于未对 $city 进行转义，此次查询会失败 */
if (!$mysqli->query("INSERT into myCity (Name) VALUES ('$city')")) {
    printf("Error: %s\n", $mysqli->sqlstate);
}
$city = $mysqli->real_escape_string($city);
/* 对 $city 进行转义之后，查询可以正常执行 */
if ($mysqli->query("INSERT into myCity (Name) VALUES ('$city')")) {
    printf("%d Row inserted.\n", $mysqli->affected_rows);
}
$mysqli->close();
?>

过程化风格

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* 检查连接 */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
mysqli_query($link, "CREATE TEMPORARY TABLE myCity LIKE City");
$city = "'s Hertogenbosch";
/* 由于未对 $city 进行转义，此次查询会失败 */
if (!mysqli_query($link, "INSERT into myCity (Name) VALUES ('$city')")) {
    printf("Error: %s\n", mysqli_sqlstate($link));
}
$city = mysqli_real_escape_string($link, $city);
/* 对 $city 进行转义之后，查询可以正常执行 */
if (mysqli_query($link, "INSERT into myCity (Name) VALUES ('$city')")) {
    printf("%d Row inserted.\n", mysqli_affected_rows($link));
}
mysqli_close($link);
?>

以上例程会输出：

Error: 42000
1 Row inserted.

注释

Note:
如果你之前都是使用mysql_real_escape_string()函数来转义 SQL 语句的，那么需要注意的是mysqli_real_escape_string()和mysql_real_escape_string()两个函数的参数顺序不同。mysqli_real_escape_string()中，$link是第一个参数，而在mysql_real_escape_string()函数中，要转义的字符串是第一个参数。

参见

mysqli_set_charset() 设置默认字符编码
mysqli_character_set_name() 返回当前数据库连接的默认字符编码

Note, that if no connection is open, mysqli_real_escape_string() will return an empty string!

For percent sign and underscore I use this:
<?php
$more_escaped = addcslashes($escaped, '%_');
?>

Presenting several UTF-8 / Multibyte-aware escape functions.
These functions represent alternatives to mysqli::real_escape_string, as long as your DB connection and Multibyte extension are using the same character set (UTF-8), they will produce the same results by escaping the same characters as mysqli::real_escape_string.
This is based on research I did for my SQL Query Builder class:
https://github.com/twister-php/sql
<?php
/**
 * Returns a string with backslashes before characters that need to be escaped.
 * As required by MySQL and suitable for multi-byte character sets
 * Characters encoded are NUL (ASCII 0), \n, \r, \, ', ", and ctrl-Z.
 *
 * @param string $string String to add slashes to
 * @return $string with `\` prepended to reserved characters 
 *
 * @author Trevor Herselman
 */
if (function_exists('mb_ereg_replace'))
{
  function mb_escape(string $string)
  {
    return mb_ereg_replace('[\x00\x0A\x0D\x1A\x22\x27\x5C]', '\\\0', $string);
  }
} else {
  function mb_escape(string $string)
  {
    return preg_replace('~[\x00\x0A\x0D\x1A\x22\x27\x5C]~u', '\\\$0', $string);
  }
}
?>
Characters escaped are (the same as mysqli::real_escape_string):
00 = \0 (NUL)
0A = \n
0D = \r
1A = ctl-Z
22 = "
27 = '
5C = \
Note: preg_replace() is in PCRE_UTF8 (UTF-8) mode (`u`).
Enhanced version:
When escaping strings for `LIKE` syntax, remember that you also need to escape the special characters _ and %
So this is a more fail-safe version (even when compared to mysqli::real_escape_string, because % characters in user input can cause unexpected results and even security violations via SQL injection in LIKE statements):
<?php
/**
 * Returns a string with backslashes before characters that need to be escaped.
 * As required by MySQL and suitable for multi-byte character sets
 * Characters encoded are NUL (ASCII 0), \n, \r, \, ', ", and ctrl-Z.
 * In addition, the special control characters % and _ are also escaped,
 * suitable for all statements, but especially suitable for `LIKE`.
 *
 * @param string $string String to add slashes to
 * @return $string with `\` prepended to reserved characters 
 *
 * @author Trevor Herselman
 */
if (function_exists('mb_ereg_replace'))
{
  function mb_escape(string $string)
  {
    return mb_ereg_replace('[\x00\x0A\x0D\x1A\x22\x25\x27\x5C\x5F]', '\\\0', $string);
  }
} else {
  function mb_escape(string $string)
  {
    return preg_replace('~[\x00\x0A\x0D\x1A\x22\x25\x27\x5C\x5F]~u', '\\\$0', $string);
  }
}
?>
Additional characters escaped:
25 = %
5F = _
Bonus function:
The original MySQL `utf8` character-set (for tables and fields) only supports 3-byte sequences.
4-byte characters are not common, but I've had queries fail to execute on 4-byte UTF-8 characters, so you should be using `utf8mb4` wherever possible.
However, if you still want to use `utf8`, you can use the following function to replace all 4-byte sequences.
<?php
// Modified from: https://stackoverflow.com/a/24672780/2726557
function mysql_utf8_sanitizer(string $str)
{
  return preg_replace('/[\x{10000}-\x{10FFFF}]/u', "\xEF\xBF\xBD", $str);
}
?>
Pick your poison and use at your own risk!

You can avoid all character escaping issues (on the PHP side) if you use prepare() and bind_param(), as an alternative to placing arbitrary string values in SQL statements. This works because bound parameter values are NOT passed via the SQL statement syntax.

Note that this function will NOT escape _ (underscore) and % (percent) signs, which have special meanings in LIKE clauses. 
As far as I know there is no function to do this, so you have to escape them yourself by adding a backslash in front of them.

To escape for the purposes of having your queries made successfully, and to prevent SQLi (SQL injection)/stored and/or reflected XSS, it's a good idea to go with the basics first, then make sure nothing gets in that can be used for SQLi or stored/reflected XSS, or even worse, loading remote images and scripts.
For example:
<?php
   
   // Assume this is a simple comments form with a name and comment.
   $name = mysqli_real_escape_string($conn, $_POST['name']);
   $comments = mysqli_real_escape_string($conn, $_POST['comments']);
   // Here is where most of the action happens. But see note below
   // on dumping back out from the database
   // We should use the ENT_QUOTES flag second parameter...
   $name = htmlspecialchars($name);
   $comments = htmlspecialchars($comments);
   $insert_sql = "INSERT INTO tbl_comments ( c_id, c_name, c_comments ) VALUES ( DEFAULT, '" . $name . "', '" . $comments . "')";
   $res = mysqli_query($conn, $insert_sql);
   if ( $res === false ) {
     // Something went wrong, handle it
   }
   // Now output page showing comments
?>
// Assume we're in a table with each row containing a name and comment
<?php
   
   $res = mysqli_query($conn, "SELECT c_name, c_comments FROM tbl_comments ORDER BY c_name ASC");
   if ( $res === false )
     // Something went wrong
   // Or as you like...
   while ( $row = mysqli_fetch_array($res, MYSQLI_BOTH) ) {
     
     // This will output safe HTML entities if they went in
     // They will be displayed, but not interpreted
     echo "<tr><td>" . $row['c_name'] . "</td>";
     echo "<td>" . $row['c_comments'] . "</td></tr>";
     // BUT, if you make this mistake...
     echo "<tr><td>" . htmlspecialchars_decode($row['c_name']) . "</td>";
     echo "<td>" . htmlspecialchars_decode($row['c_comments']) . "</td></tr>";
     // ... then your entities will reflect back as the characters, so
     // input such as this: "><img src=x onerror=alert('xss')>
     // will display the 'xss' in an alert box in the browser.
   }
   mysqli_free_result($res);
   mysqli_close($conn);
?>
In most cases, you wouldn't want to go way overboard sanitizing untrusted user input, for instance:
<?php
   $my_input = htmlspecialchars( strip_tags($_POST['foo']) );
?>
This will junk a lot of input you might actually want, if you're rolling your own forum or comments section and it's for web developers, for example. On the other hand, if legitimate users are never going to enter anything other than text, never HTML tags or anything else, it's not a bad idea.
The take-away is that mysqli_real_escape_string() is not good enough, and being overly-aggressive in sanitizing input may not be what you want.
Be aware that in the above example, it will protect you from sqli (run sqlmap on all your input fields and forms to check) but it won't protect your database from being filled with junk, effectively DoS'ing your Web app in the process.
So after protecting against SQLi, even if you're behind CloudFlare and take other measures to protect your databases, there's still effectively a DoS attack that could slow down your Web App for legitimate users and make it a nightmare filled with rubbish that some poor maintainer has to clean out, if you don't take other measures.
So aside from escaping your stings, and protecting against SQLi and stored/reflected XSS, and maliciously loaded images or JS, there's also checking your input to see if it makes sense, so you don't get a database full of rubbish!
It just never ends... :-)

When I submit data through Ajax I use a little function to reconvert the encoded chars to their original value. After that I do the escaping. Here the function:
  function my_htmlentities($input){
    $string = htmlentities($input,ENT_NOQUOTES,'UTF-8');
    $string = str_replace('&euro;',chr(128),$string);
    $string = html_entity_decode($string,ENT_NOQUOTES,'ISO-8859-15');
    return $string;
  }
G.Zanferrari

If you wonder why (besides \, ' and ") NUL (ASCII 0), \n, \r, and Control-Z are escaped: it is not to prevent sql injection, but to prevent your sql logfile to get unreadable.

Note that the “like” operator requires an *additional* level of escaping for its special characters, *on top of* that performed by mysql_escape_string. But there is no built-in function for performing this escaping. Here is a function that does it:
function escape_sql_wild($s)
 /* escapes SQL pattern wildcards in s. */
 {
  $result = array();
  foreach(str_split($s) as $ch)
   {
    if ($ch == "\\" || $ch == "%" || $ch == "_")
     {
      $result[] = "\\";
     } /*if*/
    $result[] = $ch;
   } /*foreach*/
  return
    implode("", $result);
 } /*escape_sql_wild*/

I think two additional characters need to be removed or escaped to protect from injection: ` (accent grave) and ; (semicolon). Accent grave could be used to inject into table and key names, terminating them too early (if user input is allowed as table or key names), and semicolon could be used to insert additional statements into an SQL statement. Always use ` (accent grave) to surround table, key, and column names, and always use ' (apostrophe) to surround column values in SQL statements, especially if the names or values can ever contain spaces.