MySQL select by password() does not return expecte

Question

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?

Answer 1

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.