Anther nice "feature" of mysql: If you have a table configured to UTF-8, and you use utf-8 client encoding but then accidentally try to send something that's not UTF-8 (shit happens), then mysql will happily accept your data and truncate it on the first byte it doesn't understand.
It issues a warning, but most frameworks don't care to inform the caller if a warning has happened (some don't even provide a way to access them).
Yes. You should not be sending invalid data to your database, but holy sh*t, your database shouldn't (mostly) silently alter the data you entrust it with.
If the input data is wrong and you can't deal with it, blow up in the face of the user. Don't try to "fix" it by corrupting it.
Except for causing annoyances, the truncation of data may also have security implications. Last year, I found that WordPress (which has the utf8 character set by default), was vulnerable to PHP object injection because their filter could be bypassed by (ab)using the truncation of strings in MySQL. For more details: http://vagosec.org/2013/09/wordpress-php-object-injection/
DBIx::Class (in perl) has a connection info option that calls a method that does its best to turn all of that horrible crap off:
sub connect_call_set_strict_mode {
my $self = shift;
# the @@sql_mode puts back what was previously set on the session handle
$self->_do_query(q|SET SQL_MODE = CONCAT('ANSI,TRADITIONAL,ONLY_FULL_GROUP_BY,', @@sql_mode)|);
$self->_do_query(q|SET SQL_AUTO_IS_NULL = 0|);
}
It issues a warning, but most frameworks don't care to inform the caller if a warning has happened (some don't even provide a way to access them).
Yes. You should not be sending invalid data to your database, but holy sh*t, your database shouldn't (mostly) silently alter the data you entrust it with.
If the input data is wrong and you can't deal with it, blow up in the face of the user. Don't try to "fix" it by corrupting it.
Disclaimer: This happened to me in 2008 (http://pilif.github.io/2008/02/failing-silently-is-bad/), so it might have been fixed since, but stuff like this made me lose my trust in MySQL long ago.