Python/SQLite3 escaping in WHERE-Clause

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

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

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.