如何使用 Doctrine 逃避 LIKE %$var%? [英] How to escape LIKE %$var% with Doctrine?

问题描述

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

我想得到的查询:

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

到目前为止的php代码:

 $query = Doctrine_Query::create()->from('用户 u')->哪里()；

where 子句中应该包含什么?我要匹配的变量是 $name

解决方案

没有人正确回答你的问题，所以我会尝试一下.

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

这些都不安全.让我解释几个场景.

场景 1

假设您想让用户搜索匹配的用户名，但您永远不想列出所有用户名.也许您不希望有人轻易窃取您的一百万个用户名列表.在这段代码之前的某个地方，你做了这样的事情:

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

您认为这会阻止某人将该字段留空并拉下所有用户名的列表...但实际上用户可以提交_____"或%%%%%"或任何类似的内容来获取列表所有用户名，而不仅仅是匹配 5 个或更多已知字符.

我亲眼目睹了在几个大型公共网站上使用这种形式的攻击.<​​/p>

场景 2

您的网站拥有大量用户和大量用户数据.您的用户表中有 10,000,000 行.您想让网站的用户通过搜索已知前缀来找到另一个用户的用户名.

所以你写了一些这样的代码，对上面的例子稍作修改，只在搜索字符串之后有一个通配符.

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

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

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

关于 SQLi 的 MySQL 手册提到了同样的事情(第 78-79 页)，我在 Google 上搜索了一些查询性能缓慢的示例，并找到了一个链接.

这听起来可能没什么大不了的，但对于由 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 通配符%"和_".

I am making a Doctrine query and I have to do a wildcard match in the where clause. How should I escape the variable that I want to insert?

The query I want to get:

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

The php code until now:

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

What should come in the where clause? The variable I want to match is $name

解决方案

Nobody answered your question correctly, so I'll make a stab at it.

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

Neither of these are safe. Let me explain a few scenarios.

Scenario 1

Imagine you want to let users search for matching usernames, but you never want to list ALL usernames. Perhaps you don't want somebody to easily steal a list of a million usernames from you. somewhere prior to this code, you did something like this:

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

You thought this would prevent somebody from leaving the field blank and pulling down a list of all usernames... but in reality the user can submit "_____" or "%%%%%" or anything similar to get a list of all usernames, not just matching 5 or more known characters.

I have personally seen this form of attack used on several large, public websites.

Scenario 2

You have a website with lots of users and lots of user data. You have 10,000,000 rows in your user table. You want to enable site's users to find another user's username by searching for known prefixes.

So you write some code like this, modified slightly from the example above to only have a wildcard AFTER the search string.

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

If you have an index on u.name, then this LIKE query will use the index. So if the user submits $name="john", then this query will efficiently match users like johndoe, johnwayne, johnwaynegacy, etc.

However, if the user submits $name="%john" instead, this query no longer uses the index and now requires a full table scan. On a very large database this can be a very slow query.

The MySQL manual on SQLi mentions this same thing (pages 78-79) and I googled for some examples of slow query performance and found one link.

This may not sound like a big deal, but for sites backed by an RDBMS, the RDBMS is usually a significant bottleneck, and much of the performance engineering revolves around reducing contention on the RDBMS. IF you have a handful of users launching an attack that ties up a database handle for 60+ seconds, and you have a small pool of database handles, you can see how this could quickly scale to monopolize all of your database handles and prevent legitimate users from being able to get one.

Links

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

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

Solution

Anyway, the better solution (as mentioned in the MySQL manual linked above and by commenter @Maxence, is to use addcslashes()):

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

Note that since the sql examples here use prepared statements, which are completely immune to sql injection, it is not necessary or desirable to use mysql_real_escape_string(); the escaping it performs is solely to prevent sql injection. What we're trying to prevent is wildcard injection, and that requires a function that escapes the two sql wildcard characters, '%' and '_'.

这篇关于如何使用 Doctrine 逃避 LIKE %$var%?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！