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..
4 Answers 4
Your version of mysqldb must be the problem.
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))
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 ))
3 Comments
query % connection.literal(args)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.
1 Comment
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'
9 Comments
repr(), not str(), when getting debugging info.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.
>>>) and/or in very simple scripts that you can run at your shell command-line -- avoid irrelevancies like your web server which make debugging simple problems harder. Show your data e.g.print repr(ListID).repr()to avoid ambiguity) and show us the results.