PASSWORD function issue when migrating from MySQL less than 5.7.5

While trying to migrate my database from 5.7.37 to 8.0.12, I started experiencing the following error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

This was as a result of the PASSWORD function being removed from mysql versions greater than 5.7.5


mysqlsql

Solution

The PASSWORD function has a basic implementation. It's just two rounds of SHA-1 hashing.

Meaning we can reimplement it as follows:

SELECT CONCAT('*', UPPER( SHA1( UNHEX( SHA1('mypassword') ) ) ) )

So all that you need to do is to replace all calls to the PASSWORD function in your sql with this and it should do the same job regardless of your mysql version.


For example

SELECT userName, firstName FROM Users WHERE userName='myuser' AND password = PASSWORD('mypassword')

Becomes

SELECT userName, firstName FROM Users WHERE userName='myuser' AND password = (SELECT CONCAT('*', UPPER(SHA1(UNHEX(SHA1('$password'))))))

Hope that helps.

Recent