Python TypeError when building query string

Question

I am building a query string.

query = "SELECT seqid, date, name, steamid, team, text, type, '%s' as triggerword, '%s' as weight FROM chatlogs WHERE date > '%s' AND text LIKE '%%%s%%' ORDER BY DATE" % (word, weight, self.setting['LAST_RUN_TIME'], word)

If I print the string, it returns correctly:

SELECT seqid, date, name, steamid, team, text, type, 'hunting_term' as triggerword, '0.01' as weight FROM chatlogs WHERE date > '2012-04-18 23:47:58.439124' AND text LIKE '%hunting_term%' ORDER BY DATE

However, it then fails with a TypeError exception: TypeError: not enough arguments for format string

Full code:

import database

mysqldb = database.Connection('localhost','mydb',user='myuser',password='mypass')

word = 'hunting_term'
weight = 0.01
setting = dict({'LAST_RUN_TIME':'2012-04-18 23:47:58.439124'})

query = "SELECT seqid, date, name, steamid, team, text, type, '%s' as triggerword, '%s' as weight FROM chatlogs WHERE date > '%s' AND text LIKE '%%%s%%' ORDER BY DATE" % (word, weight, setting['LAST_RUN_TIME'], word)
print query

for message in mysqldb.query(query):        # This is the line it throws the exception on
    print "A row"

database is from the tornado package

What happens if you change '%%%s%%' to '%%%%%s%%%%'?

Steven Rumbalski
– Steven Rumbalski

2012-04-19 17:18:40 +00:00
Commented Apr 19, 2012 at 17:18 — Steven Rumbalski
– Steven Rumbalski, Commented Apr 19, 2012 at 17:18

Andrew Clark · Accepted Answer · 2012-04-19 17:44:03Z

6

The issue here is that the eventual call to MySQLdb looks something like this:

query = "SELECT seqid, date, name, steamid, team, text, type, 'hunting_term' as triggerword, '0.01' as weight FROM chatlogs WHERE date > '2012-04-18 23:47:58.439124' AND text LIKE '%hunting_term%' ORDER BY DATE"
db.cursor().execute(query, ())

The first argument to db.cursor().execute() should be a format string, and the second argument should be the replacements for that format string, you can see this in the MySQLdb docs. In other words it will execute the following code:

query % ()

As you can see, this will cause the same TypeError:

>>> query % ()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: not enough arguments for format string

This means that any literal % that you want MySQL to see needs to be a %% when it is seen by db.cursor().execute(), so you should be able to fix this by changing your original format string to the following:

query = "SELECT seqid, date, name, steamid, team, text, type, '%s' as triggerword, '%s' as weight FROM chatlogs WHERE date > '%s' AND text LIKE '%%%%%s%%%%' ORDER BY DATE" % (word, weight, self.setting['LAST_RUN_TIME'], word)

However the correct way to do this is to let MySQLdb perform the substitutions for you, which would change your code to the following:

query = "SELECT seqid, date, name, steamid, team, text, type, %s as triggerword, %s as weight FROM chatlogs WHERE date > %s AND text LIKE %s ORDER BY DATE"
parameters = (word, weight, setting['LAST_RUN_TIME'], '%%%s%%' % word)
for message in mysqldb.query(query, *parameters):
    print "A row"

edited Apr 19, 2012 at 17:44

answered Apr 19, 2012 at 17:36

Andrew Clark

210k36 gold badges285 silver badges310 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Steven Rumbalski Over a year ago

Letting MySQLdb perform the substitutions for you is preferred because it will protect against sql injection.

Mark Ransom Over a year ago

@StevenRumbalski, if that's the way MySQLdb performs substitutions then it is an invitation to SQL injection, not a protection from it.

Andrew Clark Over a year ago

@MarkRansom, actually letting MySQLdb do it for you is safer, because it performs all of the necessary escaping to make sure that a %s always becomes a single value. I simplified what happens on the MySQLdb side, it is more complicated than query % parameters.

Steven Rumbalski Over a year ago

@MarkRansom: I wrote that after perusing the source: query = query % db.literal(args). I assume db.literal does the proper escaping.

Mark Ransom Over a year ago

@StevenRumbalski and F.J, OK I believe you.

Collectives™ on Stack Overflow

Python TypeError when building query string

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related