JIYIK CN >

If you don’t know how to store passwords in MySQL database, then this is the right place. In this guide, you will be able to learn about how to store hashed passwords in MySQL database, different techniques and their specific methods used to store hashed passwords.

What is hashing

Hashing converts a given number of keys or strings into a shorter fixed-length value. In hashing, fixed-size data means that any number of keys or strings convert to the same size of the hash character.

Another property of hashing is that it is a non-retrievable process, which means that if a value has been converted into a hash, the original data value cannot be retrieved.

There are different hash functions used in MySQL; these are:

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

Using MD5 (str) to store passwords in MySQL

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

First, we create a table clients in the database and create two fields of varchar data type in the table.

We insert some values ​​and create a hashed password using md5.

As you can see, the md5 function converts both of our input values ​​into 32 hexadecimal digits.

Let's show what we meant earlier when we said it was a non-retrievable function.

As you can see, we have to use the md5 function on both values ​​to get the data back; otherwise, it will generate an error.

Using SHA1(str) to store passwords in MySQL

sha1 is such a hash function, with a much larger range than the previous md5 function. It converts the data checksum 160 bits into a string of 40 hexadecimal digits.

Here is an example to give you a better understanding.

By now you should have a good understanding of how both hash functions work and the difference between them. Another thing about sha1 is that there are better versions of it available that offer a larger range.

These are as follows:

• SHA224 - It converts the data into a 224-bit checksum and returns a string of 56 hexadecimal digits.
• SHA256 - It converts the data into a 256-bit checksum and returns a string of 64 hexadecimal digits.
• SHA384 - It converts the data into a 384-bit checksum and returns a string of 96 hexadecimal digits.
• SHA512 - It converts the data into a 512-bit checksum and returns a string of 128 hexadecimal digits.

Storing passwords in MySQL using the PASSWORD function

The password function generates a hashed password using a plain text password string. If the argument is NULL, the password function returns NULL.

Let's take a few examples so you can understand better.

SELECT
PASSWORD('xyz');

Output:

6gd7gb67shy87865

Now, let's try a string that contains both numbers and characters.

SELECT
PASSWORD('xyz123');

Output:

54fg56gs32sgi3862

Another important thing to note about the password feature is that it is not supported in all MySQL versions. The supported versions are:

• MySQL 5.6
• MySQL 5.5
• MySQL 5.1
• MySQL 5.0
• MySQL 4.1

Using the ENCRYPT function to store passwords in MySQL

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

The important point is that the data type of the column should be BLOB.

For encryption, two types of functions are used:

• AES (Advanced Encryption Standard) - It uses the official AES algorithm which ensures encoding with a 128-bit key. For encryption using aes, you have to AES_ENCRYPT(str,key_str)write
• DES (Data Encryption Standard) uses the Triple-DES algorithm. For this encryption, MySQL should be configured with SSL support.

One thing to consider here is that the encryption feature is available only for Unix operating systems. So for other operating systems, we will have to use AES or DES encryption.

To familiarize you with the technology, let’s take a closer look at what encryption does.

After applying AES encryption to the values, we can see its effect in the table. Here is another example:

To retrieve our value, we must provide the correct key; otherwise, we will not be able to get back the data we need.

Here, we inserted the wrong key and the result is as follows.

Here we retrieve our other value using the correct key.