mysql Connection Phase Packets

14.2.5 Connection Phase Packets
Protocol::Handshake
Initial Handshake Packet

When the client connects to the server the server sends a handshake packet to the client. Depending on the server version and configuration options different variants of the initial packet are sent.

To permit the server to add support for newer protocols, the first byte defines the protocol version.

Since 3.21.0 the Protocol::HandshakeV10 is sent, while it was still supporting Protocol::HandshakeV9 with a compile time option.

Payload
1 protocol_version
...
Protocol::HandshakeV10
Initial Handshake Packet - protocol version 10

Payload
1 [0a] protocol version
string[NUL] server version
4 connection id
string[8] auth-plugin-data-part-1
1 [00] filler
2 capability flags (lower 2 bytes)
if more data in the packet:
1 character set
2 status flags
2 capability flags (upper 2 bytes)
if capabilities & CLIENT_PLUGIN_AUTH {
1 length of auth-plugin-data
} else {
1 [00]
}
string[10] reserved (all [00])
if capabilities & CLIENT_SECURE_CONNECTION {
string[$len] auth-plugin-data-part-2 ($len=MAX(13, length of auth-plugin-data - 8))
if capabilities & CLIENT_PLUGIN_AUTH {
string[NUL] auth-plugin name
}
Fields
protocol_version (1) -- 0x0a protocol_version

server_version (string.NUL) -- human-readable server version

connection_id (4) -- connection id

auth_plugin_data_part_1 (string.fix_len) -- [len=8] first 8 bytes of the auth-plugin data

filler_1 (1) -- 0x00

capability_flag_1 (2) -- lower 2 bytes of the Protocol::CapabilityFlags (optional)

character_set (1) -- default server character-set, only the lower 8-bits Protocol::CharacterSet (optional)

This “character set” value is really a collation ID but implies the character set; see the Protocol::CharacterSet description.

status_flags (2) -- Protocol::StatusFlags (optional)

capability_flags_2 (2) -- upper 2 bytes of the Protocol::CapabilityFlags

auth_plugin_data_len (1) -- length of the combined auth_plugin_data, if auth_plugin_data_len is > 0

auth_plugin_name (string.NUL) -- name of the auth_method that the auth_plugin_data belongs to

Note
Due to Bug#59453 the auth-plugin-name is missing the terminating NUL-char in versions prior to 5.5.10 and 5.6.2.

Returns
Protocol::HandshakeResponse from the client

Implemented By
send_server_handshake_packet()

Example
36 00 00 00 0a 35 2e 35 2e 32 2d 6d 32 00 0b 00 6....5.5.2-m2...
00 00 64 76 48 40 49 2d 43 4a 00 ff f7 08 02 00 ..dvH@I-CJ......
00 00 00 00 00 00 00 00 00 00 00 00 00 2a 34 64 .............*4d
7c 63 5a 77 6b 34 5e 5d 3a 00 |cZwk4^]:.
If CLIENT_PLUGIN_AUTH is set the server sends the name of the Authentication Method that the auth_plugin_data belongs to:

50 00 00 00 0a 35 2e 36 2e 34 2d 6d 37 2d 6c 6f P....5.6.4-m7-lo
67 00 56 0a 00 00 52 42 33 76 7a 26 47 72 00 ff g.V...RB3vz&Gr..
ff 08 02 00 0f c0 15 00 00 00 00 00 00 00 00 00 ................
00 2b 79 44 26 2f 5a 5a 33 30 35 5a 47 00 6d 79 .+yD&/ZZ305ZG.my
73 71 6c 5f 6e 61 74 69 76 65 5f 70 61 73 73 77 sql_native_passw
6f 72 64 00 ord
Note
The auth-plugin-data is the concatenation of strings auth-plugin-data-part-1 and auth-plugin-data-part-2.

Note
Only the fields up to the filler after the auth_plugin_data_part_1 are required, all other fields are optional.

Protocol::HandshakeV9:
Initial Handshake Packet - Protocol Version 9

Payload
1 [09] protocol_version
string[NUL] server_version
4 connection_id
string[NUL] scramble
Fields
protocol_version (1) -- 0x09 protocol_version

server_version (string.NUL) -- human-readable server version

connection_id (4) -- connection id

auth_plugin_data (string.NUL) -- auth plugin data for Authentication::Old

Returns
Protocol::HandshakeResponse320

Protocol::HandshakeResponse:
Depending on the servers support for the CLIENT_PROTOCOL_41 capability and the clients understanding of that flag the client has to send either a Protocol::HandshakeResponse41 or Protocol::HandshakeResponse320.

Protocol::HandshakeResponse41:
Handshake Response Packet sent by 4.1+ clients supporting CLIENT_PROTOCOL_41 capability, if the server announced it in its Initial Handshake Packet. Otherwise (talking to an old server) the Protocol::HandshakeResponse320 packet must be used.

Payload
4 capability flags, CLIENT_PROTOCOL_41 always set
4 max-packet size
1 character set
string[23] reserved (all [0])
string[NUL] username
if capabilities & CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA {
lenenc-int length of auth-response
string[n] auth-response
} else if capabilities & CLIENT_SECURE_CONNECTION {
1 length of auth-response
string[n] auth-response
} else {
string[NUL] auth-response
}
if capabilities & CLIENT_CONNECT_WITH_DB {
string[NUL] database
}
if capabilities & CLIENT_PLUGIN_AUTH {
string[NUL] auth plugin name
}
if capabilities & CLIENT_CONNECT_ATTRS {
lenenc-int length of all key-values
lenenc-str key
lenenc-str value
if-more data in ‘length of all key-values‘, more keys and value pairs
}
Fields
capability_flags (4) -- capability flags of the client as defined in Protocol::CapabilityFlags

max_packet_size (4) -- max size of a command packet that the client wants to send to the server

character_set (1) -- connection‘s default character set as defined in Protocol::CharacterSet.

username (string.fix_len) -- name of the SQL account which client wants to log in -- this string should be interpreted using the character set indicated by character set field.

auth-response (string.NUL) -- opaque authentication response data generated by Authentication Method indicated by the plugin name field.

database (string.NUL) -- initail database for the connection -- this string should be interpreted using the character set indicated by character set field.

auth plugin name (string.NUL) -- the Authentication Method used by the client to generate auth-response value in this packet. This is an UTF-8 string.

Example
On MySQL 5.5.8 with CLIENT_PROTOCOL_41 CLIENT_PLUGIN_AUTH, CLIENT_SECURE_CONNECTION, and CLIENT_CONNECT_WITH_DB set, it may look like:

54 00 00 01 8d a6 0f 00 00 00 00 01 08 00 00 00 T...............
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00 00 00 00 70 61 6d 00 14 ab 09 ee f6 bc b1 32 ....pam........2
3e 61 14 38 65 c0 99 1d 95 7d 75 d4 47 74 65 73 >a.8e....}u.Gtes
74 00 6d 79 73 71 6c 5f 6e 61 74 69 76 65 5f 70 t.mysql_native_p
61 73 73 77 6f 72 64 00 assword.
Starting with MySQL 5.6.6 the client may send attributes if CLIENT_CONNECT_ATTRS is set:

b2 00 00 01 85 a2 1e 00 00 00 00 40 08 00 00 00 ...........@....
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00 00 00 00 72 6f 6f 74 00 14 22 50 79 a2 12 d4 ....root.."Py...
e8 82 e5 b3 f4 1a 97 75 6b c8 be db 9f 80 6d 79 .......uk.....my
73 71 6c 5f 6e 61 74 69 76 65 5f 70 61 73 73 77 sql_native_passw
6f 72 64 00 61 03 5f 6f 73 09 64 65 62 69 61 6e ord.a._os.debian
36 2e 30 0c 5f 63 6c 69 65 6e 74 5f 6e 61 6d 65 6.0._client_name
08 6c 69 62 6d 79 73 71 6c 04 5f 70 69 64 05 32 .libmysql._pid.2
32 33 34 34 0f 5f 63 6c 69 65 6e 74 5f 76 65 72 2344._client_ver
73 69 6f 6e 08 35 2e 36 2e 36 2d 6d 39 09 5f 70 sion.5.6.6-m9._p
6c 61 74 66 6f 72 6d 06 78 38 36 5f 36 34 03 66 latform.x86_64.f
6f 6f 03 62 61 72 oo.bar
Caution
Currently, multibyte character sets such as UCS2, UTF16 and UTF32 are not supported.

Note
If client wants to have a secure SSL connection and sets CLIENT_SSL flag it should first send the SSL Request Packet and only then, after establishing the secure connection, it should send the Handshake Response Packet.

Protocol::HandshakeResponse320:
Old Handshake Response Packet used by old clients or if the server doesn‘t support CLIENT_PROTOCOL_41 capability.

Payload
2 capability flags, CLIENT_PROTOCOL_41 never set
3 max-packet size
string[NUL] username
if capabilities & CLIENT_CONNECT_WITH_DB {
string[NUL] auth-response
string[NUL] database
} else {
string[EOF] auth-response
}
Fields
capability_flags (2) -- capability flags of the client as defined in Protocol::CapabilityFlags

max_packet_size (3) -- max size of a command packet that the client wants to send to the server

auth-response (string.NUL) -- opaque authentication response data generated by Authentication Method indicated by the plugin name field.

database (string.NUL) -- initail database for the connection -- this string should be interpreted using the character set indicated by character set field.

Example
11 00 00 01 85 24 00 00 00 6f 6c 64 00 47 44 53 .....$...old.GDS
43 51 59 52 5f CQYR_
Note
if auth-response field is followed by a database field it must be 0-terminated.

Protocol::SSLRequest:
SSL Connection Request Packet. It is like Handshake Response Packet but is truncated right before username field. If server supports CLIENT_SSL capability, client can send this packet to request a secure SSL connection. The CLIENT_SSL capability flag must be set inside the SSL Connection Request Packet.

Payload
4 capability flags, CLIENT_SSL always set
4 max-packet size
1 character set
string[23] reserved (all [0])
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
Fields
status (1) -- 0xfe

auth_method_name (string.NUL) -- name of the authentication method to switch to

auth_method_data (string.EOF) -- initial auth-data for that authentication method

Returns
Protocol::AuthSwitchResponse or connection close

Example
If CLIENT_PLUGIN_AUTH was set and the server wants the client to authenticate with the Authentication::Native41 method it sends:

2c 00 00 02 fe 6d 79 73 71 6c 5f 6e 61 74 69 76 ,....mysql_nativ
65 5f 70 61 73 73 77 6f 72 64 00 7a 51 67 34 69 e_password.zQg4i
36 6f 4e 79 36 3d 72 48 4e 2f 3e 2d 62 29 41 00 6oNy6=rHN/>-b)A.
Protocol::OldAuthSwitchRequest:
Old Authentication Method Switch Request Packet consisting of a single 0xfe byte. It is sent by server to request client to switch to Old Password Authentication if CLIENT_PLUGIN_AUTH capability is not supported (by either the client or the server)

Payload
1 [fe]
Fields
status (1) -- 0xfe

Returns
Protocol::AuthSwitchResponse with old password hash

Example
01 00 00 02 fe
Protocol::AuthSwitchResponse:
Authentication Method Switch Response Packet which contains response data generated by the authenticatication method requested in Authentication Method Switch Request Packet. This data is opaque to the protocol.

Payload
string[EOF] auth plugin response
Fields
data (string.EOF) -- authentication response data

Returns
Protocol::AuthMoreData or OK_Packet or ERR_Packet

Example
If the client sends a mysql_native_password response, but the server has a mysql_old_password for that user, it will ask the client to switch to mysql_old_password and client would reply with:

09 00 00 03 5c 49 4d 5e 4e 58 4f 47 00 ....\IM^NXOG.
In the case it is the other way around (mysql --default-auth=mysql_old_password against a mysql_native_password user) the client will respond with the reply of the mysql_native_password plugin:

14 00 00 03 f4 17 96 1f 79 f3 ac 10 0b da a6 b3 ........y.......
b5 c2 0e ab 59 85 ff b8 ....Y...
More examples in Auth Method Switch

Protocol::AuthMoreData:
Payload
1 [01]
string[EOF] plugin data
Fields
status (1) -- 0x01

auth_method_data (string.EOF) -- extra auth-data beyond the initial challenge

mysql Connection Phase Packets

标签：ble   EDA   first   enc   x86   value   bar   concat   flag