[mysql] Storing SHA1 hash values in MySQL

I have a simple question which occured when I wanted to store the result of a SHA1 hash in a MySQL database:

How long should the VARCHAR field be in which I store the hash's result?

So the length is between 10 16-bit chars, and 40 hex digits.

In any case decide the format you are going to store, and make the field a fixed size based on that format. That way you won't have any wasted space.

If you need an index on the sha1 column, I suggest CHAR(40) for performance reasons. In my case the sha1 column is an email confirmation token, so on the landing page the query enters only with the token. In this case CHAR(40) with INDEX, in my opinion, is the best choice :)

If you want to adopt this method, remember to leave $raw_output = false.

Output size of sha1 is 160 bits. Which is 160/8 == 20 chars (if you use 8-bit chars) or 160/16 = 10 (if you use 16-bit chars).

Reference taken from this blog:

Below is a list of hashing algorithm along with its require bit size:

• MD5 = 128-bit hash value.
• SHA1 = 160-bit hash value.
• SHA224 = 224-bit hash value.
• SHA256 = 256-bit hash value.
• SHA384 = 384-bit hash value.
• SHA512 = 512-bit hash value.

Created one sample table with require CHAR(n):

CREATE TABLE tbl_PasswordDataType
(
    ID INTEGER
    ,MD5_128_bit CHAR(32)
    ,SHA_160_bit CHAR(40)
    ,SHA_224_bit CHAR(56)
    ,SHA_256_bit CHAR(64)
    ,SHA_384_bit CHAR(96)
    ,SHA_512_bit CHAR(128)
); 
INSERT INTO tbl_PasswordDataType
VALUES 
(
    1
    ,MD5('SamplePass_WithAddedSalt')
    ,SHA1('SamplePass_WithAddedSalt')
    ,SHA2('SamplePass_WithAddedSalt',224)
    ,SHA2('SamplePass_WithAddedSalt',256)
    ,SHA2('SamplePass_WithAddedSalt',384)
    ,SHA2('SamplePass_WithAddedSalt',512)
);

You may still want to use VARCHAR in cases where you don't always store a hash for the user (i.e. authenticating accounts/forgot login url). Once a user has authenticated/changed their login info they shouldn't be able to use the hash and should have no reason to. You could create a separate table to store temporary hash -> user associations that could be deleted but I don't think most people bother to do this.

A SHA1 hash is 40 chars long!