DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

Cover image for How to Hash Passwords using MySQL
Pedro Aravena for Vaultree

Posted on • Updated on

How to Hash 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')
  );
Enter fullscreen mode Exit fullscreen mode

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.

Should it be used?

CompTIA Security+ 2008 in depth says that, β€œBy the mid-1990s, weaknesses were revealed in the compression functions that could lead to collisions, and 10 years later successful attacks on MD5 were being conducted. Most security experts recommend that the family of MD hashes be replaced with a more secure hash algorithm”. These collisions mean that a hacker or malicious user could create files that would have the same exact hash as another, making it impossible to be sure that the file has not actually been tampered with.

But what should you use instead? The author goes on to say, β€œMost security experts recommend that SHA-2 be substituted in place of MD5.”

We will cover the subject of SHA-2 in the next weeks, stay tuned.

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'));
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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 Validated
validated
-------
       1
Enter fullscreen mode Exit fullscreen mode

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.

Is MD5 secure nowadays? What should I use?
A: Unfortunately, MD5 has been cryptographically broken and considered insecure. For this reason, it should not be used for anything. Instead, developers should switch to the Secure Hash Algorithm or a Symmetric Cryptographic Algorithm. With current GPUs and hash cracking tools, using MD5 is barely better than using nothing at all. It is always recommended to store user passwords using a hashing algorithm and you should find that it is equally easy to use SHA-2 in place of MD5 in any modern programming framework.

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.

Image description

Top comments (3)

Collapse
 
koleckai profile image
Wayne Luke

MD5 should be considered weak on today's internet. There have been many rainbow tables built against it and billions of passwords have been compromised. It would be better to use hash systems that are more cryptographically secure and help protect your password against rainbow table or bruteforce attacks. Blowfish/bcrypt and Argon hashing are stronger and can be passed parameters to blunt the advantage of newer hardware in the process in creating the hash.

Collapse
 
opedroaravena profile image
Pedro Aravena Author

Hey Wayne, thank you for your comment. Yes! You are right, I was trying to bring the MD5 conversation to clarify these topics you mentioned in the next article. But you've got a great point! I really appreciate it. Feel free to always add up with your knowledge! :)

Collapse
 
darkain profile image
Vincent Milum Jr

Just for clarity: hashing is NOT encryption. They are one-way algorithms. Additions, it is HIGHLY advised to not attempt to make your own implementation of highly sensitive data security systems. Languages like PHP have built in salted hash creations with complex algorithms that handle situations like timing attacks and other attacks.

πŸ€” Did you know?

Β 
🌚 You can turn on dark mode in Settings