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()?

Answer 1

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.

Answer 2

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.