Problem passing Tuple to MySQLdb in python

Question

I have some sql queries that work through python mysqldb to my mysql database, but i want to make them a little less sql-injection vulnerable so little bobby tables doesn't try to add data..

for example:

ORIGINAL:
(This works, so ListID etc is definitely valid)

sql="SELECT NAME FROM ListsTable WHERE ID=%s"%(ListID)  
c.execute(sql)  

ATTEMPTED WITH TUPLE:

sql="SELECT NAME FROM ListsTable WHERE ID=%s"  
c.execute(sql,(ListID,))  

WORKS:

sql="SELECT NAME FROM ListsTable WHERE ID=%s"  
c.execute(sql, ListID)  

I don't know why that 2nd attempt doesn't work as a tuple but accepts it as a single parameter, but either way for another statement i need to pass multiple parameters so this won't work for that:

ORIGINAL:

sql="SELECT * FROM ListsTable ORDER BY ID DESC LIMIT %s,%s"%(Page, (Page*20)+20)  
c.execute(sql)

which works, but then this doesn't, again if i try to send the parameters as a tuple:

sql="SELECT * FROM ListsTable ORDER BY ID DESC LIMIT %s,%s"  
var1=Page  
var2=(Page*20)+20  
params=(var1,var2)              
c.execute(sql,params)  

or even just

sql="SELECT * FROM ListsTable ORDER BY ID DESC LIMIT %s,%s"  
c.execute(sql,(Page,(Page*20)+20))  

I've recently got this error on my webserver log, though note this MAY be a redherring due to the many different things I've been trying as it hasn't errored before: (The error refers to the above attempt of passing the "params" variable)

File "process.py", line 98, in main  
c.execute(sql,params)  
File "/var/www/cgi-bin/MySQLdb/cursors.py", line 159, in execute  
query = query % db.literal(args)  
TypeError: not enough arguments for format string  

EDIT: in case it helps, i'm using mysqldb version 1.2.3, in case it didn't accept tuples at that version, but don't get me started on how rubbish the mysqldb docs are..

Answer 1

Your version of mysqldb must be the problem.

http://mysql-python.hg.sourceforge.net/hgweb/mysql-python/MySQLdb-2.0/file/5a7c30cd9de2/MySQLdb/cursors.py#l180

was changed to:

1.51 -        if args is not None:
1.52 -            query = query % self.connection.literal(args)
1.53          try:
1.54 +            if args is not None:
1.55 +                query = query % tuple(map(self.connection.literal, args))

http://mysql-python.hg.sourceforge.net/hgweb/mysql-python/MySQLdb-2.0/diff/98d968f5af11/MySQLdb/cursors.py

Which is later changed to:

1.144 -                query = query % tuple(map(self.connection.literal, args))
1.145 +                query = query % tuple(( get_codec(a, self.encoders)(db, a) for a in args ))

http://mysql-python.hg.sourceforge.net/hgweb/mysql-python/MySQLdb-2.0/diff/d9bb912776a5/MySQLdb/cursors.py#l1.144

Answer 2

In MySQLdb 1.2.3, a query comes from string substitution using the first argument as the model string, and the second argument as a list of parameters or a single parameter, passed through con.literal. con.literal takes what you have supplied and tries to turn it into a string that gets parsed correctly by MySQL. It adds apostrophes to strings. It outputs numbers without quotes. Or for example, if you give it a string with an apostrophe, it quotes the apostrophe.

In your particular case, MySQLdb seems like it will always do the wrong thing when you hand it a tuple that has only one item in it (con is any open connection):

>>> con.literal((10,))
('10',)
>>>

That's what Python expects to be able to create a tuple with a single item in it, but it causes MySQL to barf (scroll right to see the barfness):

mysql> select distinct sites.SiteCode, sites.SiteName, Latitude, Longitude, County, SourceID from sites, seriescatalog where sites.SiteID = seriescatalog.SiteID and sites.SiteID in (82,);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
mysql> 

This is my work-around to prevent Bobby Tabling:

siteinfoquery = """select distinct sites.SiteCode, sites.SiteName, Latitude, Longitude, County, SourceID from sites, seriescatalog where sites.SiteID = seriescatalog.SiteID and sites.SiteID in (%s);"""

cur.execute(siteinfoquery % ",".join([str(int(i)) for i in SiteID]))

BTW, the below doesn't work because con.literal (called inside MySQLdb's execute() function) quotes the resultant string, turning it from a list of numbers into a string, which then becomes a single value that doesn't match any SiteID:

cur.execute(siteinfoquery , ",".join([str(int(i)) for i in SiteID]))

>>> "(%s)" % con.literal(",".join([str(float(i)) for i in SiteID]))
"('10.0,20.0')"
>>> 

I haven't looked to see if this is a bug fixed in a later version, a current issue, my bad, or a problem unknown to them.

Answer 3

Working vs. non-working example:

import MySQLdb as mysql
from MySQLdb.constants import FIELD_TYPE
In [9]: conn = mysql.connect(host=hostname, user=user_name, passwd=password, port=port_number, db=schema_name)
In [10]: cursor = conn.cursor()
In [11]: cursor.execute('select %s + %s', (1, 3))
Out[11]: 1L

In [16]: conn = mysql.connect(host=hostname, user=user_name, passwd=password, port=port_number, db=schema_name, conv={FIELD_TYPE.LONG: long})
In [17]: cursor = conn.cursor()
In [18]: cursor.execute('select %s + %s', (1, 3))---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-18-08423fe5373f> in <module>()
----> 1 cursor.execute('select %s + %s', (1, 3))
/usr/lib64/python2.6/site-packages/MySQLdb/cursors.pyc in execute(self, query, args)
    156             query = query.encode(charset)
    157         if args is not None:
--> 158             query = query % db.literal(args)
    159         try:
    160             r = self._query(query)
TypeError: not enough arguments for format string

The conv parameter to mysql.connect was the issue for me. Without it, my query parameters are interpreted as expected. With it, I was tearing my hair out.

Answer 4

I can't replicate this with MySQLdb. I'm using 1.2.2 final. Perhaps try a simple debug, to be sure that the problem is where you have indicated:

In [13]: cur.execute('select %s + %s', (1,2))
Out[13]: 1L

In [14]: cur.fetchall()
Out[14]: ((3L,),)

UPDATE 1: So I grabbed and installed 1.2.3 final, here's my transcript:

In [1]: import MySQLdb
In [2]: MySQLdb.version_info
Out[2]: (1, 2, 3, 'final', 0)
In [3]: con = MySQLdb.connect(user='root', db='inventory')
In [4]: cur = con.cursor()
In [5]: cur.execute('select %s + %s', (1,2))
Out[5]: 1L
In [6]: cur.fetchall()
Out[6]: ((3L,),)

If only I could replicate your problem, then maybe I'd be able to offer a solution!? So what else is different between our two environments?

$ mysql --version
mysql  Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (x86_64) using readline 6.1

UPDATE 2: You can perhaps break things down a little more than above. Note the following snippet from the source for MySQLdb-1.2.3:

139     def execute(self, query, args=None):
...
158         if args is not None:
159             query = query % db.literal(args)
160         try:
161             r = self._query(query)
162         except TypeError, m:
163             if m.args[0] in ("not enough arguments for format string",
164                              "not all arguments converted"):
165                 self.messages.append((ProgrammingError, m.args[0]))

line 159 is where your args are converted/escaped and then inserted into the query string. So perhaps you could do something like this:

In [24]: con.literal((1,2,))
Out[24]: ('1', '2')

To see how arguments are being converted by the driver, prior to merging with your query string.

In [26]: "select %s + %s" % con.literal((1,2,))
Out[26]: 'select 1 + 2'