CODEWITHSUNDEEP
mysqlhashpasswords
장현수
장현수

Reputation: 365

MySQL select by password() does not return expected result

In MySQL I execute 

insert into test (id, pw) values(1, password('1234'));

I got 

Query OK, 1 row affected, 1 warning (0.01 sec)

I want to search a record by password, so I execute 

select * from test where pw = password('1234');

I expect it to return one row, but I get an empty set. What did I do wrong?

Upvotes: 0

Views: 527

Answers (1)

Drew
Drew

Reputation: 24959

The MySQL Password() function generates a nonbinary string currently (circa 2016) up to 41 characters. This is visible thru either calls to 

SHOW CREATE TABLE mysql.user;

and examining the password column (which holds a hashed value):

`Password` char(41) ...

for, say, MySQL 5.6, or by hashing the same Cleartext value and lining it up to the output of the same SHOW CREATE TABLE on MySQL 5.7

`authentication_string` text ...

The hash values are consistent, yet in a different schema layout. Again, one in a VARCHAR(41), the other in a TEXT, as the same mysql_native_password PAM is being used. For now. Password() became deprecated as of 5.7.6 which means a new Plugin is in the works. Which they should be. They are plugins afterall.

What does it all mean? It means your schema needs to have a wide enough column to handle your use of Password() (note 5.7's switch to TEXT). And remember it is deprecated so keep an ear out for changes with MySQL hashing in the next few years.

Upvotes: 1

Related Questions