Python/SQLite3 escaping in WHERE-Clause

Question

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))

Answer 1

? parameters are intended to avoid formatting problems for SQL strings (and other problematic data types like floating-point numbers and blobs).

LIKE/GLOB wildcards work on a different level; they are always part of the string itself. SQL allows to escape them, but there is no default escape character; you have to choose some with the ESCAPE clause:

escaped_foo = my_like_escape(foo, "\\")
c.execute("UPDATE cookies SET count = ? WHERE nickname LIKE ? ESCAPE '\',
          (cookies, escaped_foo))

(And you have to write your own my_like_escape function for % and _ (LIKE) or * and ? (GLOB).)

Answer 2

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))
     

Answer 3

Ummm, normally you'd want just replace 'ilike' with normal '=' comparison that doesn't interpret '%' in any special way. Escaping (effectively blacklisting of bad patterns) is error prone, e.g. even if you manage to escape all known patterns in the version of sqlLite you use, any future upgrade can put you at risk, etc..

It's not clear to me why you'd want to mass-update cookies based on a fuzzy match on user name.

If you really want to do that, my preferred approach would be to SELECT the list first and decide what to UPDATE at the application level to maintain a maximum level of control.

Answer 4

There are several very fun ways to do this with string format-ing.

From Python's Documentation:

The built-in str and unicode classes provide the ability to do complex variable substitutions and value formatting via the str.format() method:

s = "string"
c = "Cool"
print "This is a {0}. {1}, huh?".format(s,c)
#=> This is a string. Cool, huh? 

Other nifty tricks you can do with string formatting:

"First, thou shalt count to {0}".format(3) # References first positional argument
"Bring me a {}".format("shrubbery!")       # Implicitly references the first positional argument
"From {} to {}".format('Africa','Mercia')      # Same as "From {0} to {1}"
"My quest is {name}"                       # References keyword argument 'name'
"Weight in tons {0.weight}"                # 'weight' attribute of first positional arg
"Units destroyed: {players[0]}"            # First element of keyword argument 'players'.`