How to Store Password in MySQL Database

1. What is Hashing
2. Use MD5 (str) to Store Passwords in MySQL
3. Use SHA1 (str) to Store Passwords in MySQL
4. Use the PASSWORD Function to Store Passwords in MySQL
5. Use the ENCRYPT Function to Store Passwords in MySQL

If you have no idea how to store a password in the MySQL database, this is the right place. In this guide, you’ll be able to learn how to store hash passwords in MySQL database, the different techniques and their specific methods used for storing the Hash password.

What is Hashing

Hashing converts a given number of keys or a string of characters into a shorter, fixed-length value. In Hashing, the data being of fixed size means any number of keys or string of characters converted into hash characters are of the same size.

Another property of hashing is that it is a non-retrievable process which means that if a value has been converted into a hash, there is no way to retrieve the original data value.

Different functions of hashing are used in MySQL; these are the following:

1. MD5 (str)
2. SHA1 (str)
3. PASSWORD
4. ENCRYPT

Use MD5 (str) to Store Passwords in MySQL

md5 is a function that takes data 128-bit checksum and returns it as a string of 32 hex digits.

First, we create a table clients in our database and two fields of that table with the datatype varchar.

We insert some values and create a hash password using md5.

As you can see, the md5 function transformed both of our input values into 32 hex digits.

Let us show what we meant earlier when we said it is a non-retrievable function.

As you can see, we had to use the md5 function for both our values to get our data back; otherwise, it generated an error.

Use SHA1 (str) to Store Passwords in MySQL

sha1 is such a function of hashing that has a much bigger range than the previous md5 function. It converts the data checksum 160-bit into a string of 40 hex digits.

Here’s an example for your better understanding.

You would have got an ample idea by now that these two hashing functions work and by what means they are different from one another. Another thing about sha1 is that it has much better versions available, providing a much greater range.

These are as follows:

1. SHA224 - It converts the data 224-bit checksum and returns a string of 56 hex digits.
2. SHA256 - It converts the data 256-bit checksum and returns a string of 64 hex digits.
3. SHA384 - It converts the data 384-bit checksum and returns a string of 96 hex digits.
4. SHA512 - It converts the data 512-bit checksum and returns a string of 128 hex digits.

Use the PASSWORD Function to Store Passwords in MySQL

The password function generates a hashed password using a plain-text password string. The password function would return NULL in case of a NULL argument.

Let’s do a few examples for your better understanding.

SELECT
PASSWORD('xyz');

Output:

6gd7gb67shy87865

Now, let’s try a string that has digits and characters.

SELECT
PASSWORD('xyz123');

Output:

54fg56gs32sgi3862

Another important thing regarding the password function is that it does not support all MySQL versions. The supported versions are:

1. MySQL 5.6
2. MySQL 5.5
3. MySQL 5.1
4. MySQL 5.0
5. MySQL 4.1

Use the ENCRYPT Function to Store Passwords in MySQL

It is a conversion of a string of characters into hard-to-read binary data. The encrypted data can be easily decrypted later.

An important note is that the column’s datatype should be BLOB.

For Encryption, two types of functions are used:

1. AES (Advanced Encryption Standards) - It uses an Official AES algorithm that ensures an encoding with a 128-bit key. For encryption using aes, you must write AES_ENCRYPT(str,key_str).
2. DES (Data Encryption Standards) uses the Triple-DES algorithm. The condition for this encryption is that MySQL should be configured with SSL support.

A point to ponder here is that the Encrypt function is available only for the Unix operating systems. So for other operating systems, we would have to use AES or DES Encryption.

To familiarize you with this technique, let’s dive into the encryption function.

After applying the AES_Encryption on the value, we can see its effects in the table. Have another example:

To retrieve our value, we would have to provide the right key; otherwise, we would not get our desired data back.

Here, we inserted the wrong key, and the result is below.

Here, we retrieve our other values using the right key.