Re: [sqlite] FTS3 bug

[Jonas Bengtsson]

When I got that error I, for some reason, interpreted it as an
SQL-level error message, and not an FTS-level one. Now I realise that
it indicates an FTS-level error, so it makes sense. Since I thought it
was an SQL-level error I thought it was an SQL injection bug. Sorry
for crying wolf.

It would be nice if there was a mode which was more accepting towards
FTS query errors for end user authored queries, but now I'm happy when
I understand why I got the error and can deal with it appropriately.

Cheers,
 Jonas

On Sat, Jan 15, 2011 at 3:11 PM, Richard Hipp  wrote:
> On Fri, Jan 14, 2011 at 8:18 PM, Jonas Bengtsson  wrote:
>
>> Hi all,
>>
>> I've found a defect in FTS3 where it will accept matching with the
>> string "" (two quotes) but not """  (three quotes).
>>
>> Three double-quotes is not a valid query string, so FTS3/4 returns an error
> code.
>
> What are you suggesting it should do?  Ignore the error?
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3 bug

[Richard Hipp]

On Fri, Jan 14, 2011 at 8:18 PM, Jonas Bengtsson  wrote:

> Hi all,
>
> I've found a defect in FTS3 where it will accept matching with the
> string "" (two quotes) but not """  (three quotes).
>
> Three double-quotes is not a valid query string, so FTS3/4 returns an error
code.

What are you suggesting it should do?  Ignore the error?



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3 bug

[Jonas Bengtsson]

Hi Phil,

I am binding the values, not just concatenating them into the query or
manually escaping them. So that's why I believe there's a bug in FTS3.
I'm binding the value, but still it fails depending on the value of
the variable.

In Python it's:
cursor.execute('SELECT * FROM MyTable WHERE MyTable MATCH ?', ('"""',))
(binding the string """  [3 x "] to ?)

In Android it's:
cursor = db.rawQuery("SELECT * FROM MyTable WHERE MyTable MATCH ?",
new String[]{"\"\"\""});
(binding the string """  [3 x "] to ?)

But you are probably correct in that trying to sanitize user input
isn't where I want to go. Instead I will catch the exception and
ignore the error.

Cheers,
 Jonas

On Sat, Jan 15, 2011 at 12:20 PM, Philip Graham Willoughby
 wrote:
> On 15 Jan 2011, at 01:18, Jonas Bengtsson wrote:
>
>> 2) Any suggestions as to how to sanitize the user's input to avoid
>> this problem? This is just the one I've found, but I'm assuming there
>> are more.
>
> Don't try and sanitize the user's input - you will never be able to prove you 
> have accounted for everything. The correct way to do this is to stop giving 
> the user's input to sqlite in your query string. Instead you should use 
> symbolic literals to which you bind the values the user has input. If your 
> language's sqlite wrapper doesn't support binding values then report that 
> omission as a bug to the providers of that library.
>
> See sqlite3_bind_* on this page http://www.sqlite.org/c3ref/funclist.html for 
> the C interface.
>
> Best Regards,
>
> Phil Willoughby
> --
> Managing Director, StrawberryCat Limited
>
> StrawberryCat Limited is registered in England and Wales with Company No. 
> 7234809.
>
> The registered office address of StrawberryCat Limited is:
>
> 107 Morgan Le Fay Drive
> Eastleigh
> SO53 4JH
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3 bug

[Philip Graham Willoughby]

On 15 Jan 2011, at 01:18, Jonas Bengtsson wrote:

> 2) Any suggestions as to how to sanitize the user's input to avoid
> this problem? This is just the one I've found, but I'm assuming there
> are more.

Don't try and sanitize the user's input - you will never be able to prove you 
have accounted for everything. The correct way to do this is to stop giving the 
user's input to sqlite in your query string. Instead you should use symbolic 
literals to which you bind the values the user has input. If your language's 
sqlite wrapper doesn't support binding values then report that omission as a 
bug to the providers of that library.

See sqlite3_bind_* on this page http://www.sqlite.org/c3ref/funclist.html for 
the C interface.

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3 bug?

[Iker Arizmendi]

Dan Kennedy wrote:
> On 12/08/2010 04:18 AM, Iker Arizmendi wrote:
>> The function that opens a cursor for the simple tokenizer,
>> simpleOpen, does not set the "pTokenizer" member of the
>> returned cursor. Ie, it appears the following line is
>> missing:
>>
>>  c->base.pTokenizer = pTokenizer;
>>
>> which causes problems in simpleNext . Possible bug?
> 
> How do we reproduce the problem?

The problem doesn't show up with the stock tokenizers
since the functions that open cursors in sqlite3.c set
the pTokenizer member on return from xOpen. My custom
tokenizer hit upon the issue because it wraps calls to
the simple xOpen and treated the returned cursors as
opaque objects.

Iker


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3 bug?

[Dan Kennedy]

On 12/08/2010 04:18 AM, Iker Arizmendi wrote:
> The function that opens a cursor for the simple tokenizer,
> simpleOpen, does not set the "pTokenizer" member of the
> returned cursor. Ie, it appears the following line is
> missing:
>
>  c->base.pTokenizer = pTokenizer;
>
> which causes problems in simpleNext . Possible bug?

How do we reproduce the problem?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3 bug with MATCH plus OR

[Ralf Junker]

Thanks to both Scott and Dan for your answers!

Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3 bug with MATCH plus OR

[Dan Kennedy]

On Mar 3, 2010, at 6:26 PM, Ralf Junker wrote:

> The recent changes to FTS3 fixed a long standing problem with MATCH  
> and
> AND operators combined. Take this schema:
>
> drop table if exists myfts;
> create virtual table myfts using fts3 (a);
> insert into myfts values ('one');
> insert into myfts values ('two');
>
> This following query produced an "unable to use function MATCH in the
> requested context" error up to 3.6.21, IIRC. The workaround was to  
> add a
> + sign in front of the rowid. Since 3.6.22 it gladly works even  
> without
> the + sign:
>
> select * from myfts where (myfts MATCH 'one') and (rowid=1);
>
> However, a similiar problem is still present using "or" instead "and".
> Even more problematic, the +rowid workaround no longer helps. Both  
> these
> queries fail:
>
> select * from myfts where (myfts MATCH 'one') or (rowid=1);
> select * from myfts where (myfts MATCH 'one') or (+rowid=1);
>
> Is this something that should be addressed?

Unfortunately it's the nature of the virtual table interface that
not all queries that include MATCH operators can be implemented.
In theory this particular case could be supported, but it would
involve some difficult to test changes to the query planner. And
there would still be other expressions with MATCH that would not
work.

Best approach is probably to use a "rowid IN (...sub-select...)"
clause as Scott suggested.

Dan.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3 bug with MATCH plus OR

[Scott Hess]

I can't speak to the question of whether it's a real problem, but I
bet you can work around with a sub-select.  Something like:

select * from myfts where rowid = 1 OR rowid IN (select rowid from
myfts where (myfts MATCH 'one'));

-scott


On Wed, Mar 3, 2010 at 3:26 AM, Ralf Junker  wrote:
> The recent changes to FTS3 fixed a long standing problem with MATCH and
> AND operators combined. Take this schema:
>
> drop table if exists myfts;
> create virtual table myfts using fts3 (a);
> insert into myfts values ('one');
> insert into myfts values ('two');
>
> This following query produced an "unable to use function MATCH in the
> requested context" error up to 3.6.21, IIRC. The workaround was to add a
> + sign in front of the rowid. Since 3.6.22 it gladly works even without
> the + sign:
>
> select * from myfts where (myfts MATCH 'one') and (rowid=1);
>
> However, a similiar problem is still present using "or" instead "and".
> Even more problematic, the +rowid workaround no longer helps. Both these
> queries fail:
>
> select * from myfts where (myfts MATCH 'one') or (rowid=1);
> select * from myfts where (myfts MATCH 'one') or (+rowid=1);
>
> Is this something that should be addressed?
>
> Ralf
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users