CODEWITHSUNDEEP
mysqlmysql-8.0
Fabio C
Fabio C

Reputation: 539

In MySQL SERVER 8.0 the PASSWORD function not working

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

Upvotes: 40

Views: 50548

Answers (5)

Jess N
Jess N

Reputation: 11

In short, as outlined in earlier posts:

CREATE FUNCTION MYSCHEMA.PASSWORD2(s VARCHAR(50))
RETURNS VARCHAR(50) DETERMINISTIC
RETURN CONCAT('*', UPPER(SHA1(UNHEX(SHA1(s)))));

However, it does not replace the native PASSWORD() function, so you still have to rewrite some SQL statements.

Upvotes: 0

Frank Forte
Frank Forte

Reputation: 2190

Please see the answer from @rayzinnz for a direct replacement of PASSWORD().

Many people might come across this question/answer in Google looking for a way to set or reset a password. 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 clear the authentication_string for a specific user:

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

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

  4. restart mysql to get my.cnf changes:

    > systemctl restart mysql

  5. log in again and update the password (replace 'my password' with your password):

     > sudo mysql -u root
 mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'my password';
 mysql> FLUSH PRIVILEGES;
 mysql> exit;

  6. Finally, test

     > mysql -u root -p

Upvotes: 34

Kadir Erturk
Kadir Erturk

Reputation: 601

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 ....

Upvotes: 4

rayzinnz
rayzinnz

Reputation: 1958

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       |
+-------------------------------------------------+

Upvotes: 86

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

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.

Upvotes: 24

Related Questions