How the OLD_PASSWORD() function works in Mariadb?

The OLD_PASSWORD() function is a built-in function in Mariadb that returns the hashed password of a string value using the pre-4.1 hashing algorithm. The function is mainly used for compatibility purposes with older versions of Mariadb or MySQL that use the old password format. The function is not recommended for security reasons, as the old password format is less secure and more vulnerable to brute-force attacks.

Syntax

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

OLD_PASSWORD(str)

Where str is a string value that represents a password. If str is NULL, the function returns NULL.

Examples

Example 1: Hashing a simple password with the OLD_PASSWORD() function

The following example shows how to use the OLD_PASSWORD() function to hash a simple password with the pre-4.1 hashing algorithm:

SELECT OLD_PASSWORD('secret') AS OldPassword;

The output is:

+------------------+
| OldPassword      |
+------------------+
| 428567f408994404 |
+------------------+

The function returns a 16-character hexadecimal string that is the hashed password of ‘secret’ using the old password format.

Example 2: Comparing the OLD_PASSWORD() function with the PASSWORD() function

The following example shows how to use the OLD_PASSWORD() function and the PASSWORD() function to hash the same password with different hashing algorithms:

SELECT OLD_PASSWORD('secret') AS OldPassword,
       PASSWORD('secret') AS NewPassword;

The output is:

+------------------+-------------------------------------------+
| OldPassword      | NewPassword                               |
+------------------+-------------------------------------------+
| 428567f408994404 | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 |
+------------------+-------------------------------------------+

The function PASSWORD() returns a 41-character string that is the hashed password of ‘secret’ using the new password format. The new password format is more secure and less prone to collisions than the old password format.

Example 3: Using the OLD_PASSWORD() function with the SET PASSWORD statement

The following example shows how to use the OLD_PASSWORD() function with the SET PASSWORD statement to change the password of a user using the old password format:

SET PASSWORD FOR 'user'@'localhost' = OLD_PASSWORD('newpass');

The statement changes the password of the user ‘user’@’localhost’ to ’newpass’ using the old password format. This is only necessary if the user needs to connect to an older version of Mariadb or MySQL that does not support the new password format.

There are some other functions in Mariadb that are related to the OLD_PASSWORD() function. They are:

PASSWORD(): This function returns the hashed password of a string value using the new password format. The new password format is more secure and recommended for most cases. The function is also known as ENCRYPT().
MD5(): This function returns the MD5 hash of a string value. MD5 is a widely used cryptographic hash function that produces a 32-character hexadecimal string. The function is often used for generating unique identifiers or checksums of data.
SHA1(): This function returns the SHA-1 hash of a string value. SHA-1 is another popular cryptographic hash function that produces a 40-character hexadecimal string. The function is also known as SHA().

Conclusion

The OLD_PASSWORD() function is a useful function in Mariadb that allows you to hash a password using the old password format. The old password format is mainly used for compatibility purposes with older versions of Mariadb or MySQL that use the pre-4.1 hashing algorithm. The function is not recommended for security reasons, as the old password format is less secure and more vulnerable to brute-force attacks. You can also use other functions like PASSWORD(), MD5(), and SHA1() to hash passwords using different algorithms. I hope this article helped you understand how the OLD_PASSWORD() function works in Mariadb.