Prepared statements in FTS MATCH queries

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

Prepared statements in FTS MATCH queries

mailing lists
For normal tables I can use something like:

SELECT * FROM Names WHERE FirstNames=? AND or OR LastName=?;

For FTS tables I can only use

SELECT * FROM FTSNames WHERE FirstNames MATCH ? OR LastName MATCH ?; AND is not supported (still do not know why)

Is there any possibility to use prepared statements for FTS tables with an AND condition? I like to prevent code injection.

Regards,
Hartwig

_______________________________________________
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: Prepared statements in FTS MATCH queries

Dan Kennedy-4

On 1/11/62 00:32, mailing lists wrote:
> For normal tables I can use something like:
>
> SELECT * FROM Names WHERE FirstNames=? AND or OR LastName=?;
>
> For FTS tables I can only use
>
> SELECT * FROM FTSNames WHERE FirstNames MATCH ? OR LastName MATCH ?; AND is not supported (still do not know why)
>
> Is there any possibility to use prepared statements for FTS tables with an AND condition? I like to prevent code injection.

As of 3.30.0, should work with FTS5.

Dan.


>
> Regards,
> Hartwig
>
> _______________________________________________
> 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
Reply | Threaded
Open this post in threaded view
|

Re: Prepared statements in FTS MATCH queries

mailing lists
Hi Dan,

I did not know that. What was the reason that it did not work before 3.30?

Regards,
Hartwig

> Am 2019-10-31 um 19:16 schrieb Dan Kennedy <[hidden email]>:
>
>
> On 1/11/62 00:32, mailing lists wrote:
>> For normal tables I can use something like:
>>
>> SELECT * FROM Names WHERE FirstNames=? AND or OR LastName=?;
>>
>> For FTS tables I can only use
>>
>> SELECT * FROM FTSNames WHERE FirstNames MATCH ? OR LastName MATCH ?; AND is not supported (still do not know why)
>>
>> Is there any possibility to use prepared statements for FTS tables with an AND condition? I like to prevent code injection.
>
> As of 3.30.0, should work with FTS5.
>
> Dan.
>
>
>>
>> Regards,
>> Hartwig
>>
>> _______________________________________________
>> 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

_______________________________________________
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: Prepared statements in FTS MATCH queries

Dan Kennedy-4

On 1/11/62 03:03, mailing lists wrote:
> Hi Dan,
>
> I did not know that. What was the reason that it did not work before 3.30?


The implementation of the xBestIndex method of fts3/4, and fts5 prior to
3.30.0, only allowed a single MATCH constraint to be processed and
passed through to xFilter. Fts5 now uses a more complicated method to
pass constraints between those two methods, which allows the details of
multiple MATCH constraints to be passed through.

Dan.




>
> Regards,
> Hartwig
>
>> Am 2019-10-31 um 19:16 schrieb Dan Kennedy <[hidden email]>:
>>
>>
>> On 1/11/62 00:32, mailing lists wrote:
>>> For normal tables I can use something like:
>>>
>>> SELECT * FROM Names WHERE FirstNames=? AND or OR LastName=?;
>>>
>>> For FTS tables I can only use
>>>
>>> SELECT * FROM FTSNames WHERE FirstNames MATCH ? OR LastName MATCH ?; AND is not supported (still do not know why)
>>>
>>> Is there any possibility to use prepared statements for FTS tables with an AND condition? I like to prevent code injection.
>> As of 3.30.0, should work with FTS5.
>>
>> Dan.
>>
>>
>>> Regards,
>>> Hartwig
>>>
>>> _______________________________________________
>>> 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
> _______________________________________________
> 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
Reply | Threaded
Open this post in threaded view
|

Re: Prepared statements in FTS MATCH queries

mailing lists
Thanks.

Is there a difference between these statements with respect results, performance etc. or are both statements describe the same thing?

a) SELECT * FROM NamesFTS WHERE FTS MATCH 'LastName:alpha FirstNames:beta';
b) SELECT * FROM NamesFTS WHERE LastName MATCH 'alpha' AND FirstNames MATCH 'beta';

Regards,
Hartwig

> Am 2019-11-01 um 07:55 schrieb Dan Kennedy <[hidden email] <mailto:[hidden email]>>:
>
>
> On 1/11/62 03:03, mailing lists wrote:
>> Hi Dan,
>>
>> I did not know that. What was the reason that it did not work before 3.30?
>
>
> The implementation of the xBestIndex method of fts3/4, and fts5 prior to 3.30.0, only allowed a single MATCH constraint to be processed and passed through to xFilter. Fts5 now uses a more complicated method to pass constraints between those two methods, which allows the details of multiple MATCH constraints to be passed through.
>
> Dan.
>
>
>
>
>>
>> Regards,
>> Hartwig
>>
>>> Am 2019-10-31 um 19:16 schrieb Dan Kennedy <[hidden email] <mailto:[hidden email]>>:
>>>
>>>
>>> On 1/11/62 00:32, mailing lists wrote:
>>>> For normal tables I can use something like:
>>>>
>>>> SELECT * FROM Names WHERE FirstNames=? AND or OR LastName=?;
>>>>
>>>> For FTS tables I can only use
>>>>
>>>> SELECT * FROM FTSNames WHERE FirstNames MATCH ? OR LastName MATCH ?; AND is not supported (still do not know why)
>>>>
>>>> Is there any possibility to use prepared statements for FTS tables with an AND condition? I like to prevent code injection.
>>> As of 3.30.0, should work with FTS5.
>>>
>>> Dan.
>>>
>>>
>>>> Regards,
>>>> Hartwig
>>>>
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [hidden email] <mailto:[hidden email]>
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email] <mailto:[hidden email]>
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email] <mailto:[hidden email]>
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>
> _______________________________________________
> sqlite-users mailing list
> [hidden email] <mailto:[hidden email]>
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users <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
Reply | Threaded
Open this post in threaded view
|

Re: Prepared statements in FTS MATCH queries

Dan Kennedy-4

On 1/11/62 19:15, mailing lists wrote:
> Thanks.
>
> Is there a difference between these statements with respect results, performance etc. or are both statements describe the same thing?


They're the same in all important respects.

Dan.



>
> a) SELECT * FROM NamesFTS WHERE FTS MATCH 'LastName:alpha FirstNames:beta';
> b) SELECT * FROM NamesFTS WHERE LastName MATCH 'alpha' AND FirstNames MATCH 'beta';
>
> Regards,
> Hartwig
>
>> Am 2019-11-01 um 07:55 schrieb Dan Kennedy <[hidden email] <mailto:[hidden email]>>:
>>
>>
>> On 1/11/62 03:03, mailing lists wrote:
>>> Hi Dan,
>>>
>>> I did not know that. What was the reason that it did not work before 3.30?
>>
>> The implementation of the xBestIndex method of fts3/4, and fts5 prior to 3.30.0, only allowed a single MATCH constraint to be processed and passed through to xFilter. Fts5 now uses a more complicated method to pass constraints between those two methods, which allows the details of multiple MATCH constraints to be passed through.
>>
>> Dan.
>>
>>
>>
>>
>>> Regards,
>>> Hartwig
>>>
>>>> Am 2019-10-31 um 19:16 schrieb Dan Kennedy <[hidden email] <mailto:[hidden email]>>:
>>>>
>>>>
>>>> On 1/11/62 00:32, mailing lists wrote:
>>>>> For normal tables I can use something like:
>>>>>
>>>>> SELECT * FROM Names WHERE FirstNames=? AND or OR LastName=?;
>>>>>
>>>>> For FTS tables I can only use
>>>>>
>>>>> SELECT * FROM FTSNames WHERE FirstNames MATCH ? OR LastName MATCH ?; AND is not supported (still do not know why)
>>>>>
>>>>> Is there any possibility to use prepared statements for FTS tables with an AND condition? I like to prevent code injection.
>>>> As of 3.30.0, should work with FTS5.
>>>>
>>>> Dan.
>>>>
>>>>
>>>>> Regards,
>>>>> Hartwig
>>>>>
>>>>> _______________________________________________
>>>>> sqlite-users mailing list
>>>>> [hidden email] <mailto:[hidden email]>
>>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [hidden email] <mailto:[hidden email]>
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email] <mailto:[hidden email]>
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email] <mailto:[hidden email]>
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users <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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users