转义 MySQL 通配符 [英] Escaping MySQL wild cards

问题描述

在我使用的旧服务器上，我无法使用准备好的语句，我目前正在尝试在将用户输入发送到 MySQL 之前完全转义它.为此，我使用 PHP 函数 mysql_real_escape_string.

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 通配符 % 和 _，因此我也使用 addcslashes 来转义这些.

Since this function does not escape the MySQL wildcards % and _ I am using addcslashes to escape these as well.

当我发送以下内容时:

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.

转义的 \s 是否会自动筛选出

Are the escaping \s automatically screened out for

谁能解释一下?

推荐答案

_ 和 % 通常在 MySQL 中不是通配符，不应出于此目的进行转义将它们放入普通字符串文字中.mysql_real_escape_string 是正确且足够的.不应使用 addcslashes.

_ 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_string is correct and sufficient for this purpose. addcslashes should not be used.

_ 和 % 仅在 LIKE 匹配的上下文中是特殊的.当您想在 LIKE 语句中准备用于字面使用的字符串，以便 100% 匹配百分之一而不是任何以 100 开头的字符串时，您有两个级别的逃避担心.

_ and % are special solely in the context of LIKE-matching. When you want to prepare strings for literal use in a LIKE statement, 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 转义.LIKE 处理完全在 SQL 内部进行，如果您想将文字字符串转换为文字 LIKE 表达式，您必须执行此步骤即使您使用的是参数化查询！

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!

在这个方案中，_ 和 % 是特殊的，必须被转义.转义字符也必须被转义.根据 ANSI SQL，除这些之外的字符不得被转义:\' 将是错误的.(虽然 MySQL 通常会让你侥幸逃脱.)

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 not be escaped: \' would be wrong. (Though MySQL will typically let you get away with it.)

完成此操作后，您将进入转义的第二级，即普通的旧字符串文字转义.这发生在 SQL 之外，创建 SQL，因此必须在 LIKE 转义步骤之后完成.对于 MySQL，这是 mysql_real_escape_string 和以前一样；对于其他数据库，会有不同的功能，您可以使用参数化查询来避免这样做.

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_string as before; for other databases there will be a different function, of you can just use parameterised queries to avoid having to do it.

这里导致混淆的问题是，在 MySQL 中，两个嵌套转义步骤都使用反斜杠作为转义字符！因此，如果您想将字符串与文字百分号进行匹配，则必须双反斜杠转义并说 LIKE 'something\\%'.或者，如果在 PHP " 文字中也使用反斜杠转义，"LIKE 'something\\\\%'".啊！

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!

根据ANSI SQL，这是不正确的，它说:在字符串文字中，反斜杠表示文字反斜杠，转义单引号的方法是''；在 LIKE 表达式中，默认情况下根本没有转义字符.

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.

因此，如果您想以可移植的方式 LIKE 转义，您应该覆盖默认(错误)行为并指定您自己的转义字符，使用 LIKE ... ESCAPE ... 构造.为了理智，我们会选择该死的反斜杠以外的其他东西！

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!

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 ...";

或带参数(例如在 PDO 中):

or with parameters (eg. in PDO):

$q= $db->prepare("... WHERE name LIKE ? ESCAPE '=' AND ...");
$q->bindValue(1, '%'.like($name, '=').'%', PDO::PARAM_STR);

(如果您想要更多的可移植性聚会时间，您也可以尝试解释 MS SQL Server 和 Sybase，其中 [ 字符在 LIKE 中也不正确地是特殊的 语句并且必须被转义.啊.)

(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 LIKE statement and has to be escaped. argh.)

这篇关于转义 MySQL 通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！