kingster commented 2 months ago

Is there an existing issue for this?

[X] I have searched the existing issues

Current behavior

When connecting to mysql server with the auth mechanism the connection fails

Steps to reproduce

Run the https://github.com/kingster/go-urlshortner

Environment

None

Version

Local

Repository

keploy

debug.txt

slayerjain commented 2 months ago

@kingster thanks for reporting. Gourav has also discovered the issue and is investigating.

@gouravkrosx please post your findings once you conclude

gouravkrosx commented 1 month ago

@kingster @slayerjain There were significant issues with the initial handshake or authentication type.

The MySQL protocol operates in two phases: 1. Connection phase, and 2. Command phase.

Connection Phase: • This is where the server initiates the request by sending the server capabilities, including the authentication mechanism. • After receiving the packet, the client responds and requests the server to log in with the provided user. • If the server has a cached entry of the client from an earlier session, it directly uses the fast authentication method of caching_sha2_password, followed by an OK message. • If there’s no cached entry, the full authentication route is followed. After informing the client of the authentication mechanism, the client requests the server to send the public key. Upon acknowledging the packet, the server sends the public key. The client then sends the encrypted password, the server decrypts it, matches it with the authentication string, and finally sends an OK or error packet to the client.

There might be an SSL connection before the authentication phase, but we’ll skip that for now. Also, there can be different authentication mechanisms sent by the server and acknowledged by the client. In such cases, auth switch request and auth switch response packets are exchanged between the client and server, but this is uncommon as the default authentication of both server and client is usually the same, so we’ll skip this as well for now.

Other authentication mechanisms include:

Old Password Authentication
mysql_native_password
AUTH_SHA256 However, we don’t fully support them yet. During the connection phase, there were some handshake-related issues that I fixed, but it was mostly working fine.

The main problem arises in the next phase, the command phase.

Command Phase: This is the actual data transfer phase where queries are executed. Currently, we don’t fully support prepared statements. Since Gorm, being an ORM, uses prepared statements, disabling them should make it work. There are also issues with decoding certain COM_* packets. Now, addressing why we were getting the authentication type error when it wasn’t actually an authentication type error. This error occurred due to how we store information about the last executed command. For instance, if the client sends a merged packet with a close statement followed by a query, the parser may decode it incorrectly. As a result, the parser might not correctly identify the last command, leading to errors and treating some packets as [authentication-related] (https://github.com/keploy/keploy/blob/1137cdce3da2469488de5f9dfa2bb4a4a6ac7a59/pkg/core/proxy/integrations/mysql/operation.go#L224) data instead of the correct command type.

If the last command was a query, we handled the result set packets accordingly. However, if it was not a query, it would be treated as a normal close statement command, leading to parsing issues.

We will resolve this issue as soon as possible. Additionally, we are planning to refactor the MySQL parser in the near future to make it more accessible for other contributors. The MySQL module needs documentation and refactoring at this point.

Reference:

TODOs

Fix the parsing of certain prepared statement packets.
Implement the handling of prepared statement logic in test mode, similar to the implementation in the PostgreSQL parser. Ref: a, b, & c
Refactor the MySQL parser and documentation for better readability.

gouravkrosx commented 1 month ago

For ref:

CACHING_SHA2_PASSWORD (Full Auth)

CACHING_SHA2_PASSWORD (Fast Auth)

gouravkrosx commented 3 weeks ago

@kingster We have implemented support for the prepared statement and refactored it completely. Here is the branch that you can try https://github.com/keploy/keploy/tree/streaming_in_mysql, I've tested it with your application as well.

It took some time to add this feature because I wanted to make it robust and readable for any open-source contributor. If you face any problems please let us know, we are testing it thoroughly on different applications before merging it to the main branch.