MySQL: Password Encryption using the Advanced Encryption Standard Algorithm (AES_ENCRYPT())

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

This is going to be one of most important article for the MySQL community because I am going to share, what should be our best practice to store encrypted passwords into MySQL Database Server.

The MySQL provides different algorithm and function to encrypt and decrypt password data or any other sensitive information so that no one can access it in plain text format.

For different Encryption and Compression functions you can visit this MySQL Developer official site.

Generally, people are using MD5 and SHA algorithm for password encryption, but both are easy to break and vulnerable, so we should not use this in our general practice.

We should also not use PASSWORD() function because it is used by the authentication system in MySQL Server.

Advanced Encryption Standard Algorithm (AES):

This is one of the important encryption algorithm and it is highly secure because it encrypts the string using the encryption key string and returns an encrypted binary string output.

MySQL provides AES_ENCRYPT() to encrypt the string in binary format and AES_DECRYPT() to decrypt the string in plain text.

The only one problem is, we should hide the key value for security purpose by setting object level permission or we can create a view to hide the encryption key value.

You can create BINARY or BLOB data type to store AES encrypted password.

Below is a small demonstration on this:

The syntax:

First, create a table with sample data:

SELECT first row by comparing password using defined encryption key:

To DECRYPT the password into plain text: