如何在Doctrine中转义LIKE %$var%？

Question

我正在制作一个Doctrine查询，必须在where子句中进行通配符匹配。我应该如何转义要插入的变量？

我想要获取的查询：

SELECT u.* FROM User as u WHERE name LIKE %var%

到目前为止的 PHP 代码：

   $query = Doctrine_Query::create()
                ->from('User u')
                ->where();

在where子句中应该放什么？我想要匹配的变量是$name。

- Peter Smit

Answer 1

没有人正确回答你的问题，所以我来试着解答一下。

->where('u.name LIKE ?', array("%$name%"));
->where('u.username LIKE ?', '%'.$username.'%')

这两种方法都不安全。让我解释一下几种情况。 情况1 假设您想让用户搜索匹配的用户名，但从未想过要列出所有的用户名。或许您不希望有人轻易地从您那里窃取一百万个用户名列表。在此代码之前，您可能已经做了类似于以下的操作：

if (strlen(trim($name)) < 5) throw Boogey_Monster_Exception();

你认为这样可以防止用户留空并拉下所有用户名的列表，但实际上用户可以提交"_____"或"%%%%%"或类似的内容来获取所有用户名的列表，而不仅仅是匹配5个或更多已知字符的用户名。

我亲眼见过这种攻击方式在一些大型公共网站上使用。

场景2

你有一个拥有大量用户和用户数据的网站。你的用户表中有1000万行数据。你想让网站的用户通过搜索已知前缀来查找另一个用户的用户名。

因此，你编写了以下代码，稍作修改以仅在搜索字符串之后具有通配符。

->where('u.name LIKE ?', array("$name%"));

如果您在u.name上有一个索引，那么这个LIKE查询将使用该索引。因此，如果用户提交$name="john"，则此查询将有效匹配像johndoe、johnwayne、johnwaynegacy等用户。

但是，如果用户提交$name="%john"，则此查询不再使用索引，现在需要进行全表扫描。在非常大的数据库上，这可能是一个非常缓慢的查询。

MySQL关于SQLi的手册也提到了这一点（第78-79页），我搜索了一些慢查询性能的示例，并找到了一个链接。

这听起来可能不是很重要，但对于由RDBMS支持的站点来说，RDBMS通常是一个重要的瓶颈，许多性能工程都围绕着减少对RDBMS的争用展开。如果有一小部分用户发起攻击，占用一个数据库句柄60秒以上，并且您只有一个小型的数据库句柄池，您可以看到如何快速扩展以垄断所有数据库句柄并防止合法用户获得其中之一。

链接

http://dev.mysql.com/tech-resources/articles/guide-to-php-security-ch3.pdf

http://forums.mysql.com/read.php?24,13397,13397

解决方案

无论如何，更好的解决方案（如MySQL手册中所述并由评论者@Maxence提到）是使用addcslashes()：

$username = addcslashes("%something_", "%_");

注意，由于此处的sql示例使用预处理语句，完全免疫sql注入，因此不需要或不建议使用mysql_real_escape_string()；它执行的转义仅用于防止sql注入。我们要防止的是通配符注入，这需要一个函数来转义两个sql通配符字符：%和_。

- Mark E. Haase

---

我认为避免对数据库的高负荷的好方法是避免使用LIKE查询，而是使用像sphinxs这样的搜索引擎。它非常棒！ - JeanValjean

@mehaase 我看到你给其他答案打了-1分，但是你的方法也不安全，因为你忘记转义转义字符本身了。所以，至少应该像这样：addcslashes('%something_', '\\%_');。请记住，在MySQL中，'\\something' LIKE '\\something'评估为FALSE，但'\\something' LIKE '\\\\something'评估为TRUE;-) - Karolis

抱歉...问题没有指定你提到的两种情况中的任何一种。这个答案中的信息很有参考价值，但是你对其他答案的“-1”评分在我看来是错误的...其他答案对于这个问题是正确的。 - Jordan Lev

@JordanLev，你没有理解重点。问题是如何转义这个查询。如果你不转义通配符，那么你就没有正确地转义查询。我的答案展示了为什么通配符可能很危险以及如何转义它们。我对其他的问题进行了-1评分，因为它们是错误的。这是一个常见的错误，但它仍然是一个错误。如果你有任何具体的反馈，请让我知道。我总是喜欢纠正错误信息。 - Mark E. Haase

@mehaase，我理解你的观点，并感谢您在这里提供的信息。要补充的一个具体细节是，您的解决方案不适用于SQLite数据库，因为除非您显式告诉它使用反斜杠作为特定 LIKE ... 条件的转义字符，否则不会使用C样式转义。（请参见https://www.sqlite.org/lang_expr.html中的“字面值”部分）。不幸的是，我不知道如何使Doctrine的查询构建器工作（我只使用DBAL），但该概念在这里有解释：https://dev59.com/aWw05IYBdhLWcg3wTwGY#7323498。 - Jordan Lev

@JordanLev $queryBuilder->setParam('val', '%' . addcslashes($value) . '%') 对Doctrine不起作用吗？ - zozo

Answer 2

Doctrine 的文档出了一些问题，所以这里提供 Google 备份（请查看Like Expressions部分）。

...
->where('u.name LIKE ?', array("%$name%"));

- Crozin

---

如果 $name 中有特殊字符，例如 '%' 或 '_'，会发生什么情况？您应该使用 addcslashes($name, '%_') 进行转义。 - Maxence

addcslashes();？不需要为查询添加斜杠，无论您是使用ORM还是原生的SQL。Doctrine会妥当地转义而不会在数据中添加斜杠。对于原生mysql，请使用mysql_real_escape_string($string);。对于其他情况，请查阅文档；只需避免使用 addcslashes();。 - adlawson

@adlawson说：Doctrine不会为LIKE表达式转义%和_，这需要手动处理。 - Crozin

-1 不安全。尽管公平地说，很少有开发人员理解=和LIKE之间的区别。（他们认为LIKE是=的超集，添加了神奇的模式匹配功能。但实际上并不是这样。）在LIKE中不转义通配符可能会导致数据泄露或拒绝服务（通过运行异常复杂的模式查询）。 - Mark E. Haase

如果名称中有百分号，那么通配符匹配会匹配一些额外的内容。这可能是不好的，但这也可能与情况无关...因此，仅仅说这个答案完全错误是不准确的。 - Jordan Lev

@JordanLev，你需要考虑到(a)原帖没有提供足够的细节来说明是否需要未转义的通配符，以及(b)超过3,000人已经查看了这个问题。可以推测这3,000人并不都有与原帖完全相同的问题，但是他们正在寻找类似的东西。因此，一个好的SO答案应该涵盖问题允许的最一般情况。 - Mark E. Haase