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…
LIKE
& REGEXP
together- was the fastest one among the approaches I tried.Q1: Is this the right way to go?
Q2: Is this secure enough against SQL injections?
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.
%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".
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.
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.
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).'[[:>:]](.*)" ';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With