Hi all,
I've found a defect in FTS3 where it will accept matching with the
string "" (two quotes) but not """ (three quotes). I first came
across the issue on Android, but have since reproduced it in Python.
1) I assume this is the place to post FTS3 bug reports, please let me
know otherwise.
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.
Cheers,
Jonas
Python code
=========
import sqlite3
con = sqlite3.connect(':memory:')
cursor = con.cursor()
cursor.execute('CREATE VIRTUAL TABLE MyTable USING fts3 (a TEXT)')
cursor.execute('SELECT * FROM MyTable WHERE MyTable MATCH ?', ('""',))
# works well
cursor.execute('SELECT * FROM MyTable WHERE MyTable MATCH ?',
('"""',)) # SQL logic error or missing database
Example Python session
==================
$ python
Python 2.6.5 (r265:79063, Jun 12 2010, 17:07:01)
[GCC 4.3.4 20090804 (release) 1] on cygwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
cursor = con.cursor()
cursor.execute('CREATE VIRTUAL TABLE MyTable USING fts3 (a TEXT)')
cursor.execute('SELECT * FROM MyTable WHERE MyTable MATCH ?', ('""',))
# works well
cursor.execute('SELECT * FROM MyTable WHERE MyTable MATCH ?',
('"""',)) # SQL logic error or missing database
>>> con = sqlite3.connect(':memory:')
>>> cursor = con.cursor()
>>> cursor.execute('CREATE VIRTUAL TABLE MyTable USING fts3 (a TEXT)')
<sqlite3.Cursor object at 0x7ff457a0>
>>> cursor.execute('SELECT * FROM MyTable WHERE MyTable MATCH ?', ('""',)) #
>>> works well
<sqlite3.Cursor object at 0x7ff457a0>
>>> cursor.execute('SELECT * FROM MyTable WHERE MyTable MATCH ?', ('"""',)) #
>>> SQL logic error or missing database
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
sqlite3.OperationalError: SQL logic error or missing database
Android code
=========
db.execSQL("CREATE VIRTUAL TABLE MyTable USING fts3 (a TEXT)");
// match with "" (works fine)
Cursor cursor = db.rawQuery("SELECT * FROM MyTable WHERE MyTable MATCH
?", new String[]{"\"\""});
int count = cursor.getCount();
cursor.close();
// match with """ (throws exception)
cursor = db.rawQuery("SELECT * FROM MyTable WHERE MyTable MATCH ?",
new String[]{"\"\"\""});
count = cursor.getCount(); // here
cursor.close();
Android exception
=============
android.database.sqlite.SQLiteException: SQL logic error or missing database
at android.database.sqlite.SQLiteQuery.native_fill_window(Native Method)
at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:70)
at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:293)
at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:266)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
