Mysqldb python escaping? or% s?

Question

I am currently using mysqldb.

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

Please note that E = lambda x: x.encode('utf-8') 1), so my connection is established with charset = 'utf8'.

These are the errors I get 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, executed by 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 have \ inward, then dumping obviously failed.

I personally know that% s is not a good way to pass arguments due to injection attacks, etc.

Answer 1

To be more specific ... the cursor.execute() method accepts an optional argument that contains the values ​​that must be specified and interpolated into the SQL template / statement. This is NOT done using the simple % operator! cursor.execute(some_sql, some_params) NOT the same as cursor.execute(some_sql % some_params)

The Python DB-API indicates that any compatible driver / module must provide an .paramstyle attribute, which can be any of "qmark", 'numeric', 'named', 'format' or 'pyformat' ... so that theoretically can be adapted your SQL query strings for the supported form by introspection and a little brute force. This should be safer than trying to specify and interpolate the values ​​in your SQL strings yourself.

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

Answer 2

When I remember this correctly, you do not need to manually encode Unicode strings. The mysqldb module will do this for you.

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

Answer 3

You can use triple quotation marks and the source string format

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