Python String Formats with SQL Wildcards and LIKE

Question

I'm having a hard time getting some sql in python to correctly go through MySQLdb. It's pythons string formatting that is killing me.

My sql statement is using the LIKE keyword with wildcards. I've tried a number of different things in Python. The problem is once I get one of them working, there's a line of code in MySQLdb that burps on string format.

Attempt 1:

"SELECT tag.userId, count(user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user.username LIKE '%%s%'" % (query)

This is a no go. I get value error:

ValueError: unsupported format character ''' (0x27) at index 128

Attempt 2:

"SELECT tag.userId, count(user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user.username LIKE '\%%s\%'" % (query)

I get the same result from attempt 1.

Attempt 3:

like = "LIKE '%" + str(query) + "%'" totalq = "SELECT tag.userId, count(user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user.username " + like

This correctly creates the totalq variable, but now when I go to run the query I get errors from MySQLdb:

File "build/bdist.macosx-10.6-universal/egg/MySQLdb/cursors.py", line 158, in execute query = query % db.literal(args) TypeError: not enough arguments for format string

Attempt 4:

like = "LIKE '\%" + str(query) + "\%'" totalq = "SELECT tag.userId, count(user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user.username " + like

This is the same output as attempt 3.

This all seems really strange. How can I use wildcards in sql statements with python?

Answer 1

Those queries all appear to be vulnerable to SQL injection attacks.

Try something like this instead:

curs.execute("""SELECT tag.userId, count(user.id) as totalRows 
                  FROM user 
            INNER JOIN tag ON user.id = tag.userId 
                 WHERE user.username LIKE %s""", ('%' + query + '%',))

Where there are two arguments being passed to execute().