escaping search terms in queries with bind params

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

escaping search terms in queries with bind params

Puneet Kishor-2
Using FTS5 (sqlite3 3.29.x), the following works

> SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v MATCH 'Trematoda awaiting allocation’;

but the following fails

> SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v MATCH 'Trematoda (awaiting allocation)’;
Error: fts5: syntax error near “"

Since I am doing these queries in a program, and I can’t predict what characters might be present in my search term, how can I properly escape the query so the following works (showing JavaScript syntax below)

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

res('Trematoda (awaiting allocation)’);




--
Puneet Kishor
Just Another Creative Commoner
http://punkish.org/About

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: escaping search terms in queries with bind params

Dan Kennedy-4

On 7/8/62 13:50, P Kishor wrote:

> Using FTS5 (sqlite3 3.29.x), the following works
>
>> SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v MATCH 'Trematoda awaiting allocation’;
> but the following fails
>
>> SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v MATCH 'Trematoda (awaiting allocation)’;
> Error: fts5: syntax error near “"
>
> Since I am doing these queries in a program, and I can’t predict what characters might be present in my search term, how can I properly escape the query so the following works (showing JavaScript syntax below)
>
> function res(q) {
> const s = 'SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v MATCH ?’;
> return db.prepare(s).get(q);
> }
>
> res('Trematoda (awaiting allocation)’);

Define "works". What do you want it to do for this input?

One approach would be to strip out all characters that may not be part
of fts5 barewords before passing the query to fts5:

https://sqlite.org/fts5.html#fts5_strings

Or you could try the query as input first, then strip out the special
characters and try again only if the first attempt failed - so that
users could use advanced syntax if they get it right.

Dan.




>
>
>
>
> --
> Puneet Kishor
> Just Another Creative Commoner
> http://punkish.org/About
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users