Storing Password Hashes: Columns, Lengths and Truncation

The column that holds a password hash is boring right up until it is too narrow, at which point it fails in the worst way available: quietly, only for new accounts, and only in production.

How long the values actually are

Bcrypt is the only one of those that is genuinely fixed. Argon2 grows with its own parameters, because the memory, time and parallelism values are written into the string, and a salt or output length change moves it again.

Use 255 and stop thinking about it

VARCHAR(255) costs nothing on any database anyone runs. It is a variable length type, so a 60 character bcrypt hash occupies 60 characters plus a length byte whatever the declared maximum is. Sizing the column to exactly 60 buys you no storage and one future outage.

That outage is the classic one. A team migrates from bcrypt to Argon2, deploys, and every account created after the deploy cannot log in. The hash was written into a 60 character column, MySQL in its default non-strict configuration cut it at 60 without raising anything, and what came back out was a prefix that verification rejects. The password was correct every time.

Charset and collation

Hashes are ASCII. The encoded formats use base64 alphabets, dollar signs, commas and equals signs, and nothing above 127 ever appears. Declaring the column utf8mb4 is harmless. Declaring it with a case insensitive collation is also harmless in itself, since you never ask the database to compare hashes.

Which is the actual rule here.

Never compare hashes in SQL

-- Wrong, in several directions at once.
SELECT * FROM users
 WHERE email = ? AND password_hash = ?;

This cannot work with any salted hash, since you do not know the salt until you have read the row. It also drags the comparison into the database, where a case insensitive collation genuinely can make two different hashes look equal, and where the comparison is not constant time.

Fetch the row by its identifier, then verify in your application code with the library function, which reads the parameters out of the stored value and compares properly.

const user = await db.user.findUnique({ where: { email } });
if (!user) return fail();

const ok = await bcrypt.compare(password, user.passwordHash);

The column next to it

You do not need a salt column, because the salt is inside the hash. You do not need an algorithm column either, since every encoded format names itself in its first few characters, and a prefix check tells you which verifier to call.

Two fields that do earn their place: a timestamp of when the password last changed, which is what session invalidation keys off, and a nullable hash for accounts that only ever sign in through an identity provider.

Make that column nullable rather than storing an empty string, and check for null before you verify. Some libraries treat an empty stored hash as a mismatch and some throw, and there is at least one where an empty candidate against an empty hash comes back true.

Things not to do to this column

• No unique index. Salted hashes are unique anyway, and a collision would be a bug you want to see rather than an insert that fails.
• No ordinary index either. Nothing should ever search by hash.
• Keep it out of logs, out of API responses and out of any serialiser default. Frameworks that expose model attributes automatically are the usual culprit.
• Exclude it from the analytics copy of the database, or mask it there. A hash in a warehouse that half the company can query is a hash that has left the building.

One migration to run before any other

If you are planning a change of hash function, widen the column first, in its own deploy, and let it sit. Then change the writing code. Doing both at once is how you find out that the two changes had to be in that order.

The migration write up covers the rest of that sequence, including how to verify both formats while the old ones drain away.