What is the best way to avoid user input of regular expressions in MySQL?

Question

I would like to use user input, designated as $ danger_string, and use it as part of RegEx in a MySQL query.

What is the best way to do this? I want to use a custom string as a literal - if it contains any characters that mean something in MySQL RegEx, these characters should not affect my regular expression.

$dangerous_string = $_GET["string"]; //do something here $dangerous_string = what_goes_here($dangerous_string); $sql = "SELECT * FROM table WHERE search_column REGEX '[[:<:]]$dangerous_string'"; //etc.... 

Answer 1

AFAIK, for MySQL regex there is no native way of escaping. You can do this in PHP using preg_quote (http://www.php.net/manual/en/function.preg-quote.php), which will probably do the job for you, but obviously not intended for this goal.

My preferred way, if I were in your situation, was to create a whitelist of regular expressions in PHP, which can then be applied to your dangerous string:

 $safeString = preg_replace('/[^\w]/','',$dangerousString); 

This removes any non-primary characters from your string (for example, everything except A-Za-z0-9_).

NB I believe that other answers will not remove / invoke regular expressions of regular expressions, which I believe is your requirement.

Answer 2

You need to make sure that quotation marks and ticks are correctly processed before going to the database. The best way to do this:

  mysql_real_escape_string ([php doc][1]) 

This method is available in the PHP and C ++ mysql client libraries.

This should ensure that any "dangerous_string" is no longer dangerous and can be used in the quoted string used by RegEx.