Proper way to store BCrypt Hashes on MySQL

Question

Searching for the proper way to store BCrypt hashes in MySQL I found this question and it only made me more confuse.

The accepted answer point out that we should use:

CHAR(60) BINARY or BINARY(60)

But other people on the comments argue that instead we should use:

CHAR(60) CHARACTER SET latin1 COLLATE latin1_bin

or even:

COLLATE latin1_general_cs

I am not a specialist on databases so could anyone explain me the difference between all these options and which one is truly better for storing BCrypt hashes?

Answer 1

My answer is in the line of "what is proper", rather than "what will work".

Do not use latin1. Sure, it might work, but it is ugly to claim that the encrypted string is text when it is not.

Ditto for saying CHAR....

Simply say BINARY(...) if fixed length or VARBINARY(...) if it can vary in length.

However, there is a gotcha... Whose BCrypt are you using? Does it return binary data? Or a hex string? Or maybe even Base64?

My above answer assumed it returns binary data.

If it returns 60 hex digits, then store UNHEX(60_hex_digits) into BINARY(30) so that it is packed smaller.

If it is Base64, then CHARACTER SET ascii COLLATE ascii_bin would be "proper". (latin1 with a case-sensitive collation would also work.)

If it is binary, then, again, BINARY(60) is the 'proper' way to do it.

The link you provided looks like Base64, but is it? And is it up to 60 characters? Then I would use

VARCHAR(60) CHARACTER SET ascii COLLATE ascii_bin

And explicitly state the charset/collation for the column, thereby overriding the database and/or table "defaults".

All the Base64 chars (and $) are ascii; no need for a more complex charset. Collating with a ..._bin means "compare bytes exactly"; more specifically "don't do case folding". Since Base64 depends on distinguishing between upper and lower case letters, you don't want case folding.