3

I have the following python code which pushes data to db:

cursor.execute("INSERT INTO "+ DATA_TABLE + """ (fk_id, title, streetaddress,json) 
                 values (%(fk_id)s, %(title)s, %(address)s, %(json)s ) """ ,(
                 result))

But I get the error:

  File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 185, in unicode_literal
    return db.literal(u.encode(unicode_literal.charset))
UnicodeEncodeError: 'latin-1' codec can't encode characters in position 0-5: ordinal not in range(256)

Is it because the %s expects value to be a valid latin-1 string while dict is giving it a utf-8 string? Whats a clean workaround?

Note: The json is safely created using json.dumps. It has several characters of different languages.

jerrymouse
  • 16,964
  • 16
  • 76
  • 97
  • 1
    -1: Do not build SQL statements like this. Google for "Little Bobby Tables" to see what can happen. – S.Lott Dec 29 '11 at 14:17
  • @S.Lott: assuming the `DATA_TABLE` variable doesn't come from user input, this code is *not* vulnerable to SQL injection. – Luke Woodward Dec 29 '11 at 17:06
  • @LukeWoodward: While your assumption is often true, the time that it's not true is fatal. Also, building SQL like this is inefficient. Using the correct value binding in the API means that the RDBMS back-end works with a single, standardized query into which values are bound. This saves parsing time, and can (sometimes) dramatically improve performance. – S.Lott Dec 29 '11 at 17:09
  • @S.Lott Yes DATA_TABLE is NOT coming from user. Its a system defined variable which is guaranteed to have safe value. I need to do it this way because if I write `result['data_table'] = DATA_TABLE` and `"""INSERT INTO %(data_table)s ...`, I get the error: `(1064, "You have an error in your SQL syntax... near ''my_data_table'` – jerrymouse Dec 30 '11 at 08:01
  • Dynamic SQL with on-the-fly table names is usually a design smell. Without more information I can only suggest (again) that it's usually a bad idea. – S.Lott Dec 30 '11 at 13:31

2 Answers2

4

you should try to convert all your string in unicode

cursor.execute(u"INSERT INTO "+ unicode(DATA_TABLE) + u""" (fk_id, title, streetaddress,json) 
                 values (%(fk_id)s, %(title)s, %(address)s, %(json)s ) """ ,(
                 result))

and pass charset="utf-8" to connect as specified in http://mysql-python.sourceforge.net/MySQLdb.html

Xavier Combelle
  • 10,968
  • 5
  • 28
  • 52
  • Thanks for the answer @xaview-combelle but that ain't working. I am still getting `UnicodeEncodeError: 'latin-1' codec can't encode characters in position 0-5: ordinal not in range(256)` on this very same line. – jerrymouse Dec 29 '11 at 13:47
0

I don't think this is an issue with the python %s formatting code. According to the python string formatting documentation, it can format unicode w/o trouble. However, note that the resulting string will be unicode.

Have you looked at all the related questions, e.g. How to make MySQL handle UTF-8 properly?

Community
  • 1
  • 1
jrennie
  • 1,937
  • 12
  • 16