How do I create a PDO parameterized query with a LIKE statement?
For those using named parameters, here's how to use LIKE
with %
partial matching for MySQL databases:
WHERE column_name LIKE CONCAT('%', :dangerousstring, '%')
where the named parameter is :dangerousstring
.
In other words, use explicitly unescaped %
signs in your own query that are separated and definitely not the user input.
Edit: Concatenation syntax for Oracle databases uses the concatenation operator: ||
, so it'll simply become:
WHERE column_name LIKE '%' || :dangerousstring || '%'
However there are caveats as @bobince mentions here that:
The difficulty comes when you want to allow a literal
%
or_
character in the search string, without having it act as a wildcard.
So that's something else to watch out for when combining like and parameterization.
You can also try this one. I face similar problem but got result after research.
$query = $pdo_connection->prepare('SELECT * FROM table WHERE column LIKE :search');
$stmt= $pdo_connection->prepare($query);
$stmt->execute(array(':search' => '%'.$search_term.'%'));
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($result);
Figured it out right after I posted:
$query = $database->prepare('SELECT * FROM table WHERE column LIKE ?');
$query->execute(array('value%'));
while ($results = $query->fetch())
{
echo $results['column'];
}
$query = $database->prepare('SELECT * FROM table WHERE column LIKE ?');
$query->bindValue(1, "%$value%", PDO::PARAM_STR);
$query->execute();
if (!$query->rowCount() == 0)
{
while ($results = $query->fetch())
{
echo $results['column'] . "<br />\n";
}
}
else
{
echo 'Nothing found';
}