Storing SHA1 hash values in MySQL

Question

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?

Answer 1

I would use VARCHAR for variable length data, but not with fixed length data. Because a SHA-1 value is always 160 bit long, the VARCHAR would just waste an additional byte for the length of the fixed-length field.

And I also wouldn’t store the value the SHA1 is returning. Because it uses just 4 bit per character and thus would need 160/4 = 40 characters. But if you use 8 bit per character, you would only need a 160/8 = 20 character long field.

So I recommend you to use BINARY(20) and the UNHEX function to convert the SHA1 value to binary.

I compared storage requirements for BINARY(20) and CHAR(40).

CREATE TABLE `binary` (
    `id` int unsigned auto_increment primary key,
    `password` binary(20) not null
);
CREATE TABLE `char` (
    `id` int unsigned auto_increment primary key,
    `password` char(40) not null
);

With million of records binary(20) takes 44.56M, while char(40) takes 64.57M. InnoDB engine.

Answer 2

A SHA1 hash is 40 chars long!

Answer 3

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)
);

Answer 4

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).

Answer 5

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.

Answer 6

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.

Answer 7

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.