SQLite Forum

TEXT values can contain arbitrary binary data
Login
> SQLite expects text values to be encoded in the database encoding.

This is incorrect.  

SQLite3 expects that incoming string values will correspond to the constraints which you the programmer have specified apply to the value so passed as regards to the encoding (UTF-8 or UTF-16 depending on the API call used), and that the value is a properly formatted C String (a bunch of non-NULL codepoints followed by a NULL codepoint terminator).

SQLite3 will then return string values in the format corresponding to the API used by the programmer to retrieve the text string (UTF-8 or UTF-16), as a C String (a bunch on non-NULL codepoints followed by a NULL codepoint).

If the API used to "send" the value to SQLite3 specifies a different encoding than the database encoding, then SQLite3 will translate the "value" for storage.

Similarly, if the API used to "retrieve" the value from SQLite3 specified a different encoding that the database encoding, then SQLite3 will translate the "value" for presentement to the user.

If the API used to "send", the database encoding, and the API used to "retrieve" indicate the **same** encoding, then SQLite3 does not inspect or molest the data in any way (it has no need to do so).

The only difference between a blob and text is that text has an encoding specified by the user which may be used to provide semantic meaning to the bag-o-bytes in the event that this information is required (such as translating encodings, applying string functions, et al).

If one wishes to ensure that a text field contains only validly encoded text then one may write a user-defined function for use in a check constraint.  If you do not, SQLite3 **believes and relies** on the fact that you are telling the truth and if you are not, then the GIGO principle applies.