SQLite NUL Characters: Why Your Strings Might Be Truncated
Nul Characters in Strings in SQLite
I explain how SQLite allows NUL characters in strings but truncates them in functions like length() and quote(), leading to confusing results. You might think a string is short, but it actually contains hidden data. I show you how to detect these invisible characters using BLOB casts and remove them to clean your database.
Small. Fast. Reliable. Choose any three.
- bruce511
Using this quirk allows for "hiding" data in the database. Because data after the nul is more-or-less invisible to generic dbBrowser type programs.
If you suspect it is happening you can read it (by casting the SELECT as a BLOB, but obviously that's not a common pattern.
Personally I've never done it, and clearly it's not something useful for security, but it does open the door to interesting meta-data storage opportunities. Again with the proviso that it is "untrustworthy".
- ventana
So, one fun consequence of this is that Unicode multi-byte strings (not UTF-8 but something like UTF-32) cannot be stored as strings in sqlite without a huge pain. Not that I ever planned to use multi-byte fixed length encodings, but good to know!
A good moment to appreciate the elegance of UTF-8 which allowed to encode multi-byte characters preserving the semantics of C strings.
- nasretdinov
The only really confusing part to me is that SQLite has separate STRING and BLOB type.ls. I always thought SQLite only really supports INTEGER, REAL and TEXT (aka BLOB) types. And even then the types aren't enforced. So it's really interesting to see that you still somehow can distinguish TEXT from BLOB for example.
P.S. I meant default settings -- I know that strict mode, etc, exists, but it's not the default, so few people change it