Rendered at 15:48:13 GMT+0000 (Coordinated Universal Time) with Cloudflare Workers.
bruce511 13 hours ago [-]
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".
nasretdinov 7 hours ago [-]
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
ventana 13 hours ago [-]
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.
bawolff 12 hours ago [-]
I guess, but it seems like if your encoding is not ascii compatible its better to just store as a blob anyway. Even without the null issue its not like string functions not expecting utf-32 would give reasonable answers.
ddlsmurf 12 hours ago [-]
Can I ask in which use case you'd want UTF-32 text in a column ?
ventana 10 hours ago [-]
I won't! But I can imagine a Windows developer (well, UTF-16, not UTF-32, but still NULs are everywhere) who might want to put something like that to the database without proper converting.
Rohansi 11 hours ago [-]
Also why would you store UTF-32 in text column when SQLite clearly doesn't support it? The docs show there are only methods to pass UTF-8 or UTF-16 text.
> This seems like a bug. But it is how SQLite works.
I think you can say that about every bug.
Groxx 15 hours ago [-]
>* The length() SQL function only counts characters up to and excluding the first NUL.*
>The quote() SQL function only shows characters up to and excluding the first NUL.
>The .dump command in the CLI omits the first NUL character and all subsequent text in the SQL output that it generates. In fact, the CLI omits everything past the first NUL character in all contexts.
That's just all kinds of "oh no", wow.
I mean, I can't come up with a better strategy, but... oof. C-style strings being a thing at all really hurts.
inigyou 14 hours ago [-]
The better strategy is that it should just work, isn't it?
ventana 13 hours ago [-]
> C-style strings being a thing at all really hurts.
Well, C strings exist and are probably here to stay.
I believe Pascal strings, where the length is stored in the 0th character, were much worse (also, obligatory reference to Joel Spolsky's “Back to Basics” and “fucked strings” [1]).
Looks like we only got enough memory to spare to allow arbitrary length strings with arbitrary characters – that is, being able to use 2 or 4 bytes for the length of every string – in 1990s or so, when C++ strings and similar types became popular.
> Pascal strings, where the length is stored in the 0th character, were much worse
Until unicode became widely supported, many databases used to reserve 1 byte for the length of a VARCHAR, typically at the 0th position of the column. The content was understandably limited to 255 characters.
Because of that extra byte per row, it is considered a waste of memory to use VARCHAR for data that is expected to be fixed-length. Well, it's an even larger waste in some cases. A certain popular ORM continues to insist on mapping UUID to VARCHAR(36), wasting a whopping 21 bytes per row! Certainly something to keep in mind at a time when both RAM and disks are expensive.
SQLite of course doesn't care, and stores all string as TEXT.
Scaevolus 15 hours ago [-]
It's not really a big problem. You should be storing arbitrary binary strings as blobs (and length() works properly with them), but the underlying encoding is nearly identical: https://www.sqlite.org/fileformat.html#record_format
deepsun 13 hours ago [-]
Then DBMS should not allow to save a string that would make some function return wrong results. Either truncate with a warning on INSERT (MySQL approach) or better throw an error.
ComputerGuru 13 hours ago [-]
Note that this breaks a lot of core functionality unless you CAST(s as TEXT) before you use it, eg iirc LIKE won’t work. Then you’re back to the same problem.
adzm 11 hours ago [-]
So, it basically matches the behavior of a string in C, which can also have data in the allocation beyond the null terminator.
I really don't see why this is a problem. It gives out exactly what you give it.
kristianp 10 hours ago [-]
Not really, the example shows when the output is different/truncated.
DonHopkins 14 hours ago [-]
As long as we're supporting in-band signals in strings, how about making DEL rub out the previous character?
shermantanktop 13 hours ago [-]
Add forward-cursor and back-cursor, and maybe we can embed a Turing machine in a string.
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".
P.S. I meant default settings -- I know that strict mode, etc, exists, but it's not the default, so few people change it
A good moment to appreciate the elegance of UTF-8 which allowed to encode multi-byte characters preserving the semantics of C strings.
https://sqlite.org/c3ref/bind_blob.html
I think you can say that about every bug.
>The quote() SQL function only shows characters up to and excluding the first NUL.
>The .dump command in the CLI omits the first NUL character and all subsequent text in the SQL output that it generates. In fact, the CLI omits everything past the first NUL character in all contexts.
That's just all kinds of "oh no", wow.
I mean, I can't come up with a better strategy, but... oof. C-style strings being a thing at all really hurts.
Well, C strings exist and are probably here to stay.
I believe Pascal strings, where the length is stored in the 0th character, were much worse (also, obligatory reference to Joel Spolsky's “Back to Basics” and “fucked strings” [1]).
Looks like we only got enough memory to spare to allow arbitrary length strings with arbitrary characters – that is, being able to use 2 or 4 bytes for the length of every string – in 1990s or so, when C++ strings and similar types became popular.
[1]: https://www.joelonsoftware.com/2001/12/11/back-to-basics/
Until unicode became widely supported, many databases used to reserve 1 byte for the length of a VARCHAR, typically at the 0th position of the column. The content was understandably limited to 255 characters.
Because of that extra byte per row, it is considered a waste of memory to use VARCHAR for data that is expected to be fixed-length. Well, it's an even larger waste in some cases. A certain popular ORM continues to insist on mapping UUID to VARCHAR(36), wasting a whopping 21 bytes per row! Certainly something to keep in mind at a time when both RAM and disks are expensive.
SQLite of course doesn't care, and stores all string as TEXT.
I really don't see why this is a problem. It gives out exactly what you give it.