转义字符串用于MySQL全文搜索 [英] Escape string for use in MySQL fulltext search

问题描述

我使用Laravel 4并设置了以下查询：

$ p $ < ））{
$ keyword = Input :: get（'keyword'）;
$ search = DB :: connection（） - > getPdo（） - > quote（$ keyword）;
$ query-> whereRaw（MATCH（resources.name，resources.description，resources.website，resources.additional_info）AGAINST（？IN BOOLEAN MODE），
array（$ search）
）;



$ b $ p
$ b

这个查询在正常使用情况下运行正常，但是，如果用户输入一个字符串如 ++ ，则会引发错误。查看 MySQl文档，有一些关键字，例如 + 和 - ，它们有特定的用途。有没有一个函数可以将字符串中的这些特殊字符转义出来，这样就可以像上面那样在全文搜索中使用，而不会引发任何错误？

这里是一个例子/ b>

  {error：{type：Illuminate \\Database\ \ QueryException，message：SQLSTATE [42000]：语法错误或访问冲突：1064语法错误，意外的'+'（SQL：select * from`resources`其中`duplicate` = 0且MATCH（resources.name （'c ++'IN BOOLEAN MODE）），file：\ / var \ / www \ / html\ / [...] \ / vendor \ / laravel \ / framework\ / src\ / Illuminate\\\ Database\ / Connection.php，line：555}} 
  
  
 
 
 
  $ search = str_ireplace（['+'，' - ']，''，$ keyword）; 
 
 $ search = filter_var（$ keyword，FILTER_SANITIZE_STRING）; 
 
 $ search = DB :: connection（） - > getPdo（） - > quote（$ keyword）; 
  

我假设我需要使用正则表达式。这里最好的方法是什么？

解决方案

只有单词和运算符才具有布尔搜索模式的含义。运营商是： + ， - ，> < / code>，（），〜， * ，， @distance 。单词字符是：大写，小写字母，数字（数字）和 _ 。我认为您可以使用以下两种方法之一：



1. 用空格替换所有非单词字符（我更喜欢这种方法）。可以用正则表达式完成：
   
   

     $ search = preg_replace（'/ [^ \p {L} \p {N} _] + / u'，''，$ keyword）; 
     
    
    $ li $  $ $ p>  $ search = preg_replace（'/ [+ \->< \（\）〜* \@] + /'，''，$ keyword）; 
     




只有单词被全文搜索引擎并可以被搜索。非单词字符没有编入索引，所以将它们留在搜索字符串中是没有意义的。

参考文献：

• 布尔全文搜索


• 微调MySQL全文搜索（请参阅：字符集修改）


• PHP：preg_replace


• PHP：Unicode字符属性


• PHP：正则表达式模式中的可能修饰符

I am using Laravel 4 and have set up the following query:

if(Input::get('keyword')) {
    $keyword = Input::get('keyword');
    $search = DB::connection()->getPdo()->quote($keyword);
    $query->whereRaw("MATCH(resources.name, resources.description, resources.website, resources.additional_info) AGAINST(? IN BOOLEAN MODE)", 
        array($search)
    );
}

This query runs fine under normal use, however, if the user enters a string such as ++, an error is thrown. Looking at the MySQl docs, there are some keywords, such as + and - which have specific purposes. Is there a function which will escape these types of special characters from a string so it can be used in a fulltext search like above without throwing any errors?

Here is an example of an error which is thrown:

{"error":{"type":"Illuminate\\Database\\QueryException","message":"SQLSTATE[42000]: Syntax error or access violation: 1064 syntax error, unexpected '+' (SQL: select * from `resources` where `duplicate` = 0 and MATCH(resources.name, resources.description, resources.website, resources.additional_info) AGAINST('c++' IN BOOLEAN MODE))","file":"\/var\/www\/html\/[...]\/vendor\/laravel\/framework\/src\/Illuminate\/Database\/Connection.php","line":555}}

Solutions I've tried:

$search = str_ireplace(['+', '-'], ' ', $keyword);

$search = filter_var($keyword, FILTER_SANITIZE_STRING);

$search = DB::connection()->getPdo()->quote($keyword);

I'm assuming I will need to use regex. What's the best approach here?

解决方案

Only the words and operators have meaning in Boolean search mode. Operators are: +, -, > <, ( ), ~, *, ", @distance. After some research I found what word characters are: Upper case, Lower case letters, Numeral (digit) and _. I think you can use one of two approaches:

1. Replace all non word characters with spaces (I prefer this approach). This can be accomplished with regex:

   $search = preg_replace('/[^\p{L}\p{N}_]+/u', ' ', $keyword);
   

2. Replace characters-operators with spaces:

   $search = preg_replace('/[+\-><\(\)~*\"@]+/', ' ', $keyword);
   

Only words are indexed by full text search engine and can be searched. Non word characters isn't indexed, so it does not make sense to leave them in the search string.

References:

• Boolean Full-Text Searches
• Fine-Tuning MySQL Full-Text Search (see: "Character Set Modifications")
• PHP: preg_replace
• PHP: Unicode character properties
• PHP: Possible modifiers in regex patterns

这篇关于转义字符串用于MySQL全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！