Running a SQLite query where search criterion may contain '\\' in the string

Python Help
1

I’ve currently got a query defined as follows:
dbcursor.execute('''SELECT DISTINCT field1, field2 FROM table WHERE field3 = (?) ORDER BY field3;''', (search_criterion))

search_criterion is the first element of a tuple derived from the results of an earlier query.

I’m running into the following issue when search_criterion contains a double backslash in the table it’s been retrieved from:
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 25 supplied.

In this instance, search_criterion is a 25 character string comprised of the following: 1017 ALYX 9SM\\Ethel Cain

I’m guessing it’s the presence of \\ in the string that’s causing the issue. How do I construct the query to make it impervious to the presence of \\ in the search string (as it is when processed in Sqlite3 directly)?

2

Aren’t the args supossed to be a tuple?
In which case you need a comma like this (search_criterion,).

3

Passing a tuple as you’ve suggested throws an error: sqlite3.ProgrammingError: Error binding parameter 1: type 'tuple' is not supported

Elsewhere in my code I have many instances of queries along the lines of the following:
dbcursor.execute('''UPDATE table set column = (?) WHERE rowid = (?);''', (islive, row_to_process)) where the likes of row_to_process is defined as records_to_process[3] derived from records_to_process = dbcursor.fetchall()

That executes without issue.

Changing the query to
dbcursor.execute(f'SELECT DISTINCT field1, field2 FROM table WHERE field3 = "{search_criterion}" ORDER BY field3;') works.

Can someone explain why given I understand it’s generally preferable to use the (?) syntax in writing queries?

4

You may already know this, but for posterity - this has to do with a form of malicious exploit called SQL injection. The ‘?’ placeholder syntax can be converted by the database library into a prepared statement, which allows the database engine to distinguish SQL directives from values in cases where a string value might be interpretable as SQL. This is such a common and widespread vulnerability that it is best practice to always use parameterized statements for SQL queries, but especially when any part of a value in the query may have come from user input.

At first I thought your problem was to do with character escaping, but according to the SQLite docs:

C-style escapes using the backslash character are not supported because they are not standard SQL.

So it shouldn’t be interpreting the backslash characters, at least as I read it. Barry is right about your first problem - the second argument to execute is expected to be a sequence. Without the trailing comma, it is interpreting your variable as a sequence of characters (hence trying to bind 25 arguments). Adding the comma like so should work, afaik - so I’m not sure about the second error you’re receiving.

dbcursor.execute(
    '''SELECT DISTINCT field1, field2 FROM table WHERE field3 = (?) ORDER BY field3;''', 
    (search_criterion,)
)

For why using an f-string worked: using the ‘(?)’ syntax uses a prepared statement to execute the query. The query and parameters are sent the database engine as separate values, and the database engine itself binds the parameter values to their placeholders. Using an f-string performs string interpolation in Python before the query is sent to the database engine, so in that case the engine only sees a single complete query with no parameters, and it doesn’t try to perform any parameter binding.

2 Likes
5

Just to be clear this will not pass a security audit as it allows sql injection attack.

Please show your code that raises the tuple error. As @flyinghyrax says it works for us.

6

@flyinghyrax , thanks for a great explanation of what happens in the innards of the database engine. I was aware that the reason for using (?) is to avoid SQL injection, but not what goes on behind the scenes in the db engine, which is why I was curious as to what caused me to have to revert to using a f-string.

@barry-scott I’ll have to reconstruct it, as I went ahead and used the f-string approach.

I’ll reintroduce the offending line in a minute - just got to recreate it this side.

So:

dbcursor.execute('''SELECT DISTINCT genre, style FROM alib WHERE albumartist = (?) ORDER BY albumartist;''', (album_artist)) throws
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 25 supplied.

and:
dbcursor.execute('''SELECT DISTINCT genre, style FROM alib WHERE albumartist = (?) ORDER BY albumartist;''', (album_artist,)) throws no error.

I must’ve mistyped something somewhere to trigger the tuple error. Apologies for the runaround, it had me stumped last night.

2 Likes