How the MD5() function works in Mariadb?

The MD5() function is a useful tool for generating a 32-digit hexadecimal hash value from a given string.

Posted on

The MD5() function is a useful tool for generating a 32-digit hexadecimal hash value from a given string. It can be used for various purposes, such as password encryption, data integrity verification, and digital signature generation.

Syntax

The syntax of the MD5() function is as follows:

MD5(str)

The function takes one argument:

  • str: A string value that represents the input to be hashed. It can be any valid expression that returns a string, such as a column name, a literal, or a function.

The function returns a string value that represents the 32-digit hexadecimal hash value of the input string.

Examples

In this section, we will show some examples of how to use the MD5() function in different scenarios.

Example 1: Generating a hash value from a literal string

Suppose you want to generate a hash value from a literal string, such as ‘Hello World’. You can use the MD5() function to do so. For example, you can execute the following statement:

SELECT MD5('Hello World');

This will return the hash value of the string ‘Hello World’, which is ‘b10a8db164e0754105b7a99be72e3fe5’. For example, the result might look like this:

+----------------------------------+
| MD5('Hello World')               |
+----------------------------------+
| b10a8db164e0754105b7a99be72e3fe5 |
+----------------------------------+

Note that the hash value is case-sensitive, meaning that changing the case of any letter in the input string will result in a different hash value. For example, the hash value of ‘hello world’ is ‘5eb63bbbe01eeed093cb22bb8f5acdc3’, which is different from the hash value of ‘Hello World’.

Example 2: Generating a hash value from a column value

Suppose you have a table called users that stores the information of various users, such as their name, email, and password. The password column is a plain text that represents the password of the user. You want to generate a hash value from the password of each user, so that you can store it in a more secure way. You can use the MD5() function to do so. For example, you can execute the following statement:

SELECT name, email, MD5(password) AS hashed_password FROM users;

This will return the name, email, and hashed password of each user, or an empty result set if the table is empty. For example, the result might look like this:

+----------+---------------------+----------------------------------+
| name     | email               | hashed_password                  |
+----------+---------------------+----------------------------------+
| Alice    | [email protected]   | 21232f297a57a5a743894a0e4a801fc3 |
| Bob      | [email protected]     | e99a18c428cb38d5f260853678922e03 |
| Charlie  | [email protected] | 04e8c90e44d2f016b67d8f5672e1aaec |
+----------+---------------------+----------------------------------+

Note that the hashed password is not reversible, meaning that you cannot get the original password from the hash value. This is a desirable feature for password encryption, as it prevents anyone from accessing the password even if they have access to the database.

Example 3: Generating a hash value from a function result

Suppose you want to generate a hash value from the result of a function, such as NOW() or CONCAT(). You can use the MD5() function to do so. For example, you can execute the following statement:

SELECT MD5(NOW());

This will return the hash value of the current date and time, which is a dynamic value that changes every second. For example, the result might look like this:

+----------------------------------+
| MD5(NOW())                       |
+----------------------------------+
| 9f6c9c1f2a9c9f8a9f6c9c1f2a9c9f8a |
+----------------------------------+

Similarly, you can execute the following statement:

SELECT MD5(CONCAT('Hello', ' ', 'World'));

This will return the hash value of the concatenation of the strings ‘Hello’, ’ ‘, and ‘World’, which is the same as the hash value of the string ‘Hello World’. For example, the result might look like this:

+------------------------------------+
| MD5(CONCAT('Hello', ' ', 'World')) |
+------------------------------------+
| b10a8db164e0754105b7a99be72e3fe5   |
+------------------------------------+

There are some other functions that are related to the MD5() function and can be used to perform other hashing operations in Mariadb. Here are some of them:

  • SHA1(): This function returns a 40-digit hexadecimal hash value from a given string, using the SHA-1 algorithm.
  • SHA2(): This function returns a hexadecimal hash value from a given string, using the SHA-2 algorithm. It can accept a second argument that specifies the bit length of the hash value, which can be 224, 256, 384, or 512.
  • PASSWORD(): This function returns a hashed password from a given string, using the password hashing algorithm of Mariadb. It is mainly used for creating and verifying passwords for Mariadb user accounts.
  • ENCRYPT(): This function returns a hashed string from a given string, using the Unix crypt() system call. It can accept a second argument that specifies a salt value for the hashing process.

Conclusion

The MD5() function is a powerful and flexible function that can help you generate a 32-digit hexadecimal hash value from a given string. It can be used for various purposes, such as password encryption, data integrity verification, and digital signature generation. You can also use some other related functions to perform other hashing operations, such as SHA-1, SHA-2, PASSWORD, or ENCRYPT. By using these functions, you can achieve a better security and reliability of your data.