逃避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.

当我发送像： p>

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.

是否自动筛选出来的

任何人都可以解释这一点吗？

Can anyone explain this?

推荐答案

_ 和％一般不是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.

_ code>％仅在 LIKE -matching的上下文中是特殊的。当您想在 LIKE 语句中准备字符串时，以便 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 \\\\\％'。Argh！

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 ... 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屋！