What data type to use for hashed password field and what length

Question

I m not sure how password hashing works  will be implementing it later   but need to create database schema now   I m thinking of limiting passwords to 4-20 characters  but as I understand after encrypting hash string will be of different length   So  how to store these passwords in the database

User · Answer

I ve always tested to find the MAX string length of an encrypted string and set that as the character length of a VARCHAR type  Depending on how many records you re going to have  it could really help the database size

User · Answer

You should use TEXT  storing unlimited number of characters  for the sake of forward compatibility  Hashing algorithms  need to  become stronger over time and thus this database field will need to support more characters over time  Additionally depending on your migration strategy you may need to store new and old hashes in the same field  so fixing the length to one type of hash is not recommended

User · Answer

You might find this Wikipedia article on salting worthwhile  The idea is to add a set bit of data to randomize your hash value  this will protect your passwords from dictionary attacks if someone gets unauthorized access to the password hashes

User · Answer

You can actually use CHAR length of hash  to define your datatype for MySQL because each hashing algorithm will always evaluate out to the same number of characters  For example  SHA1 always returns a 40-character hexadecimal number

User · Answer

for md5 vARCHAR 32  is appropriate  For those using AES better to use varbinary

User · Answer

for md5 vARCHAR 32  is appropriate  For those using AES better to use varbinary

User · Answer

It really depends on the hashing algorithm you re using  The length of the password has little to do with the length of the hash  if I remember correctly  Look up the specs on the hashing algorithm you are using  run a few tests  and truncate just above that

User · Answer

As a fixed length string  VARCHAR n  or however MySQL calls it   A hash has always a fixed length of for example 12 characters  depending on the hash algorithm you use   So a 20 char password would be reduced to a 12 char hash  and a 4 char password would also yield a 12 char hash

User · Answer

Update  Simply using a hash function is not strong enough for storing passwords  You should read the answer from Gilles on this thread for a more detailed explanation   For passwords  use a key-strengthening hash algorithm like Bcrypt or Argon2i  For example  in PHP  use the password hash   function  which uses Bcrypt by default    hash   password hash  rasmuslerdorf   PASSWORD DEFAULT     The result is a 60-character string similar to the following  but the digits will vary  because it generates a unique salt     2y 10  vGA1O9wmRjrwAVXD98HNOgsNpDczlqm3Jq7KnEd1rVAGv3Fykk1a   Use the SQL data type CHAR 60  to store this encoding of a Bcrypt hash  Note this function doesn t encode as a string of hexadecimal digits  so we can t as easily unhex it to store in binary   Other hash functions still have uses  but not for storing passwords  so I ll keep the original answer below  written in 2008     It depends on the hashing algorithm you use   Hashing always produces a result of the same length  regardless of the input   It is typical to represent the binary hash result in text  as a series of hexadecimal digits   Or you can use the UNHEX   function to reduce a string of hex digits by half    MD5 generates a 128-bit hash value   You can use CHAR 32  or BINARY 16  SHA-1 generates a 160-bit hash value   You can use CHAR 40  or BINARY 20  SHA-224 generates a 224-bit hash value   You can use CHAR 56  or BINARY 28  SHA-256 generates a 256-bit hash value   You can use CHAR 64  or BINARY 32  SHA-384 generates a 384-bit hash value   You can use CHAR 96  or BINARY 48  SHA-512 generates a 512-bit hash value   You can use CHAR 128  or BINARY 64  BCrypt generates an implementation-dependent 448-bit hash value  You might need CHAR 56   CHAR 60   CHAR 76   BINARY 56  or BINARY 60    As of 2015  NIST recommends using SHA-256 or higher for any applications of hash functions requiring interoperability  But NIST does not recommend using these simple hash functions for storing passwords securely   Lesser hashing algorithms have their uses  like internal to an application  not for interchange   but they are known to be crackable

User · Answer

You should use TEXT  storing unlimited number of characters  for the sake of forward compatibility  Hashing algorithms  need to  become stronger over time and thus this database field will need to support more characters over time  Additionally depending on your migration strategy you may need to store new and old hashes in the same field  so fixing the length to one type of hash is not recommended

User · Answer

Update  Simply using a hash function is not strong enough for storing passwords  You should read the answer from Gilles on this thread for a more detailed explanation   For passwords  use a key-strengthening hash algorithm like Bcrypt or Argon2i  For example  in PHP  use the password hash   function  which uses Bcrypt by default    hash   password hash  rasmuslerdorf   PASSWORD DEFAULT     The result is a 60-character string similar to the following  but the digits will vary  because it generates a unique salt     2y 10  vGA1O9wmRjrwAVXD98HNOgsNpDczlqm3Jq7KnEd1rVAGv3Fykk1a   Use the SQL data type CHAR 60  to store this encoding of a Bcrypt hash  Note this function doesn t encode as a string of hexadecimal digits  so we can t as easily unhex it to store in binary   Other hash functions still have uses  but not for storing passwords  so I ll keep the original answer below  written in 2008     It depends on the hashing algorithm you use   Hashing always produces a result of the same length  regardless of the input   It is typical to represent the binary hash result in text  as a series of hexadecimal digits   Or you can use the UNHEX   function to reduce a string of hex digits by half    MD5 generates a 128-bit hash value   You can use CHAR 32  or BINARY 16  SHA-1 generates a 160-bit hash value   You can use CHAR 40  or BINARY 20  SHA-224 generates a 224-bit hash value   You can use CHAR 56  or BINARY 28  SHA-256 generates a 256-bit hash value   You can use CHAR 64  or BINARY 32  SHA-384 generates a 384-bit hash value   You can use CHAR 96  or BINARY 48  SHA-512 generates a 512-bit hash value   You can use CHAR 128  or BINARY 64  BCrypt generates an implementation-dependent 448-bit hash value  You might need CHAR 56   CHAR 60   CHAR 76   BINARY 56  or BINARY 60    As of 2015  NIST recommends using SHA-256 or higher for any applications of hash functions requiring interoperability  But NIST does not recommend using these simple hash functions for storing passwords securely   Lesser hashing algorithms have their uses  like internal to an application  not for interchange   but they are known to be crackable

User · Answer

I ve always tested to find the MAX string length of an encrypted string and set that as the character length of a VARCHAR type  Depending on how many records you re going to have  it could really help the database size

User · Answer

You might find this Wikipedia article on salting worthwhile  The idea is to add a set bit of data to randomize your hash value  this will protect your passwords from dictionary attacks if someone gets unauthorized access to the password hashes

User · Answer

Hashes are a sequence of bits  128 bits  160 bits  256 bits  etc   depending on the algorithm   Your column should be binary-typed  not text character-typed  if MySQL allows it  SQL Server datatype is binary n  or varbinary n    You should also salt the hashes  Salts may be text or binary  and you will need a corresponding column

User · Answer

You can actually use CHAR length of hash  to define your datatype for MySQL because each hashing algorithm will always evaluate out to the same number of characters  For example  SHA1 always returns a 40-character hexadecimal number

User · Answer

Update  Simply using a hash function is not strong enough for storing passwords  You should read the answer from Gilles on this thread for a more detailed explanation   For passwords  use a key-strengthening hash algorithm like Bcrypt or Argon2i  For example  in PHP  use the password hash   function  which uses Bcrypt by default    hash   password hash  rasmuslerdorf   PASSWORD DEFAULT     The result is a 60-character string similar to the following  but the digits will vary  because it generates a unique salt     2y 10  vGA1O9wmRjrwAVXD98HNOgsNpDczlqm3Jq7KnEd1rVAGv3Fykk1a   Use the SQL data type CHAR 60  to store this encoding of a Bcrypt hash  Note this function doesn t encode as a string of hexadecimal digits  so we can t as easily unhex it to store in binary   Other hash functions still have uses  but not for storing passwords  so I ll keep the original answer below  written in 2008     It depends on the hashing algorithm you use   Hashing always produces a result of the same length  regardless of the input   It is typical to represent the binary hash result in text  as a series of hexadecimal digits   Or you can use the UNHEX   function to reduce a string of hex digits by half    MD5 generates a 128-bit hash value   You can use CHAR 32  or BINARY 16  SHA-1 generates a 160-bit hash value   You can use CHAR 40  or BINARY 20  SHA-224 generates a 224-bit hash value   You can use CHAR 56  or BINARY 28  SHA-256 generates a 256-bit hash value   You can use CHAR 64  or BINARY 32  SHA-384 generates a 384-bit hash value   You can use CHAR 96  or BINARY 48  SHA-512 generates a 512-bit hash value   You can use CHAR 128  or BINARY 64  BCrypt generates an implementation-dependent 448-bit hash value  You might need CHAR 56   CHAR 60   CHAR 76   BINARY 56  or BINARY 60    As of 2015  NIST recommends using SHA-256 or higher for any applications of hash functions requiring interoperability  But NIST does not recommend using these simple hash functions for storing passwords securely   Lesser hashing algorithms have their uses  like internal to an application  not for interchange   but they are known to be crackable

User · Answer

I ve always tested to find the MAX string length of an encrypted string and set that as the character length of a VARCHAR type  Depending on how many records you re going to have  it could really help the database size

User · Answer

As a fixed length string  VARCHAR n  or however MySQL calls it   A hash has always a fixed length of for example 12 characters  depending on the hash algorithm you use   So a 20 char password would be reduced to a 12 char hash  and a 4 char password would also yield a 12 char hash

User · Answer

Always use a password hashing algorithm  Argon2  scrypt  bcrypt or PBKDF2   Argon2 won the 2015 password hashing competition  Scrypt  bcrypt and PBKDF2 are older algorithms that are considered less preferred now  but still fundamentally sound  so if your platform doesn t support Argon2 yet  it s ok to use another algorithm for now   Never store a password directly in a database  Don t encrypt it  either  otherwise  if your site gets breached  the attacker gets the decryption key and so can obtain all passwords  Passwords MUST be hashed   A password hash has different properties from a hash table hash or a cryptographic hash  Never use an ordinary cryptographic hash such as MD5  SHA-256 or SHA-512 on a password  A password hashing algorithm uses a salt  which is unique  not used for any other user or in anybody else s database   The salt is necessary so that attackers can t just pre-calculate the hashes of common passwords  with a salt  they have to restart the calculation for every account  A password hashing algorithm is intrinsically slow      as slow as you can afford  Slowness hurts the attacker a lot more than you because the attacker has to try many different passwords  For more information  see How to securely hash passwords   A password hash encodes four pieces of information    An indicator of which algorithm is used  This is necessary for agility  cryptographic recommendations change over time  You need to be able to transition to a new algorithm  A difficulty or hardness indicator  The higher this value  the more computation is needed to calculate the hash  This should be a constant or a global configuration value in the password change function  but it should increase over time as computers get faster  so you need to remember the value for each account  Some algorithms have a single numerical value  others have more parameters there  for example to tune CPU usage and RAM usage separately   The salt  Since the salt must be globally unique  it has to be stored for each account  The salt should be generated randomly on each password change  The hash proper  i e  the output of the mathematical calculation in the hashing algorithm    Many libraries include a pair functions that conveniently packages this information as a single string  one that takes the algorithm indicator  the hardness indicator and the password  generates a random salt and returns the full hash string  and one that takes a password and the full hash string as input and returns a boolean indicating whether the password was correct  There s no universal standard  but a common encoding is   algorithm parameters salt output  where algorithm is a number or a short alphanumeric string encoding the choice of algorithm  parameters is a printable string  and salt and output are encoded in Base64 without terminating     16 bytes are enough for the salt and the output   See e g  recommendations for Argon2   Encoded in Base64  that s 21 characters each  The other two parts depend on the algorithm and parameters  but 20   40 characters are typical  That s a total of about 82 ASCII characters  CHAR 82   and no need for Unicode   to which you should add a safety margin if you think it s going to be difficult to enlarge the field later   If you encode the hash in a binary format  you can get it down to 1 byte for the algorithm  1   4 bytes for the hardness  if you hard-code some of the parameters   and 16 bytes each for the salt and output  for a total of 37 bytes  Say 40 bytes  BINARY 40   to have at least a couple of spare bytes  Note that these are 8-bit bytes  not printable characters  in particular the field can include null bytes   Note that the length of the hash is completely unrelated to the length of the password

User · Answer

It really depends on the hashing algorithm you re using  The length of the password has little to do with the length of the hash  if I remember correctly  Look up the specs on the hashing algorithm you are using  run a few tests  and truncate just above that

User · Answer

You can actually use CHAR length of hash  to define your datatype for MySQL because each hashing algorithm will always evaluate out to the same number of characters  For example  SHA1 always returns a 40-character hexadecimal number

User · Answer

I ve always tested to find the MAX string length of an encrypted string and set that as the character length of a VARCHAR type  Depending on how many records you re going to have  it could really help the database size

User · Answer

You might find this Wikipedia article on salting worthwhile  The idea is to add a set bit of data to randomize your hash value  this will protect your passwords from dictionary attacks if someone gets unauthorized access to the password hashes

User · Answer

It really depends on the hashing algorithm you re using  The length of the password has little to do with the length of the hash  if I remember correctly  Look up the specs on the hashing algorithm you are using  run a few tests  and truncate just above that

User · Answer

Hashes are a sequence of bits  128 bits  160 bits  256 bits  etc   depending on the algorithm   Your column should be binary-typed  not text character-typed  if MySQL allows it  SQL Server datatype is binary n  or varbinary n    You should also salt the hashes  Salts may be text or binary  and you will need a corresponding column

User · Answer

You can actually use CHAR length of hash  to define your datatype for MySQL because each hashing algorithm will always evaluate out to the same number of characters  For example  SHA1 always returns a 40-character hexadecimal number

User · Answer

You might find this Wikipedia article on salting worthwhile  The idea is to add a set bit of data to randomize your hash value  this will protect your passwords from dictionary attacks if someone gets unauthorized access to the password hashes

User · Answer

As a fixed length string  VARCHAR n  or however MySQL calls it   A hash has always a fixed length of for example 12 characters  depending on the hash algorithm you use   So a 20 char password would be reduced to a 12 char hash  and a 4 char password would also yield a 12 char hash

User · Answer

Hashes are a sequence of bits  128 bits  160 bits  256 bits  etc   depending on the algorithm   Your column should be binary-typed  not text character-typed  if MySQL allows it  SQL Server datatype is binary n  or varbinary n    You should also salt the hashes  Salts may be text or binary  and you will need a corresponding column

User · Answer

Hashes are a sequence of bits  128 bits  160 bits  256 bits  etc   depending on the algorithm   Your column should be binary-typed  not text character-typed  if MySQL allows it  SQL Server datatype is binary n  or varbinary n    You should also salt the hashes  Salts may be text or binary  and you will need a corresponding column

User · Answer

Always use a password hashing algorithm  Argon2  scrypt  bcrypt or PBKDF2   Argon2 won the 2015 password hashing competition  Scrypt  bcrypt and PBKDF2 are older algorithms that are considered less preferred now  but still fundamentally sound  so if your platform doesn t support Argon2 yet  it s ok to use another algorithm for now   Never store a password directly in a database  Don t encrypt it  either  otherwise  if your site gets breached  the attacker gets the decryption key and so can obtain all passwords  Passwords MUST be hashed   A password hash has different properties from a hash table hash or a cryptographic hash  Never use an ordinary cryptographic hash such as MD5  SHA-256 or SHA-512 on a password  A password hashing algorithm uses a salt  which is unique  not used for any other user or in anybody else s database   The salt is necessary so that attackers can t just pre-calculate the hashes of common passwords  with a salt  they have to restart the calculation for every account  A password hashing algorithm is intrinsically slow      as slow as you can afford  Slowness hurts the attacker a lot more than you because the attacker has to try many different passwords  For more information  see How to securely hash passwords   A password hash encodes four pieces of information    An indicator of which algorithm is used  This is necessary for agility  cryptographic recommendations change over time  You need to be able to transition to a new algorithm  A difficulty or hardness indicator  The higher this value  the more computation is needed to calculate the hash  This should be a constant or a global configuration value in the password change function  but it should increase over time as computers get faster  so you need to remember the value for each account  Some algorithms have a single numerical value  others have more parameters there  for example to tune CPU usage and RAM usage separately   The salt  Since the salt must be globally unique  it has to be stored for each account  The salt should be generated randomly on each password change  The hash proper  i e  the output of the mathematical calculation in the hashing algorithm    Many libraries include a pair functions that conveniently packages this information as a single string  one that takes the algorithm indicator  the hardness indicator and the password  generates a random salt and returns the full hash string  and one that takes a password and the full hash string as input and returns a boolean indicating whether the password was correct  There s no universal standard  but a common encoding is   algorithm parameters salt output  where algorithm is a number or a short alphanumeric string encoding the choice of algorithm  parameters is a printable string  and salt and output are encoded in Base64 without terminating     16 bytes are enough for the salt and the output   See e g  recommendations for Argon2   Encoded in Base64  that s 21 characters each  The other two parts depend on the algorithm and parameters  but 20   40 characters are typical  That s a total of about 82 ASCII characters  CHAR 82   and no need for Unicode   to which you should add a safety margin if you think it s going to be difficult to enlarge the field later   If you encode the hash in a binary format  you can get it down to 1 byte for the algorithm  1   4 bytes for the hardness  if you hard-code some of the parameters   and 16 bytes each for the salt and output  for a total of 37 bytes  Say 40 bytes  BINARY 40   to have at least a couple of spare bytes  Note that these are 8-bit bytes  not printable characters  in particular the field can include null bytes   Note that the length of the hash is completely unrelated to the length of the password

User · Answer

Update  Simply using a hash function is not strong enough for storing passwords  You should read the answer from Gilles on this thread for a more detailed explanation   For passwords  use a key-strengthening hash algorithm like Bcrypt or Argon2i  For example  in PHP  use the password hash   function  which uses Bcrypt by default    hash   password hash  rasmuslerdorf   PASSWORD DEFAULT     The result is a 60-character string similar to the following  but the digits will vary  because it generates a unique salt     2y 10  vGA1O9wmRjrwAVXD98HNOgsNpDczlqm3Jq7KnEd1rVAGv3Fykk1a   Use the SQL data type CHAR 60  to store this encoding of a Bcrypt hash  Note this function doesn t encode as a string of hexadecimal digits  so we can t as easily unhex it to store in binary   Other hash functions still have uses  but not for storing passwords  so I ll keep the original answer below  written in 2008     It depends on the hashing algorithm you use   Hashing always produces a result of the same length  regardless of the input   It is typical to represent the binary hash result in text  as a series of hexadecimal digits   Or you can use the UNHEX   function to reduce a string of hex digits by half    MD5 generates a 128-bit hash value   You can use CHAR 32  or BINARY 16  SHA-1 generates a 160-bit hash value   You can use CHAR 40  or BINARY 20  SHA-224 generates a 224-bit hash value   You can use CHAR 56  or BINARY 28  SHA-256 generates a 256-bit hash value   You can use CHAR 64  or BINARY 32  SHA-384 generates a 384-bit hash value   You can use CHAR 96  or BINARY 48  SHA-512 generates a 512-bit hash value   You can use CHAR 128  or BINARY 64  BCrypt generates an implementation-dependent 448-bit hash value  You might need CHAR 56   CHAR 60   CHAR 76   BINARY 56  or BINARY 60    As of 2015  NIST recommends using SHA-256 or higher for any applications of hash functions requiring interoperability  But NIST does not recommend using these simple hash functions for storing passwords securely   Lesser hashing algorithms have their uses  like internal to an application  not for interchange   but they are known to be crackable

User · Answer

As a fixed length string  VARCHAR n  or however MySQL calls it   A hash has always a fixed length of for example 12 characters  depending on the hash algorithm you use   So a 20 char password would be reduced to a 12 char hash  and a 4 char password would also yield a 12 char hash

User · Answer

It really depends on the hashing algorithm you re using  The length of the password has little to do with the length of the hash  if I remember correctly  Look up the specs on the hashing algorithm you are using  run a few tests  and truncate just above that

[mysql] What data type to use for hashed password field and what length?

Examples related to mysql

Examples related to hash

Examples related to types

Examples related to passwords

Examples related to cryptography