doctrine2 dql，在进行类似比较时使用 setParameter 和 % 通配符

发布于 2024-09-24 16:45:45 字数 849 浏览 0 评论 0原文

我想使用参数占位符 - 例如 ?1 - 和 % 通配符。也就是说，类似于：“u.name LIKE %?1%”（尽管这会引发错误）。该文档有以下两个示例： 1.

// Example - $qb->expr()->like('u.firstname', $qb->expr()->literal('Gui%'))
public function like($x, $y); // Returns Expr\Comparison instance

我不喜欢这个，因为没有针对代码注入的保护。

// $qb instanceof QueryBuilder

// example8: QueryBuilder port of: "SELECT u FROM User u WHERE u.id = ?1 OR u.nickname LIKE ?2 ORDER BY u.surname DESC" using QueryBuilder helper methods
$qb->select(array('u')) // string 'u' is converted to array internally
   ->from('User', 'u')
   ->where($qb->expr()->orx(
       $qb->expr()->eq('u.id', '?1'),
       $qb->expr()->like('u.nickname', '?2')
   ))
   ->orderBy('u.surname', 'ASC'));

我不喜欢这样，因为我需要在对象的属性中搜索术语 - 也就是说，我需要两边都有通配符。

原文

I want to use the parameter place holder - e.g. ?1 - with the % wild cards. that is, something like: "u.name LIKE %?1%" (though this throws an error). The docs have the following two examples:
1.

// Example - $qb->expr()->like('u.firstname', $qb->expr()->literal('Gui%'))
public function like($x, $y); // Returns Expr\Comparison instance

I do not like this as there is no protection against code injection.

// $qb instanceof QueryBuilder

// example8: QueryBuilder port of: "SELECT u FROM User u WHERE u.id = ?1 OR u.nickname LIKE ?2 ORDER BY u.surname DESC" using QueryBuilder helper methods
$qb->select(array('u')) // string 'u' is converted to array internally
   ->from('User', 'u')
   ->where($qb->expr()->orx(
       $qb->expr()->eq('u.id', '?1'),
       $qb->expr()->like('u.nickname', '?2')
   ))
   ->orderBy('u.surname', 'ASC'));

I do not like this because I need to search for terms within the object's properties - that is, I need the wild cards on either side.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

稳稳的幸福 2024-10-01 16:45:45

将参数绑定到查询时，DQL 的工作方式几乎与 PDO 完全相同（这是 Doctrine2 在底层使用的）。

因此，当使用 LIKE 语句时，PDO 将关键字和 % 通配符视为单个标记。您不能在占位符旁边添加通配符。绑定参数时必须将它们附加到字符串中。

$qb->expr()->like('u.nickname', '?2')
$qb->getQuery()->setParameter(2, '%' . $value . '%');

请参阅 PHP 手册中的注释。

When binding parameters to queries, DQL pretty much works exactly like PDO (which is what Doctrine2 uses under the hood).

So when using the LIKE statement, PDO treats both the keyword and the % wildcards as a single token. You cannot add the wildcards next to the placeholder. You must append them to the string when you bind the params.

$qb->expr()->like('u.nickname', '?2')
$qb->getQuery()->setParameter(2, '%' . $value . '%');

See this comment in the PHP manual.

回复收藏 0 原文

吹梦到西洲 2024-10-01 16:45:45

所选答案错误。它有效，但不安全。

您应该对百分号之间插入的术语进行转义：

->setParameter(2, '%'.addcslashes($value, '%_').'%')

百分号“%”和符号下划线“_”被 LIKE 解释为通配符。如果没有正确转义，攻击者可能会构造任意复杂的查询，从而导致拒绝服务攻击。此外，攻击者有可能获得他不应该获得的搜索结果。有关攻击场景的更详细描述，请访问：https://stackoverflow.com/a/7893670/623685

The selected answer is wrong. It works, but it is not secure.

You should escape the term that you insert between the percentage signs:

->setParameter(2, '%'.addcslashes($value, '%_').'%')

The percentage sign '%' and the symbol underscore '_' are interpreted as wildcards by LIKE. If they're not escaped properly, an attacker might construct arbirtarily complex queries that can cause a denial of service attack. Also, it might be possible for the attacker to get search results he is not supposed to get. A more detailed description of attack scenarios can be found here: https://stackoverflow.com/a/7893670/623685

回复收藏 0 原文

~没有更多了~