LIKE statement and special characters in PDO / PHP

Question

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)?

score 4 · Accepted Answer · answered Apr 28 '10 at 11:44

4

You need to escape the % character,

  $valeur = '\%';

answered Apr 28 '10 at 11:44

ZZ Coder

74,484
29
137
169

Yep. I knew it. But if you think about the principle: prepare() and bindValue() have been made for both optimization *and* avoiding functions like mysql_real_escape(), escape(), and so on. So if I have to do a str_replace( '%', '\%' ...) this would mean there's one (important) goal missed. That's why I was looking for an elegant solution, instead of feeling like going back to the old "php safe_mode" times. – Olivier Pons Apr 29 '10 at 14:04
I didn't get your point. Prepared statement has nothing to do with semantics of wildcard. The wildcard character shouldn't change its meaning when used as arguments for prepared statements. – ZZ Coder Apr 29 '10 at 14:55
What I mean is: old code = "select * from xx where t=".mysql_real_escape(blabla)."
new code : prepare("select * from xx where t=:tmp") then bindValue(':tmp', $val). using your suggestion this would give : bindValue(':tmp', str_replace('%', '\%', $val)). This is not clean code to me. We shouldn't use str_replace() here. There has to be another way, because this way is the principle of "escaping unwanted characters", which is a principle that **should** not exist anymore thanks to prepare() and bindValue(). I hope I've been more clear this time :) – Olivier Pons Apr 30 '10 at 09:33
So, your solution works well, but it's a temporary patch, and I try my best to avoid temporary patches, because I hate them. But thanks anyway, that will do the trick if I can't find a clean solution... – Olivier Pons Apr 30 '10 at 09:35
Hi, I may have missed it. So I'll try to explain better my p.o.v. : The variable $valeur may contain unwanted chars like '%'. If so, I **have** to change $valeur: I **must** escape chars like '%'. If (I'm wrong tell me). So this implies I **must** use stuff like mb_strreplace() and so on. What I'm trying to say is that pdo has been done for many things **including** safety. Safety with params. This way you never have to escape or to touch variables you pass to your query: you do prepare() then bind() then exec(). You don't care about what's in $valeur. – Olivier Pons Dec 08 '10 at 17:19
They've made it so you **never** have to care about SQL injection. You never have to care about what's in your variables... And if I have to escape the '%' sign, this means I do have to care of what's in my variable (again, tell me if I'm wrong here). This means with all the queries I'll do, I will have to change them in specific cases (where I want to use 'like' statements). So this implies that, IMHO: having to escape (no matter what the char is (here it's '%')) = breaking the part of pdo that has been done to "avoid being forced to escape variables in php code". – Olivier Pons Dec 08 '10 at 17:25

score 2 · Answer 2 · answered Dec 13 '12 at 18:09

2

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

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

answered Dec 13 '12 at 18:09

Michael Butler

6,079
3
38
46

score 0 · Answer 3 · answered Sep 15 '12 at 01:01

0

I achieved that using string comparison functions:

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

I suspect performance will suffer.

answered Sep 15 '12 at 01:01

Jon Hulka

1,259
10
15

1

Your comment is right: the performance suffers *a lot*. **`:/`** – Olivier Pons Sep 17 '12 at 06:45
@Olivier Pons Thanks for verifying that. In my case it doesn't matter too much, and I'd rather go with the more elegant solution. – Jon Hulka Sep 17 '12 at 21:07

score 0 · Answer 4 · answered Dec 24 '13 at 04:50

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

score -1 · Answer 5 · answered Dec 08 '10 at 08:11

-1

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.

answered Dec 08 '10 at 08:11

cabbey

226
3
14

Many thanks for answering, man but...I did this so I just don't have to care anymore of what's in $valeur. With your code I do have to care of what's in $valeur. Suppose that the $valeur is read from a POST and it's only the "*" char. Guess what will happen? All the rows returned. (That's what is happening in my example code anyway, so your answer is not the right solution). With your code I would have to escape characters and my question is like: "how to **avoid** being forced to escape characters in code php? I don't want to modify what's coming in. pdo should handle this itself". – Olivier Pons Dec 08 '10 at 17:34
PDO only protects you from user input. It assumes that the SQL is safe/sane. If you want protection (what escapes are all about) the values to be protected have to go through the parameters. It's not going to make guesses about wildcard variable syntax and how to interpret what you wanted to do. You need to do that. You seem to think PDO is something it is not. – cabbey Dec 08 '10 at 19:24
From the manual: `PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.` – cabbey Dec 08 '10 at 19:24

LIKE statement and special characters in PDO / PHP

5 Answers5