# What's the algorithm behind MySQL's sha256_password hashing scheme?

MySQL's old mysql_native_password hashing scheme was the equivalent of this in PHP:

sha1(sha1('password', true));


That's a hex-encoded SHA-1 hash of a binary SHA-1 hash of the password, without any salting.

MySQL 8.0 introduced a two variants of a new hashing scheme based on SHA256 called caching_sha2_password and sha256_password, the former being the default (docs). Despite their name, neither appears to be vanilla SHA256.

(Yes, I know SHA256 is not a great choice for password hashing, but it's a lot better than SHA-1 and it wasn't up to me!)

Here's an example. I created a hash for the password password, and it created a mysql.users password hash like this:

$A$005$wU"H/k5|5;f!kP_&N4cvqu6bppuYjCvqhg2blU.NcJHkkhaVj.QNt7pipg4p3  I'm guessing that (separating by $ chars), A means it's SHA256 (the scheme may support other SHA2 variants in future), and that the 005 is a salt, but the rest of the string isn't a common format - it doesn't look like either regular hex output or base64, nor is it raw binary.

Can anyone tell me the actual algorithms for these new schemes, in PHP or similar code?

## Update

Thanks to @kelalaka for some important pointers, I had a crack at writing this in PHP:

$originalhash = 'wU"H/k5|5;f!kP_&N4cvqu6bppuYjCvqhg2blU.NcJHkkhaVj.QNt7pipg4p3';$binaryhash = base64_decode($originalhash);$salt_length = 20;
$hash_length = 43;$rawsalt = substr($binaryhash, 0,$salt_length);
$rawhash = substr($binaryhash, $salt_length);$password = 'password';
$iterations = 5;$iteration_multiplier = 1000;
$it =$iterations * $iteration_multiplier;$hash = $rawsalt .$password;
for ($i = 0;$i < $it;$i++) {
$hash = substr(hash('sha256',$hash, true), 0, $hash_length); }$hashoutput = base64_encode($rawsalt .$hash);
var_dump($originalhash,$hashoutput);


However, this doesn't produce matching output:

string(61) "wU"H/k5|5;f!kP_&N4cvqu6bppuYjCvqhg2blU.NcJHkkhaVj.QNt7pipg4p3"
string(72) "wUH/k55fkPN4cvqu6bppuYjCvqgx75cg5UeVzAVpx0OzU7KS2Klujh3rbHzrAena3/MBAA=="

• It's too long
• I suspect the encoding scheme is not base64; notice the salt diverges from the original even though it is unchanged.
• I'm not sure whether the starting string should be salt + password or password + salt.
• I'm not clear when the truncation of the binary hash should be performed - in the loop, or after?
• You need more than that, serialize. See serialized_string.append(salt.c_str(), salt.length()); . It uses https://github.com/mysql/mysql-server/blob/4869291f7ee258e136ef03f5a50135fe7329ffb9/mysys/crypt_genhash_impl.cc and there base64 is applied. Note that, you turned this question into a SO question, not Information securiy. – kelalaka Jan 31 at 10:31
• Seems I can't move it myself, but at least I voted to do so! – Synchro Jan 31 at 11:54
• I twas better to keep it before the coding updates, accepting it and asking a new question on SO based on the knowledge. Unfortunately, changing the question is not a good act once it got an answer... – kelalaka Jan 31 at 12:00
• Also keep an eye on github.com/hashcat/hashcat/issues/2305 - discussion of exact algorithm should eventually surface there. – Royce Williams Feb 6 at 15:52
• Yeah, exactly - it's a Frankenstein mashup of human-readable and binary that I believe is either a bug, or a misguided understanding of what \$[type]\$[salt]\$[hash] notation is for. I'm tempted to file a bug with the MySQL project about it. And looks like philsmd figured it out - see the GitHub. Basically a sha256crypt variant, default 5000 rounds, with a 20-byte raw binary salt (which is highly unusual and, IMO, an implementation error). – Royce Williams Feb 7 at 7:50 ## 1 Answer According to the MySQL Server Blog, it is: The advantage of mysql_native_password is that it support challenge-response mechanism which is very quick and does not require encrypted connection. However, mysql_native_password relies on SHA1 algorithm and NIST has suggested to stop using it. caching_sha2_password tries to combine the best of both worlds. XOR( SHA256(pwd),SHA256(SHA256SHA256(pwd)) , Nonce) \$A\$005\$wU"H/k5|5;f!kP_&N4cvqu6bppuYjCvqhg2blU.NcJHkkhaVj.QNt7pipg4p3

• Expected format

DELIMITER[digest_type]DELIMITER[iterations]DELIMITER[salt][digest]

Where the delimiter is \$

• digest_type:

A => SHA256

• iterations:

005 => 5*ITERATION_MULTIPLIER

• salt:

Random string. Length SALT_LENGTH

• digest:

SHA2 digest. Length STORED_SHA256_DIGEST_LENGTH