Use SQL REGEXP (mariadb) for filter in where with special characters in Laravel [duplicate]

Question

mariadb version 10.4

$query->where('column', 'REGEXP', '[[:<:]]'.$string.'[[:>:]]');

This query provides a search with whole words only and that's working fine without special characters.

Example

Searching row:

foo (bar baz)

Filter:

$string = "(bar";
$query->where('column', 'REGEXP', '[[:<:]]'.$string.'[[:>:]]');

Error:

SQLSTATE[42000]: Syntax error or access violation: 1139 Got error 'missing )

Now I tried with addcslashes and preg_quote. I succeed to escape ( character with preg_quote but the filter won't work for that string because the filter works with whole words.

Any suggestion for better filtering by the whole word will be also appreciated.

One more example

Searching row:

foo(bar baz

Filter:

$filter = 'foo(bar';
$query->where('column', 'REGEXP', '[[:<:]]'.$string.'[[:>:]]');

If ( will be escaped the search will not work

Answer 1

There are a lot of punctuation marks that have special meaning in REGEXP strings. ( is just one of many.

If the goal is the search for the 4 characters (bar with a trailing "word boundary", then one of these may work -- depending on the version of MySQL/MariaDB and what escaping is needed to get from your app to the server:

\(bar[[:>:]]
\\(bar[[:>:]]
\\\\(bar[[:>:]]
[(]bar[[:>:]]

\(bar\b
\\(bar\\b
\\\\(bar\\\\b
[(]bar\b
[(]bar\\b
[(]bar\\\\b

In any case your app must do some escaping. Given that, it may be better to remove punctuation:

[[:<:]]bar[[:>:]]
\bbar\b
\\bbar\\b
\\\\bbar\\\\b

Consider also using LIKE "(bar"

Consider also using a FULLTEXT index

MATCH(col) AGAINST("bar" IN BOOLEAN MODE)

For maximal performance, this may be best (with a FULLTEXT index):

MATCH(col) AGAINST("bar" IN BOOLEAN MODE)
AND col LIKE "(bar"