Problem passing Tuple to MySQLdb in python

Question

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

eg:

ORIGINAL:
(This works, so ListID, etc. Definitely valid)

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

ATTEMPT FROM STUPE:

 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 do not know why this second attempt does not work as a tuple, but accepts it as one parameter, but in any case, for another operator I need to pass several parameters so that this does not help:

ORIGINAL:

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

but this is not the case 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 recently got this error in my web server log, although note that it MAY be redone due to a lot of different things that I tried because it wasn’t wrong before: (The error refers to the above attempt by passing the variable "params")

 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 this helps, I use mysqldb version 1.2.3 if it does not accept tuples in this version, but don’t make me start with how mysqldb junk files ...

Answer 1

Your version of mysqldb should be the problem.

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

has been 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 will later be 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 put and tries to turn it into a string that is correctly processed by MySQL. He adds apostrophes to the lines. It prints numbers without quotes. Or, for example, if you give him a line with an apostrophe, it quotes an apostrophe.

In your particular case, MySQLdb seems like it will always do the wrong thing when you pass it a tuple in which there is only one element (con is any open connection):

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

What Python expects will be able to create a tuple with one element in it, but it forces MySQL barf (scroll right to see 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 job 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 following does not work because con.literal (called inside the MySQLdb execute () function) quotes the resulting string, turning it from a list of numbers to a string, which then becomes the only value that does not 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 did not see if the error was fixed in a later version, the current problem, my error or an unknown problem.

Answer 3

I cannot replicate this using MySQLdb. I am using the final version 1.2.2. Perhaps try a simple debugging to make sure the problem is where you pointed:

 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 is my decryption:

 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 I could replicate your problem, maybe I can offer a solution !? So what 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 break things a little more than higher. 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 arguments are converted / escaped and then inserted into the query string. So maybe you could do something like this:

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

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

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

Answer 4

Working or 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 for mysql.connect was a problem for me. Without it, the parameters of my request are interpreted as expected. With this, I tore off my hair.