Is it safe to execute an SQL query like this in Python using MySQLdb?

Question

I noticed that most sources say that it is best to execute SQL queries in Python, something like this:

cursor.execute( 'select * from coworkers where name = :1 and clue > :2', [ name, clue_threshold ] ) 

Other sources say

 cursor.execute( "select * from coworkers where name = %s and clue > %s", ( name, clue_threshold ) ) 

which, I think, is pretty similar.

In any case, the way I do it is to create a dictionary and save the values. For example, the initial biz_info dictionary looks like this:

 biz_info = { 'business' : None, 'name' : None, 'neighborhood' : None, 'address' : None, 'city' : None, 'state' : None, 'zip_code' : None, 'latitude' : None, 'longitude' : None, 'phone' : None, 'url' : None, 'yelp_url' : None, } 

then I execute an SQL statement like this

 execute_sql( cur, "insert into " + TABLE_BIZ_NAME + """ values ( NULL, %(name)s, %(neighborhood)s, %(address)s, %(city)s, %(state)s, %(zip_code)s, %(latitude)s, %(longitude)s, %(phone)s, %(url)s, %(yelp_url)s, NULL )""" , biz_info ) 

Is it safe against sql injection? I want to use dictionaries to store information, because it simplifies management.

Honestly, I'm not even quite sure what the difference between using %s , %d and %()s means in parameterized queries. Basically, all I know is not to use

 cursor.execute( "select * from coworkers where name = '%s' and clue > %d" % ( name, clue_threshold ) ) 

Answer 1

The method for passing parameters to sql command lines depends on the database (sqlite, for example, uses ? ).

According to the MySQLdb documentation , you can use the paramstyle parameter to specify the preferred way to format the string ( format or pyformat ).

The first example in your question does not seem to be supported. In any case, I would say that as long as you do not format the entire string, as in the last example, you are safe, since it can be assumed that the query parameters will be correctly escaped.

Answer 2

Your insert statement should explicitly state the names of the fields you need to set to protect against breakage from schema changes. Also, I find your code too repetitive. I would write paste something more like this:

 cursor.execute \ ( "insert into " + TABLE_BIZ_NAME + "(" + ", ".join(biz_info.keys()) + ") values (" + ", ".join(("%s",) * len(biz_info)) + ")", biz_info.values() ) 

Thus, field names need to be specified only once, when creating a biz_info dict. And any future changes need only to be updated there.