Storing hexadecimal values as binary in MySQL

Question

I was thinking about how I'm storing passwords in my database : appropriately salted SHA1 strings in a CHAR(40) field. However, since the character data in there is actually just a hex representation of a 160 bit number, I thought it might be better to store it as BINARY(20).

CREATE TABLE users (
    password BINARY(20)
    /* snip */
);

INSERT INTO users (password) VALUES (UNHEX(SHA1('mypassword'));

As I see it, one benefit of this approach is that it halves the size of that field, but I can imagine there's probably some downsides too.

What's your opinion?

Answer 1

The hard disk space savings of storing your hashed passwords as binary rather than varchar are probably insignificant. How many users are you likely to have in this table? Multiply that by the space difference between BINARY(20) and VARCHAR(n) and I think you'll find it's not a significant savings. Personally, I would prefer the hex representation because at least I can type it in a query if I'm doing some ad-hoc operation during development or writing a unit test to validate password related operations. Hex is somewhat more readable than binary if I happen to be loading a data dump in a text editor, etc. My bottom line is that the hex representation would be more convenient during the development cycle.

Answer 2

Here is my breakdown:

1. If you use strings instead of binary, use a fixed length field. Since the hashing algos all output a fixed length you can save yourself some space there.
2. Since you are only doing an equality comparison, there is no need for indexes. Binary fields have no collation type or character set.
3. BINARY column types have no odd storage caveats like BLOBs do.
4. Each hexadecimal character represents 4 bits in the 8 (or 7) bits it consumes. This means that binary storage is twice as efficient.
5. MOST IMPORTANT: Unless you are working in an embedded system where each byte counts, don't do it. Having a character representation will allow you better debugging. Plus, every time a developer is working a problem like this I have to wonder why. Every architectural decision like this has trade-offs and this one does not seem like it adds value to your project.
6. You can always convert to BINARY later with a simple SQL script.

In short, use a fixed length text field. There is no gain to counting bytes in the current world, especially when change is easy to achieve.

Hope this helps.

Answer 3

Why reinvent the wheel? Why not use CHAR(41) like table `mysql.user' uses? It's a well-known format, so any future maintainers won't be scratching their heads over your special scheme? Make it easy on everyone by just noting "just like MySQL passwords."

Answer 4

We used binary for a ton of different ids in our database to save space, since the majority of our data consisted of these ids. Since it doesn't seem like you need to save space (as it's just passwords, not some other huge scale item), I don't see any reason to use binary here.

The biggest problem we ran into was constantly, annoyingly, having binary data show up in the console (everytime you type select * you hear a million beeps), and you have to always do select HEX() or insert UNHEX(), which is a pain.

Lastly, if you mix and match (by mistake) binary and HEX/UNHEX and join on this value, you could match records you never intended to.