问题描述
我正在尝试使用 LIKE 在我的数据库中搜索 name 字段.如果我像这样手工"制作 SQL:
I am trying to search the name field in my database using LIKE. If I craft the SQL 'by hand` like this:
$query = "SELECT * " . "FROM `help_article` " . "WHERE `name` LIKE '%how%' " . ""; $sql = $db->prepare($query); $sql->setFetchMode(PDO::FETCH_ASSOC); $sql->execute();
然后它会返回'how'的相关结果.
然而,当我把它变成一个准备好的语句时:
Then it will return relevant results for 'how'.
However, when I turn it into a prepared statement:
$query = "SELECT * " . "FROM `help_article` " . "WHERE `name` LIKE '%:term%' " . ""; $sql->execute(array(":term" => $_GET["search"])); $sql->setFetchMode(PDO::FETCH_ASSOC); $sql->execute();
我总是得到零结果.
我做错了什么?我在代码的其他地方使用了准备好的语句,它们工作正常.
What am I doing wrong? I am using prepared statements in other places in my code and they work fine.
推荐答案
绑定的 :placeholders 不能用单引号括起来.这样它们就不会被解释,而是被视为原始字符串.
The bound :placeholders are not to be enclosed in single quotes. That way they won't get interpreted, but treated as raw strings.
当你想使用一个作为 LIKE 模式时,然后将 % 与值一起传递:
When you want to use one as LIKE pattern, then pass the % together with the value:
$query = "SELECT * FROM `help_article` WHERE `name` LIKE :term "; $sql->execute(array(":term" => "%" . $_GET["search"] . "%"));
哦,实际上你需要先清理这里的输入字符串(addcslashes).如果用户在参数中提供了任何无关的 % 字符,则它们将成为 LIKE 匹配模式的一部分.请记住,整个 :term 参数作为字符串值传递,并且该字符串中的所有 % 都成为 LIKE 子句的占位符.
Oh, and actually you need to clean the input string here first (addcslashes). If the user supplies any extraneous % chars within the parameter, then they become part of the LIKE match pattern. Remember that the whole of the :term parameter is passed as string value, and all %s within that string become placeholders for the LIKE clause.