How to use `bcrypt` algorithm within `encrypt` function in MySQL for verifying password?

Question

I have bcrypted value($2y$10$zQaDT8hXM4pLmBdwN0xEseda/oKJAQKMKMzUrV8jbs6Epz28BXzBS) of password (qwe). But when I am verifying I am getting wrong result hash value.

mysql> select '$2y$10$zQaDT8hXM4pLmBdwN0xEseda/oKJAQKMKMzUrV8jbs6Epz28BXzBS' = encrypt('qwe', '$2y$10$zQaDT8hXM4pLmBdwN0xEseda/oKJAQKMKMzUrV8jbs6Epz28BXzBS') as is_valid; 
+----------+
| is_valid |
+----------+
|        0 |
+----------+

select encrypt('qwe', '$2y$10$zQaDT8hXM4pLmBdwN0xEseda/oKJAQKMKMzUrV8jbs6Epz28BXzBS') as hash;
+---------------+
| hash          |
+---------------+
| $2tBKnsbV2Szg |
+---------------+

md5 works fine

mysql> select '$1$$.dCRcHz4ApIYzcA0g/qz3/' = encrypt('qwe', '$1$$.dCRcHz4ApIYzcA0g/qz3/') as is_valid; 
+----------+
| is_valid |
+----------+
|        1 |
+----------+

How to add support of bcrypt to MySQL?

Under no circumstances should a plain text password hit MySQL, even if at the query level. Otherwise you risk writing the passwords to log (query log, general log, slow query log, etc). Which is horrific. So no, don't even bother... — ircmaxell
– ircmaxell, Commented Nov 30, 2013 at 3:14
+1 for using bcrypt to store passwords. Even if your method is flawed, it’s a serious step up from most webapps out there. Keep going! — Benjamin Barenblat
– Benjamin Barenblat, Commented Nov 30, 2013 at 7:06
MySQL know about this problem. Passwords and Logging 5.5 and Passwords and Logging 5.6 — sectus
– sectus, Commented Dec 2, 2013 at 5:17
Also Is it possible to hide the password in MySQL GeneralSlow Query Logs — sectus
– sectus, Commented Dec 2, 2013 at 5:24
@ircmaxell there are scenarios man, I have one of them and I can't do anything about it ... I have developed an app which needs to send some automatic report emails after each transaction (with our own mail server). I'm forced to save the email pass in plain-text in DB because I don't want to let the user to enter the in-app report mail password, let the mail to login to the server and send the report mail after that transaction. If I encrypt the password, how can I login to my mail server automatically? At the same time it's not my user business to enter the report mail password ... — Mehdi
– Mehdi, Commented Feb 17, 2020 at 8:10

user149341 · Accepted Answer · 2013-11-30 03:54:26Z

30

You can't. The MySQL ENCRYPT() function uses the operating system's crypt() function — if your operating system does not support bcrypt hashes, MySQL will not support them either.

Also, do not use the MySQL ENCRYPT() function. As ircmaxell noted, any data you pass to a MySQL query may end up in server log files, so it's potentially unsafe to use it for anything password-related.

answered Nov 30, 2013 at 3:54

user149341

Sign up to request clarification or add additional context in comments.

2 Comments

sectus Over a year ago

But I can generate hash with crypt in PHP. I suppose that my system support bcrypt.

user149341 Over a year ago

PHP 5.3 and later use their own implementation of crypt(), instead of the one from the operating system.

Collectives™ on Stack Overflow

How to use `bcrypt` algorithm within `encrypt` function in MySQL for verifying password?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related