MySQLPopular PostsSQL

Best practices for storing passwords securely in a database

This is the 4th post in a series on MySQL performance.

A hacker can also read passwords if you can.

Adobe suffered a data breach that affected at least 38 million users in October 2013. Three million encrypted customer credit cards and login credentials for an unknown number of users were exposed. Link

Antipattern: Don’t store passwords in plaintext

Typically, a password is stored in some table as a string attribute column. A password should never be stored or passed over the network in clear text.

If an attacker can read the SQL statement you use to insert a password, they can see the password directly. This applies also to SQL statements used to change a password or verify that user input matches a stored password.

Although it may sound silly, there are websites that store the user’s passwords in plaintext.

Cupid Media hack exposed 42m online dating passwords Link

Hackers have several options for stealing passwords, including:

  • As the SQL statement is sent from the application to the database server, network packets are intercepted.
  • Analyzing SQL query logs on the database server. Assuming the attacker has access to the database server host, they may be able to access log files that record SQL statements executed by the database server.
  • Read data from database backup files on the server or on backup media.

Sending Passwords in Email

The password is stored in plain text in the database, so retrieving it is simple in your application. On request, your application can send an email to a user’s email address. Emailing a password in plain text is a serious security risk. In multiple ways, hackers can intercept, log, and store emails.

Antipattern: Don’t store encrypted passwords

It sounds much better to encrypt the passwords. But hold on. Due to the algorithm’s reversibility, since the attacker has access to the encrypted passwords and with some plain text and their encrypted form, he can guess the logic of encryption and reverse it. No matter how complex the algorithm is, all passwords will be reversed as quickly as in plain text if he succeeds.

Antipattern: Real Threat of storing recoverable password

An application that can recover your password and send it to you must store it in plain text or at least with some form of reversible encoding. It’s possible for a hacker to read a password illicitly if your application is able to read passwords for a legitimate purpose.

The following are best practices:

  1. Passwords should not be recoverable from the database.
  2. Password lengths should not be guessed from the database.
  3. Different hashes should be used for passwords that are the same, or similar.

Hashing the password

According to requirement one above, users’ passwords should not be recoverable from the database.

Use a one-way cryptographic hash function to encode the password. The hash function converts an input string into an unrecognizable string, called a hash.

Another characteristic of a hash is that it cannot be reversed. The input string cannot be recovered from its hash because the hashing algorithm is designed to “lose” some information about it. Good hashing algorithms should take as much effort to crack as they would guess the input by trial and error.

In requirement two above, it is specified that the database should not give any hints as to password length.

Since the hash returned by a hash function is a fixed-length string, even the length of the original string is obscured.

The downside of this approach

Even if you store hashes instead of passwords and the attacker gains access to your database, he can still use brute force to try to guess passwords. It may take him a long time to guess each password. However, he can create a database of hashes of likely passwords against which to compare the hashes in your database. It relies on a precomputed table known as the Rainbow table, which is an approach to reversing cryptographic hash functions.

Imagine you are a hacker who just stole 38 million hashes from Adobe. You can check those hashes in a rainbow table, and if they match, the rainbow table will also contain the string they were hashed from, retrieving it for you.

Antipattern: Obsolete Hash Function

Passwords are often stored using obsolete irreversible cryptographic functions (MD5, SHA1, etc).

The 2012 LinkedIn hack refers to the computer hacking of LinkedIn on June 5, 2012. Passwords for nearly 6.5 million user accounts were stolen. In May 2016, LinkedIn discovered an additional 100 million email addresses and hashed passwords that claimed to be additional data from the same 2012 breach. Link

Recommended: Salting & Hashing the Password

According to requirement three above, identical, or even similar, passwords should have different hashes.

You can defeat this kind of “dictionary attack” by including salt in your password-encoding expression. A salt is a string of meaningless bytes you concatenate with the user’s password before passing the result to the hash function. The hash produced by a salted password will not match the hash in the attacker’s hash database, even if the user chose a word in the dictionary as their password.

Salt values should differ between passwords so that an attacker must generate a new dictionary table of hashes for each password. He is then back at square one.

If a hacker knows the salt, can he retrieve the password? In practice, no. You can hash the salt if you wish, but knowing the salt would not compromise the password in any way. You would have to create a new table for every salt if you tried to use a rainbow table attack.

Use only the hash in your SQL query and compute the hash in your application code. Intercepting a hash does an attacker a little good since he can’t reverse it to get the password.

The downside of this approach

Is this approach completely foolproof? Dictionary attacks and brute-force attacks are still possible since they are too fast, and this is considered a downside.

What would be a better workaround?

Most Recommended: Salting and Hashing with Iteration Count

The iteration count is essentially hashing what has been hashed previously for n times. Using that password, the system generates a unique salt for that user and hashes both of them together. It then concatenates the generated hash with the randomly generated salt and hashes it again n times.

It will add extra security to the basic “hashing and salting” technique. This will obviously take more time.

PBKDF2, which stands for Password-Based Key Derivation Function 2, is an example of this technique. The function applies a pseudorandom function and a salt value to the input password and then repeats the process n times to generate the derived key.

This is how PBKDF2 is defined:

DK = PBKDF2(PRF, Password, Salt, c, dkLen)
* PRF : pseudorandom function of two parameters with output length hLen (e.g., a keyed HMAC) 
* Password : master password from which a derived key is generated
* Salt : sequence of bits, known as a cryptographic salt
* c : number of iterations desired
* dkLen : desired bit-length of the derived key
* DK : generated derived key

A technique like this is much slower than a common hash function. It’s an advantage in case someone tries to brute-force the password, but it has to be fast enough that the user doesn’t notice any delay.

Additional Security: Use of Pepper

Salt can be combined with pepper to increase protection, but pepper is common to all users. This pepper data is not stored in a database, but in the sources of an application, in a configuration file, or in an environment variable. After understanding a database, an attacker must guess the pepper or retrieve it in another way to break hashes.

Passwords Can Be Reset Instead of Recovered

Despite the fact that passwords are stored in a more secure manner, you still need to help users who have forgotten their passwords. Your database now stores a hash instead of their password, so you can’t recover it. The hash cannot be reversed more easily than an attacker could.

But you can allow a user access in other ways.

As a first alternative, when a user who has forgotten his password requests help, instead of emailing his password to him, the application can send an email containing a temporary password generated by it. Additionally, the application may expire the temporary password after a short time, so if the email is intercepted, it’s more likely not to allow unauthorized access. The application should also be designed so that the first thing the user does after logging in changes their password.

As a second alternative, instead of including a new password in an email, a unique token is assigned to a request in a database table. After that, the token is included in the email. A token can also be sent via another message, such as SMS, as long as the address is associated with the account requesting the password reset. As a result, if an unauthorized person requests a password reset, the account owner will only receive a fictitious email. If the application receives a request for the special reset_password screen, the value in the token parameter must match a row in the PasswordResetRequest table, and the expiration timestamp must still be upcoming.


Here is a minimum recommendation for storing your users’ passwords in a safe manner:

  • Use Argon2id with a minimum configuration of 15 MiB of memory, an iteration count of 2, and 1 degree of parallelism.
  • If Argon2id is not available, use scrypt with a minimum CPU/memory cost parameter of (2¹⁶), a minimum block size of 8 (1024 bytes), and a parallelization parameter of 1.
  • For legacy systems using bcrypt, use a work factor of 10 or more and with a password limit of 72 bytes.
  • If FIPS-140 compliance is required, use PBKDF2 with a work factor of 310,000 or more and set it with an internal hash function of HMAC-SHA-256. Use a strong random number generator to create a salt. The US National Institute of Standards and Technology recommends a salt length of 16 bytes (128 bits). Take 32 bytes (256 bits) of output from PBKDF2 as the final password hash. Store the iteration count, the salt, and the final hash in your password database.
  • Consider using pepper to provide additional defense in depth.

Leave a Reply

Your email address will not be published.