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()?
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.
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:
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.
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().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')));
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. IsOLD_PASSWORD()just unsalted MD5? You could do tests to find out. If it is, it's utterly useless in terms of protection.OLD_PASSWORD()appears to return a halved MD5 string (16 chars), but isn't actually.