SQLite strings with NUL

Question

1. Can strings in SQLite 3 include NUL characters?
2. If the answer to 1 is "yes", how can they be written in SQL queries? SQLite doesn't seem to have chr or char functions.

Answer 1

In general, no - SQLite internally is not 8-bit clean, probably due to its Tcl heritage. While NULs do not cause corruption problems, SQLite typically stops processing strings at the first embedded NUL character.

This is true even for operators such as GLOB. For instance, you cannot match a BLOB column with GLOB when you have embedded NUL characters, e.g. this

select * from table where blobcol glob x'00022a';

will only match empty blob values: While you can use literal BLOB syntax (i.e. x'hexdigits') and use the resulting values where strings are used, SQLite typically only uses the part before the first NUL.

At least,. this is the state of affairs up to including SQLite 3.26.0.

Note that SQLite also has a BLOB type which can store embedded NULs without any issues, but there is very little functionality in SQLite to use them, and they are often harder to use from SQL interface libraries.

They also silently convert to strings in many contexts, at which point the embedded NULs start causing issues again.