SQLite Forum

TEXT values can contain arbitrary binary data
I think I missed your original point just a tad. I thought you were confused about why SQLite will store a BLOB as text when you ask it to, and then keep on treating it like text.

I then went on to prove other DBs do it too, but I now realize your confusion is more precisely about the BLOB being not valid UTF8 whilst you have set the pragma encoding to be specifically UTF8.

All I can say (to reiterate what others have said): SQLite doesn't care.

While the SQLite connection can speak UTF8, and *can* store UTF8 text, it *can* also still store ANY bytes, it places Zero requirement on a Text column to only store UTF8-happy text. In the end, it's all just bytes.  
(I won't go into reasons - this topic is rehashed on the forum ad-infinitum, you can find several long dissertations in just the recent history [search bag-'o-bytes for fun], so I will stop here and simply assert that it is so.)

Put another way, if I ask you to read to me from a Russian book, but translate it to French on the fly so I can understand, it will ONLY work as long as the book contains valid Russian words. If the book suddenly starts containing nonsense garbled letters, or perhaps valid Chinese words, then no amount of Russian-to-French translation will help me, no matter how good of a translator you are.  If the Author of the book however strictly ensured that only valid Russian words could be placed in the book, then one could fairly expect a fully comprehensible translation - but SQLite is not such an author, and it believes (unwisely perhaps) that you are the Author-in-chief.