Problem with MySQL cursor

[Diez B. Roggisch]

Florian Lindner wrote:

> Carsten Haese wrote:
> 
>> On Thu, 2007-10-11 at 15:14 +0200, Florian Lindner wrote:
>>> Hello,
>>> I have a function that executes a SQL statement with MySQLdb:
>>> 
>>> def executeSQL(sql,  *args):
>>>     print sql % args
>>>     cursor = conn.cursor()
>>>     cursor.execute(sql, args)
>>>     cursor.close()
>>> 
>>> it's called like that:
>>> 
>>>     sql = "INSERT INTO %s (%s) VALUES (%s)"
>>>     executeSQL(sql,  DOMAIN_TABLE, DOMAIN_FIELD, domainname)
>> 
>> You can't use parameter binding to substitute table names and column
>> names, or any other syntax element, into a query. You can only bind
>> parameters in places where a literal value would be allowed (more or
>> less, the real rules are more complicated, but this rule of thumb gets
>> you close enough). You have to construct the query string like this, for
>> example:
>> 
>> sql = "INSERT INTO "+DOMAIN_TABLE+"("+DOMAIN_FIELD+") VALUES (%s)"
>> executeSQL(sql, domainname)
> 
> Ok, I understand it and now it works, but why is limitation? Why can't I
> just the string interpolation in any playes and the cursor function
> escapes any strings so that they can't do harm to my query?

Because the function doesn't know that you wanted

select * from user where email = 'foo at bar'

instead of

select * from user where email = 'foo'; drop table user where '' = ''

And of course you don't gain anything from using USER INPUT in the
string-interpolation for creating the sql statement!!!

Diez