Toto Toto - 7 months ago 21
SQL Question

Why should a bcrypt() hash be stored in (var)binary?

In our php application, we generate password hashes with

password_hash()
(using bcrypt).

bcrypt hashes should be stored in
BINARY
or
VARBINARY
(MySQL).

I totally understand this requirement if the comparison and/or search is done inside the database. (collation, case sensitive vs case insensitive).

If the database is only used as storage, and the comparision is done on the php application with
password_verify()
, can we stay with
CHAR
or
VARCHAR
?

IF not, why?

Answer

You can store the result of password_hash() is something you can save in a normal VARCHAR(255) column, it's not binary data, just a string that looks like:

$2y$10$.vGA1O9wmRjrwAVXD98HNOgsNpDczlqm3Jq7KnEd1rVAGv3Fykk1a

These are, of course, case sensitive but they'll never use anything but regular letters, numbers, and a select few bits of syntax.

This column does not need to be indexed, in fact that would make almost no sense. The password_verify() function works against a specific password and is deliberately slow, testing versus every user in the system would take a long time. This is to make it harder for people to brute-force guess passwords.