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_&amp;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_&amp;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 &lt; $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_&amp;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?
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_&amp;N4cvqu6bppuYjCvqhg2blU.NcJHkkhaVj.QNt7pipg4p3


From the source code sha2_password.cc



  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


From file i_sha2_password.h

  #define CRYPT_SALT_LENGTH 20
  
  const unsigned int STORED_SHA256_DIGEST_LENGTH = 43;