Preparing user-defined search term(s) for query

Viewed 312

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 field LIKE '%etc%' AND field REGEXP '[[:<:]]etc[[:>:]]'

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 field LIKE '$wordLike' AND field 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[[:>:]]`

A couple of notes…

  • 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: Is this the right way to go?
Q2: Is this secure enough against SQL injections?


Some additional info

About MySQL REGEXP…

Following characters are escaped by preg_quote()

. \ + * ? [ ^ ] $ ( ) { } = ! < > | : -

Following is the list of [occasionally] special characters in REGEXP

. \ + * ? [ ^ ] $ ( ) { } | -

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.

About MySQL LIKE…

The only 2 special characters in LIKE are

_ %

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

3 Answers
Related