I'm looking for something similar to preg_quote, but for the MySQL regexp syntax.
Any ideas?
1
-
Possible duplicate of What's the best way to escape user input for Regular Expressions in MySQL?Flimm– Flimm2018-12-31 10:07:11 +00:00Commented Dec 31, 2018 at 10:07
Add a comment
|
5 Answers
MySQL regexps are the ‘extended’ POSIX variant (ERE), available in PHP as the deprecated ereg_ functions.
Unfortunately there is no ereg_quote in PHP, however PCRE's special characters are a superset of ERE's special characters, and backslash-escaping a non-special punctuation character doesn't harm it, so you can get away with using preg_quote safely.
(Naturally you will need parameterised queries or mysql_real_escape_string after that quoting, to stop the backslashes being misinterpreted as MySQL's non-ANSI-standard string literal escapes.)
5 Comments
bobince
Slight caveat: the null control character.
preg_quote escapes it to \000, which is a format MySQL doesn't recognise. But then MySQL's regex engine can't cope with null characters anyway, it takes them as terminators.
JochenJung
I had to use str_replace('?', '\\?', preg_quote()), since preg_quote() could produce ? when it doesen't know spectial UTF8 characters. And ? needs to be escaped in the regexp.
Mir-Ismaili
A problem!
mysql_real_escape_string will escape backslash characters. This feature causes all backslashes to be escaped again (if you use mysql_real_escape_string after quoting)!bobince
@Mir-Ismaili yes, you should only
mysql_real_escape_string if you are injecting the regex into a SQL query string literal, in which case you do actually need the extra backslashes. Normally, it would be better to use parameterised queries and avoid the need for extra escaping.
Ruslan Stelmachenko
And to complete the answer: to use parameterized REGEXP you can use
CONCAT like this: WHERE field REGEXP CONCAT('regexp part 1', ?, 'regexp part 2') and bind preg_quote($field) to ? parameter.Sadly, PHP's preg_quote() messes up MySQL REGEXP by escaping a colon sign (:) which MySQL's REGEXP does not understand
2 Comments
Nathan Stretch
So is the best approach to preg_quote then replace all escaped colons with unescaped colons?
Flimm
I don't think this answer is correct. Try it with MySQL:
$statement = $wpdb->prepare( "SELECT %s REGEXP %s", 'hello:you', 'hello\\:you' )There's no native MySQL function for that. You might just need to use preg_quote before passing the regular expression to the MySQL query.
1 Comment
Flimm
preg_quote does not escape & which is needed for MySQL.Try this: (PHP)
$tags="test'*\\\r blue";
$tags=mysql_real_escape_string($tags);
$tags=preg_replace('/([.*?+\[\]{}^$|(\)])/','\\\\\1',$tags);
$tags=preg_replace('/(\\\[.*?+\[\]{}^$|(\)\\\])/','\\\\\1',$tags);
Comments
Thank @bobince's good answer. But it has a problem if you need to use mysql_real_escape_string after quoting, that I mentioned it in a comment.
Actually preg_quote and mysql_real_escape_string have overlap and it makes this problem! mysql_real_escape_string must not escape \ in this case. So I suggest:
function regexpEscape(/*string*/ $input) { // Uncomment `string` for PHP 7.0+
return addcslashes(preg_quote($input), "\0'\"\n\r\x1A"); // charlist = All characters that escape by real_escape_string except backslash
}
(For charlist see: http://php.net/manual/en/mysqli.real-escape-string.php)
I know this is not an ideal way, but couldn't find a better way.
1 Comment
Flimm
There shouldn't be a problem with escaping the regex, and then escaping using
mysql_real_escape_string