Why are mysql password hashes internally saved with a star (asterisk)?

All we need is an easy explanation of the problem, so here it is.

I was reading upon some mysql internals, when going through the mysql.user table in the my mysql shell, I get

mysql> select * from mysql.user limit 1 \G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password: *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

The password is obviously hashed, but why does it begin with the star (asterisk)?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

In addition to the password starting with an asterisk, here is algorithm for PASSWORD()

SET @plaintextpassword = 'whatever password you want';
SELECT CONCAT('*',UPPER(SHA1(UNHEX(SHA1(@plaintextpassword)))));

EXAMPLE

mysql> SET @plaintextpassword = 'whatever password you want';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT UPPER(SHA1(UNHEX(SHA1(@plaintextpassword)))) PWD_CREATION;
+------------------------------------------+
| PWD_CREATION                             |
+------------------------------------------+
| D09AF2704D843A5E4E84362830C7EC1CEA40DF8A |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT PASSWORD(@plaintextpassword) PWD_FUNCTION;
+-------------------------------------------+
| PWD_FUNCTION                              |
+-------------------------------------------+
| *D09AF2704D843A5E4E84362830C7EC1CEA40DF8A |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

I learned this algorithm long ago from Hashing Algorithm in MySQL PASSWORD()

Method 2

Ok, found about this in the documentation itself.

This was a change introduced in mysql 4.1 so that the earlier password lengths of 16 characters and newer password lengths of 40 characters could be simultaneously supported. The Password column was made 41 bytes (chars) long, and the newer passwords would begin with a mandatory * to identify them.

From the documentation:

Password hashes in the 4.1 format always begin with a “*” character, whereas passwords in the pre-4.1 format never do.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply