Geek Answers Handbook

Escaping unicode strings for MySQL in Python (exceptions.UnicodeEncodeError exception)

I use Twisted to asynchronously access our database in Python. My code is as follows:

from twisted.enterprise import adbapi from MySQLdb import _mysql as mysql ... txn.execute(""" INSERT INTO users_accounts_data_snapshots (accountid, programid, fieldid, value, timestamp, jobid) VALUES ('%s', '%s', '%s', '%s', '%s', '%s') """ % (accountid, programid, record, mysql.escape_string(newrecordslist[record]), ended, jobid))

This worked until I came across this character: ®, which caused the thread to throw an exception: `exceptions.UnicodeEncodeError: 'ascii' codec can't encode character u'\xae' in position 7: ordinal not in range(128)

However, if I do not use MySQLdb_mysql.escape_string (), I get database errors when the input contains quotes, etc. (sure). An exception occurs before accessing the database, so database mapping does not matter at all.

What is the best way to avoid this content without exception for Unicode characters? The ideal solution is one where I can pass Unicode characters that will not interfere with a MySQL query without problems; however, deleting the Unicode character string, replacing them with question marks, distorting them or anything else that stops the outputs would be acceptable.

+4
source share
2 answers

Do not format such lines. This is a massive security hole. Cannot cite correctly. Do not try.

Use the second parameter to execute. Simply put, instead of txn.execute("... %s, %s ..." % ("xxx", "yyy")) do txn.execute("... %s, %s ...", ("xxx", "yyy")) . Pay attention to the comma instead of the percent sign. In other databases or with a different database binding, can you use a different character instead of "% s", for example ? or :1, :2, :3 :foo:, :bar:, :baz: :1, :2, :3 or :foo:, :bar:, :baz: but the idea is the same. (You can see the documentation for paramstyle in the DB-API 2.0 documentation if you are interested in alternatives.)

I already wrote about this in the past . Discussing this post may be of particular interest to you.

Also let me emphasize that this is the only right way to do this . You may have seen the MySQL documentation talking about quoting strings in various ways. You may have written PHP applications that do not have the proper means to pass database parameters. I guarantee that all of these sources of information are incorrect and lead to serious and persistent security problems: do not interpolate the parameters in your SQL strings.

+11
source

You can try:

newrecordslist [record] .decode ("UTF-8")

The rights symbol is about http://www.python.org/dev/peps/pep-0249/ .

+2
source

More articles: