Python MySQLdb: Query parameters as a named dictio

Question

I want to pass query parameters to cursor.execute() method of MySQLdb as a named dictionary, such that they are escaped from SQL injection.

Can you explain why this gives KeyError:

>>> c.execute('select id from users where username=%(user)s', {'user':'bob',})
KeyError: 'user'

MySQLdb manual http://mysql-python.sourceforge.net/MySQLdb.html says:

paramstyle

String constant stating the type of parameter marker formatting expected by the interface. Set to 'format' = ANSI C printf format codes, e.g. '...WHERE name=%s'. If a mapping object is used for conn.execute(), then the interface actually uses 'pyformat' = Python extended format codes, e.g. '...WHERE name=%(name)s'. However, the API does not presently allow the specification of more than one style in paramstyle.

Answer 1

MySQLdb allows dicts as query parameters. This response shows all different ways to do it. You only need to provide a secuence as such parameter (tuple, dict...) as second parameter to "execute". DO NOT format your query as only one parameter to "execute" method or you will be likely exposed to SQL injection attacks. See:

"SELECT * FROM users WHERE username = '%s'" % (user)

Think what would happen if:

user = "peter;DROP TABLE users"   :_(

The other way is secured as it lets the MySQLdb library to handle the necessary checking.

I do not know what is wrong, because your query works fine for me:

# Connect to db
# Open a cursor
stmt = "SELECT * FROM users WHERE username = %(user)s"
cursor.execute(stmt, {"user": "bob"})
user = cursor.fetchone()
print user

{'username': 'bob', 'alias': 'bobby', 'avatar': 'default', 'fullname': 'bob'}

Can you give us more info?

Answer 2

The line in the documentation following what you pasted may answer your question:

Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc.