5 ms·

I need to share something as literally today I fixed a bug in our project that's somewhat related. MS SQL encodes everything with UTF-16. Emojis (code points)

I need to share something as literally today I fixed a bug in our project that's somewhat related. MS SQL encodes everything with UTF-16. Emojis (code points) require up to 4 bytes. If it's a grapheme (emoji constructed from emojis), it will be even more. We are using Django. If you check length of an emoji, it will give you `1` and Django assumes utf8 everywhere. If you try to save it to PostgreSQL with char field `max_length=1` it will work just fine, but on MS SQL it will fail as it requires 2 characters (4 bytes) in the DB! I tried it with MS SQL 2017, 2019 with different collations on nvarchar and I'm pretty sure there's no way around it. > Because in CHAR(n) and VARCHAR(n) or in NCHAR(n) and NVARCHAR(n), the n defines the byte storage size, not the number of characters that can be stored, it's important to determine the data type size you must convert to, in order to avoid data truncation. https://docs.microsoft.com/en-US/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15#utf8 https://docs.microsoft.com/en-US/sql/relational-databases/co...

gecko 5y ago
Generically, anything by Microsoft will historically have used UCS-2, and will use UTF-16 these days, so this is utterly unsurprising to me as an experienced Windows dev. Conversely, Linux (and POSIX, more generally) deciding that filename encoding is a per-filename and untracked thing is a bit lit, from my perspective. Point being: when it comes to handling unicode and foreign characters, just, like...always read the documentation. Assume nothing.
- capitainenemo 5y ago
  Yep. Microsoft is the main reason for 2 of my favourite unicode links. https://utf8everywhere.org/ https://utf8everywhere.org/ and https://simonsapin.github.io/wtf-8/ https://simonsapin.github.io/wtf-8/ And, apparently it's mildly inaccurate to say it uses UTF-16... it's more like UCS-2 with UTF-16 hacked in, with no validation. Thus WTF-8.
  - ChrisSD 5y ago
    No, it's UTF-16 with no validation at the kernel level. Invalid UTF-16 is also invalid UCS-2 as those code points were explicitly barred from use. In practice, only malware will create such broken names. High level software (e.g. Microsoft's own VSCode) will not handle broken UTF-16. And indeed the in-built UTF-8 code page will lossily decode UTF-16 (unpaired surrogates are replaced with the Unicode replacement character).
    - capitainenemo 5y ago
      Hm. Are you sure? Because the utf8everywhere article (and various microsoft related framework discussions) seem to suggest there's no validation anywhere. You can easily create partial codepoints and just hitting backspace in a text field can do it. That seems to imply there's no UTF-16 validation even at a higher level. But I will readily defer to your expertise on this. I've not coded in microsoft land for like 18 years. MFC was my last experience in this, where I still have this vague memory of being shocked by an API returning an int32 and instructing on casting to a void pointer (overloaded response message). No wonder they had issues with 64 bit migration at the time. Edit: cite on the utf8everywhere thing. "in plain Windows edit control (until Vista), it takes two backspaces to delete a character which takes 4 bytes in UTF-16. On Windows 7, the console displays such characters as two invalid characters, regardless of the font being used." Maybe they've improved since though. But surely there's a lot of that baggage in the libraries.
      - ChrisSD 5y ago
        I mean, Vista is ~15 years old at this point. If anything that's still part of Windows makes the backspace mistake then if nothing else it's impressive it's survived this long without being noticed.
      - user-the-name 5y ago
        Windows Vista came out fifteen years ago.
pimeys 5y ago
With SQL Server 2019, you can write UTF-8 data to `VARCHAR` and `CHAR` fields, if the table collation is `LATIN1_GENERAL_100_CI_AS_SC_UTF8`. I feel the pain of UTF-16. When I was writing the Rust crate for the TDS protocol, all N-columns for strings always require you to do a full copy from `Vec<u16>` to `Vec<u8>` and back.
- tomwojcik 5y ago
  I used this collation on 2019 when testing and the only thing that has changed was the error message (more verbose).
tootie 5y ago
This kind of bug gives me nightmares. I'm afraid to ask how long it took you to solve.
- tomwojcik 5y ago
  2 days to identify and understand the problem because I wasn't able to reproduce at all, only on prod. Then ~1 day to fix but I've decided to handle not emojis per se, but unicode characters (which makes more sense but requires more knowledge) which was finished within the next 2 days. In the meantime I set up a test project with PostgreSQL, 3 MySQL databases and 2 MS SQL Server so all operations were executed on all 6 at once. The worst thing is Django won't raise an exception on max_length exceeded even with MS SQL because on the application level its length is 1, so only the DB (DataError on constraint created by Django) will complain.

gecko 5y ago

Generically, anything by Microsoft will historically have used UCS-2, and will use UTF-16 these days, so this is utterly unsurprising to me as an experienced Windows dev. Conversely, Linux (and POSIX, more generally) deciding that filename encoding is a per-filename and untracked thing is a bit lit, from my perspective. Point being: when it comes to handling unicode and foreign characters, just, like...always read the documentation. Assume nothing.

capitainenemo 5y ago
Yep. Microsoft is the main reason for 2 of my favourite unicode links. https://utf8everywhere.org/ https://utf8everywhere.org/ and https://simonsapin.github.io/wtf-8/ https://simonsapin.github.io/wtf-8/ And, apparently it's mildly inaccurate to say it uses UTF-16... it's more like UCS-2 with UTF-16 hacked in, with no validation. Thus WTF-8.
- ChrisSD 5y ago
  No, it's UTF-16 with no validation at the kernel level. Invalid UTF-16 is also invalid UCS-2 as those code points were explicitly barred from use. In practice, only malware will create such broken names. High level software (e.g. Microsoft's own VSCode) will not handle broken UTF-16. And indeed the in-built UTF-8 code page will lossily decode UTF-16 (unpaired surrogates are replaced with the Unicode replacement character).
  - capitainenemo 5y ago
    Hm. Are you sure? Because the utf8everywhere article (and various microsoft related framework discussions) seem to suggest there's no validation anywhere. You can easily create partial codepoints and just hitting backspace in a text field can do it. That seems to imply there's no UTF-16 validation even at a higher level. But I will readily defer to your expertise on this. I've not coded in microsoft land for like 18 years. MFC was my last experience in this, where I still have this vague memory of being shocked by an API returning an int32 and instructing on casting to a void pointer (overloaded response message). No wonder they had issues with 64 bit migration at the time. Edit: cite on the utf8everywhere thing. "in plain Windows edit control (until Vista), it takes two backspaces to delete a character which takes 4 bytes in UTF-16. On Windows 7, the console displays such characters as two invalid characters, regardless of the font being used." Maybe they've improved since though. But surely there's a lot of that baggage in the libraries.
    - ChrisSD 5y ago
      I mean, Vista is ~15 years old at this point. If anything that's still part of Windows makes the backspace mistake then if nothing else it's impressive it's survived this long without being noticed.
    - user-the-name 5y ago
      Windows Vista came out fifteen years ago.

pimeys 5y ago

With SQL Server 2019, you can write UTF-8 data to `VARCHAR` and `CHAR` fields, if the table collation is `LATIN1_GENERAL_100_CI_AS_SC_UTF8`. I feel the pain of UTF-16. When I was writing the Rust crate for the TDS protocol, all N-columns for strings always require you to do a full copy from `Vec<u16>` to `Vec<u8>` and back.

tomwojcik 5y ago
I used this collation on 2019 when testing and the only thing that has changed was the error message (more verbose).

tootie 5y ago

This kind of bug gives me nightmares. I'm afraid to ask how long it took you to solve.

tomwojcik 5y ago
2 days to identify and understand the problem because I wasn't able to reproduce at all, only on prod. Then ~1 day to fix but I've decided to handle not emojis per se, but unicode characters (which makes more sense but requires more knowledge) which was finished within the next 2 days. In the meantime I set up a test project with PostgreSQL, 3 MySQL databases and 2 MS SQL Server so all operations were executed on all 6 at once. The worst thing is Django won't raise an exception on max_length exceeded even with MS SQL because on the application level its length is 1, so only the DB (DataError on constraint created by Django) will complain.