Client/Server Communication — Chapter 4
Highlights from the book
In continuation to below post
The goal is to give you the ability to look at a binary dump of the client/
server communication and be able to understand what happened. This chapter can also be helpful if you are trying to write a MySQL proxy server, a security application to audit MySQL traffic on your network.
Protocol Overview
The server listens for connections on a TCP/IP port or a local socket. When a client connects, a handshake and authentication are performed. If successful, the session begins. The client sends a command, and the server responds with a data set or a message appropriate for the type of command that was sent. When the client is finished, it sends a special command telling the server it is done, and the session is terminated.
The basic unit of communication is the application-layer packet. Commands consist of one packet. Responses may include several.
Packet Format
There are two types of packets: compressed and noncompressed. The decision on which one will be used for the session is made during the handshake stage and depends on the capabilities and settings of both the client and the server.
Additionally, regardless of the compression option, the packets are divided into two categories: commands sent by the client, and responses returned by the server.
Server response packets are divided into four categories: data packets, end-of-data-stream packets, success report (OK) packets, and error message packets.
All packets share the common 4-byte header.
Relationship Between MySQL Protocol and OS Layer
If you try to run a network sniffer on the MySQL port, you will notice that sometimes several MySQL protocol packets are contained in one TCP/IP packet, and sometimes a MySQL packet spans several TCP/IP layer packets, while some fit into exactly one TCP/IP packet. If you somehow manage to intercept the local socket traffic, you will observe a similar effect. Some buffer writes will have exactly one packet, while others may contain several. If the lower-level socket-buffer write operation has a limit on the maximum number of bytes it can handle in one chunk, you may also see one MySQL packet being transferred in several buffer writes.
Authenticating Handshake
The session between a client and a server begins with an authenticating handshake. Before it can begin, the server checks whether the host that the client is connecting from is even allowed to connect to this server. If it is not, an error message packet is sent to the client notifying it that the host is not allowed to connect.
In the case of successful host verification, the server sends a greeting packet with the standard 4-byte header. The client responds with a credentials packet. If the SSL capability option is enabled both on the client and on the server, the client will first send the initial part of the response packet without the credentials string. When the server receives it, it will see the SSL capability bit enabled in the capabilities mask, and know that it should expect the rest of the communication in SSL.
At this point the handshake is complete, and the client begins to issue commands.
Authentication Protocol Security
Neither the old nor the new protocol ever sends the user password across the connection in plain text. However, there are a number of weaknesses in the old protocol. First, knowing the value of the password hash allows the attacker to perform authentication without actually knowing the password. If the attacker can get read access to the user table in the MySQL database, or obtain the value of the stored password hash some other way, she will be able to authenticate with a specially modified version of the MySQL client library.
Second, even without having access to the hash, the correct password can be guessed in a small number of attempts if the attacker can intercept the authentication traffic between the client and the server on a few occasions. This is possible due to the weakness in the encryption method of the old protocol.
The authentication method now uses SHA1 hashes for encryption, which are much more resistant to cracking. Also, the changed challenge-verification algorithm removed the ability to authenticate by knowing just the value of the password hash rather than the actual password.
Protocol Capabilities Bit Mask
During the authentication handshake, the client and the server exchange information on what the other is able or willing to do. This enables them to adjust their expectations of their peer and not send the data in some unsupported format. The exchange of information is accomplished through fields containing the bit mask of protocol capabilities.
Command Packet
Once the authentication is complete, the client begins sending commands to the server using command packets.
Server Responses
Once the server receives a command, it processes it and sends one or more response packets. Several types of responses are discussed in this section.
Data Field
Data fields are critical components in many of the server response packets. A data field consists of a length specifier sequence followed by the actual data value. When returning the data for prepared statements fields and when the data value is not a string, the data is sent in the raw binary format with the low byte first without a length specifier.
OK Packet
An OK packet is sent to indicate that the server successfully completed the command. This type of packet is appropriate for commands that do not return a result set. Its format, however, permits sending some extra status information, such as the number of modified records, the value of the automatically generated primary key, or a custom status message in a string format.
Error Packet
When something goes wrong with the processing of a command, the server responds with an error packet.
EOF Packet
The end-of-file ( EOF) packet is used to communicate a number of messages:
• End-of-field information data in a result set
• End-of-row data in a result set
• Server acknowledgment of COM_SHUTDOWN
• Server reporting success in response to COM_SET_OPTION and COM_DEBUG
• Request for the old-style credentials during the authentication
ResultSet Packets
A large number of queries produce a result set. Some examples are SELECT, SHOW, CHECK, REPAIR, and EXPLAIN. Any time the expected information from a query is more than a simple status report, a result set is returned.
When reporting the result of a regular query (sent with COM_QUERY ), the field data is converted to the string format. When using a prepared statement ( COM_PREPARE ), the field data is sent in its native format with the
low byte first. After all of the data rows have been sent, the packet sequence is terminated with an EOF packet.
Reference
