OLD_PASSWORD Function in 5.7.5+

Question

I'm working with a legacy codebase here that currently uses OLD_PASSWORD() as a simple hashing function. This codebase now needs to connect to a database running the newest revision of MySQL 5.7.

The equivalent of PASSWORD() seems to be: UPPER(SHA1(UNHEX(SHA1(password)))).

Is there a similar equivalent to OLD_PASSWORD()?

Velez

Using OLD_PASSWORD() for user authentication is a terrible idea, so if you can replace this with something actually secure you'd be in much better shape. Is OLD_PASSWORD() just unsalted MD5? You could do tests to find out. If it is, it's utterly useless in terms of protection. – Debatable

I'm very aware it's not secure. As I said this is a legacy codebase. It's currently out of my control to change. OLD_PASSWORD() appears to return a halved MD5 string (16 chars), but isn't actually. – Velez

I found A python implementation of the old MySQL PASSWORD() function. I can't speak a word of Python but the algorithm looks ridiculous. – Herman

Forcing people to update their passwords once every two decades is not necessarily a bad idea. – Debatable

Did they remove OLD_PASSWORD() in 5.7 or what is the reason you have to emulate that? Function still available in MariaDB 10, you should go with MariaDB or Percona products, faster and more reliable than the original MySQL. – Minnesota

@DanFromGermany Yep, it's removed as of version 5.7.5. If Google Cloud SQL offered MariaDB we would choose it for sure. – Velez

Answer 1

It appears there is no equivalent to OLD_PASSWORD() using MySQL functions except if the server allows globals to be set. By executing the query SET @@global.old_passwords = 1;, as a user with super permissions, the PASSWORD() function then hashes passwords using the OLD_PASSWORD() algorithm.

If, like in our case above, you do not have a super user (Google CloudSQL does not support them), then a replacement algorithm is needed. Below are replacements for different languages:

C | Perl | PHP | Python | SQL

Disclaimer: MySQL's old password functions are a joke in modern day security, and should not be used if at all possible; these algorithms are a mess.

Velez

After taking over a legacy system, that was recently upgraded without fixing the passwords, and the actual system users were hashed using PASSWORD() function, I found these scripts quite helpful. I have now managed to integrate them in my upgrade method and am now updating to the new SHA1 hashed after successful authentication with PASSWORD(). – Fingertip

Answer 2

I tried to write a replace-user-defined function for OLD_PASSWORD directly in SQL and it seems it's working. The code is a translation of the PHP version found in this post.

DROP FUNCTION IF EXISTS OLD_PASSWORD;
DELIMITER $$
CREATE FUNCTION OLD_PASSWORD (input BLOB) 
RETURNS CHAR(16)
DETERMINISTIC
BEGIN 
  DECLARE nr BIGINT;
  DECLARE nr2 BIGINT;
  DECLARE ad BIGINT;
  DECLARE inlen INT;
  DECLARE i INT;
  DECLARE b CHAR;
  DECLARE tmp INT;
  DECLARE output CHAR(16);
  
  SET nr = 1345345333;
  SET nr2 = 0x12345671;
  SET ad = 7;
  SET inlen = LENGTH(input);
  SET i = 1;

  IF (input = '' OR input IS NULL) THEN
    RETURN input;
  END IF;

  
  WHILE i <= inlen DO
    SET b = MID(input, i, 1);
    IF b != ' ' AND b != '\t' THEN
      SET tmp = ORD(b);
      SET nr = nr ^ ((((nr & 63) + ad) * tmp) + ((nr << 8) & 0xFFFFFFFF));
      SET nr2 = nr2 + (((nr2 << 8) & 0xFFFFFFFF) ^ nr);
      SET ad = ad + tmp;
    END IF;
    SET i = i + 1;
  END WHILE;
    
  SET nr  = nr  & ((1 << 31) - 1);
  SET nr2 = nr2 & ((1 << 31) - 1);      
  SET output = LOWER(CONCAT(LPAD(HEX(nr),8,'0'), LPAD(HEX(nr2),8,'0'))); 
        
  RETURN output;
END$$
DELIMITER ;

Hope this could help. But pay attention: it's not safe to keep passwords in this format.

Culverin

Answer 3

max's answer is great, except that it sometimes fails.

For instance, when the input string is 'my', the return value is a string of length 15 instead of 16. This is because the return value's initial character is 0. Just try

select old_password('my'); 

to confirm this.

The remedy is to apply the lpad() function to hex(nr) and hex(nr2) -- the following line

SET output = LOWER(CONCAT(HEX(nr), HEX(nr2)));

should be changed to

SET output = LOWER(CONCAT(LPAD(HEX(nr),8,'0'), LPAD(HEX(nr2),8,'0'))); 

Knighten

This is a great find. I have incorporated the change from this answer to the related answer by max so the mistake is no longer there (and also few more mistakes were fixed since it was written). – Rodrigo

thanks mindconnect.cc and @JosefAdamcik, good job! – Culverin