SQLitePreparedStatement not properly escaping single quotes?

Databases
1

Here’s the code in question

dim rs as RowSet
Var stmt As SQLitePreparedStatement
stmt = SQLitePreparedStatement(db.Prepare("SELECT *, rank FROM SongSearch WHERE SongSearch MATCH ? ORDER BY rank LIMIT 5;"))
stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
stmt.Bind(0, Artist + " " + Title)
rs = stmt.SelectSQL

Everything works fine as long as my string variables, Artist and Title, don’t have a quote character. For example, Artist = “Ariana Grande”, Title = “Into You” executes correctly and delivers the proper search results. But if Artist = “Journey”, Title = “Don’t Stop Believin” I get error 25, Column Index Out Of Range.

Isn’t SQLitePreparedStatement supposed to take care of that for you?
How can I see the fully escaped query that stmt is sending to the SelectSQL method?
Trying to manually escape it, for example, changing it to “Don’'t Stop Believin” also fails with the same error.

Any guidance would be welcomed, thanks in advance.

2

What if you try this instead?

var rs as RowSet

rs = db.SelectSQL( _
    "SELECT *, rank FROM SongSearch WHERE SongSearch MATCH ? ORDER BY rank LIMIT 5", _
    Artist + " " + Title )
3

This also fails with a quote mark. Or even when I manually escape the quotes.

I think this has something to do with it being an FTS5 query, which apparently is very picky. If I remove the quote mark entirely I don’t get the full result set I should.

4

Can you share a sample project?

5

Figured it out. FTS5 requires each search token to be escaped and enclosed in double quotes, with the entire query enclosed in single quotes. So SQLitePreparedStatements will be incompatible with it.

This is the code that ultimately ended up working:

var sstr() as string = split(Artist + " " + Title, " ")
var query as string
for each str as string in sstr
  query = query + Chr(34)+ ReplaceAll(ReplaceAll(str, "'", "''"), Chr(34), Chr(34)+Chr(34)) + Chr(34)+ " "
next
rs = db.SelectSQL( _
"SELECT *, rank FROM SongSearch WHERE SongSearch MATCH '" + query + "' ORDER BY rank LIMIT 5")

Thanks for the responses everyone.