On 05/24/2018 01:27 AM, Ben Asher wrote:
Also one other question: with this method of using a parameter in the MATCH
expression, do I still need to manually escape double quotes in the string
bound to the parameter by replacing them with a pair of double quotes (for
FTS5)?
Maybe. What do you need this to do?
If you execute:
SELECT rowid FROM test_fts_index WHERE text MATCH '"' || ? || '"*'
then the core evaluates the SQL expression ('"' || ? || '"*') and passes
the results to FTS5. So if you bind the 5 byte string [a b c] to the
variable, then your query is equivalent to:
SELECT rowid FROM test_fts_index WHERE text MATCH '"a b c"*';
If you bind the 3 byte string [a"c], then your query is as:
SELECT rowid FROM test_fts_index WHERE text MATCH '"a"b"c"*';
and FTS5 reports an error.
Dan.
I'm not seeing any errors, but now I'm second guessing myself.
Ben
On Wed, May 23, 2018 at 10:46 AM, Ben Asher <benashe...@gmail.com> wrote:
Ah I see. Clever! The final solution ended up being:
SELECT rowid FROM test_fts_index WHERE text MATCH '"' || ? || '"*'
with || on either side of the ?. Does that sound right? Without that extra
||, sqlite returned a syntax error (near ?).
As follow up, it'd be great to see this solution documented somewhere on
the FTS5 page: https://sqlite.org/fts5.html. I'm glad there's a way to
accomplish this, but the solution is a bit more clever than I would have
come up with on my own.
Thanks again for your help!
Ben
On Wed, May 23, 2018 at 1:15 AM, Dan Kennedy <danielk1...@gmail.com>
wrote:
On 05/23/2018 06:02 AM, Ben Asher wrote:
Hi there! I'm working with a FTS5 query like this:
SELECT rowid FROM text_fts_index WHERE text MATCH ?
The parameter is something like this: "user input"*
The idea here is to do a MATCH prefix-type query in FTS5. The problem
with
this query is that it breaks if the user input contains double quotes.
What
I want to do is something like this:
SELECT rowid FROM text_fts_index WHERE text MATCH "?"*
Maybe:
SELECT rowid FROM test_fts_index WHERE text MATCH '"' ? || '"*'
Dan.
This 2nd version of the query doesn't appear to work though because the ?
is escaped in the quotes and doesn't appear to be recognized as a
variable.
Is there a way I'm missing to do this kind of MATCH prefix query and only
escape the user input?
Thanks!
Ben
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
Ben
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
