packet 254 not implemented -- still struggling with

[marcstein]

I know that this is not a new issue, but I'm trying to connect to a MySQL 8 server, which has been set to use legacy (v5.7) authentication. This is working in other languages that have an issue with the new authentication process (e.g. Nim), but I can't get it to work in Crystal. I've tried it on both OSX and Ubuntu. No difference.

I've tried the published solutions like CREATE/ALTER USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword'; and the user is definitely using the native_password auth.

Short of switching languages or databases, can anyone suggest something to try?

Thanks in advance for any suggestions!

[Exception.txt] (https://github.com/crystal-lang/crystal-mysql/files/12506665/Exception.txt)

[willy610]

Into MySQL 9 now. Everyone must encourage more security. Workaround as for ver 8 is not possible anymore as I understand. I will try too contribute by implementing the 'sha2_password' There are work in progress as I understand.

Please give me links etc to what is achieved so far. (Especially Crystal source..) (Someone locked into LUA. Perhaps also Rust will give some feedback. And Ruby and Js)

[ysbaddaden]

The rust crate implements the protocol (not a wrapper) and has support for the caching_sha2_password auth. It would likely be helpful to understand how it works, in addition to the mysql-client C library maybe.

[bcardiff]

The protocol documentation can be found at https://dev.mysql.com/doc/dev/mysql-server/latest/page_caching_sha2_authentication_exchanges.html

[willy610]

Thanks.
Find sources and also notes in this threads of course. My approach is to implement caching_sha2_password only. >= mysql 9 Will remove older versions behavior in the source. In order to avoid attacks from smarter hackers than me. Approach, but than we got 2 libraries?

Is golang worth looking at?

[bcardiff]

I don't think that dropping the current authentication method is a good call. First it would be disruptive when upgrading an application, and second, I am not sure there is a risk in the client keep supporting old authentication scheme. Is the server that should be configured to not allow it. Am I wrong?

If needed we could make it harder to use old authentication, but I would not suggest dropping the support for it.

It seems that a couple of years ago I started to do some refactors to support sha2_password in https://github.com/crystal-lang/crystal-mysql/compare/master...bcardiff:crystal-mysql:sha2_password but I didn't finish it. I don't recall any specifics so feel free to ignore it. But the key seems to be extracting the list of authentication plugins available.

Please share if you are blocked, contributions are appreciated! 🙇

[willy610]

Thank you for the link. Got your points on dropping old behavior. I will do more rethinking on how make maintainable source. Perhaps by enabling cutting methods, cutting case, avoiding complex conditions mixing compile time and run time too much etc Than we have verifications on older version of mysql server. Work to do!

[willy610]

Just for the record: One of the variants of 'caching_sha2_password' is now working. That is the 'PasswordFastAuthSuccess' . ( And the old one 'mysql_native_password' works)

Same image works on

• Mac Mini M4 Pro and mysql Ver 9.0.1 for macos15.1 on arm64 (Homebrew) no implement of 'mysql_native_password' and on
• Mac Mini M2 Pro and mysql Ver 8.0.3 for macos14.2 on on arm64 (Homebrew) implements 'mysql_native_password' only

Still a lot of work to do!! By the way - allow me to say crystal and golang are a very good environment, maridb client is well enginered, Rust will not even compile the source. Don't mention the mysql original client source (C/C++). But I love C otherwise!

I don't now if it's matter just now but I'm working a git crystal/mysql source copy and not a cloned one.

[bcardiff]

👏

I'm not sure how you are going to send a PR if you are not working from a fork. But let's not make that a stopper.

Feel free to send your progress somehow in case you rather have feedback at this stage.

Thanks

[willy610]

The capability 'perform_full_authentication' defined by mysql server seems to be hard to implement. And perhaps also unnecessary?

Looking into the mysqlserver 'sql/aut/sha_2password.cc' I found /* We either failed to authenticate or did not find entry in the cache. In either case, move to full authentication and ask the password */ if (vio->write_packet(vio, (uchar *)&perform_full_authentication, 1)) return CR_AUTH_HANDSHAKE;

This is the only placed where that capability is used. So... Use case:

1. The client sends wrong user and/or password. How to provide new values in a 'perform_full_authentication' round trip? Suggestion is: raise 'denied'

2. The user and/or password has been valid sofar but is actually changed by someone. How to provide new values in a 'perform_full_authentication' round trip? Suggestion is: raise 'denied'

So my suggestion at the moment is to not implement 'perform_full_authentication'. (instead raise 'denied') It will never be used!! And it will imply a mighty set of new crystal source.

I can't see any difference - as a Use Case - whether the DB.open holds fixed or parametric values of user/password. Either it works or one get a 'perform_full_authentication'. Don't try to perform a meaningless? 'perform_full_authentication' but pick up proper values into source or parameters in the DB.open

What to do?

Besides that the auth 'caching_sha2_password' and 'mysql_native_password' works as mentioned above so now I'm working on commands. There are some issues with messages sequencing. And styling, documentation, test/verifications and more

[bcardiff]

Adding support to just caching_sha2_password seems fine.

If you are not familiar with Docker and GitHub actions tweaking those to add CI for the additions can happen later, by someone else. No worries.

If you have instructions on how you setup a mysql using the newer authentication and that works for you, I can take care of the CI setup probably. But feel free to keep working on those for sure.

[willy610]

Thank you for your patience.

I am 'struggling' to set up a proper folder with my changes so that that folder can be easily referenced as 'dependendcy: - mysql: - path: 'myworkingmysqllib' in all my old crystal mysql project shard.yml files. Any hints/links on that topic?

Later on we can decide how to continue. At least I will match a cloned version but it might end up in a .zip file ? or a PR or some kind of CI. OK?

[bcardiff]

In your apps shard.yml or shard.override.yml you should have

dependencies:
  mysql:
    path: /path/to/your/crystal-mysql

After that you need to do shards install, that should leave a symlink in your apps ./lib/mysql pointing to your working directory. You can also this step manually if you prefer.

With that you should be able to compile your apps with your crystal-mysql working copy.

Feel free to attach a .zip or send a link to it here in this issue if doing git/github is a blocker.

[willy610]

In order to earn time I will attach a zip file here. I have built and run it on (as above)

• Mac Mini M4 Pro and mysql Ver 9.0.1 for macos15.1 on arm64 (Homebrew) no implement of 'mysql_native_password' and on
• Mac Mini M2 Pro and mysql Ver 8.0.3 for macos14.2 on on arm64 (Homebrew) implements 'mysql_native_password' only

1. I have done nothing on README.md, version.cr and no spec
2. There is an issue with crystal spec 'should connect with credentials'. Got syntax error from mysqlserver
3. Error messages from crystal spec are truncated
4. There might be more issues around any user/password commands
5. I introduced 'draining' of inmessages in order to really consume the TCP bytestream. Necessary?
6. I have an active Github Desktop showing changes on my modified clone but I'm really unsure how to continue. Fork to my account? I kind of looking for contribution credits, in the long term, to show my patience family!

crystal-mysql.zip

[willy610]

Hera is a good link too on ALTER USER ...

https://dev.mysql.com/blog-archive/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

[willy610]

Zipped source above build and executed on

• mysql Ver 8.0.28 for macos10.15 on x86_64 (Homebrew)
• on user' test' the capability classic 'mysql_native_password' was invoked
• Crystal 1.8.2 (2023-05-09), LLVM: 15.0.7

[bcardiff]

FYI I tested the code locally and it works. I need to do some cleanups and probably specs/CI because currently a test user is created with old auth method.

Still on my radar. Thanks for the patch!

[bcardiff]

@willy610 in your tests, what kind of connection are you using with MySQL? Unix Socket, plain unsecured socket, or TLS?

[willy610]

In my test I have only used 'localhost' and I think that implies Unix Socket

As I understand a mysql server just serve one connection type after restart.

So I have used 'localhost' in the DB.Open in all my tests.

I have tried using explicit ip adress - not localhost - to other machines but my mysql server is not listening. And that's ok I think

I think I can elaborate and start one of my mysql server to listen to port on ip instead of localhost. Give som days just.

In that case it will be 'plain unsecured socket'

Neither TLS nor SSL is investigated

So

-Unix Socket YES (On macOS)

-plain unsecured socket NO

-TLS NO

2024-12-17 21:07 skrev Brian J. Cardiff:

@willy610 [1] in your tests, what kind of connection are you using with MySQL? Unix Socket, plain unsecured socket, or TLS?

-- Reply to this email directly, view it on GitHub [2], or unsubscribe [3]. You are receiving this because you were mentioned.Message ID: @.***>

Links:

[1] https://github.com/willy610 [2] https://github.com/crystal-lang/crystal-mysql/issues/111#issuecomment-2549503373 [3] https://github.com/notifications/unsubscribe-auth/ABPLCPYBUFN2SMYCHQYTTKL2GB76PAVCNFSM6AAAAABSGW4NAOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKNBZGUYDGMZXGM

[willy610]

@bcardiff Just setting up 'plain unsecured socket' - that is no local host - between a mysql Ver 8.0.3 and a Ver 9.0.1 Of course there were error in connection phase. I will immediately solve this. Sorry for my stubborn stick to the 'issues' track.

[bcardiff]

I was checking your contributions and they do work in some scenarios but not all. That's fine, it's still a big step forward.

With your contributions we are able to connect to mysql 8.0 using caching_sha2_password. Most of the specs pass. But some specs creates users and perform a FLUSH PRIVILEGES. This invalidates the password cache as explained in https://dev.mysql.com/blog-archive/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

The code path that would authenticate in that situation is not implemented in your previous contributions. So only cached passwords are supported. That could be fine as a step forward!

crystal-mysql only supports plain unsecured socket connection. A TCP socket is always used. Unfortunately that is the kind of connection that seems discouraged by MySQL going forward.

Time for some maintenance in this shard to unblock things I guess 😅 .

[willy610]

I didn't see this coming with none localhost...

When launching a mysql server without 'localhost' two connection types are accepted

• Plain access on default port imply the client must implement trusted authentication according to 'cachingSha2PasswordPerformFullAuthentication' capability. Misleading name
• TLS connection. I think this require some refactoring in using OpenSSL::SSL::Socket::Client ?

Two execution paths but probably partly using shared logic/architecture/knowledge.

Will elaborate the paths and try to implement

[willy610]

Thanks for feed back.

Just for the record

The very SQL FLUSH PRIVILEGES will reset also the user.plugin to 'caching_sha2_password' That caused me problem. Solved temporarily

1. update user.plugin to 'my_sql_native_password'

2. In client excute any sql statement for this user (BESIDE FLUSH PRIVILEGES) Things will be cashed!!

3. update user.plugin to 'caching_sha2_password'

4. Continue. Will work.

My solution can't connect with a brand new user with plugin 'caching_sha2_password' This is a true limitation in my implementation. Will work on that

2024-12-20 22:04 skrev Brian J. Cardiff:

I was checking your contributions and they do work in some scenarios but not all. That's fine, it's still a big step forward.

With your contributions we are able to connect to mysql 8.0 using caching_sha2_password. Most of the specs pass. But some specs creates users and perform a FLUSH PRIVILEGES. This invalidates the password cache as explained in https://dev.mysql.com/blog-archive/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

The code path that would authenticate in that situation is not implemented in your previous contributions. So only cached passwords are supported. That could be fine as a step forward!

crystal-mysql only supports plain unsecured socket connection. A TCP socket is always used. Unfortunately that is the kind of connection that seems discouraged by MySQL going forward.

Time for some maintenance in this shard to unblock things I guess 😅 .

-- Reply to this email directly, view it on GitHub [1], or unsubscribe [2]. You are receiving this because you were mentioned.Message ID: @.***>

Links:

[1] https://github.com/crystal-lang/crystal-mysql/issues/111#issuecomment-2557714202 [2] https://github.com/notifications/unsubscribe-auth/ABPLCPZJJJBC46HKGDWBEI32GSA6FAVCNFSM6AAAAABSGW4NAOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKNJXG4YTIMRQGI --=_947adf39cba68eef0e87177133a2a7ee Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=UTF-8

Thanks for feed back.

Just for the record

The very SQL FLUSH PRIVILEGES will = reset also the user.plugin to 'caching_sha2_p= assword'
That caused me problem. Solved temporarily

1. update user.plugin to 'my_sql_native_password'

2. In client excute any sql statement for this user (BESIDE FLUSH PRIVILEGES)
Things will be cashed!!

3. update user.plugin to 'caching_sha= 2_password'

4. Continue. Will work.

My solution can't connect with a brand new user with plugin 'caching_sha2_password'
This is a true limita= tion in my implementation. Will work on that

2024-12-20 22:04 skrev Brian J. Cardiff:

I was checking your contributions and they do work in some = scenarios but not all. That's fine, it's still a big step forward.

With your contributions we are able to connect to mysql 8.0= using caching_sha2_password. Most of = the specs pass. But some specs creates users and perform a FLUSH PRIVILEGES. This invalidates the password cache = as explained in https://dev.mysql.com/blog-archive/mysql-8-0-4-= new-default-authentication-plugin-caching_sha2_password/

The code path that would authenticate in that situation is = not implemented in your previous contributions. So only cached passwords ar= e supported. That could be fine as a step forward!

crystal-mysql only supports plain unsecured socket connecti= on. A TCP socket is always used. Unfortunately that is the kind of connecti= on that seems discouraged by MySQL going forward.

Time for some maintenance in this shard to unblock things I= guess =F0=9F=98=85 .

—
Reply to this email directly, view it on GitHub, or unsubscribe.
You = are receiving this because you were mentioned.

--=_947adf39cba68eef0e87177133a2a7ee--

[willy610]

Will look into secure connections to remote mysql servers. And unsecure. I will focus on the one preferred by mysql. https://dev.mysql.com/doc/refman/8.4/en/encrypted-connection-protocols-ciphers.html

Secure connections have impact on resolving caching_sha2_password with full_authentication

So my work order will be:

1. Secure connections and unsecure
2. Resolving caching_sha2_password with full_authentication

[willy610]

I need some hints on how to continue with cypher in the caching_sha2_password authentication.

Of course I want to use a crystal native implementation if it's there. Like in cipher = OpenSSL::Cipher.new("some_ident")

Looking into mariadb client (sha256_pw.c) I find they are using EVP_PKEY_encrypt from openssl.

Then in https://docs.openssl.org/3.3/man3/EVP_PKEY_encrypt/ I found that the cypher is a OAEP (for RSA keys)

Also golang (mysql.go) use - implement - what they say RSA-OAEP in there rsa.go

Rust talks about PKCS#1 OAEP and refers to https://datatracker.ietf.org/doc/html/rfc8017#section-7.1 in the source https://docs.rs/rsa/latest/src/rsa/oaep.rs.html#40-49

Asking openssl openssl ciphers about names gives a list but noting on 'OAEP' or 'EVP_PKEY'

So if a cipher according to rfc8017 is present in Crystal OpenSSL::Cipher("???") I want to find it with it's proper name. Else it's implementing time for me at least for encrypt (for use at the client side only)

[ysbaddaden]

The auth_sha256_client function from sha256_pw.c in mariadb's source code is pretty explicit and lucky for us: it even uses plain OpenSSL.

You need the server public key, either provided with the connection or will need to request it. Then it is used to initialize the EVP_PKEY_CTX. OpenSSL will then use the information from the pubkey to set the context up (hash digest, ...).

You can go with raw LibC calls to OpenSSL for starters. There likely isn't Crystal bindings for EVP_PKEY_CTX yet. We can use the experience here to add them to stdlib later. But let's get this working first.

[willy610]

Status report.

1. My usage of embedding c openssl does not produce correct result. The function is isolated so anyone more experienced with integration crystal and C might look into it. Param passing etc might be wrong. It's also a distinct entry for native implementation.
2. I have analyzed all messages send and received towards the mysql server and synthesized it to an FSM (kind of Mealy Machine) and have made an ad hoc implementation. Works well. Will be documented separately. I think the FSM pattern is very powerful and easy to maintain and extend as mysql develops.
3. Have some bug when retrieving resulsets on mysql 8.3.0
4. TLS not in focus at the moment but I will/want look int that later on.

I will provide a zip on my development 'lib' still with some traces etc later this week. Can be used for openssl debug

[bcardiff]

Thanks! I am working on TLS support actually :-)

[willy610]

Attached you find my latest contribution.

The embedding of c openssl still does not work.

You also find a separate file FSMConnection.html. That FSM is implemented in the connection.cr. The machine is aware if tls is used and will avoid the roundtrip with RSAOAEP which is legal. So I'm really keen on the design of your tls and hope my assumptions are valid and useful.

For the record: Anyone with general interest in Finite State Machine could download the attachment and look inte the FSMConnection.html

mysql-20250208.zip

[willy610]

I have made a native implementation in crystal of what is needed to conform to mysql usage of RSAOEP etc.

That means the mysql plugins sha256_password and caching_sha2_password now are treated correctly by a crystal client at connection time. (No OPENSSL)

It's been a pleasure to use the expressive, compact and reliable crystal from strong pattern down to metal modulus arithmetic.

Now I will clean things up before I share some code. No Windows verification done. There are at least some issues around LittleEndian/Intel

And of course I will listen to any demands

[willy610]

Long time no seen?

I dare say I'm very close to a technical correct MYSQL_TLSCLIENT working in pure crystal.

The initial phase (an interchange of a set of hello messages) seems to work but now all traffic should be imbedded in TSL.

So I think the approach today on read/write as 'streamlike' must be refactored to 'buffered' in order to be able to encrypt/decrypt etc

It's a matter of traditional crystal programming mixed reuse functions from initial phase.

In case you are familiar of TLS the negotiation with MYSQLSERVER ends up in

• sign_algo is PSSWithSHA256
• supported_curve is CurveP256
• suite is TLS_CHACHA20_POLY1305_SHA256

Classes in pure crystal are

1. SHA1
2. SHA256
3. HMAC
4. HKDF
5. ECDH (CurveP256)
6. PK (Public Key) rudimentary/sufficient

Found a shard librfc8439 for CHACHA20_POLY1305

I don't think it is fruitful too try to merge changes onto present mysql but one way to continue from here is to, soon,

1. put the work on my private github,
2. as private (not public, internal)
3. invite interested youngsters
4. accept issues for discussions
5. no pull request
6. updates from me only