php 转义 MySQL 通配符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3683746/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Escaping MySQL wild cards
提问by Columbo
On an older server I'm using that I can't use prepared statements on I am currently trying to fully escape user input before sending it to MySQL.
For this I am using the PHP function mysql_real_escape_string.
在我使用的较旧的服务器上，我无法使用准备好的语句，我目前正在尝试在将用户输入发送到 MySQL 之前完全转义它。为此，我使用 PHP 函数mysql_real_escape_string。
Since this function does not escape the MySQL wildcards % and _ I am using addcslashesto escape these as well.
由于此函数不会转义 MySQL 通配符 % 和 _ 我也addcslashes用来转义这些。
When I send something like:
当我发送类似的东西时：
test_test " '
to the database and then read it back the database shows:
到数据库然后读回数据库显示：
test\_test " '
Looking at this I can't understand why the _ has a preceding backslash but the " and ' don't. Since they are all escaped with \ surely _ ' and " should all appear the same, i.e. all have the escape character visible or all not have it visible.
看着这个，我不明白为什么 _ 前面有一个反斜杠，而“和 ' 没有。因为它们都用 \ 转义，当然 _ ' 和“应该都看起来一样，即都有转义字符可见或都没有它可见。
Are the escaping \s automatically screened out for
转义的 \s 是否会自动筛选出来
Can anyone explain this?
谁能解释一下？
回答by bobince
_and %are not wildcards in MySQL in general, and should not be escaped for the purposes of putting them into normal string literals. mysql_real_escape_stringis correct and sufficient for this purpose. addcslashesshould not be used.
_并且%通常不是 MySQL 中的通配符，并且不应出于将它们放入普通字符串文字的目的而进行转义。mysql_real_escape_string为此目的是正确和充分的。addcslashes不应该使用。
_and %are special solely in the context of LIKE-matching. When you want to prepare strings for literal use in a LIKEstatement, so that 100%matches one-hundred-percent and not just any string starting with a hundred, you have two levels of escaping to worry about.
_并且%仅在LIKE-matching的上下文中是特殊的。当您想在LIKE语句中准备用于字面使用的字符串，以便100%匹配 100% 而不仅仅是任何以 100 开头的字符串时，您需要担心两个级别的转义。
The first is LIKE escaping. LIKE handling takes place entirely inside SQL, and if you want to turn a literal string into an literal LIKE expression you must perform this step even if you are using parameterised queries!
第一个是 LIKE 转义。LIKE 处理完全在 SQL 内部进行，如果您想将文字字符串转换为文字 LIKE 表达式，即使您正在使用参数化查询，也必须执行此步骤！
In this scheme, _and %are special and must be escaped. The escape character must also be escaped. According to ANSI SQL, characters other than these must notbe escaped: \'would be wrong. (Though MySQL will typically let you get away with it.)
在这个方案中，_和%是特殊的，必须被转义。转义字符也必须被转义。根据 ANSI SQL，不能转义除这些之外的字符：\'会是错误的。（虽然 MySQL 通常会让你侥幸逃脱。）
Having done this, you proceed to the second level of escaping, which is plain old string literal escaping. This takes place outside of SQL, creating SQL, so must be done after the LIKE escaping step. For MySQL, this is mysql_real_escape_stringas before; for other databases there will be a different function, of you can just use parameterised queries to avoid having to do it.
完成此操作后，您将进入转义的第二级，即普通的旧字符串文字转义。这发生在 SQL 之外，创建 SQL，因此必须在 LIKE 转义步骤之后完成。对于 MySQL，这mysql_real_escape_string和以前一样；对于其他数据库，会有不同的功能，您可以使用参数化查询来避免这样做。
The problem that leads to confusion here is that in MySQL uses a backslash as an escape character for both of the nested escaping steps! So if you wanted to match a string against a literal percent sign you would have to double-backslash-escape and say LIKE 'something\\%'. Or, if that's in a PHP "literal which also uses backslash escaping, "LIKE 'something\\\\%'". Argh!
这里导致混淆的问题是，在 MySQL 中，两个嵌套转义步骤都使用反斜杠作为转义字符！因此，如果您想将字符串与文字百分号进行匹配，则必须双反斜杠转义并说LIKE 'something\\%'. 或者，如果这是在"也使用反斜杠转义的 PHP文字中，"LIKE 'something\\\\%'". 啊！
This is incorrect according to ANSI SQL, which says that: in string literals backslashes mean literal backslashes and the way to escape a single quote is ''; in LIKE expressions there is no escape character at all by default.
根据ANSI SQL，这是不正确的，它说：在字符串文字中，反斜杠表示文字反斜杠，转义单引号的方法是''; 在 LIKE 表达式中，默认情况下根本没有转义字符。
So if you want to LIKE-escape in a portable way, you should override the default (wrong) behaviour and specify your own escape character, using the LIKE ... ESCAPE ...construct. For sanity, we'll choose something other than the damn backslash!
因此，如果您想以可移植的方式 LIKE 转义，您应该覆盖默认（错误）行为并使用LIKE ... ESCAPE ...构造指定您自己的转义字符。为了理智，我们会选择该死的反斜杠以外的东西！
function like($s, $e) {
return str_replace(array($e, '_', '%'), array($e.$e, $e.'_', $e.'%'), $s);
}
$escapedname= mysql_real_escape_string(like($name, '='));
$query= "... WHERE name LIKE '%$escapedname%' ESCAPE '=' AND ...";
or with parameters (eg. in PDO):
或带参数（例如在 PDO 中）：
$q= $db->prepare("... WHERE name LIKE ? ESCAPE '=' AND ...");
$q->bindValue(1, '%'.like($name, '=').'%', PDO::PARAM_STR);
(If you want more portability party time, you can also have fun trying to account for MS SQL Server and Sybase, where the [character is also, incorrectly, special in a LIKEstatement and has to be escaped. argh.)
（如果您想要更多的可移植性聚会时间，您也可以尝试解释 MS SQL Server 和 Sybase，其中的[字符在LIKE语句中也是错误的特殊字符，并且必须进行转义。啊。）
