PDO / PHP 中的 LIKE 语句和特殊字符

发布于 2024-08-30 23:00:28 字数 497 浏览 4 评论 0原文

我已经检查了诸如这个（如何在 PHP 中使用 LIKE 语句创建 PDO 参数化查询）。我最终得到了这个解决方案：

$sql  = "SELECT count(*) ".
        "FROM mytable ".
        "WHERE num_certif LIKE CONCAT('%',:val,'%')";
$valeur = 'azert';
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':val', $val);

这可行，但这是我的问题：如何处理 '%' 字符？（即 $valeur = '%'；返回所有行）？

原文

I've already checked answers to questions like this one (How do I create a PDO parameterized query with a LIKE statement in PHP).
I've ended up to this solution:

$sql  = "SELECT count(*) ".
        "FROM mytable ".
        "WHERE num_certif LIKE CONCAT('%',:val,'%')";
$valeur = 'azert';
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':val', $val);

This works, but here is my problem: how do I handle the '%' char? (i.e. $valeur = '%'; returns all the rows)?

分享到QQ

分享到微博

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

发布评论

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

花开柳相依 2024-09-06 23:00:29

您需要转义 % 字符，

  $valeur = '\%';

You need to escape the % character,

  $valeur = '\%';

回复收藏 0 原文

堇色安年 2024-09-06 23:00:29

PostgreSQL 用户注意...您可以使用 CONCAT 函数代替

   SELECT count(*)
   FROM mytable
   WHERE num_certif LIKE '%' || :val || '%'

Note for PostgreSQL users... instead of the CONCAT function you can use

   SELECT count(*)
   FROM mytable
   WHERE num_certif LIKE '%' || :val || '%'

回复收藏 0 原文

爱你是孤单的心事 2024-09-06 23:00:29

我使用字符串比较函数实现了这一点：

WHERE INSTR(LCASE(num_certif),LCASE(:val))>0

我怀疑性能会受到影响。

I achieved that using string comparison functions:

WHERE INSTR(LCASE(num_certif),LCASE(:val))>0

I suspect performance will suffer.

回复收藏 0 原文

北陌 2024-09-06 23:00:29

我一直在使用非常简单的东西，就像这样：

    $select_str = "SELECT * FROM table_x WHERE text_field LIKE '%".$value."%'";

    $StHandler = $this->dbHandler->prepare($select_str);
    $StHandler->execute();

根据您要查找的内容，您只能使用百分之一。例如，如果您希望它以您的值开头并稍后包含任何字符，您将使用 '".$value."%'"

希望这会有所帮助

I have been using something very simple, like this:

    $select_str = "SELECT * FROM table_x WHERE text_field LIKE '%".$value."%'";

    $StHandler = $this->dbHandler->prepare($select_str);
    $StHandler->execute();

You can use only one % depending on what you're looking for. For example, if you want it to start with your value and have any characters later, you will use '".$value."%'"

Hope this helps

回复收藏 0 原文

折戟 2024-09-06 23:00:29

为了避免必须进行自己的转义，需要转义的内容必须是 pdo 保护的数据的一部分，即绑定参数。它不能保护您免受硬编码查询中任何内容的影响。

$sql  = "SELECT count(*) ".
    "FROM avs_souscript ".
    "WHERE num_certif =\"\" ".
    "AND date_annul=\"\" ".
    "AND user=:sess_user ".
    "AND user!=\"\" ".
    "AND num_certif LIKE :num_certif_search";
$valeur = 'azert'; //I assume this actually came from some user input
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':num_certif_search', '%'.$valeur.'%');

（或者您也可以将它们放在 $valuer = "%{$userInput}%"; 赋值中，无论哪种方式，它们都应该位于绑定参数中，而不是在 sql 中。

移动这一点从 sql 到 php 的愚蠢字符串连接也是制作可扩展应用程序的好习惯。扩展 Web 服务器场比扩展数据库服务器容易得多。

In order to avoid having to do your own escaping, the stuff that needs to be escaped has to be part of the data that pdo protects, namely the bound arguments. It doesn't protect you from anything in the hard coded query.

$sql  = "SELECT count(*) ".
    "FROM avs_souscript ".
    "WHERE num_certif =\"\" ".
    "AND date_annul=\"\" ".
    "AND user=:sess_user ".
    "AND user!=\"\" ".
    "AND num_certif LIKE :num_certif_search";
$valeur = 'azert'; //I assume this actually came from some user input
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':num_certif_search', '%'.$valeur.'%');

(or alternately you could put them in the $valuer = "%{$userInput}%"; assignment, either way, they should be in the bound argument, not in the sql.

Moving this bit of silly string concat from the sql out to the php is also good practice for making a scalable application. It's much easier to scale a farm of web servers than it is to scale the database server.

回复收藏 0 原文

~没有更多了~