问题描述
我正在尝试在程序中编写搜索功能:
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?
推荐答案
准备好的语句不会转义任何内容.准备语句时,查询将进行预编译,因此只需要填充占位符(?
).由于无法更改预编译查询的SQL,因此无需转义.
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.
已添加:
一些常识性推理:在您的情况下,当用户在搜索框中输入%
时,您的$search
变量包含字符串%%%
. MySQL怎么知道,它应该逃避哪个%
,应该独自离开哪个?
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?
这篇关于PDO/MYSQL准备好的语句不转义字符?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!