Using named parameters with PDO for LIKE
The bound :placeholders
are not to be enclosed in single quotes. That way they won't get interpreted, but treated as raw strings.
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"] . "%"));
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.
Admin
Updated on June 03, 2022Comments
-
Admin almost 2 years
I am trying to search the
name
field in my database usingLIKE
. If I craft the SQL 'by hand` like this:$query = "SELECT * \n" . "FROM `help_article` \n" . "WHERE `name` LIKE '%how%'\n" . ""; $sql = $db->prepare($query); $sql->setFetchMode(PDO::FETCH_ASSOC); $sql->execute();
Then it will return relevant results for 'how'.
However, when I turn it into a prepared statement:$query = "SELECT * \n" . "FROM `help_article` \n" . "WHERE `name` LIKE '%:term%'\n" . ""; $sql->execute(array(":term" => $_GET["search"])); $sql->setFetchMode(PDO::FETCH_ASSOC); $sql->execute();
I am always getting zero results.
What am I doing wrong? I am using prepared statements in other places in my code and they work fine.
-
jswolf19 over 12 yearsEscaping in standard SQL isn't as simple as that. in PL/SQL and Transact SQL, for example, you have to supply the character to be used as an escape character.
-
mario over 12 years@jswolf19: Wasn't even sure about escaping in MySQL, and I usually rather strip any non-alphanumeric characters. (Might be sufficient for OP too.)
-
jswolf19 over 12 yearsI think MySQL does default to
\
, but OP doesn't specify a DB, and standard SQL doesn't specify a default escape character. You have to supply it with thea LIKE b ESCAPE c
syntax.