How to Encrypt Passwords using MySQL

2022 has already registered a growing number of cyberattacks, showing that anyone can be a victim; from individuals to large corporations. In the web environment, I say that security is threatened by two aspects, interest and/or curiosity.

Interest

The interest is linked to two factors, harming someone or, for one's benefit.

For example, a hacker, when attacking a website or a system, is interested in harming or demonstrating one's vulnerability, that is, he wants to harm a person/institution or wants to take advantage of it. Advantage how? Take a look.

Curiosity

Answer me honestly, have you ever wanted to access someone's Facebook account? Or certain WhatsApp conversations? You do not need to answer, but we all know that human curiosity is also one of the reasons information leaks.

Encrypting passwords in the database

One reason not to encrypt passwords in the database is to validate the user's password. That is, how to compare the user's password saved in the database with the password entered on the login screen?

Simply follow these three-step task:

1 Create the structure (table).
2 Insert the data.
3 Validate the stored password.

1 Creating the table to insert user data in an encrypted form.

For didactic purposes, our table will only include three fields, id, login, and password:

CREATE TABLE 'user'(   'id' BIGINT NOT NULL AUTO_INCREMENT,   'login' VARCHAR(20) NOT NULL,   'password' VARCHAR(50) NOT NULL,   PRIMARY KEY('id')  );

2 Inserting values in our users' table.

At this point, we are going to insert the records (password) already encrypted, but first, I will point out two forms of encryption existing in MySQL:

Password

MySQL's native function for data encryption works unidirectionally, that is, it is not possible to reverse the information. Its data entry is done through strings, with a return also in the same data type.
The function returns a 42-character binary string or null if the input argument is null.

I do not consider this the safest technique, and the reason is quite simple. The returned hash is interpreted by MySQL. If you need to change your DBMS, this second one will certainly not be able to perform the same encoding. In other words, this will give you a massive headache.

MD5

MD5 is the most used encryption algorithm, being native to several DBMSs and programming languages. In this format the problem I mentioned above is not found, you can export the data to another DBMS and you will continue to have your authentication without problems. Like the password, the MD5 is unidirectional, not being able to decrypt it. The result of the function is a return of a binary hash string of 32 hexadecimal digits.

Now that you know about two of MySQL's encryption types and which one I recommend, let's insert the records into our user table.

INSERT INTO user        (id,         Login,         password)  VALUES (NULL,      'admin',      MD5('admin'));

Note that to encrypt the password there is no secret, just pass the value to be encrypted to the md5() function.

3 Now, how to compare and validate an encrypted password?

In the login form, when forwarding the information to the database, the password may already be encrypted. If so, just compare with the existing one, or you can forward the password as it was typed and, in the database, encrypt and compare it.

I'll show you for didactic purposes only, it does not mean that this is the best or the correct way to do it. First, let's create a function, in which we will pass the login and password as parameters.

DELIMITER $ DROP FUNCTION IF EXISTS `fun_valid_user`$ CREATE FUNCTION `fun_valid_user`(p_login VARCHAR(20)                , p_password VARCHAR(50) ) RETURNS INT(1) BEGIN DECLARE l_ret INT(1) DEFAULT 0;     SET l_ret = IFNULL((SELECT DISTINCT 1                       FROM user                      WHERE login = p_login                       AND password = MD5(p_password)),0); RETURN l_ret; END$ DELIMITER;

In the function, I check if there is any information in the database that matches the values passed to the parameters p_login and p_password. If so, the function will return the value 1 (One), otherwise, 0 (Zero).
Note that to compare the password I used the MD5() function, passing the p_password parameter as a value.
Be aware: Note that I didn't use quotes when passing the value to the MD5 function, this is because the p_password parameter is already a string. If you pass the p_password value with double quotes ('p_password'), the function will encrypt the p_password term and compare with the value that is in the database, and this will not work.
With the function created, we just execute the call passing the values and we have the result, validated or not.

SELECT fun_valid_user('admin','admin') as Validatedvalidated-------       1

FAQ:
Are there other ways of encrypting data in MySQL?
A: Yes, see more information on the MySQL documentation page.

Can I also encrypt the user login?
A: Yes, this may be a business rule, but not so common.

As you can see, there is no secret to encrypting passwords. It's not rocket science. If you have any questions, or would like to share a different way of going through this process, please share with us.

At Vaultree we are building an encrypted future. We love sharing valuable information and trends to help you keep your data safe. Sign up to stay in the loop and discuss the hottest trends in cybersec with a team of experts.