Re: [sqlite] Sanitising user input for FTS5 MATCH parameter
Thanks for the details Dan On Sat, 21 Dec 2019 at 18:40, Dan Kennedy wrote: > > On 20/12/62 22:03, test user wrote: > > Hello, > > > > I have a search box on a website that uses FTS5/MATCH. > > > > MATCH seems to take its own custom language for matching. > > > > 1. Is it safe to just pass the users query to MATCH ? via the SQLite bind > > FFI? > > Users could specify a query that uses excessive resources. In > particular, prefix searches for very common prefixes on large databases > can use a lot of memory. I think it's otherwise safe though. > > > - This would give them full access to the FTS5 matching language. > > > > 2. If not, how should I be sanitising user input? > > > > - E.g. How can I transform a string of words and text into a query? What > > characters should I be removing or escaping? How can I prevent them using > > the FTS5 keywords "AND" "OR" etc? > It really depends on what you want to allow. And how you want the query > interpreted. If you want all input to be treated as a single phrase, > enclose it in double-quotes, doubling any embedded " characters SQL > style. Or, if you wanted the input treated as a list of terms separated > by implicit AND, split the input on whitespace and then enclose each > term in double-quotes. Details here: > >https://www.sqlite.org/fts5.html#full_text_query_syntax > > Dan. > > > > > > > > Thanks > > ___ > > 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 > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sanitising user input for FTS5 MATCH parameter
On 20/12/62 22:03, test user wrote: Hello, I have a search box on a website that uses FTS5/MATCH. MATCH seems to take its own custom language for matching. 1. Is it safe to just pass the users query to MATCH ? via the SQLite bind FFI? Users could specify a query that uses excessive resources. In particular, prefix searches for very common prefixes on large databases can use a lot of memory. I think it's otherwise safe though. - This would give them full access to the FTS5 matching language. 2. If not, how should I be sanitising user input? - E.g. How can I transform a string of words and text into a query? What characters should I be removing or escaping? How can I prevent them using the FTS5 keywords "AND" "OR" etc? It really depends on what you want to allow. And how you want the query interpreted. If you want all input to be treated as a single phrase, enclose it in double-quotes, doubling any embedded " characters SQL style. Or, if you wanted the input treated as a list of terms separated by implicit AND, split the input on whitespace and then enclose each term in double-quotes. Details here: https://www.sqlite.org/fts5.html#full_text_query_syntax Dan. Thanks ___ 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
Re: [sqlite] Sanitising user input for FTS5 MATCH parameter
Yeh but it doesn’t explicitly say anything about how secure it is to pass a string directly to “match”. Other scalar values that are passed to a query via binding are safe as they are just data saved to the DB file. But as the MATCH x string contains a language it is going to be interpreted or compiled which is why I was asking how safe that would be. On Fri, 20 Dec 2019 at 16:05, Jose Isaias Cabrera wrote: > > test user, on Friday, December 20, 2019 10:03 AM, wrote... > > > > Hello, > > > > I have a search box on a website that uses FTS5/MATCH. > > > > MATCH seems to take its own custom language for matching. > > > > 1. Is it safe to just pass the users query to MATCH ? via the SQLite bind > > FFI? > > > > - This would give them full access to the FTS5 matching language. > > > > 2. If not, how should I be sanitising user input? > > > > - E.g. How can I transform a string of words and text into a query? What > > characters should I be removing or escaping? How can I prevent them using > > the FTS5 keywords "AND" "OR" etc? > > Have you taken a look at the FTS5 site[1]? It has lots of information > there that may be helpful. > > josé > > [1] https://www.sqlite.org/fts5.html > > ___ > 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
Re: [sqlite] Sanitising user input for FTS5 MATCH parameter
test user, on Friday, December 20, 2019 10:03 AM, wrote... > > Hello, > > I have a search box on a website that uses FTS5/MATCH. > > MATCH seems to take its own custom language for matching. > > 1. Is it safe to just pass the users query to MATCH ? via the SQLite bind > FFI? > > - This would give them full access to the FTS5 matching language. > > 2. If not, how should I be sanitising user input? > > - E.g. How can I transform a string of words and text into a query? What > characters should I be removing or escaping? How can I prevent them using > the FTS5 keywords "AND" "OR" etc? Have you taken a look at the FTS5 site[1]? It has lots of information there that may be helpful. josé [1] https://www.sqlite.org/fts5.html ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sanitising user input for FTS5 MATCH parameter
Hello, I have a search box on a website that uses FTS5/MATCH. MATCH seems to take its own custom language for matching. 1. Is it safe to just pass the users query to MATCH ? via the SQLite bind FFI? - This would give them full access to the FTS5 matching language. 2. If not, how should I be sanitising user input? - E.g. How can I transform a string of words and text into a query? What characters should I be removing or escaping? How can I prevent them using the FTS5 keywords "AND" "OR" etc? Thanks ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
