This is all ancient history, but sometimes you have to deal with ancient systems.
Way back in MySQL 4.x, MySQL had a PASSWORD() function that was used to set MySQL-managed user credentials. You gave it a string and it returned a hex string. It was never intended to be used by clients to hash passwords for their own use (indeed the 5.7 docs tell you not to) but nothing prevented it.
Later, MySQL changed the hashing algorithm and had a way to toggle if the PASSWORD() function used the old way or the new way via old_passwords. Somewhere along the way they also made an OLD_PASSWORD() function that only used the old algorithm.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.62-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select PASSWORD('123456');
+-------------------------------------------+
| PASSWORD('123456') |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select OLD_PASSWORD('123456');
+------------------------+
| OLD_PASSWORD('123456') |
+------------------------+
| 565491d704013245 |
+------------------------+
1 row in set (0.00 sec)
Then MySQL 8.0 came along and all of those functions were removed.
But users had done Bad Things and used those functions to generate hashes and stored those in their own databases and needed ways to replicate the function. And the internet provided solutions in various languages, including python, PHP, and a replacement SQL function.
The devil is in the details though, because based on the language, it matters if the password is 7-bit ASCII or Unicode.
The PASSWORD() and OLD_PASSWORD() functions both treat their input as a string of bytes, not a string of Unicode characters. If the input is 7-bit ASCII those are the same and it doesn’t matter. If it’s Unicode, however, multibyte characters are hashed as individual bytes rather than as characters.
With a little python3 code and a handy mysql-5.7 Dockerfile we can demonstrate this:
# old_password.py
import sys
def mysql_old_password_chars(password):
"""Treat the password as a string of Unicode characters -- WRONG"""
password = password.replace(" ", "").replace("\t", "")
# build the old password in nr and nr2
nr = 1345345333
add = 7
nr2 = 0x12345671
for c in (ord(x) for x in password):
nr ^= (((nr & 63)+add)*c) + (nr << 8) & 0xFFFFFFFF
nr2 = (nr2 + ((nr2 << 8) ^ nr)) & 0xFFFFFFFF
add = (add + c) & 0xFFFFFFFF
return "%08x%08x" % (nr & 0x7FFFFFFF, nr2 & 0x7FFFFFFF)
def mysql_old_password_bytes(password):
"""Treat the password as a string of bytes -- CORRECT"""
password = password.replace(" ", "").replace("\t", "")
# build the old password in nr and nr2
nr = 1345345333
add = 7
nr2 = 0x12345671
for c in password.encode('utf8'):
nr ^= (((nr & 63)+add)*c) + (nr << 8) & 0xFFFFFFFF
nr2 = (nr2 + ((nr2 << 8) ^ nr)) & 0xFFFFFFFF
add = (add + c) & 0xFFFFFFFF
return "%08x%08x" % (nr & 0x7FFFFFFF, nr2 & 0x7FFFFFFF)
if __name__ == '__main__':
password = sys.argv[1]
print("chars: " + mysql_old_password_chars(password))
print("bytes: " + mysql_old_password_bytes(password))
# Dockerfile FROM ubuntu:14.04 RUN apt-get update && apt-get install -y mysql-server # start mysqld in the foreground CMD mysqld
Let’s get our MySQL 5.7 server going:
docker build --tag mysql-5.7 . docker run --rm -d --name=mysql57 mysql-5.7
Now run some tests. Start with some simple 7-bit ASCII strings:
$ docker exec mysql57 mysql --default-character-set=utf8 --skip-column-names -e 'select old_password("123456");'
565491d704013245
$ python3 old_password.py '123456'
chars: 565491d704013245
bytes: 565491d704013245
$ docker exec mysql57 mysql --default-character-set=utf8 --skip-column-names -e 'select old_password("Pa$$ W0rD");'
69d9eae853c7ddf5
$ python3 old_password.py 'Pa$$ W0rD'
chars: 69d9eae853c7ddf5
bytes: 69d9eae853c7ddf5
So far so good. Now throw in something above 7-bit ASCII, like a simple ‘ô’
$ docker exec mysql57 mysql --default-character-set=utf8 --skip-column-names -e 'select old_password("Allô");'
4ae9b3f6595c3f70
$ python3 old_password.py 'Allô'
chars: 3ff55a3d63bf3485
bytes: 4ae9b3f6595c3f70
The well-known python solution that uses characters fails here. To be fair, it probably worked in python2 which used bytestrings not Unicode strings.
The “nice” thing about PHP here though is that because it doesn’t understand Unicode natively at all, it just works with a simple port of the python version.
<?php
function mysql_old_password($password)
{
# build the old password in nr and nr2
$nr = 1345345333;
$add = 7;
$nr2 = 0x12345671;
$password = str_replace([' ', '\n'], '', $password);
for ($index = 0; $index < strlen($password); $index++) {
$c = ord($password[$index]);
$nr ^= ((($nr & 63) + $add) * $c) + ($nr << 8) & 0xFFFFFFFF;
$nr2 = ($nr2 + (($nr2 << 8) ^ $nr)) & 0xFFFFFFFF;
$add = ($add + $c) & 0xFFFFFFFF;
}
return sprintf("%08x%08x", $nr & 0x7FFFFFFF, $nr2 & 0x7FFFFFFF);
}
echo mysql_old_password($argv[1]) . "\n";
$ php --version PHP 8.1.32 (cli) (built: May 21 2025 23:22:09) (NTS) Copyright (c) The PHP Group Zend Engine v4.1.32, Copyright (c) Zend Technologies $ php old_password.php '123456' 565491d704013245 $ php old_password.php 'Pa$$ W0rD' 69d9eae853c7ddf5 $ php old_password.php 'Allô' 4ae9b3f6595c3f70
The well-known SQL replacement for a user-defined OLD_PASSWORD() function also breaks with non-7bit-ASCII because it uses LENGTH() to calculate the string length with MID() to get the character which is encoding aware. Any attempt to use the function with a multibyte string will fail.
All of this is dealing with a decade-old technology and hopefully you never have to encounter it. But if you come across this blog post you are probably working with an ancient system and I hope this helps you.
Digital Ramblings 