36

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

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
Fabio C
  • 489
  • 1
  • 4
  • 11
  • What is your mysql version ? – Madhur Bhaiya Sep 13 '18 at 19:33
  • Are you sure there is nothing preceding the select in you query string? – Uueerdo Sep 13 '18 at 19:34
  • 1
    @MadhurBhaiya mysql Server version is 8.0.12 – Fabio C Sep 13 '18 at 19:35
  • 1
    @FabioC This function does not exists in MySQL version 8.0 – Madhur Bhaiya Sep 13 '18 at 19:36
  • You shouldn't have been using the PASSWORD() function for your own passwords anyway. The manual for that function has said for years that it's for use by MySQL's system tables only. Use [SHA2()](https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_sha2) instead. – Bill Karwin Sep 13 '18 at 19:43
  • Bcrypt is the current industry standard for password hashing. SHA2 is intended to be a fast hashing algorithm and it's more likely bcrypt stands the test of time. Plus it does seeding so you don't have to think about that. – Evert Feb 15 '20 at 23:19

4 Answers4

71

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       |
+-------------------------------------------------+
MrBlack
  • 369
  • 4
  • 10
rayzinnz
  • 1,639
  • 1
  • 17
  • 17
  • 5
    Why the downvote? This answers the question to people looking for a replacement to PASSWORD when they are locked into specific versions out of their control. – rayzinnz Mar 30 '20 at 17:46
  • 7
    What about asterisk at the beginning? SELECT CONCAT('*', UPPER(SHA1(UNHEX(SHA1('mypass'))))) – MrBlack Apr 22 '20 at 08:17
  • @MrBlack It seems the asterisk is needed, because `PASSWORD(...)` produces a string literal with a leading single asterisk. – tom_mai78101 Jan 04 '22 at 15:46
  • @tom_mai78101 yes and I added it to the answer, initially it was missed – MrBlack Jan 18 '22 at 09:57
25

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. log in again and update the password:

     > mysql -u root
 mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'my password';
 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
Frank Forte
  • 2,031
  • 20
  • 19
  • OP is not asking to reset mysql user password. – Nik Mar 09 '23 at 05:54
  • True, but I think many or even most people will have this same question with the *goal* of resetting their password and don't really care about the function itself. I don't think it hurts to have both answers in this thread (note the 22 upvotes). – Frank Forte Mar 10 '23 at 14:29
  • Most people also wants to reset mssql password, would like to shere it here as well??? or linux/windows/..... etc. share everything you know... – Nik Mar 12 '23 at 09:43
  • after 2) you need to remove "skip-grant-tables" and restart mysql – Teson Jul 12 '23 at 09:00
  • @Teson, we do that in step 4, but you can probably do that after step 2 as well. – Frank Forte Jul 13 '23 at 12:05
  • @FrankForte , if rembember it correctly, mysql wouldn't allow me to change password as "skip-grant-tables" was enabled. So 4) is too late. – Teson Jul 17 '23 at 12:49
23

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.

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • 5
    Here's the official reference saying it was actually removed [here](https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_password) – Uueerdo Sep 13 '18 at 19:39
  • 3
    - Do you know what was put in place of PASSWORD? – Fabio C Sep 13 '18 at 19:47
  • 1
    @FabioC check my updated answer at the bottom. I have given two references. Much more advanced encryption functions have been added instead – Madhur Bhaiya Sep 13 '18 at 19:49
  • 3
    @MadhurBhaiya I'm reading the document now. And I'm going to use a new way. Thank you very much. – Fabio C Sep 13 '18 at 19:56
3

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 ....
Kadir Erturk
  • 583
  • 6
  • 8