How the PASSWORD() function works in Mariadb?

The PASSWORD() function is a built-in function in Mariadb that returns the hashed password of a string value using the new password format.

Posted on

The PASSWORD() function is a built-in function in Mariadb that returns the hashed password of a string value using the new password format. The function is useful for creating or updating passwords for user accounts in Mariadb or MySQL. The function is also known as ENCRYPT().

Syntax

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

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 PASSWORD() function

The following example shows how to use the PASSWORD() function to hash a simple password with the new password format:

SELECT PASSWORD('secret') AS NewPassword;

The output is:

+-------------------------------------------+
| NewPassword                               |
+-------------------------------------------+
| *14E65567ABDB5135D0CFD9A70BBD9E9A3786EA9F |
+-------------------------------------------+

The function 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 2: Comparing the PASSWORD() function with the OLD_PASSWORD() function

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

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

The output is:

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

The function PASSWORD() returns a 41-character string that is the hashed password of ‘secret’ using the new password format. The function OLD_PASSWORD() returns a 16-character hexadecimal string that is the hashed password of ‘secret’ using the old password format. The old password format is less secure and more vulnerable to brute-force attacks.

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

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

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

The statement changes the password of the user ‘user’@’localhost’ to ’newpass’ using the new password format. This is the recommended way of changing passwords for user accounts in Mariadb or MySQL.

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

  • OLD_PASSWORD(): This function returns the hashed password of a string value 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.
  • 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 PASSWORD() function is a useful function in Mariadb that allows you to hash a password using the new password format. The new password format is more secure and recommended for most cases. You can also use other functions like OLD_PASSWORD(), MD5(), and SHA1() to hash passwords using different algorithms. I hope this article helped you understand how the PASSWORD() function works in Mariadb.