Re: [pysqlite] error: Could not decode to UTF-8

Eric S. Johansson wrote:
> OperationalError: Could not decode to UTF-8 column 'message_originator' with
> text 'Sch<F6>[email protected]'
> 
> how do I prevent injecting this kind of data 

You can update to pysqlite >= 2.5.0, which will slap you at insertion
time instead of (possibly much) later when fetching the data:

You'd then get an exception like this when you try to insert the data:

pysqlite2.dbapi2.ProgrammingError: You must not use 8-bit bytestrings
unless you use a text_factory that can interpret 8-bit bytestrings (like
text_factory = str). It is highly recommended that you instead just
switch your application to Unicode strings.

And this is exactly what I'd recommend: switch your application to
Unicode throughout.

> and if I fail to prevent injection,
> how do I handle this kind of error?  I fixed this instance by editing the record
> with sqlitemanager.

A custom text_factory can help here:

>>> con.execute("select ?", ("aaa" + chr(150) + "bbb",)).fetchone()
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
pysqlite2.dbapi2.ProgrammingError: You must not use 8-bit bytestrings
unless you use a text_factory that can interpret 8-bit bytestrings (like
text_factory = str). It is highly recommended that you instead just
switch your application to Unicode strings.
>>>

>>> con.text_factory = lambda bin: bin.decode("utf8", "replace")
>>> print con.execute("select ?", ("aaa" + chr(150) + "bbb",)).fetchone()
(u'aaa\ufffdbbb',)

"replace" adds U+FFFD, 'REPLACEMENT CHARACTER' for byte sequences it
cannot decode (*)

-- Gerhard

(*) http://www.amk.ca/python/howto/unicode