The security of the PASSWORD function in MySQL

Question

The PASSWORD function in MySQL can be translated to the code in php:

function mysql_password($string){
    $pass = strtoupper(
            sha1(
                    sha1($string, true)
            )
    );
    $pass = '*' . $pass;
    return $pass;
}

which returns a 41 character hash.

I am worried about this function because if I run:

SELECT PASSWORD('securepassword');

on any device it returns the same hash:

*214C2FAF32F109AE748170BFABDDFB9B05889E64

Surely that means that if my database is breached an infiltrator can easily create a rainbow table with a load of popular passwords and match the hashes - is this not something I should be worried about?

I was hoping that the PASSWORD function was unique to every device? How would I go about making it unique for every device?

See also [Is the MySQL password function vulnerable to this?](http://stackoverflow.com/questions/10935794/is-the-mysql-password-function-vulnerable-to-this) — Sjoerd, Feb 22 '17 at 13:27

score 2 · Accepted Answer · answered Feb 22 '17 at 13:14

Surely that means that if my database is breached an infiltrator can easily create a rainbow table with a load of popular passwords and match the hashes.

That is correct.

The hashes don't differ from user to user. This means that an attacker can calculate a hash once and check it against the hashes of all the users. A salt would make this impossible.

In fact, the hashes don't differ at all, so an attacker could precompute a lot of hashes even before gaining access to your system.

Another problem is that the hash function used (2x SHA1) is very fast, and a brute-force attack can try many passwords in a short time. With commodity hardware it would be possible to try 3,000,000,000 passwords per second.

This password hashing method does not meet modern security standards.

Thank you very much. Can you recommend a way of how I would go about making the hash unique for every device? — maxisme, Mar 01 '17 at 11:30
@Maximilian you can read about secure hashing of passwords [here](http://security.stackexchange.com/questions/211/how-to-securely-hash-passwords) — MiaoHatola, Mar 01 '17 at 13:27

niilzon · Answer 2 · 2017-02-22T12:41:05.737

1

Given the fact that no unique Salt is used, it is normal (and extremely necessary !!) that the same hashing function applied on the same string produces the same results every time.

Hash functions ARE deterministic by definition

By the way, preventing the usage of Rainbow Tables is done thanks to salting, since the hashed String is always different (given unique salts as should be)

=====

edit : To answer the actual question more thoroughly (the "unique for every device" part) : if the hash was unique for every device, doing a database migration on another machine would be impossible since all the hashes would not match anymore - in the case of fields used for passwords, users would not be able to log in anymore :P You can certainly imagine other similar problems (distributed databases, ..)

edited Feb 22 '17 at 12:41

answered Feb 22 '17 at 12:33

niilzon

1,587
2
10
17

Thank you @niilzon. I understand that this is normal but is this not a massive security flaw in my system? – maxisme Feb 22 '17 at 12:35
If you want to improve your security on that regard, you should salt the passwords. It is not a 'massive' flaw but salting is a best practice. It would significantly slow down the cracking effort in case of a database dump – niilzon Feb 22 '17 at 12:37
Thank you very much. Can you recommend a way of doing this? – maxisme Mar 01 '17 at 11:30

score 0 · Answer 3 · answered Mar 01 '17 at 13:22

I was hoping that the PASSWORD function was unique to every device

There aren't enough secure algorithms to go round - conventionally this is dealt with by using a unique salt per account, but if you wanted weaker security I suppose you could have a device specific salt.

MySQL supports client certificates for authentication out of the box along with plugin authenticators and there are several available as standard (including support for Unix PAM).

(MariaDB also has the same APIs)

The security of the PASSWORD function in MySQL

3 Answers3

Linked