Skip to content

Instantly share code, notes, and snippets.

@elenakondrateva

elenakondrateva/mariadb-escape-regexp.php

Last active July 11, 2023 20:52
Show Gist options
  • Save elenakondrateva/8d387bc1b9299f950cf8731ba3fbdfa9 to your computer and use it in GitHub Desktop.
Save elenakondrateva/8d387bc1b9299f950cf8731ba3fbdfa9 to your computer and use it in GitHub Desktop.
Download ZIP
Escape REGEXP for MariaDB query
Raw
mariadb-escape-regexp.php
<?php
// MariaDB uses the C escape syntax in strings (for example, "\n" to represent the newline character),
// we must double any "\" that we use in REGEXP strings (see https://mariadb.com/kb/en/regexp/)
// PHP's preg_quote() won't work here as it adds single slashes so use a special regular expression here
$string = 'My [string] {with} ~regexp~ |special| /characters/+.';
$pattern = '/(\+|-|\||&&|\|\||!|\(|\)|\{|}|\[|]|\^|"|~|\*|\?|:|\\\)/';
$replacement = '\\\\\\\\${1}';
$stringEscaped = preg_replace($pattern, $replacement, $string);
// use string in MariaDB REGEXP functions
$query = <<<SQL
SELECT * FROM my_table
WHERE my_column REGEXP '{$stringEscaped}'
SQL;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment