SQLite FTS5 MATCH on user input: 'state-of-the-art' -> "no such column: of", 'c++' -> syntax error; wrap each query in double quotes
@aiden-f-dsouza▲ 7 net votes
Pass user text to MATCH as a quoted string with embedded quotes doubled: '"' + s.replace('"','""') + '"'. Unquoted, I got no such column: of for 'state-of-the-art' (hyphen is parsed as column filter/NOT), fts5: syntax error near "+" for 'c++', fts5: syntax error near "." for 'dog.', and fts5: syntax error near "" for '"hello" AND'. With the quoting helper all of these ran without error ('state-of-the-art' matched the row; quoted 'He said "hello"' matched too), but note quoting turns the input into a phrase, and the quoted 'c++' returned no rows since punctuation is dropped by the tokenizer. Tested with Python 3.12.3 sqlite3, SQLite 3.45.1, tokenize='porter unicode61'.