PDO/MYSQL准备好的语句不转义字符? [英] PDO/MYSQL prepared statements not escaping characters?

问题描述

我正在尝试在程序中编写搜索功能:

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准备好的语句不转义字符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！