help with mysql cursor.execute()

[William Gill]

Dennis Lee Bieber wrote:
> On Sun, 14 Aug 2005 19:28:04 GMT, William Gill <noreply at gcgroup.net>
> declaimed the following in comp.lang.python:
> 
> 
>>I have been trying to pass parameters as indicated in the api.
>>when I use:
>>
>>   sql= 'select * from %s where cusid = %s ' % name,recID)
>>   Cursor.execute(sql)
>>
>>it works fine, but when I try :
>>
>>   sql= 'select * from %s where cusid like %s '
>>   Cursor.execute(sql,(name,recID))
>>
> 
> 	Hypothesis: the database TABLE may need to be filled in externally.
> .execute() parsing is designed to properly quote arguments for data
> fields where needed.
> 
> 	I suspect you are getting quote marks around the table name, which
> is not a position they are expected.
> 

 From my testing, your suspicion is correct, as is your suggestion.

   sql = 'select * from %s where cusid = %%s ' % tablename
   Cursor.execute(sql, (recID,))

works,

Bill
> 	You'll likely have to use a two-step process: use string formatting
> to fill in table and field names (if you are getting those from user
> input, you'll have to validate that there isn't an injection attack --
> ie, user didn't enter "name; delete from name" as the table to be
> processed); then use .execute() to pass the field values.
> 
> 	If using MySQLdb, you could always read the source files... (Though,
> unfortunately, the very bottom is a compiled library and hence
> unreadable...
> 
> 
> 	You'll find .execute() invokes an .escape()
> 
> Py>     escape(...)
> Py>         escape(obj, dict) -- escape any special characters in object
> obj
> Py>         using mapping dict to provide quoting functions for each
> type.
> Py>         Returns a SQL literal string.
> 
> 	Note the last line: it returns a "literal string" -- in the form
> needed to pass /data/. That won't work for field and table names, and
> MySQLdb doesn't attempt any semantic parsing to find out is being
> substituted -- if just converts (escapes) ALL parameters based on
> datatype, THEN does a normal Python string formatting operation.