CODEWITHSUNDEEP
pythonmysql
disappearedng
disappearedng

Reputation: 503

mysqldb python escaping ? or %s?

I am currently using mysqldb.

What is the correct way to escape strings in mysqldb arguments?

Note that E = lambda x: x.encode('utf-8') 1) so my connection is set with charset='utf8'.

These are the errors I am getting for these arguments: w1, w2 = u'你好', u'我好'

self.cur.execute("SELECT dist FROM distance WHERE w1=? AND w2=?", (E(w1), E(w2)))
ret = self.cur.execute("SELECT dist FROM distance WHERE w1=? AND w2=?", (E(w1), E(w2)))

File "build/bdist.linux-i686/egg/MySQLdb/cursors.py", line 158, in execute TypeError: not all arguments converted during string formatting

self.cur.execute("SELECT dist FROM distance WHERE w1=%s AND w2=%s", (E(w1), E(w2)))

This works fine, but when w1 or w2 has \ inside, then the escaping obviously failed.

I personally know that %s is not a good method to pass in arguemnts due to injection attacks etc.

Upvotes: 4

Views: 5823

Answers (3)

Jim Dennis
Jim Dennis

Reputation: 17530

To be more specific ... the cursor.execute() method takes an optional argument which contains values to be quoted and interpolated into the SQL template/statement. This is NOT done with a simple % operator! cursor.execute(some_sql, some_params) is NOT the same as cursor.execute(some_sql % some_params)

The Python DB-API specifies that any compliant driver/module must provide a .paramstyle attribute which can be any of 'qmark', 'numeric', 'named', 'format', or 'pyformat' ... so that one could, in theory, adapt your SQL query strings to the supported form through introspection and a little munging. This should still be safer than trying to quote and interpolate values into your SQL strings yourself.

I was particularly amused to read Warning Never, never, NEVER use Python string ... interpolation ... Not even at gunpoint. in the PsycoPG docs.

Upvotes: 4

unbeknown
unbeknown

Reputation:

When I remember it right, then you don't need to manually encode your unicode strings. The mysqldb module will do this for you.

And the mysqldb module uses %s as parameters instead of ?. This is the reason for the error in your first example.

Upvotes: 1

YOU
YOU

Reputation: 123937

You could use triple quotes and raw string format 

self.cur.execute(r"""SELECT dist FROM distance ... """,...)

Upvotes: 0

Related Questions