[Tutor] Parameterized Queries failing on MySQL

[Peter Otten]

Shea Grove wrote:

>> I'm using pyodbc to interact with MS SQL Server and I'm starting to
>> support mysql.  My issue is that when I use a parameterized query, it
>> works for SQL Server, but crashes when I point to MySQL.  

I assume it raise an Exception.

>> Is there a different syntax that I should be using? or module?

MySQLdb uses a different paramstyle "format" (see 
http://www.python.org/dev/peps/pep-0249/ )

>> query='insert into RestaurantTable values (?, ?, ?, ?, ?, ?, ?, ?, ?)'

Make this

query = 'insert into RestaurantTable values (%s, %s, %s, %s, %s, %s, %s, %s, 
%s)'

>> mcursor.execute(query, params)

but *never* change the above to

mcursor.execute(query % params) #WRONG

which unfortunately will work most of the time with that paramstyle while 
making your app vulnerable to sql injection as demonstrated here: 
http://xkcd.com/327/

Peter