sqlite full text search sqlite breaks when there is a dash in input

Question

I've got a form with a search bar on my web app

@app.route('/search', methods=['POST','GET'])
def row_search():
    if request.method == 'POST':
        result = request.form
        print(result['search'])
        cursor.execute("""SELECT rowid,projeYili,projeAdi,ogretmenler,ogrenciler 
                    FROM projects
                    WHERE projects MATCH '{}'
                    """.format(result['search']))
    return render_template('index.html', projects=cursor.fetchall())

I've used MATCH to search in table named projects but if i enter special characters(?,-,.) in my search bar i get an error

for example if i enter 2018-2019 in my search bar i get this error sqlite3.OperationalError: no such column: 2019 code works perfect with text with no special characters

And your first step is to not put user-supplied values directly in a sql query; bind it to a parameter in the query instead. https://bobby-tables.com/python — Shawn, Jan 18 '20 at 22:58
@Shawn im using FTS5 youre right i changed my code to bind the query and values but its still giving the same error — , Jan 19 '20 at 06:04
This might be useful: https://stackoverflow.com/questions/1296180/android-quotes-within-an-sql-query-string — jignatius, Jan 19 '20 at 06:48

score 2 · Accepted Answer · answered Jan 19 '20 at 07:55

In a column filter, a dash means to NOT look at the following columns. It's possible that what you're entering as a search query is getting parsed so that -2019 is considered a column filter. I'm not sure how it's happening given column filters are marked with a colon, but it would explain the error message. I'm able to reproduce it, will try to dig in more later.

You can, however, search for a multi-word phrase by enclosing it in double quotes, so "2018-2019" and "2018 2019" will both match (The default tokenizer settings use dash as a word separator). Searching for 2018 2019, though, will match anything with both words no matter where they appear, not just adjacent.

Examples:

sqlite> CREATE VIRTUAL TABLE test USING fts5(body);
sqlite> INSERT INTO test VALUES ('in the years 2018-2019 something happened.');
sqlite> INSERT INTO test VALUES ('It was 2018 and then it was 2019');
sqlite> SELECT * FROM test WHERE test MATCH '2018-2019';
Error: no such column: 2019
sqlite> SELECT * FROM test WHERE test MATCH '"2018-2019"';
body                                      
------------------------------------------
in the years 2018-2019 something happened.
sqlite> SELECT * FROM test WHERE test MATCH '"2018 2019"';
body                                      
------------------------------------------
in the years 2018-2019 something happened.
sqlite> SELECT * FROM test WHERE test MATCH '2018 2019';
body                                      
------------------------------------------
in the years 2018-2019 something happened.
It was 2018 and then it was 2019

For more details about the query synax, see the documentation.

sqlite full text search sqlite breaks when there is a dash in input

1 Answers1