Python MySQLdb: query parameters as a named dictionary

Question

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

Can you explain why this gives a 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

A string constant indicating the type of parameter marker formatting expected by the interface. Set "format" = printf ANSI C format codes, for example. '... WHERE name =% s'. If a connection object is used for conn.execute (), then the interface actually uses 'pyformat' = Python extended formatting codes, for example. '... WHERE name =% (name) s'. However, the API currently does not allow specification of more than one style in paramstyle. *

Answer 1

The line in the documentation in which you insert may answer your question:

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

Answer 2

MySQLdb allows dicts as query parameters. This answer shows all sorts of ways to do this. You only need to ensure privacy as such a parameter (tuple, dict ...) as the second parameter to "execute". DO NOT format your query as only one parameter to the execute method, otherwise you will probably encounter SQL injection attacks. Cm:

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

Think about what happens if:

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

Another way is protected, as it allows the MySQLdb library to handle the necessary validation.

I don’t know what is wrong, because your request is 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 information?