准备用户定义的搜索字词进行查询

本文介绍了准备用户定义的搜索字词进行查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

对于搜索功能，我写了一个由PHP脚本执行的MySQL查询。我没有做全文搜索。相反，我使用以下方法进行搜索：

For a search feature I wrote a MySQL query to be executed by a PHP script. I'm not doing a fulltext search. Instead, I'm doing a search using the following method:

... WHERE字段LIKE'％etc％'AND字段REGEXP'[ [：<：]] etc [[：>：]]'

现在，我的想法是准备这些动态值PHP，像：

Now, my idea is to prepare these dynamic values in PHP, like:

$word = '2*3%5_1^0'; // just an example $wordLike = strtr($word,array('\\'=>'\\\\','%'=>'\\%','_'=>'\\_')); // instead of my old solution: // $wordLike = preg_replace('~([%_])~', '\\\\$1', $word); $wordLike = $db_con->escape('%' . $wordLike . '%'); $spaces = '[[:blank:]]|[[:punct:]]|[[:space:]]'; // I'm not sure about the difference between blank & space, though $wordRX = preg_quote($word); $wordRX = $db_con->escape('(^|'.$spaces.')'.$wordRX.'($|'.$spaces.')'); // instead of my old solution: // $wordRX = $db_con->escape('[[:<:]]' . $wordRX . '[[:>:]]');

然后使用这些值，如...

and then use these values like in…

... WHERE字段LIKE'$ wordLike'AND字段REGEXP'$ wordRX'

其中，与此示例输入结果

which, with this example input, results in

... WHERE field LIKE '%2*3\\%5\\_1^0%' AND field REGEXP '[[:<:]]2\\*3%5_1\\^0[[:>:]]`

一些笔记...

• 在我的实际代码中，我使它处理多个单词，这只是伪代码。
• 我用来搜索单词的方法 - with LIKE & REGEXP 一起是我尝试过的方法中最快的。
• 我知道我应该使用PDO，请不要输入任何事情，除非它与我的问题有关

• In my actual code I'm making it handle multiple words, this is just the pseudo code.
• The method I'm using to search the word(s) -with LIKE & REGEXP together- was the fastest one among the approaches I tried.
• I know I should use PDO instead, please don't input anything about that unless it's relevant to my issue

Q1：这是正确的方法吗？ br /> Q2：这对SQL注入是否足够安全？

Q1: Is this the right way to go? Q2: Is this secure enough against SQL injections?

---

关于MySQL REGEXP ...

About MySQL REGEXP…

以下字符由 preg_quote（）

。 \ + *？ [^] $（）{} =！ < > | ：

以下是中的[偶然]特殊字符列表REGEXP

。 \ + *？ [^] $（）{} | -

在 REGEXP 中还有其他结构，但是'全部都被单/双括号包围，因为我知道所有的括号都将被 preg_quote（）我觉得我不应该担心他们。

There are also additional constructs in REGEXP but they're all surrounded by single/double brackets, and because I know all the brackets will be escaped by preg_quote() I feel like I shouldn't be concerned about them.

关于MySQL LIKE ...

About MySQL LIKE…

LIKE 中只有2个特殊字符是

The only 2 special characters in LIKE are

_％

所以逃避他们似乎有一个解决方法。 如果我没有任何东西，请更正我。

So escaping them seems enough a workaround. Please correct me if I'm missing anything.

推荐答案

从您提到的 mysql_real_escape_string（）功能应用程序做好针对SQL注入的清理工作。

Appart from what you mention mysql_real_escape_string() function should do fine for sanitization against SQL injection.

你只需要正确的esc猿无论用户输入使用适当的转义功能，如果您将它描绘为处理此用户输入的链接处理块，您将以哪种顺序（从最后到第一个）以及什么来逃避/解开，何时，您应该在确保一个干净的输入的同时，确定是一个不同的问题。

You just have to properly escape whatever user input using the appropiate escaping function(s), if you picture it as chained processing blocks processing this user input you will know in which order (from last to first) and what to escape/unescape and when, and you should be okay as far as securing a clean input goes (validation is a different issue).

而且，正如您已经看到的那样， quote（）或 Mysqli prepare（）是一个更好的方法。

And, as you already seem to know, quote() on PDO or Mysqli prepare() are a better approach.