How should 'raw binary data' hashes be stored in MySQL?

Question

I'm wanting to store hashed passwords in MySQL, I'm using PHP:

<?php
    $salt = '!£$%^&*()#';
    $username = 'abc';
    $password = '123';
    $hash = hash('sha1', $username . $salt . $password, true);
?>

The true parameter in hash() will return the value as raw binary data. But I don't understand what this means exactly. How should it be correctly stored in MySQL?

Answer 1

The last parameter of the hash() function indicates how the hash value is returned:

• Either as raw binary data. This way you get the direct output of the specific hash function you're using, in this case sha1.
• Or as a string containing a hexadecimal representation of the same raw binary data.

They are both the same and differ only in their representation. Unless you have a good reason, I would suggest that you use the hexadecimal representation and store it as a string in your database. This way it is much easier to debug problems, since you could easily print out the hexadecimal hash value.

Answer 2

The "normal" way of doing this, AFAIK, is to use the addslashes() function.

e.g.:

$hash = hash('sha1', $username . $salt . $password, true);
$query_safe_hash = addslashes($hash);
$query_safe_username = addslashes($username);
$query = "INSERT INTO DBTable(username, password) VALUES ('$query_safe_username', '$query_safe_hash')";
mysql_query($query) or die("Failed to store credentials!");

Side note: from a crypto best practices standpoint, the salt should be a known length, should be generated randomly, and prepended to your hash before being stored to the database. Something like

$salt = generate_random_salt();
$query_safe_hash = addslashes($salt) . addslashes(hash('sha1', $salt . $username . $password, true);

Then to verify the user's credentials, you retrieve the stored hash, remove the slashes, and strip the known salt length from the beginning of the stored hash and use the same salt to generate a hash of the provided credentials, then compare. This helps harden your hash algo against various cryptanalysis attacks (in particular, differential cryptanalysis).

Answer 3

If you do want to store a raw binary string in MySQL, declare the column BINARY(16) if it's of a known fixed length of 16 bytes, VARBINARY(32) if it's of variable length up to 32 bytes, or one of the BLOB types for binary fields that potentially get very long (e.g., BLOB up to 64K, LONGBLOB up to 4G).

Answer 4

I found the solution.

Normal (hexed) hashes of sha1() are always CHAR(40) in length. When you return a hash as raw binary data in php, it will return a string as CHAR(20), saving 50% database space yet representing the exact same value. This is because 2 characters of hex can be compressed into 1 character, thus halving it the space needed.

So store the password as CHAR(20) and use the *_bin collation.