14

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?

mega6382
  • 9,211
  • 17
  • 48
  • 69
sectus
  • 15,605
  • 5
  • 55
  • 97
  • You'd handle this in the application logic, not the database. – ceejayoz Nov 30 '13 at 02:57
  • @ceejayoz, i know it. But... – sectus Nov 30 '13 at 03:00
  • 22
    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 Nov 30 '13 at 03:14
  • @ircmaxell, it is good point, i'll redo my application. But i'll keep question just as theoretical. Also PostgreSQL supports crypt with blowfish. – sectus Nov 30 '13 at 03:28
  • 2
    +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 Nov 30 '13 at 07:06
  • 3
    MySQL know about this problem. [Passwords and Logging 5.5](http://dev.mysql.com/doc/refman/5.5/en/password-logging.html) and [Passwords and Logging 5.6](http://dev.mysql.com/doc/refman/5.6/en/password-logging.html) – sectus Dec 02 '13 at 05:17
  • 2
    Also [Is it possible to hide the password in MySQL GeneralSlow Query Logs](http://stackoverflow.com/q/11983381/1503018) – sectus Dec 02 '13 at 05:24
  • 1
    @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 Feb 17 '20 at 08:10
  • @Mehdi you should *never* under any circumstances store it in plain text. If you must store it and retrieve it, encrypt it with keys stored outside of the database (in a config file for example). Note that encryption is not hashing (this answer is specifically about hashing). But note that the cases where you should be able to recover the password (encrypt instead of hash) should be **very** rare, and you should do exceptional diligence on alternatives (such as Oauth2, etc) before doing it. – ircmaxell Feb 22 '20 at 17:57
  • These comments are silly. The asker isn't asking if they should do it, but if they can do it. Just because you can't imagine a valid use case doesn't mean there isn't one. For example, I have a SQL set of dummy data used to test an internal system. It's much better for me to `ENCRYPT()` a plain value so another person testing can modify it or read it simply than to dump in a hash that is difficult to reverse. – Andrew White Oct 11 '21 at 23:55

1 Answers1

27

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.

  • But I can generate hash with crypt in PHP. I suppose that my system support bcrypt. – sectus Nov 30 '13 at 12:30
  • 5
    PHP 5.3 and later use their own implementation of `crypt()`, instead of the one from the operating system. –  Nov 30 '13 at 16:42