12

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.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
mercador
  • 753
  • 2
  • 8
  • 14
  • The code you posted doesn't raise a KeyError, actually. You must've been using a different query, and given that you marked an answer that points out you can use parameters for anything other than values a accepted suggests your actual query differs. – Martijn Pieters Dec 31 '18 at 14:42

2 Answers2

15

MySQLdb allows dictionary as query parameters. This response shows all different ways to do it. You only need to provide a sequence 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 is equal to a string like:

peter';DROP TABLE users;SELECT * from users where ''='

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?

The Godfather
  • 4,235
  • 4
  • 39
  • 61
Alberto Megía
  • 2,225
  • 3
  • 23
  • 33
3

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.

RocketDonkey
  • 36,383
  • 7
  • 80
  • 84
  • 3
    I am inserting a ***column value***. The line in the documentation means that the following code is illegal: `c.execute('select id from %s where username=%s', ('users', 'bob'))` – mercador Oct 11 '12 at 03:04
  • 2
    @mercador Right, the `from %s` piece is what is causing the failure since you are trying to parameterize the table name. The benefit of using `execute` in this way is that it helps prevent SQL injection, but if you take the proper security precautions (which I'm not an expert on, but would involve proper sanitization/escaping of the parameters), you can use Python's built in string formatting to build the query and then pass it to `execute`. Again, SQL injection is the main concern. Hope this helps! – RocketDonkey Oct 11 '12 at 03:23
  • 4
    Yet the code in the question **doesn't use SQL parameters for anything other than a value**. – Martijn Pieters Dec 31 '18 at 14:41