Search code examples
pythonsqlsqliteescapingsql-like

Python/SQLite3 escaping in WHERE-Clause

How should i do real escaping in Python for SQLite3?

If i google for it (or search stackoverflow) there are tons of questions for this and every time the response is something like:

dbcursor.execute("SELECT * FROM `foo` WHERE `bar` like ?", ["foobar"])

This helps against SQL-Injections, and is enough if i would do just comperations with "=" but it doesn't stripe Wildcards of course.

So if i do 

cursor.execute(u"UPDATE `cookies` set `count`=? WHERE `nickname` ilike ?", (cookies, name))

some user could supply "%" for a nickname and would replace all of the cookie-entries with one line. I could filter it myself (ugh… i probably will forget one of those lesser-known wildcards anyway), i could use lowercase on nick and nickname and replace "ilike" with "=", but what i would really like to do would be something along the lines of:

foo = sqlescape(nick)+"%"
cursor.execute(u"UPDATE `cookies` set `count`=? WHERE `nickname` ilike ?", (cookies, foo))
Solution

  • You've avoided outright code injection by using parametrized queries. Now it seems you're trying to do a pattern match with user-supplied data, but you want the user-supplied portion of the data to be treated as literal data (hence no wildcards). You have several options:

    1. Just filter the input. SQLite's LIKE only understands % and _ as wildcards, so it's pretty hard to get it wrong. Just make sure to always filter inputs. (My preferred method: Filter just before the query is constructed, not when user input is read).

    2. In general, a "whitelist" approach is considered safer and easier than removing specific dangerous characters. That is, instead of deleting % and _ from your string (and any "lesser-known wildcards", as you say), scan your string and keep only the characters you want. E.g., if your "nicknames" can contain ASCII letters, digits, "-" and ".", it can be sanitized like this:

      name = re.sub(r"[^A-Za-z\d.-]", "", name)

      This solution is specific to the particula field you are matching against, and works well for key fields and other identifiers. I would definitely do it this way if I had to search with RLIKE, which accepts full regular expressions so there are a lot more characters to watch out for.

    3. If you don't want the user to be able to supply a wildcard, why would you use LIKE in your query anyway? If the inputs to your queries come from many places in the code (or maybe you're even writing a library), you'll make your query safer if you can avoid LIKE altogether:

      • Here's case insensitive matching:

        SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE

      • In your example you use prefix matching ("sqlescape(nick)+"%""). Here's how to do it with exact search:

        size = len(nick)
cursor.execute(u"UPDATE `cookies` set `count`=? WHERE substr(`nickname`, 1, ?) = ?", 
                (cookies, size, nick))