Setting PDO emulate to false results in SQL wildcard Like query failure

Question

If I set PDO emulate to false, as I have read multiple times should be done for SQL injection security, using wildcards in a LIKE query results in no output.

This is the setting:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

this is a query that is failing:

$query = $db->prepare("SELECT * FROM video WHERE name like :search and removed='0' and verified='1' or subgenre like :search and removed='0' and verified='1' LIMIT :lb, :lt");
$query->bindValue(':search', '%'.$search.'%', PDO::PARAM_STR);
$query->bindValue(':lb', $limitbottom, PDO::PARAM_INT);
$query->bindValue(':lt', $limittop, PDO::PARAM_INT);
$query->execute();
$array = $query->fetchAll(PDO::FETCH_ASSOC);

if I remove the emulate setting, the query runs as expected and returns an array. If I understand everything correctly, setting emulate to false should be in the script to prevent SQL injection. How do I correct this? Is it possible to have like queries with wildcards and emulation off?

PHP is 5.4.10 & MySQL is 5.1.66

Please see [this post](http://stackoverflow.com/questions/10113562/pdo-mysql-use-pdoattr-emulate-prepares-or-not) on security and emulated prepared statements. They are *not* more (or less) secure than emulated ones. — RickN, Jan 10 '13 at 15:20
off topic, but be aware that wildcard searches with a `%` at the begining are very slow. It has to read the entire table to get the results. You may get away with it on a small data table, but on a table with any significant amount of data, it will grind your code to a halt. — SDC, Jan 10 '13 at 15:21
@SDC thanks for that info, never realized the performance issue. — JimmyBanks, Jan 10 '13 at 15:26
@RikkusRukkus so if I understand from what I'm reading there correctly, I should remove the setting from the script and let it decide on its own. That's interesting as I've read many other suggestions to set it to false. — JimmyBanks, Jan 10 '13 at 15:34
I'm pretty sure that PDO won't 'decide on its own' to turn off emulated prepared statements. Apart from having to tweak your code a bit, there are no downsides to turning them off. On the other hand, the benefits might not be worth the hassle. — RickN, Jan 10 '13 at 15:45
@RikkusRukkus well i meant it will fall back if the query doesnt work, poor word choice on my part. However, even if I kept them off, as it stands right now I don't know how to make the `like` query with the wildcard run. — JimmyBanks, Jan 10 '13 at 15:47
@RikkusRukkus It does also say in the post you linked to, that setting emulation to off is recommended. So I'm still at a stand-still in my decision on what to do. — JimmyBanks, Jan 10 '13 at 15:49

JimmyBanks · Accepted Answer · 2013-01-11T14:42:15.263

The problem is that the parameter :search has been used twice, and this is not supported with emulation off.

This solves the issue:

$query = $db->prepare("SELECT * FROM video WHERE name like :search1 and removed='0' and     verified='1' or subgenre like :search2 and removed='0' and verified='1' LIMIT :lb, :lt");
$query->bindValue(':search1', '%'.$search.'%', PDO::PARAM_STR);
$query->bindValue(':search2', '%'.$search.'%', PDO::PARAM_STR);
$query->bindValue(':lb', $limitbottom, PDO::PARAM_INT);
$query->bindValue(':lt', $limittop, PDO::PARAM_INT);
$query->execute();
$array = $query->fetchAll(PDO::FETCH_ASSOC);

Now the query will return the array as expected.

Setting PDO emulate to false results in SQL wildcard Like query failure

1 Answers1