Troubleshoot PASSWORD Function Issues in MySQL Server 8.0

When attempting to execute the PASSWORD function in MySQL Server version 8.0.12, certain scenarios may trigger an error. This article aims to address such issues and provide a viable solution.

Error Code 1064

If you encounter the error "Error Code: 1064. You have an error in your SQL syntax...", it indicates an issue with the syntax of your query. Specifically, the PASSWORD function syntax has changed in MySQL Server version 8.0.

New Syntax

With MySQL Server 8.0, the PASSWORD function has been deprecated and replaced with a different hashing mechanism. To maintain compatibility, you should use the following syntax to generate a hash equivalent to the one produced by the PASSWORD function in previous versions:

CONCAT('*', UPPER(SHA1(UNHEX(SHA1('my_password')))))

Example

In the provided query, "pwd = PASSWORD('2018')" represents the comparison of the "pwd" column with a static password. To retrieve users with the password '2018' using the new syntax, you can modify the query as follows:

SELECT * 
FROM users 
WHERE login = 'FABIO' 
  AND pwd = CONCAT('*', UPPER(SHA1(UNHEX(SHA1('2018')))))
LIMIT 0, 50000