I see. Yep that clears things up. Thanks again!
Ben
On Wed, May 23, 2018 at 11:36 AM, Dan Kennedy <danielk1...@gmail.com> wrote:
> 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
>
--
Ben
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
