2
$\begingroup$

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?
$\endgroup$
  • $\begingroup$ 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. $\endgroup$ – kelalaka Jan 31 at 10:31
  • $\begingroup$ Seems I can't move it myself, but at least I voted to do so! $\endgroup$ – Synchro Jan 31 at 11:54
  • $\begingroup$ 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... $\endgroup$ – kelalaka Jan 31 at 12:00
  • 1
    $\begingroup$ Also keep an eye on github.com/hashcat/hashcat/issues/2305 - discussion of exact algorithm should eventually surface there. $\endgroup$ – Royce Williams Feb 6 at 15:52
  • 2
    $\begingroup$ 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). $\endgroup$ – Royce Williams Feb 7 at 7:50
2
$\begingroup$

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)

enter image description here

\$A\$005\$wU"H/k5|5;f!kP_&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;

| improve this answer | |
$\endgroup$
  • $\begingroup$ That's useful, but not quite there - this tells me how the password is used in an auth conversation. When it's generated on the server, there is no nonce involved. The code sample suggests it's 3 rounds of regular sha256, but that's nothing like what I get out of it - I added an example to demonstrate. $\endgroup$ – Synchro Jan 30 at 17:39
  • $\begingroup$ @Synchro I've looked into source code to figure out what you actually need. $\endgroup$ – kelalaka Jan 30 at 19:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.