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

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:

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

Be the First to Comment!

Notify of