How Do I Mix PDO params and wildcards

PHP
1

Consider


SELECT id, full_account_number, name
FROM gl_accounts
WHERE full_account_number = :accountNumber

Simple enough…

But how is this going to work with a LIKE?


SELECT id, full_account_number, name
FROM gl_accounts
WHERE full_account_number LIKE ':accountNumber%'

Will that work? (I’m going to try it, but wondering.)

K, doesn’t work. I’ll use mysql_real_escape_string for now…

2

It works if you put the % in the variable instead of in the query :slight_smile:


SELECT id, full_account_number, name
FROM gl_accounts
WHERE full_account_number LIKE :accountNumber
1 Like
3

My presumption was that this would be escaped by the PDO library. After all, how can PDO know I’m searching for a wildcard or searching for a string containing % ?

4

Interesting question.

You would this is would need to know if you had used LIKE before the bound parameter, but the PHP source doesn’t seem to lend itself to this idea.

I’m off to hunt some more. :slight_smile:

5

Why should PDO care? It’s PDO’s job to pass the query to MySQL, not to bother with the semantics of said query.

Anyway, I’m sure my solution works; I’ve used it :slight_smile:

6

But it would need to know if the % needs escaping though wouldn’t it?

7

opens up my php editor and SQLYog

I’ll be back later

8

Alright.

Simple database


    id  name
------  -----------------------
     1  Scallio
     2  ScallioXTX
     3  Scallio XTX
     4  Scallio%

and the code


$pdo=new PDO('mysql:host=localhost;dbname=xxxx', 'xxxx', 'xxxx');

echo '<h1>Using "scallio%"</h1>';
$stmt=$pdo->prepare('SELECT name FROM persons WHERE name LIKE ?');
$stmt->bindValue(1, 'scallio%');
$stmt->execute();
while ($row=$stmt->fetch())
	var_dump($row);

echo '<h1>Using "scallio\\%"</h1>';
$stmt=$pdo->prepare('SELECT name FROM persons WHERE name LIKE ?');
$stmt->bindValue(1, 'scallio\\%');
$stmt->execute();
while ($row=$stmt->fetch())
	var_dump($row);

and the result:


[size=4][B]Using "scallio%"[/B][/size]

array
  'name' => string 'Scallio' (length=7)
  0 => string 'Scallio' (length=7)

array
  'name' => string 'ScallioXTX' (length=10)
  0 => string 'ScallioXTX' (length=10)

array
  'name' => string 'Scallio XTX' (length=11)
  0 => string 'Scallio XTX' (length=11)

array
  'name' => string 'Scallio%' (length=8)
  0 => string 'Scallio%' (length=8)

[size=4][B]Using "scallio\\%"[/B][/size]

array
  'name' => string 'Scallio%' (length=8)
  0 => string 'Scallio%' (length=8)

In other words:

If you want to use the wildcard, just use the %
If you want to match a percentage character, escape it: \%

9

I’m not saying it doesn’t work, I’m wondering how it works.

Also, you’re escaping the value before passing it to PDO to be escaped, that sounds icky. Which is technical term, trust me. :stuck_out_tongue:

10

If I were to issue the queries from the command line they would be


SELECT name FROM persons WHERE NAME LIKE "scallio%";

and


SELECT name FROM persons WHERE NAME LIKE "scallio\\%";

do you see a pattern here? :wink:

I fail to see why this would be icky… Maybe PDO is just really smart :stuck_out_tongue:

11

Thing is, if you’re relying on PDO to filter your inputs, the % character not being escaped can cause misleading behavior yes/no?

Does it need to be escaped during an insert query for example? Or does it only have significance during LIKE syntax??

1 Like
12

If I remember correctly, when you use MySQL with PDO then the prepare statement (bindParam/bindValue) only append quotes to strings, and to any existing quotes in the value.

I.e. if your string is “It’s a nice day.” it becomes " ‘It’‘s a nice day.’ ".

I dont have time to verify this at the moment, but that is what I remember from our tests when we started to use PDO a few years ago. If anyone have the time to check this with the latest version of PHP/MySQL drivers, please post what you find in this thread. Thanks.

1 Like