Careful with storing Base64 in MySQL

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.