caching_sha2_password iterations handling error

[asjdf]

Bug Report

1. Minimal reproduce step (Required)

In MySQL the default number of rounds is 5000. The MySQL server being tested has been configured with 10000 rounds using the caching_sha2_password_digest_rounds server system variable.

A hash with 10000 iterations gets retrieved that starts like this:

$A$00A$... instead of $A$010$...

2. What did you expect to see?

The number of iterations should be decoded in hexadecimal not decimal

related info

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_caching_sha2_password_digest_rounds

hashcat/hashcat#3049

[asjdf]

related code :

tidb/parser/auth/caching_sha2.go

Line 172 in ff78940

rounds := fmt.Sprintf("%03d", iterations/ITERATION_MULTIPLIER)

tidb/parser/auth/caching_sha2.go

Line 204 in ff78940

iterations, err := strconv.Atoi(string(pwhashParts[2]))

[dveeden]

This affects accounts that have an authentication_string in the mysql.user table that has a different value than \x30\x30\x35 (005). The 5 here means 5x1000=5000 iterations.

mysql> SELECT SUBSTRING(authentication_string FROM 4 FOR 3) FROM mysql.user WHERE user='testuser';
+-----------------------------------------------+
| SUBSTRING(authentication_string FROM 4 FOR 3) |
+-----------------------------------------------+
| 005                                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(SUBSTRING(authentication_string FROM 4 FOR 3)) FROM mysql.user WHERE plugin='caching_sha2_password';
+----------------------------------------------------+
| HEX(SUBSTRING(authentication_string FROM 4 FOR 3)) |
+----------------------------------------------------+
| 303035                                             |
+----------------------------------------------------+
1 row in set (0.01 sec)

5000 iterations is hardcoded in TiDB in NewHashPassword for new caching_sha2_password password hashes.

In MySQL this can be configured with caching_sha2_password_digest_rounds, which also defaults to 5000.

mysql> SET PERSIST_ONLY caching_sha2_password_digest_rounds=10000;
Query OK, 0 rows affected (0.02 sec)

mysql> RESTART;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'foobar'@'%' IDENTIFIED WITH caching_sha2_password BY 'abcdefghij';
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***

Query OK, 0 rows affected (0.08 sec)

mysql> SELECT HEX(SUBSTRING(authentication_string FROM 4 FOR 3)) FROM mysql.user WHERE plugin='caching_sha2_password';
+----------------------------------------------------+
| HEX(SUBSTRING(authentication_string FROM 4 FOR 3)) |
+----------------------------------------------------+
| 303035                                             |
| 303041                                             | ←-----------
| 303035                                             |
| 303035                                             |
| 303035                                             |
| 303035                                             |
| 303035                                             |
| 303035                                             |
+----------------------------------------------------+
8 rows in set (0.00 sec)

mysql> SELECT SUBSTRING(authentication_string FROM 4 FOR 3) FROM mysql.user WHERE plugin='caching_sha2_password';
+-----------------------------------------------+
| SUBSTRING(authentication_string FROM 4 FOR 3) |
+-----------------------------------------------+
| 005                                           |
| 00A                                           | ←-----------
| 005                                           |
| 005                                           |
| 005                                           |
| 005                                           |
| 005                                           |
| 005                                           |
+-----------------------------------------------+
8 rows in set (0.01 sec)

So for 10*1000 = 10_000 iterations the correct value is \x30\x30\x41/00A.

So this impacts only accounts that were migrated from MySQL to TiDB and were created on MySQL with non-default settings for caching_sha2_password.

Example:

package main

import "fmt"

func main() {
	for _, v := range []int{5, 10} {
		fmt.Printf("%2d - dec: %#v\n", v, []byte(fmt.Sprintf("%03d", v)))
		fmt.Printf("%2d - hex: %#v\n", v, []byte(fmt.Sprintf("%03X", v)))
	}
}

output:

 5 - dec: []byte{0x30, 0x30, 0x35}
 5 - hex: []byte{0x30, 0x30, 0x35}
10 - dec: []byte{0x30, 0x31, 0x30}
10 - hex: []byte{0x30, 0x30, 0x41}