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?
解决方案
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?