MariaDB全文搜索在BOOLEAN模式中允许哪些字符？

Question

I want to pass user input through PHP to an SQL query on a MariaDB 10.1.37 InnoDB table to do a search. I want to protect against building invalid SQL queries. Currently I am removing the known operator characters like this:

preg_replace('/[\+\-\<\>\(\)\~\*\"]/', ' ', $user_input)

Then I append a * character to each word. So if the user input is foo bar the query will be:

SELECT columnname, MATCH(columnname) AGAINST('foo* bar*' IN BOOLEAN MODE) AS score
FROM tablename;

Unfortunately there are other characters that a user can input that will break the query. For example, if the user inputs % the query will be AGAINST('%*' IN BOOLEAN MODE) which gives the error: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*' .

I could try to filter out characters that cause problems (a blacklist) or I could try to whitelist characters that I know are ok.

I can't find anything in the documentation to define what characters are allowed or disallowed.

Is there a known set of characters that is either allowed or disallowed?

Answer 1

Take the opposite approach? Decide what characters to allow.

preg_replace("/[^-'a-zA-Z0-9]/", ' ', $user_input)

Keep:

• Dash for hyphenated words
• Apostrophe for contractions

Remove:

• Plus and asterisk -- because of FT syntax
• Slash and double-quote -- PHP syntax

Then use double-quotes, not apostrophe, in

AGAINST("foo* bar*" IN BOOLEAN MODE)