PDO/MYSQL prepared statements not escaping characters?

Question

I'm trying to write power a search function in my program:

$search = "%".$_POST['search']."%";
$query=$connection->prepare("SELECT * FROM TABLE WHERE COLUMN LIKE ?");
$query->execute(array($search));

However, it seems that users can simply enter % and it returns all results. How do I prevent this from happening? I was under the impression that using prepared statements would have escaped these characters. Does this apply to other characters (\, ', etc) as well? How do I fix this?

Answer 1

Prepared statements don't escape anything. When you prepare a statement, your query gets precompiled, so that it only needs the placeholders ( ? ) to be filled in. Since theres no way to change the SQL of precompiled query, no escaping is needed.

To fix this, escape % and _ manually.

Added:

A bit of common sense reasoning: In your case, when a user enters % into a searchbox, your $search variable contains string %%%. How would MySQL know, which % it should escape, and which it should leave alone?

Answer 2

You'll need to protect against the inclusion of % or _ in your application code if you don't want it as valid input.

if (strpos($_POST['search'], '%') !== FALSE || strpos($_POST['search'], '_') !== FALSE) {
  $query = $connection->prepare("SELECT * FROM TABLE WHERE COLUMN LIKE ?");
  $query->execute(array($search));
}
else echo "% and _ are not allowed!";

As for characters like ', they're treated as part of the string passed in place of the ? placeholder, not concatenated in to build a SQL statement. So they are safe.

Answer 3

You must properly escape user input, to let the server know which characters are wildcards, and which are data to match.

$search = '%' . str_replace(array('_', '%'), array('\\_', '\\%'), $_POST['search']) . '%';

This will escape those characters that would otherwise be considered wildcards. $search would now contain something along the lines of:

%100\%%

which will match I always give 100% when working but not I do 100 pushups daily.