16 ms·

This is also a security vulnerability because MySQL/MariaDB silently truncate strings at the first invalid character. This can result in data manipulation atta

This is also a security vulnerability because MySQL/MariaDB silently truncate strings at the first invalid character. This can result in data manipulation attacks where a higher level layer validates the complete input as UTF-8 for insertion into the DB, but the database only stores half the string. https://bugzilla.mozilla.org/show_bug.cgi?id=1253201 https://bugzilla.mozilla.org/show_bug.cgi?id=1253201

wanderr 5y ago
Any data in your database that can come from external input should be treated as untrusted and validated before it's used. Otherwise validation bugs or bypasses will result in bad data and exploits that persist beyond the fix. Edit: I’m not arguing against the need to use utf8mb4
- capitainenemo 5y ago
  Agreed. The confusing part here as I see it is where validation layer A (correctly) asserts the data is valid UTF-8 and safe then assumes the database persists what it passes to it, since no error is reported. Then, subsystem B trusts reading the database field (since it passed validation layer A). Obviously more validation layers can be added, but at this point validation layer C called by subsystem B needs to know what the initial input from layer A is in order to differentiate it from the db value which was manipulated - a rather tricky thing to do sometimes. (I guess you could add a hash to the db to check the db is storing your strings, but really.. come on) Upgrading to utf8mb4 is probably safer than hoping enough validation layers thrown at it solves the problem.
  - wanderr 5y ago
    Yes. I’ve been burned by this exact issue in the past, not realizing that utf8 was not really utf8 in mysql-land. That is a major, major WTF IMO. But I’ve also seen people relying on validation at the time of insertion so many times that I wanted to warn against that, too. Not in argument against the need for utf8mb4.
- kstrauser 5y ago
  While that’s true, I trust PostgreSQL to store exactly what I’ve asked it to store. At some point, you have to trust something to do its job, or else everything built on top of it is a castle of sand. Imagine a bug like this in ext4. No one would reasonably contend that the layers on top of it should be validating that the files you write out are the ones you’ll read back in. We write unit tests for all kinds of stuff, but we’re not that thorough.
- colpabar 5y ago
  Sure, but the issue here is that the default mysql "utf8" encoding is not actually utf8. You can write as many validation layers as you want, but if they are assuming that utf8 actually means utf8, they won't help, and mysql will potentially screw it up when it gets stored.
capitainenemo 5y ago
I do find this behaviour rather incomprehensible actually. I understand that if the "utf8" is BMP it can't have invalid characters in it. What puzzles me is that the approach they went for seems to be almost the worst possible one from a security and data integrity perspective. Assuming a SQL error can't be thrown for some legacy reason (and you'd think a flag to opt into that would be an option), why not replace the invalid parse with U+FFFD like most things do? Or at a bare minimum, only strip the invalid character which at least makes attacks a bit harder and results in a little less data loss.
- deleted 5y ago
  [deleted]
- pshc 5y ago
  Quietly corrupting data and shipping with the wrong default settings is what MySQL is all about! Keeps the consultants busy.
  - taf2 5y ago
    It’s also easy to imagine how it happens… const char *buffer = malloc(…; memcpy;… etc… pretty fast pretty straight forward
- capitainenemo 5y ago
  Just wanted to reply to myself that I just learned from evanelias' comment a "strict mode" flag is indeed an option. I suppose it's time to audit all my mysqls to see which ones have this enabled, and what other mysql-isms this flag might break. I know it's definitely not enabled on one updated and maintained BSD - this could be because they didn't want to break existing systems during upgrade. I suspect this problem is still extremely common. But good to know there's some form of defense.
tyre 5y ago
Another fun one that came up at my first job: If you run up against the size limit of a text column in MySQL, it silently truncates. This came up when a developer used YAML for some configuration file. There was all sorts of confounding behaviour because YAML is still valid when truncated. Every database has limits, but at least alert the user! I’d rather a query fail than alter data. Edit: This is something that might be useful in CS classes, to reflect what being a real-world engineer can often entail. Set an assignment with some silly nonsense like this and students have to debug it.
- cornel_io 5y ago
  There's a setting for this, but like many settings MySQL has (along with InnoDB, the storage engine) historically chosen exceptionally bad defaults. It's possible to tune the thing to be screaming fast and pretty robust, but it takes a lot of expertise and TBH these days most people would be much better off going either NoSQL or Postgres instead.
- leksak 5y ago
  If the point of CS education is to produce programmers and software engineers as opposed to computer scientists, yes.
  - taftster 5y ago
    What percentage of CS graduates go onto become computer scientists? Seems like vast majority of CS degree holders aren't practicing "pure" computer science, as you would seemingly hope.
    - leksak 5y ago
      Probably a very low amount. When I was studying I participated as a student representative at the CS programme I was attending and this was something that was actively being addressed. The problem was multi-pronged. The courses had become increasingly geared toward industry, and students that had an interest weren't really fit for continuing their studies. In the end, the longer 5 year program (MSE) was allowed to remain more vocational while those seeking a bachelor's were brought along a much more theoretical path so they could then do a master's followed by a PhD.
dehrmann 5y ago
It's like MySQL and PHP were meant for each other.
- anon776 5y ago
  How dare you. Also accurate.
  - chris_wot 5y ago
    PHP has improved a lot.
    - JodieBenitez 5y ago
      yeah... it also has a strict mode... that is off by default.
evanelias 5y ago
Silent truncation only occurs if strict sql_mode is disabled. Strict sql_mode has been the default since MySQL 5.7, released over 6 years ago. All prior versions have hit EOL support, so there is literally no supported version of MySQL which has this behavior out-of-the-box. The topic of MySQL utf8 vs utf8mb4 was discussed in depth just six days ago on HN: https://news.ycombinator.com/item?id=29793916 https://news.ycombinator.com/item?id=29793916
- capitainenemo 5y ago
  That's good to know, however I was encountering this MySQL truncation just last year in COTS software, so clearly there's a long tail. As a related example, log4j 1.x has been deprecated for years, but 100% of atlassian uses it with their own maintained version. In fact, when the log4j thing came up, we audited all our existing COTS/internal and virtually all of it was still on the "EOL" log4j 1.x. Since you're familiar with this. What happens with an existing upgrade - is strict mode enabled by a typical distro package upgrade or does it only impact new installs?
  - evanelias 5y ago
    For sure, there's a huge long tail. However, if a company is using 6+ year old EOL software that has flaws, that's outside of the vendor's control. > What happens with an existing upgrade sql_mode is a setting, so it depends on whether the default is being overridden: e.g. does the my.cnf config file specify a value explicitly? Does your application's connection logic set a session-level override for each connection? DBaaS vendors add an additional wrinkle since they tend to have custom UIs and APIs for global settings. > typical distro package upgrade Many Linux distros made it so that attempting to install "mysql" actually installs MariaDB instead, so when going this route, it's unfortunately always very hard to predict what you'll get or what will happen!
    - KronisLV 5y ago
      > Many Linux distros made it so that attempting to install "mysql" actually installs MariaDB instead, so when going this route, it's unfortunately always very hard to predict what you'll get or what will happen! MariaDB is indeed pretty pleasant to use and for most use cases can be a drop in replacement for MySQL, should you not want to use MySQL, but also wouldn't want/need/be able to use PostgreSQL or something else: https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ I might be in the minority here, but i actually rather enjoy MySQL/MariaDB, since MySQL Workbench is still the best DBA software that i've used, especially because of the ER modelling and schema sync functionality, and their approach to schemas/users within a DB feels more reasonable than that of Oracle/PostgreSQL. That said, both PL/SQL and PL/pgSQL feel more powerful than whatever MySQL/MariaDB has. Also, in regards to MariaDB in particular, their versions have diverged from MySQL somewhat, so you might still want to look into the differences, if migrating over: https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ As for predictability - nowadays i just run specific versions in containers (e.g. 10.7.1 or maybe 10.7 which corresponds to the patest PATCH release), to sidestep the distro differences altogether. It also lets me easily launch new instances, give resource limits to them, have multiple versions in parallel and mount the data directories wherever i need them, for easier backups etc.
ajnin 5y ago
The day I realized that is the day I stopped using MySQL. It was a very long time ago, I was burned by this exact issue. I don't know what's more fundamental to a database than storing the data you ask it to. If it's storing something else, then it failed at its job spectacularly. At the time I experienced this bug I think it didn't even give a warning, but I think that's actually worse with one : it knows it's storing incorrect data but does so anyways. And it still has not been fixed after all those years. I don't know what use is a character encoding that partially supports Unicode (you just have to try to find out in what way) and is used by nobody else. I don't really care that at some point in the future utf8 is going to become an alias to utf8mb4, a DB capable of this behaviour cannot be trusted, and it allowed me to discover postgres which seems superior in just about every way, so I don't need to go back.

wanderr 5y ago

Any data in your database that can come from external input should be treated as untrusted and validated before it's used. Otherwise validation bugs or bypasses will result in bad data and exploits that persist beyond the fix. Edit: I’m not arguing against the need to use utf8mb4

capitainenemo 5y ago
Agreed. The confusing part here as I see it is where validation layer A (correctly) asserts the data is valid UTF-8 and safe then assumes the database persists what it passes to it, since no error is reported. Then, subsystem B trusts reading the database field (since it passed validation layer A). Obviously more validation layers can be added, but at this point validation layer C called by subsystem B needs to know what the initial input from layer A is in order to differentiate it from the db value which was manipulated - a rather tricky thing to do sometimes. (I guess you could add a hash to the db to check the db is storing your strings, but really.. come on) Upgrading to utf8mb4 is probably safer than hoping enough validation layers thrown at it solves the problem.
- wanderr 5y ago
  Yes. I’ve been burned by this exact issue in the past, not realizing that utf8 was not really utf8 in mysql-land. That is a major, major WTF IMO. But I’ve also seen people relying on validation at the time of insertion so many times that I wanted to warn against that, too. Not in argument against the need for utf8mb4.
kstrauser 5y ago
While that’s true, I trust PostgreSQL to store exactly what I’ve asked it to store. At some point, you have to trust something to do its job, or else everything built on top of it is a castle of sand. Imagine a bug like this in ext4. No one would reasonably contend that the layers on top of it should be validating that the files you write out are the ones you’ll read back in. We write unit tests for all kinds of stuff, but we’re not that thorough.
colpabar 5y ago
Sure, but the issue here is that the default mysql "utf8" encoding is not actually utf8. You can write as many validation layers as you want, but if they are assuming that utf8 actually means utf8, they won't help, and mysql will potentially screw it up when it gets stored.

capitainenemo 5y ago

I do find this behaviour rather incomprehensible actually. I understand that if the "utf8" is BMP it can't have invalid characters in it. What puzzles me is that the approach they went for seems to be almost the worst possible one from a security and data integrity perspective. Assuming a SQL error can't be thrown for some legacy reason (and you'd think a flag to opt into that would be an option), why not replace the invalid parse with U+FFFD like most things do? Or at a bare minimum, only strip the invalid character which at least makes attacks a bit harder and results in a little less data loss.

deleted 5y ago
[deleted]
pshc 5y ago
Quietly corrupting data and shipping with the wrong default settings is what MySQL is all about! Keeps the consultants busy.
- taf2 5y ago
  It’s also easy to imagine how it happens… const char *buffer = malloc(…; memcpy;… etc… pretty fast pretty straight forward
capitainenemo 5y ago
Just wanted to reply to myself that I just learned from evanelias' comment a "strict mode" flag is indeed an option. I suppose it's time to audit all my mysqls to see which ones have this enabled, and what other mysql-isms this flag might break. I know it's definitely not enabled on one updated and maintained BSD - this could be because they didn't want to break existing systems during upgrade. I suspect this problem is still extremely common. But good to know there's some form of defense.

tyre 5y ago

Another fun one that came up at my first job: If you run up against the size limit of a text column in MySQL, it silently truncates. This came up when a developer used YAML for some configuration file. There was all sorts of confounding behaviour because YAML is still valid when truncated. Every database has limits, but at least alert the user! I’d rather a query fail than alter data. Edit: This is something that might be useful in CS classes, to reflect what being a real-world engineer can often entail. Set an assignment with some silly nonsense like this and students have to debug it.

cornel_io 5y ago
There's a setting for this, but like many settings MySQL has (along with InnoDB, the storage engine) historically chosen exceptionally bad defaults. It's possible to tune the thing to be screaming fast and pretty robust, but it takes a lot of expertise and TBH these days most people would be much better off going either NoSQL or Postgres instead.
leksak 5y ago
If the point of CS education is to produce programmers and software engineers as opposed to computer scientists, yes.
- taftster 5y ago
  What percentage of CS graduates go onto become computer scientists? Seems like vast majority of CS degree holders aren't practicing "pure" computer science, as you would seemingly hope.
  - leksak 5y ago
    Probably a very low amount. When I was studying I participated as a student representative at the CS programme I was attending and this was something that was actively being addressed. The problem was multi-pronged. The courses had become increasingly geared toward industry, and students that had an interest weren't really fit for continuing their studies. In the end, the longer 5 year program (MSE) was allowed to remain more vocational while those seeking a bachelor's were brought along a much more theoretical path so they could then do a master's followed by a PhD.

dehrmann 5y ago

It's like MySQL and PHP were meant for each other.

anon776 5y ago
How dare you. Also accurate.
- chris_wot 5y ago
  PHP has improved a lot.
  - JodieBenitez 5y ago
    yeah... it also has a strict mode... that is off by default.

evanelias 5y ago

Silent truncation only occurs if strict sql_mode is disabled. Strict sql_mode has been the default since MySQL 5.7, released over 6 years ago. All prior versions have hit EOL support, so there is literally no supported version of MySQL which has this behavior out-of-the-box. The topic of MySQL utf8 vs utf8mb4 was discussed in depth just six days ago on HN: https://news.ycombinator.com/item?id=29793916 https://news.ycombinator.com/item?id=29793916

capitainenemo 5y ago
That's good to know, however I was encountering this MySQL truncation just last year in COTS software, so clearly there's a long tail. As a related example, log4j 1.x has been deprecated for years, but 100% of atlassian uses it with their own maintained version. In fact, when the log4j thing came up, we audited all our existing COTS/internal and virtually all of it was still on the "EOL" log4j 1.x. Since you're familiar with this. What happens with an existing upgrade - is strict mode enabled by a typical distro package upgrade or does it only impact new installs?
- evanelias 5y ago
  For sure, there's a huge long tail. However, if a company is using 6+ year old EOL software that has flaws, that's outside of the vendor's control. > What happens with an existing upgrade sql_mode is a setting, so it depends on whether the default is being overridden: e.g. does the my.cnf config file specify a value explicitly? Does your application's connection logic set a session-level override for each connection? DBaaS vendors add an additional wrinkle since they tend to have custom UIs and APIs for global settings. > typical distro package upgrade Many Linux distros made it so that attempting to install "mysql" actually installs MariaDB instead, so when going this route, it's unfortunately always very hard to predict what you'll get or what will happen!
  - KronisLV 5y ago
    > Many Linux distros made it so that attempting to install "mysql" actually installs MariaDB instead, so when going this route, it's unfortunately always very hard to predict what you'll get or what will happen! MariaDB is indeed pretty pleasant to use and for most use cases can be a drop in replacement for MySQL, should you not want to use MySQL, but also wouldn't want/need/be able to use PostgreSQL or something else: https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ I might be in the minority here, but i actually rather enjoy MySQL/MariaDB, since MySQL Workbench is still the best DBA software that i've used, especially because of the ER modelling and schema sync functionality, and their approach to schemas/users within a DB feels more reasonable than that of Oracle/PostgreSQL. That said, both PL/SQL and PL/pgSQL feel more powerful than whatever MySQL/MariaDB has. Also, in regards to MariaDB in particular, their versions have diverged from MySQL somewhat, so you might still want to look into the differences, if migrating over: https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ As for predictability - nowadays i just run specific versions in containers (e.g. 10.7.1 or maybe 10.7 which corresponds to the patest PATCH release), to sidestep the distro differences altogether. It also lets me easily launch new instances, give resource limits to them, have multiple versions in parallel and mount the data directories wherever i need them, for easier backups etc.

ajnin 5y ago

The day I realized that is the day I stopped using MySQL. It was a very long time ago, I was burned by this exact issue. I don't know what's more fundamental to a database than storing the data you ask it to. If it's storing something else, then it failed at its job spectacularly. At the time I experienced this bug I think it didn't even give a warning, but I think that's actually worse with one : it knows it's storing incorrect data but does so anyways. And it still has not been fixed after all those years. I don't know what use is a character encoding that partially supports Unicode (you just have to try to find out in what way) and is used by nobody else. I don't really care that at some point in the future utf8 is going to become an alias to utf8mb4, a DB capable of this behaviour cannot be trusted, and it allowed me to discover postgres which seems superior in just about every way, so I don't need to go back.