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

Gerhard Häring wrote:
> 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:

that's good advice.  I'm currently running the 2.4.0


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

I don't think it's possible or even practical to do a full Unicode conversion in
my application.  What goes in are e-mail messages, what comes out are e-mail
messages plus some processing specific headers.  The two messages should be
identical (excluding the additions).  I store the message in raw form and as a
e-mail object (pickled).  The other elements of the record are either taken
directly from the SMTP protocol (message originator, message recipient) or
extracted from the e-mail object for searching/display etc.

What seems to be the problem is that the message originator field gets the
"junk" characters.  This seems to be relatively common trick by spammers.  I
guess they're trying to play on programming bugs that caused the message to be
delivered.  I fooled them.  This programming bug causes messages to get queued
for hours at a time until I notice something is wrong.

This brings us to my current puzzlement/frustration with Unicode.  I really
don't grok how to convert from an 8-bit string to Unicode and back to identical
8-bit string.  the closest I can get to this is using string escape.  I use this
technique with the raw form of a message when storing it inside sqlite.  I know
I could've used a blob but that didn't occur to me until a bit later.

I'll take a look at my code and add string-escape encoding to message recipient,
originator, and subject fields.

thanks for the advice.  I'll use what I can.

---eric