Caching_sha2_password` scramble has the nonce in the wrong position — fast auth never succeeds, ProxySQL rejects the connection

[bogmir]

Summary

caching_sha2_password reuses the mysql_native_password scramble helper, which prepends the nonce to the double-hashed password. The caching_sha2 exchange appends it. As a result the scramble MyXQL sends never matches what the server expects.

Against MySQL this is invisible — the server answers a mismatching scramble with perform_full_authentication instead of an error, and the connection then succeeds via the RSA public-key exchange. Against a server that verifies the scramble eagerly, every connection fails.

Affected code

lib/myxql/protocol/auth.ex:

def sha256_password(password, auth_plugin_data) do
  sha_hash(:sha256, password, auth_plugin_data)
end

defp sha_hash(type, password, auth_plugin_data) do
  password_sha = :crypto.hash(type, password)

  bxor_binary(
    password_sha,
    :crypto.hash(type, auth_plugin_data <> :crypto.hash(type, password_sha))
    #              ^^^^^^^^^^^^^^^^ nonce first
  )
end

sha_hash/3 is shared with mysql_native_password/2, where nonce-first is correct (SHA1). For caching_sha2 the documented exchange is:

XOR(SHA256(password), SHA256(SHA256(SHA256(password)) <> nonce))

i.e. the nonce is appended, not prepended.

A second bug this uncovers

Once the ordering is fixed, the server starts replying 0x01 0x03, which falls through to:

def decode_auth_response(<<0x01, rest::binary>>), do: auth_more_data(data: rest)

so <<3>> reaches :public_key.pem_decode/1 as if it were an RSA key and the connection crashes. The fast auth success reply also has an OK packet trailing it, which recv_packet/3 discards by design ("even if next packet follows, ignore it") — leaving it in the socket desyncs the next exchange. Both MySQL 8.4 and ProxySQL do send that trailing OK.

Why CI doesn't catch it

MySQL never errors on a wrong scramble, so tests pass. The existing coverage is caching_sha2_password (public key exchange) — the full-auth path — and there is no test asserting fast auth, which is exactly the path that never runs. #212 added 8.4 to CI but only changed ci.yml, docker-compose.yml and test_helper.exs; nothing in lib/. Its description states fast auth was verified working, but per the measurements above the server answers perform_full_authentication on every connection, so the connections it saw succeeding were going through the RSA exchange.

Fix

PR: #213

• corrects the caching_sha2 scramble ordering (split out from the native helper, which keeps nonce-first)
• decodes 0x01 0x03 as :fast_auth_success and consumes the trailing OK
• adds known-answer vectors for both plugins plus a regression test that caching_sha2 does not match the prepended-nonce ordering
• adds a caching_sha2_password (cached, fast auth) test that connects twice so the second connection exercises the previously-unreachable path

Full suite on MySQL 8.4.10: 222 tests, 0 failures (excluding two UNIX-socket tests that fail identically before and after, because my server runs in Docker).

Environment

• MyXQL 0.9.0 and master (fd9ffde)
• MySQL 8.4.10 (Docker mysql:8.4), and ProxySQL 3306 fronting Percona XtraDB Cluster 8.4.8
• Elixir 1.19.5 / OTP 26

[bogmir]

Hi @wojtekmach, sorry for the ping, we've had this issue with the library when using ProxySQL. We are going to use a fork of the repo for now so no urgency, but it would be good to have this fixed in the main library as well. We used Claude Code to fix the caching_sha2_password nonce prepended instead of appended in PR #213: MySQL masks it by falling back to full auth, but ProxySQL rejects every connection. I'm happy to rework it however it suits you and thanks for maintaining MyXQL, btw.