0

I have a simple FTS5 query like source

function getRes(q) {
    const sel = 'SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v MATCH ?';
    return db.prepare(sel).get(q);
}

The above fails for getRes('Trematoda (awaiting allocation)') with the following error

SqliteError: fts5: syntax error near "Trematoda"

Of course, it works fine for getRes('Trematoda') so it is the parens that are causing the problem. Since I can't guarantee what characters will be in my query, how can I ensure this succeeds? From my Perl+Postgres days, I seem to remember that binding automatically did all the escaping properly. Seems that is not the case with JavaScript+SQLite. On further testing, it fails in the SQLite shell as well but it does work fine in TablePlus, a GUI for SQLite.

punkish
  • 13,598
  • 26
  • 66
  • 101
  • 1
    Parentheses have a special meaning in strings matched against fts tables; has nothing to do with the actual parameter binding. – Shawn Aug 07 '19 at 07:07
  • 1
    If you want to match that entire phrase, enclose it in double quotes inside the string being bound. See https://www.sqlite.org/fts5.html#full_text_query_syntax for more. – Shawn Aug 07 '19 at 07:21
  • thanks. That worked. I did not realize that parens were meaningful in the MATCH queries. If you post your comment as an answer, I will mark it as accepted, and it might be useful for others as well. – punkish Aug 12 '19 at 08:10

0 Answers0