Welcome to the MySQL technical articles shared by the great SQL community. If you have any questions or want to learn, you can leave a message in the comment area below. After seeing it, you will answer it
0. Introduction
With the same account and password, the manual client connection can succeed, but it fails through MySQL Connectors. Why?
1. Phenomenon description
A program written through MySQL C API reports an error and login fails when the user logs in.
However, if you log in manually through the mysql client, and then start the client program, no error will be reported, and the program will run successfully.
2. Packet capture analysis
Learn to grab bags, more than 90%Programmers. sudo tcpdump -i any tcp and port xxx -s 1500 -w filename -v
Package when C program login fails
The first two packages are normal. The third package is seen for the first time. The one parsed by wireshark is called AuthSwitchRequest
Look at the contents of this package.
After the mysql client logs in successfully, execute the package when the C program logs in successfully
Take a look at the contents of the AuthSwithRequest package.
The packet data of successful and failed authentication are different, which are 03 and 04 respectively.
3. AuthSwitchRequest package
First, take a look at the official explanation of the AuthSwitchRequest package.
Protocol::AuthSwitchRequest: Authentication Method Switch Request Packet. If both server and client support CLIENT_PLUGIN_AUTH capability, server can send this packet to ask client to use another authentication method. Payload 1 [fe] string[NUL] plugin name string[EOF] auth plugin data
It can be seen that the first byte of this packet payload is 0xfe, which is different from 01 in the packet capture. Moreover, AuthSwitchRequest should be followed by plugin name, which is a string. In the packet capture, the content is 04.
So wireshard unpacked incorrectly.
In the protocol, we found the package whose first byte of payload is 01 and found the package of AuthMoreData.
Protocol::AuthMoreData: Payload 1 [01] string[EOF] plugin data
The description of this package is followed by a string type, which is somewhat different from our packet capture. Let's ignore this first.
You can only find out what this package is in the code.
4. Server authentication
At present, user authentication defaults to caching_ sha2_ The plugin of password is MySQL in previous versions_ native_ password.
mysql > select user,host,plugin from mysql.user; +------------------+-----------+-----------------------+ | user | host | plugin | +------------------+-----------+-----------------------+ | mysql.infoschema | localhost | caching_sha2_password | | mysql.session | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | root | localhost | caching_sha2_password | +------------------+-----------+-----------------------+ mysql > show variables like '%auth%'; +-------------------------------+-----------------------+ | Variable_name | Value | +-------------------------------+-----------------------+ | default_authentication_plugin | caching_sha2_password | +-------------------------------+-----------------------+ 1 row in set (0.01 sec)
Caching of server-side authentication code in sql/auth/sha2password.cc file_ sha2_ password_ Authenticate function.
In this function, we find the code of the contract, which can correspond to the AuthMoreData of our packet capture.
996 if (pkt_len != sha2_password::CACHING_SHA2_DIGEST_LENGTH) return CR_ERROR;
997
998 std::pair<bool, bool> fast_auth_result =
999 g_caching_sha2_password->fast_authenticate(
1000 authorization_id, reinterpret_cast<unsigned char *>(scramble),
1001 SCRAMBLE_LENGTH, pkt,
1002 info->additional_auth_string_length ? true : false);
1003
1004 if (fast_auth_result.first) {
1005 /*
1006 We either failed to authenticate or did not find entry in the cache.
1007 In either case, move to full authentication and ask the password
1008 */
1009 if (vio->write_packet(vio, (uchar *)&perform_full_authentication, 1))
1010 return CR_AUTH_HANDSHAKE;
1011 } else {
1012 /* Send fast_auth_success packet followed by CR_OK */
1013 if (vio->write_packet(vio, (uchar *)&fast_auth_success, 1))
1014 return CR_AUTH_HANDSHAKE;
1015 if (fast_auth_result.second) {
1016 const char *username =
1017 *info->authenticated_as ? info->authenticated_as : "";
1018 LogPluginErr(INFORMATION_LEVEL,
1019 ER_CACHING_SHA2_PASSWORD_SECOND_PASSWORD_USED_INFORMATION,
1020 username, hostname ? hostname : "");
1021 }
1022
1023 return CR_OK;
1024 }
1025Firstly, fast is carried out_ Authenticate, according to this result, fast_auth_result.first, different packages are sent to perform_full_authentication and fast_auth_success.
791 static char request_public_key = '\2'; 792 static char fast_auth_success = '\3'; 793 static char perform_full_authentication = '\4';
You can see that when our C program fails to log in, it sends us perform_full_authentication, and fast is sent successfully_ auth_ Success package.
Under what circumstances will perform occur_ full_ Where's the authentication package? Given the instructions in the code, we won't look at fast_ The code logic of authenticate is. You can get the general situation from the description.
1006 We either failed to authenticate or did not find entry in the cache. 1007 In either case, move to full authentication and ask the password
In other words, if there is no record in the cache or authentication fails, perform will be entered_ full_ Authentication process. We start with the name caching of this authentication plug-in_ sha2_ Password can know that this is an authentication plug-in with cache.
This just explains why after logging in manually with mysql client, our C program logs in successfully, because there are records in the cache.
So: why can manual client authentication succeed? And our own C program will fail?
Let's look at the client authentication analysis.
5. Client authentication
The code logic of client authentication is in SQL common / client_ Caching in authentication.cc_ sha2_ password_ auth_ In the client function.
514 if (pkt_len != 1 || *pkt != perform_full_authentication) {
515 DBUG_PRINT("info", ("Unexpected reply from server."));
516 return CR_ERROR;
517 }
518
519 /* If connection isn't secure attempt to get the RSA public key file */
520 if (!connection_is_secure) {
521 public_key = rsa_init(mysql);
......
523 if (public_key == NULL && mysql->options.extension &&
524 mysql->options.extension->get_server_public_key) {
525 // If no public key; request one from the server.
......
540 }
541
542 if (public_key) {
543 /*
......
584 } else {
585 set_mysql_extended_error(mysql, CR_AUTH_PLUGIN_ERR, unknown_sqlstate,
586 ER_CLIENT(CR_AUTH_PLUGIN_ERR),
587 "caching_sha2_password",
588 "Authentication requires secure connection.");
589 return CR_ERROR;
590 }
591 } else {
592 /* The vio is encrypted already; just send the plain text passwd */
593 if (vio->write_packet(vio, (uchar *)mysql->passwd, passwd_len))
594 return CR_ERROR;
595 }You can see that the client receives the perform_ full_ After the authentication package, according to the connection_is_secure branches.
The connection in our client must not have SSL enabled, so we will enter the if (!connection_is_secure) process.
By default, SSL authentication is enabled for mysql client login, so the else process is followed.
When we log in using the mysql client, the command is as follows: ssl is enabled:
shell> bin/mysql -h127.0.0.1 -uroot -P3301 -ppassword
If the client wants to disable ssl, it needs to add the – ssl mode = disable option.
Therefore, this explains why the client can log in successfully.
Now let's look at the if (!connection_is_secure) process and find that the following if process is not entered, so public will not be generated_ Key, causing public_key is empty.
if (public_key == NULL && mysql->options.extension && 524 mysql->options.extension->get_server_public_key)
Specifically, MySQL - > options. Extension - > get is not set for our connection options_ server_ public_ key.
6. Solutions
When the ssl option is not enabled, we need to set get_server_public_key option.
+ bool get_server_public_key = true; + mysql_options(m_client, MYSQL_OPT_GET_SERVER_PUBLIC_KEY, + &get_server_public_key);
Reference documents
connection-phase-packets, https://dev.mysql.com/doc/int...
authentication method mismatch, https://dev.mysql.com/doc/int...
Enjoy GreatSQL :)
This article is composed of blog one article multi posting platform OpenWrite release!