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.
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.
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.
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.
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') );
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:
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 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'));
Note that to encrypt the password there is no secret, just pass the value to be encrypted to the md5() function.
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 Validated validated ------- 1
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.