Python / SQLite3 output in WHERE-clause

Question

How do I do real escaping in Python for SQLite3?

If I have Google for it (or to search for stackoverflow), there are a lot of questions for this, and each time the answer is something like:

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

This helps against SQL injection, and this is enough if I only did compilations with "=", but this, of course, does not break Wildcards.

So if i do

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

some user could put "%" for an alias and replace all cookies with one line. I could filter it myself (pah ... I probably still forget one of these lesser-known wildcards), I could use lowercase letters with nick and nickname and replace "ilike" with "=", but then what I really would like to do would be something like:

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

Answer 1

, . , , , , , , (, ). :

• . SQLite LIKE only % _ , . , . ( : , ).

• " " , . % _ ( " ", ), , . , "" ASCII, "-" ".", :

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

  , , . , RLIKE, , , .

• , , LIKE ? (, , ), , LIKE:

  • :

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

  • ( "sqlescape(nick)+"%"" ). , :

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

Answer 2

? SQL ( , ).

LIKE/GLOB ; . SQL , escape- ; ESCAPE:

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

( my_like_escape % _ (LIKE) * ? (GLOB).)

Answer 3

Ummm, "ilike" "=", "%" - . ( ) , . sqlLite, ..

, cookie .

, , , UPDATE , .

Answer 4

There are some very interesting ways to do this with the format-ing line .

From the Python Documentation :

Built-in classes strand unicodeprovide the ability to perform complex changes of variables and format values using the method str.format():

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

Other great tricks you can use when formatting strings:

"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'.`