Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Python MySQLdb: Query parameters as a named dictionary [closed]

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.

like image 903
mercador Avatar asked Oct 11 '12 02:10

mercador


People also ask

What is %s in Python SQL?

We need to supply values in placeholders ( %s ) before executing a query. Pass Python variables at the placeholder's position when we execute a query. We need to pass the following two arguments to a cursor. execute() function to run a parameterized query.

How do you pass dynamic values in SQL query in Python?

Assigning SQL Query to a Python Variable Here {0} and {1} are string interpolators allowing us to pass values dynamically during run time. Dynamic values can be substituted directly to {0} and {1} by their names, such as {column_name} and {value_holder}. But it is often beneficial to avoid call by name approach.

What does Fetchall return in Python?

fetchall() Method. The method fetches all (or all remaining) rows of a query result set and returns a list of tuples. If no more rows are available, it returns an empty list.

What is Mycursor Python?

The MySQLCursor of mysql-connector-python (and similar libraries) is used to execute statements to communicate with the MySQL database. Using the methods of it you can execute SQL statements, fetch data from the result sets, call procedures.


2 Answers

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?

like image 133
Alberto Megía Avatar answered Oct 16 '22 18:10

Alberto Megía


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.

like image 38
RocketDonkey Avatar answered Oct 16 '22 17:10

RocketDonkey