-2

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?

raphael75
  • 2,982
  • 4
  • 29
  • 44
  • 1
    "but what I get" --- but you get it where? – zerkms May 06 '16 at 20:37
  • Are you using a framework, ORM, or any abstraction layer? – MonkeyZeus May 06 '16 at 20:37
  • 1
    Neither PHP not PDO converts your data. It remains exactly the same. Thus there is not a single problem with your code. – Your Common Sense May 06 '16 at 20:37
  • More context needed: http://sandbox.onlinephpfunctions.com/code/f85d98c120b1ea1316d6041e826047a6b510f1ef – AbraCadaver May 06 '16 at 20:38
  • @ zerkms: In the resulting query. – raphael75 May 06 '16 at 20:38
  • I am using no framework, ORM, or abstraction layer. – raphael75 May 06 '16 at 20:38
  • 1
    "In the resulting query" --- no it does not. Mysql returns exactly the data you've put there. – zerkms May 06 '16 at 20:39
  • @zerkms: :criteria is a token in the original query that's in an external file. The token will be replaced with the value passed in from PDO when the query runs. PDO automatically handles escaping the value when not using LIKE, but in this case it's not working as expected. – raphael75 May 06 '16 at 20:42
  • @raphael75 "PDO automatically handles escaping the value when not using LIKE, but in this case it's not working as expected." --- it does not. PDO does not do anything automagically, everything it does need to be stated explicitly. – zerkms May 06 '16 at 20:47
  • @MarkBaker you were right in your comments. Have found the info you mentioned here: http://php.net/manual/pt_BR/pdostatement.bindparam.php deleted the answer. I didn't knew the binding removed quotes. Thanks for pointing, and explaining it. – Nelson Teixeira May 06 '16 at 20:55
  • Because of thinks like that in the past (thing like the issue OP presents and the bind being 'smart' and then messing around variables) and although many don't like this aproach, I tend to validate all variables to avoid sql injection and concatenate a query in a string that I can debug and have more control about. – Nelson Teixeira May 06 '16 at 20:58
  • Let me ask this question. If I have this line in PHP: $search = '6008';$str = '%' . $search . '%'; How can I prevent it from %-encoding $str? It's seeing $str as %60 in hex, which evaluates to ` (reference: http://www.asciitable.com/), and then seeing 08% as the rest of the string. – raphael75 May 06 '16 at 20:59
  • To me the answer is: bind has to be generic by it's own nature. It's hard to avoid these cases. In this case I would not use bind and use a more controllable string, making a variable validation before. – Nelson Teixeira May 06 '16 at 21:02
  • @raphael75 encoding it by *what*? php does not do that for sure. – zerkms May 06 '16 at 21:27
  • can you please post your complete code @raphael75 most probably there is an error in your syntax making $search not being escaped properly. That is why %60 is interpreted as ` –  May 07 '16 at 09:45

3 Answers3

0

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • I'd like to point out that OP is very likely using a framework of some sort and the `$_POST` data is being manipulated before it is ever assigned to `$search`. It's also possible that OP could have some unknown JS code doing something similar. [I've had an issue like this before](http://stackoverflow.com/questions/20081165/some-chars-encoded-during-post-while-others-are-not) – MonkeyZeus May 06 '16 at 20:48
0

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.

Nelson Teixeira
  • 6,297
  • 5
  • 36
  • 73
-1

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.

Djuro
  • 384
  • 2
  • 9