how to prevent conversion of %(number) to code

Question

I have this line in PHP that is building a query parameter in PDO:

$p[':criteria'] = '%' . $search . '%';

The line of MySQL in question looks like this:

d.d_name LIKE :criteria

The problem is if I put a number in $search, it's converting it to a character. For example, if I set $search to:

6008

I want it to be:

%6008%

but what I get is:

`08%

It looks like it is %-encoding the 1st 2 characters. I tried using urldecode() to revert it, but it didn't work. It kept the string as `08%.

How can I stop this from happening?

Answer 1

Bind mecanism is too generic because it has to deal with many types and there are some edge cases like this.

So in this case, instead of using bind, mount your query as a string:

$sql = "...whatever d.d_name LIKE '%" . $search . "%'";

Debug $search to be sure it doesn't have any quotes or anything before concatenating. If it has remove with trim or something like that.

Also be sure to validate $search correctly because of sql-injection attacks.

Answer 2

This is just an illusion. You are trying to watch your query using inappropriate tool, like a browser. And this tool does some conversion. Absolutely irrelevant to SQL stuff.

While for a database your query remains exactly the same. And there is not a single problem with any conversions. Just run your query and get the result.

Answer 3

I reccomend you to read about string escaping, it may help you on solving this problem, and alse good thing too know as some programming basics.