如何使用 Doctrine 转义 LIKE %$var% ？
我正在进行 Doctrine 查询，并且必须在 where 子句中进行通配符匹配。我应该如何转义要插入的变量？
我想要得到的查询：
SELECT u.* FROM User as u WHERE name LIKE %var%
到目前为止的 php 代码：
$query = Doctrine_Query::create()
->from('User u')
->where();
where 子句中应该包含什么？我要匹配的变量是 $name
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
没有人正确回答你的问题，所以我会尝试一下。
这些都不安全。让我解释几个场景。
场景 1
假设您想让用户搜索匹配的用户名，但您永远不想列出所有用户名。也许您不希望有人轻易窃取您的一百万个用户名列表。在此代码之前的某处，您做了类似的事情：
您认为这会阻止某人将字段留空并下拉所有用户名的列表...但实际上用户可以提交“_____”或“%%%%” %" 或类似的内容来获取所有用户名的列表，而不仅仅是匹配 5 个或更多已知字符。
我亲眼目睹过在几个大型公共网站上使用这种形式的攻击。
场景 2
您的网站拥有大量用户和大量用户数据。您的用户表中有 10,000,000 行。您希望站点用户能够通过搜索已知前缀来查找其他用户的用户名。
因此，您编写了一些像这样的代码，对上面的示例稍作修改，只在搜索字符串后面有一个通配符。
如果您在 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())：
请注意，由于这里的sql示例使用准备好的语句，它完全不受sql注入的影响，所以没有必要也不可取地使用mysql_real_escape_string()；它执行的转义只是为了防止 sql 注入。我们试图防止的是通配符注入，这需要一个转义两个 sql 通配符“%”和“_”的函数。
Nobody answered your question correctly, so I'll make a stab at it.
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:
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.
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()):
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 的文档发生了一些问题，因此这里是 Google 副本（选中“Like 表达式”） 部分）
Something bad happened to Doctrine's documentation so here's the Google copy (check Like Expressions section)