Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Preparing user-defined search term(s) for query

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.

like image 315
inhan Avatar asked Aug 16 '12 19:08

inhan


People also ask

What is S in SQL query?

%s is a placeholder used in functions like sprintf. Check the manual for other possible placeholders. $sql = sprintf($sql, "Test"); This would replace %s with the string "Test".

What is query term?

Query terms (keywords) are the words contained in a user query. Boolean operators or wildcards are not considered as query terms. They are operators used to link query terms.


2 Answers

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

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).

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

like image 195
NotGaeL Avatar answered Oct 04 '22 04:10

NotGaeL


try this use mysql_real_escape_string()

$word = '2*3%5_1^0';

$query = 'SELECT * FROM TABLE_NAME WHERE field REGEXP "(.*)[[:<:]]'.mysql_real_escape_string($word).'[[:>:]](.*)" ';
like image 42
Chintan Avatar answered Oct 04 '22 04:10

Chintan