What's the best way to escape user input for Regular Expressions in MySQL?

Question

I'd like to take user input, denoted as $dangerous_string, and use it as part of a RegEx in a MySQL query.

What's the best way to go about doing this? I want to use the user's string as a literal -- if it contains any characters that mean something in MySQL RegEx, those characters should not actually 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

Well, taking a regex is something this was clearly not made for, have you tried: http://us.php.net/manual/en/function.mysql-escape-string.php

You may also have to addslashes to double escape and make it work....

Answer 2

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

My preferred way if I were in your situation would be to construct a regex whitelist in PHP that you can then apply to your dangerous string:

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

This removes any non-word characters (i.e. anything except A-Za-z0-9_) from your string.

NB I believe the other answers given will not remove/escape regex special characters, which I believe is your requirement.

Answer 3

You need to ensure that quotes and ticks are properly handled before passing to the database. The best method for this is:

   mysql_real_escape_string  ([php doc][1])

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

This should ensure any 'dangerous_string' is no longer dangerous and can be used within a quoted string used by RegEx.