Writing a simple query, getting a unicodeEncodeError, python 2.7

Question

The query:

query = "SELECT * from "+str(tablename)+" where user='"+str(user)+"' AND review_title='"+rt+"'"

The error message:

UnicodeEncodeError: 'ascii' codec can't encode character u'\xe9' in position 3: ordinal not in range(128)

The code:

rt = review_title.replace("'", "\\'")
rt = rt.replace('"', '\\"')
rt = unicode(rt).encode('utf-8')
query = "SELECT * from "+str(tablename)+" where user='"+str(user)+"' AND review_title='"+rt+"'"

In this case the tablename is 'ta_rest_review', the user is 'KANNONEPL...' and rt is 'Excelente pero \"OJO a la CUENTA\"'

You have a `é` character somewhere in your values, but your sample input is missing that character. — Martijn Pieters, Oct 26 '14 at 02:28
When reporting on a Python problem, **always** include the traceback. If it is the actual `query` assignment, then that accented `é` character is in either the `user` or the `tablename` value. — Martijn Pieters, Oct 26 '14 at 02:30
In that case your problem with an `UnicodeEncodeError` is caused by calling `str()` on a `unicode` object with characters outside the valid ASCII range, an entirely different problem from the [post you commented on earlier](http://stackoverflow.com/a/19833471). — Martijn Pieters, Oct 26 '14 at 02:31

Martijn Pieters · Accepted Answer · 2015-01-21T15:25:37.400

You should not use string formatting to interpolate your data. Use SQL parameters instead!

Use the appropriate query parameter syntax for your database driver, it'll either use question marks or %s placeholders:

cursor.execute(
    'SELECT * from {0} WHERE user=%s AND review_title=%s'.format(tablename),
    (user, rt)
)

You'll still have to interpolate the tablename (make absolutely sure it is an existing table name, don't take user input to determine this!)

The database driver can then take care of escaping the user and rt values correctly for you, including encoding Unicode values to an encoding supported by the database.

Moreover, you avoid running the risk of a SQL injection security flaw if you try to 'escape' your database input yourself.

Look at your database driver documenation; the module should have a paramstyle variable that defines what style of parameter placeholder the driver supports, as well as explain if multiple styles are supported.

That worked, thanks for your answer. Strange that I couldn't find it on here before. — Lawrence DeSouza, Oct 26 '14 at 03:26

Writing a simple query, getting a unicodeEncodeError, python 2.7

1 Answers1