Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Escape string for use in MySQL fulltext search

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?

like image 954
chipit24 Avatar asked Oct 22 '14 12:10

chipit24


People also ask

How do I escape a string in MySQL?

Backslash ( \ ) and the quote character used to quote the string must be escaped.

Which character is the escape character for MySQL?

MySQL recognizes the following escape sequences. \0 An ASCII NUL (0x00) character. \' A single quote (“'”) character. \" A double quote (“"”) character.

What does escape mean in MySQL?

The ESCAPE keyword is used to escape pattern matching characters such as the (%) percentage and underscore (_) if they form part of the data.


1 Answers

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
like image 65
Rimas Avatar answered Sep 19 '22 14:09

Rimas