The usual choice for a collation in MySQL is utf8_general_ci (or whataver_ci). ci means “case insensitive” (there are also the _cs collations). All is fine with that, but if you are storing and then comparing Base64-encoded data, you may have a hidden issue. Base64 encoded data is case-sensitive, but the comparison with _ci collations is case-insensitive. So you are storing some base64 and then try to query for it:
user1 base64: A543rFgTregW
user2 base64: A543rFGtregw
When you SELECT * FROM table WHERE base64column="..."
you can get the incorrect user.
I agree this is rare (as you should rarely query for binary data, no matter the representation), but it can waste a lot of time researching when it happens (it hasn’t happened to me, luckily).
The solution is to use VARBINARY
.
The usual choice for a collation in MySQL is utf8_general_ci (or whataver_ci). ci means “case insensitive” (there are also the _cs collations). All is fine with that, but if you are storing and then comparing Base64-encoded data, you may have a hidden issue. Base64 encoded data is case-sensitive, but the comparison with _ci collations is case-insensitive. So you are storing some base64 and then try to query for it:
user1 base64: A543rFgTregW
user2 base64: A543rFGtregw
When you SELECT * FROM table WHERE base64column="..."
you can get the incorrect user.
I agree this is rare (as you should rarely query for binary data, no matter the representation), but it can waste a lot of time researching when it happens (it hasn’t happened to me, luckily).
The solution is to use VARBINARY
.
Recent Comments