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

User · Answer

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.

User · Answer

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

User · Answer

A SHA1 hash is 40 chars long

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Storing SHA1 hash values in MySQL

Examples related to mysql

Examples related to database-design

Examples related to hash

Examples related to sha1