How to bind LIKE values using the PDO extension?

Question

In this query

select wrd from tablename WHERE wrd LIKE '$partial%'

I'm trying to bind the variable '$partial%' with PDO. Not sure how this works with the % at the end.

Would it be

select wrd from tablename WHERE wrd LIKE ':partial%'

where :partial is bound to $partial="somet"

or would it be

select wrd from tablename WHERE wrd LIKE ':partial'

where :partial is bound to $partial="somet%"

or would it be something entirely different?

score 39 · Accepted Answer · edited Feb 18 '19 at 11:14

39

You could also say:

SELECT wrd FROM tablename WHERE wrd LIKE CONCAT(:partial, '%')

to do the string joining at the MySQL end, not that there's any particular reason to in this case.

Things get a bit more tricky if the partial wrd you are looking for can itself contain a percent or underscore character (since those have special meaning for the LIKE operator) or a backslash (which MySQL uses as another layer of escaping in the LIKE operator — incorrectly, according to the ANSI SQL standard).

Hopefully that doesn't affect you, but if you do need to get that case right, here's the messy solution:

$stmt= $db->prepare("SELECT wrd FROM tablename WHERE wrd LIKE :term ESCAPE '+'");
$escaped= str_replace(array('+', '%', '_'), array('++', '+%', '+_'), $var);
$stmt->bindParam(':term', $escaped);

edited Feb 18 '19 at 11:14

Script47

14,230
4
45
66

answered Apr 27 '10 at 15:12

bobince

528,062
107
651
834

1

To escape or not to escape ... It depends on the intent of the character. Was it's intent to be a wildcard or a literal? I wouldn't expect a prepared statement to decide this for me. It's job is to escape characters in the value that would terminate the `LIKE` clause and inject unwanted sql. – Stoutie Oct 15 '12 at 18:32
+1 for the 'messy' solution. The only time I wouldn't use it is if I wanted to give my users the ability to put wildcards in their own search string. – Jon Hulka Nov 26 '12 at 22:40
I would like to add a note why the str_replace does not cycle - `Because str_replace() replaces left to right` so first, the `+` gets escaped, then `%` and in the end the `_` ... so no +++++++++ :-) – jave.web Mar 28 '16 at 00:58

karim79 · Answer 2 · 2010-04-27T15:13:10.423

$var = "partial%";
$stmt = $dbh->prepare("select wrd from tablename WHERE wrd LIKE :partial");
$stmt->bindParam(":partial", $var);
$stmt->execute(); // or $stmt->execute(array(':partial' => $var)); without 
                  // first calling bindParam()
$rs = $stmt->fetchAll();

Using question mark parameters:

$stmt = $dbh->prepare('select wrd from tablename WHERE wrd LIKE ?');
$stmt->execute(array('partial%'));
$rs = $stmt->fetchAll();

http://www.php.net/manual/en/pdo.prepare.php

score 0 · Answer 3 · answered Mar 06 '17 at 12:37

0

You can use addcslashes before prepared statement. I tested on mysql.

$value = addcslashes($value, '%');
$stmt = $db->prepare('select * from products where description like ?');
$stmt->execute(["$value%"]);

answered Mar 06 '17 at 12:37

hovszabolcs

101
1
2

score 0 · Answer 4 · answered Jan 23 '19 at 18:46

I think the accepted answer (by @bobince) can be simplified a bit.

You can reduce it to something like this to handle underscore, percentage, etc in the param but still match the LIKE query with partial%:

$stmt = $dbh->prepare("select wrd from tablename WHERE wrd LIKE :partial");
$stmt->execute([":partial" => addcslashes($value, '_%') . "%"]);
$rows = $stmt->fetchAll();

score -1 · Answer 5 · answered Feb 13 '16 at 12:57

The below code it shows only the first keywords in the database!

"SELECT wrd FROM tablename WHERE wrd LIKE CONCAT(:partial, '%')"

Try this one if you want to search all the keywords from the database

"SELECT wrd FROM tablename WHERE wrd LIKE :partial";
$stmt->execute(array(':partial'=>'%'.$YourVarHere.'%'));

score -2 · Answer 6 · answered Aug 20 '15 at 09:29

-2

This is how you should do it

bindValue(':partial', '%' . $_GET['partial'] . '%');

Thanks,

Qwerty

answered Aug 20 '15 at 09:29

Qwerty

748
1
9
25

This is such an incredibly bad idea. – a coder Jul 13 '18 at 20:40
@acoder without an explanation your comment isn't helpful. – kojow7 Dec 17 '20 at 18:17
Even with abstraction, using a raw unfiltered _GET value in a query sounds like a really bad time waiting to happen. – a coder Dec 18 '20 at 14:23

score -4 · Answer 7 · edited Apr 03 '14 at 07:21

Who has written the answare (may be karim79):

$var ="partial%"
$stmt =$dbh->prepare("select wrd from tablename WHERE wrd LIKE :partial")
$stmt->bindParam(":partial",$var)
$stmt->execute(); //or$stmt->execute(array(':partial'=>$var)); without 
                  // first calling bindParam()
$rs =$stmt->fetchAll();

Using question mark parameters:

$stmt =$dbh->prepare('select wrd from tablename WHERE wrd LIKE ?');
$stmt->execute(array('partial%'));
$rs =$stmt->fetchAll();

Many thanks to him. I was searching for the code & saw many examples, but i couldn't resolve my issue. This time I have succeed to do it. I used the 'Using question mark parameters:' section of the code.

For others help, if you want to retrieve the value from a variable you may change the code to

$stmt->execute(array($variable.'%'));

instead of

$stmt->execute(array('partial%'));

Because the word 'partial' is specified in the answer and can't be changed. Thanks a lot.

How to bind LIKE values using the PDO extension?

7 Answers7

Linked

Related