In MySQL SERVER 8.0 the PASSWORD function not working

Question

Error while executing the PASSWORD function in MySQL Server version 8.0.12

I have the following query:

SELECT * 
FROM users 
WHERE login = 'FABIO' 
  AND pwd = PASSWORD('2018') 
LIMIT 0, 50000

I am getting this 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

Answer 1

OP's MySQL Server version is 8.0.12. From MySQL Documentation, PASSWORD function has been deprecated for version > 5.7.5:

Note

The information in this section applies fully only before MySQL 5.7.5, and only for accounts that use the mysql_native_password or mysql_old_password authentication plugins. Support for pre-4.1 password hashes was removed in MySQL 5.7.5. This includes removal of the mysql_old_password authentication plugin and the OLD_PASSWORD() function. Also, secure_auth cannot be disabled, and old_passwords cannot be set to 1.

As of MySQL 5.7.5, only the information about 4.1 password hashes and the mysql_native_password authentication plugin remains relevant.

Instead, of the PASSWORD function, you can use much better and secure encryption functions from here. More details from the MySQL server team can be seen here.

Answer 2

If you need a replacement hash to match the password() function, use this: SHA1(UNHEX(SHA1())); E.g.

mysql> SELECT PASSWORD('mypass');
+-------------------------------------------+
| PASSWORD('mypass')                        |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+

and replacement that gives the same answer in version 8:

mysql> SELECT CONCAT('*', UPPER(SHA1(UNHEX(SHA1('mypass')))));
+-------------------------------------------------+
| CONCAT('*', UPPER(SHA1(UNHEX(SHA1('mypass'))))) |
+-------------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4       |
+-------------------------------------------------+

Answer 3

With MySQL 8.0.22, I had to do the following:

1. update /etc/mysql/my.cnf and add lines:

    [mysqld]
   
    skip-grant-tables
   

2. restart mysql and run some queries:

    > systemctl restart mysql
    > sudo mysql
    mysql> UPDATE mysql.user SET authentication_string=null WHERE User='root';
    FLUSH PRIVILEGES;
    mysql> exit;
   

3. log in again and update the password:

    > mysql -u root
    mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'dbtastic5332';
    mysql> FLUSH PRIVILEGES;
   

4. update /etc/mysql/my.cnf and remove the line skip-grant-tables

    > systemctl restart mysql
   

5. Finally, test

    > mysql -u root -p 
   

Answer 4

you may create another function that is similar to PASSWORD

SET GLOBAL log_bin_trust_function_creators = 1;
delimiter $$
CREATE FUNCTION PASSWORD2 (pass_in varchar(50)) RETURNS varchar(50)
BEGIN
  declare n_pass varchar(50);
  set n_pass = CONCAT('*', UPPER(SHA1(UNHEX(SHA1(pass_in))))); 
  return n_pass;
END$$

Then

SELECT PASSWORD2("my_super_scret_password") FROM MyUserTable ....