Full-Text Search vulnerable to some special characters

[dogpixels]

By default, SQL's Full-Text Search seems to handle some special characters within the AGAINST string as code, even though they're within quotes and bound.

For example:

$query = $myTable
    ->find('matches', [
        [
            'match' => 'textcol1',
            'against' => '*'
        ]
    ]);

will cause

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'

This behaviour has been observed in closely described here: https://stackoverflow.com/questions/25088183/mysql-fulltext-search-with-symbol-produces-error-syntax-error-unexpected/25972465#25972465

[chris48s]

Hi @dogpixels . Thanks for raising this. The issue you are reporting here seems to be an acknowledged (but not yet fixed) bug in MySQL itself: https://bugs.mysql.com/bug.php?id=78485

I don't think there is any 'fix' to apply in this plugin as there isn't any additional escaping (for example) that can be added to pass that input in a way that will cause MySQL to process the statement without error. I will leave the issue open though as it provides useful information if other users hit the same problem.

[Anantkprajapati]

Hello @chris48s - you can replace the special character like
const SPECIAL_CHARACTERS = '-+~/\<>'":*$#@()!,.?`=%&^';
str_split(self::SPECIAL_CHARACTERS, 1);
str_replace($this->replaceSymbols, ' ', $queryValue);

[chris48s]

You could do that if your only objective is to issue the query and not throw an error.

..but if a user writes the code

$query = $myTable
    ->find('matches', [
        [
            'match' => 'textcol1',
            'against' => '*'
        ]
    ]);

and then I silently change their code to

$query = $myTable
    ->find('matches', [
        [
            'match' => 'textcol1',
            'against' => ''
        ]
    ]);

that's going to return the user some result, but probably not the result the user expects or wants and that is unexplained. Its better for the user to hit the error and understand the problem. Note that this is a MySQL bug affecting only InnoDB tables. These characters are valid and using them in a query against a table using MyISAM will work as expected.

[ivan-nezhura]

Hello @chris48s - you can replace the special character like
const SPECIAL_CHARACTERS = '-+~/<>'":*$#@()!,.?`=%&^';
str_split(self::SPECIAL_CHARACTERS, 1);
str_replace($this->replaceSymbols, ' ', $queryValue);

This is redundant list. I think, only list of operators should be replaced.
const SPECIAL_OPERATORS = '+-@<>()~*"';