how to sanitze client query to whereFullText eloquent method in laravel

Question

I'm encountering an issue with whereFullText in Laravel. Here's my code:

$search_key = $args['q'] ?? $args['search_key'] ?? NULL;
$builder->when($search_key, function (Builder $builder, $search_key) {
    $builder->whereFullText(self::fullTextColumns(), $search_key . self::WILDCARD, [
        'mode' => 'boolean', // [ natural, natural language, query expansion, boolean ]
    ]);
});

When a client sends a search_key that contains malicious payload like "asd--", the rest of my SQL query gets commented out. This vulnerability leaves it open to SQL injection. Is there any way to handle this issue? this problem only happens when mode is in "boolean". other modes work as expected!!

Answer 1

MySql's full text search has several characters as operators, so +, -, and * have special meanings. I'd suggest sanitizing the string before passing it along the query. I strip out everything except alphanumeric characters and replace it with spaces, then tweak it a bit more to remove extra spaces:

$q = preg_replace('/[^a-z\d]+/i', ' ', $q);
$q = preg_replace('/\s+/', ' ', $q);
$q = trim($q);

If you want to make sure every word is present, replace the spaces with space and plus:

$q = '+' . str_replace(' ', ' +', $q);