如何使用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（'User u'）
- > where （）;
where子句应该出现什么？我想要匹配的变量是$ name
没有人回答你的问题，所以我会做一个刺。
- > where（'u.name LIKE？'，array（％$ name％））;
- > where（'u.username LIKE？'，'％'。$ username。'％'）
这两个都不安全。让我解释一些情况。
情景1
想像你想让用户搜索匹配的用户名，但您不希望列出所有用户名。也许你不想让别人轻易从你那里窃取一百万用户名的列表。在这段代码之前，你做了这样的事情：
if（strlen（trim（$ name））< 5）抛出Boogey_Monster_Exception（）;
您认为这可能会阻止某人离开该领域空白并拉下所有用户名的列表。但实际上用户可以提交_____或%%%%%或任何类似的东西，以获取所有用户名的列表，而不仅仅是匹配5个或更多的已知字符。
我亲自看到这种形式的攻击用于几个大型的公共网站。
情景2
您有一个拥有大量用户和大量用户数据的网站。您的用户表中有10,000,000行。您想要通过搜索已知的前缀来启用站点的用户查找其他用户的用户名。
所以你写一些这样的代码，稍微修改一下，只有一个搜索字符串之后的通配符。
- > where（'u.name LIKE？'，array（$ name％ ））;
如果您在u.name上有一个索引，则此LIKE查询将使用该索引。所以如果用户提交$ name =john，那么这个查询将有效匹配像johndoe，johnwayne，johnwaynegacy等等的用户。
但是，如果用户提交$ name =％john，此查询不再使用索引，现在需要进行全表扫描。在一个非常大的数据库上，这可能是一个非常慢的查询。
SQLi上的MySQL手册提到了同样的事情（第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手册和commenter @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屋！
