O'Reilly logo

Understanding MySQL Internals by Sasha Pachev

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 packet sequence number set to 0, and the body in the format shown in Table 4-2.

Table 4-2. Fields of the server's greeting packet

Offset in the body





Protocol version number. Decimal 10 (0x0A) in recent versions. Although some changes were made in the protocol in versions 4.0 and 4.1, the protocol version number remained the same because the changes were fully backward-compatible.


ver_len = strlen (server_version) + 1

Zero-terminated server version string. The length is variable, and is calculated according to the formula in the Length column. The subsequent offsets are a function of the length of this field.

ver_len + 1


Internal MySQL ID of the thread that is handling this connection. Low byte first.

ver_len + 5


In version 4.0 and earlier, the random seed string in its entirety. In 4.1 and later, the first 8 bytes of the 20-byte random seed string. At the end is a terminating zero. Starting in version 4.1, the length of this field is controlled by the value of SCRAMBLE_LENGTH_323, defined in include/mysql_com.h. In the earlier versions, the macro is SCRAMBLE_LENGTH, defined in sql/sql_parse.cc. With the terminating zero byte, the length of the string is one greater than the value of the macro.

ver_len + 14


Server capabilities bit mask with the low byte first. See Table 4-5 later for the meaning of different bits.

ver_len + 16


Default character set code, or more precisely, the code of the default collation. A character set collation is a set of rules that defines a sequential order among characters. A list of available collations and their codes can be obtained by executing SHOW COLLATION LIKE '%' in version 4.1.

ver_len + 17


The server status bit mask with the low byte first. Reports whether the server is in transaction or autocommit mode, if there are additional results from a multistatement query, or if a good index (or some index) was used for query optimization. For details, see the SERVER_* values in include/mysql_com.h.

ver_len + 19


Reserved for future use. Currently zeroed out.

ver_len + 32


Present only in version 4.1 and later. The rest of the random seed string terminated with a zero byte. The length is equal to the value of SCRAMBLE_LENGTH – SCRAMBLE_LENGTH_323 + 1.

The client responds with a credentials packet. The format differs between versions up to and including 4.0, and versions 4.1 and later. Table 4-3 shows the format for the pre-4.1 era. Table 4-4 shows the format for versions 4.1 and later, if the client understands and is willing to use the 4.1 protocol.

Table 4-3. Fields of the client's credentials packet, up to MySQL version 4.0

Offset in the body





Protocol capabilities bit mask of the client, low byte first.



Maximum packet length that the client is willing to send or receive. Zero values means the client imposes no restrictions of its own in addition to what is already there in the protocol.


Varies; see description

Credentials string in following format: zero-terminated MySQL username, then if the password is not empty, scrambled password (8 bytes). This can be optionally followed by the initial database name, in which case a zero byte terminator is added immediately after the XOR encrypted password, followed by the database name string without a terminating zero byte.

Table 4-4. Fields of the client's credentials packet, MySQL version 4.1 and later

Offset in the body





Protocol capabilities bit mask of the client, low-byte first.



Maximum packet length that the client is willing to send or receive. Zero values means the client imposes no restrictions of its own in addition to what is already there in the protocol.



Default character set (or more precisely, collation) code of the client.



Reserved space; currently zeroed out.


Varies; see description

Credentials string in the following format: zero-terminated username, then the length of the SHA1 encrypted password (decimal 20), followed by its value (20 bytes), which is optionally followed by the zero-terminated initial database name.

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. The client switches to the SSL layer, and resends the entire response packet securely this time. It would be more efficient, of course, to not resend the initial part of the response, but, for historical reasons, this small overhead allowed the code on the server to stay fairly clean without thorough rework.

Once the server receives the credentials packet, it verifies the information. From this point, it can respond in three different ways:

  • If the check succeeds, the standard OK response packet is sent (for details, see the "Server Responses" section, later in the chapter).

  • If the credentials did not meet the expectations of the server, the standard error message response is sent.

  • The third possibility comes from the need to support the transition from 4.0 to 4.1. In some cases, the DBA may have upgraded both the client and the server to 4.1, but forgot or chose not to upgrade the user table in the mysql database, which contains user names and their respective password hashes. If the entry for that user has the old-style password hash, it is impossible to authenticate with the new authentication protocol.

In that event, the server sends a special packet with the 1-byte-long body containing decimal 254, which means: "please send the authentication credentials in the old format." The client responds with a packet whose body contains a zero-terminated encrypted password string. The server responds with either OK or a standard error message.

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. This is possible due to a flaw in the way the expected response to the challenge is computed—it is uniquely determined by the value of the password hash and the value of the challenge (for details, see scramble_323( ) and check_scramble_323( ) in sql/password.c). Therefore, 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 encryption is done using a home-cooked XOR procedure (see the scramble_323( ) function mentioned earlier), which lacks true cryptographic strength.

These weaknesses have been addressed in version 4.1. 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.

Despite the added improvements, do not feel complacent about the security of the new protocol. It is still recommended to block access to the MySQL port on the firewall, and if this is not possible, require the clients to use SSL.

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.

The bit mask can be either 4 or 2 bytes long, depending on the context. The newer (4.1 and later) clients and servers understand 4-byte masks as well as 2-byte ones. The older (4.0 and earlier) ones can handle only 2-byte masks.

The server, regardless of the version, always announces its capabilities with a 2-byte bit mask. Although both newer clients and servers understand the 4-byte mask, the first packet in the dialog must be understood by any client regardless of the version. For this reason, even the newer clients expect the greeting packet to contain a 2-byte mask.

Once the client knows that it is talking to a newer server, it can announce its capabilities with a 4-byte mask. However, if the newer client detects that it is talking to an older server, it will announce the capabilities with only a 2-byte mask. Naturally, the older clients can only send a 2-byte mask; they are not aware of 4-byte ones.

Table 4-5 explains the meaning of the bits used in the capabilities' bit mask. The values are defined in include/mysql_com.h.

Table 4-5. Protocol capability bits

Bit macro symbol

Hex value




Apparently was used in the early development of 4.1 to indicate that the server is able to use the new password format.



Normally, in reporting the results of an UPDATE query, the server returns the number of records that were actually modified. If this flag is set, the server is being asked to report the number of records that were matched by the WHERE clause. Not all of those will necessarily be updated, as some may already contain the desired values.



This flag will be set for all modern clients. Some old clients expect to receive only 1 byte of flags in the field definition record, while the newer ones expect 2 bytes. If this flag is cleared, the client is old and wants only 1 byte for field flags. This flag will also be set by the modern server to indicate that it is capable of sending the field definition in the new format with 2 bytes for field flags. Old servers (pre-3.23) will not report having this capability.



This flag is also set for all modern clients and servers. It indicates that the initial default database can be specified during authentication.



If set, the client is asking the server to consider the syntax db_name.table_name.col_name an error. This syntax is normally accepted.



When set, indicates that the client or the server is capable of using the compressed protocol.



Apparently was created to indicate that the client is an ODBC client. At this point, it does not appear to be used.



When set, indicates that the client is capable of uploading local files with LOAD DATA LOCAL INFILE.



When set, communicates to the server that the parser should ignore the space characters between identifiers and subsequent '.' or '(' characters. This flag enables syntax such as:

	db_name .table_name


	length (str)

which would normally be illegal.



When set, indicates that the client or the server is capable of using the new protocol that was introduced in version 4.1.



When set, the client is communicating to the server that it is accepting commands directly from a human. For the server, this means that a different inactivity timeout value should be applied. The server has two settings: wait_timout and interactive_timeout. The former is for regular clients, while the latter is for the interactive ones. This distinction was created to deal with applications using buggy persistent connection pools that would lose track of established connections without closing them first, keep creating new ones, and eventually overflow the server max_connections limit. The workaround was to set wait_timeout to a low value that would disconnect the lost connections sooner. This, unfortunately, had a side effect of disconnecting interactive clients too soon, which was solved by giving them a separate timeout.



When set, indicates the capability of the client or the server to use SSL.



Used internally in the client code in versions 3.23 and 4.0. SIGPIPE is a Unix signal sent to a process when the socket or the pipe it is writing to has already been closed by the peer. However, a thread in a threaded application on some platforms may get a SIGPIPE signal spuriously under some circumstances. Versions 3.23 and 4.0 permit the client programmer to choose whether SIGPIPE should be ignored. Version 4.1 just blocks it during the client initialization and does not worry about the issue from that point on.



When set in the packet coming from the server, indicates that the server supports transactions and is capable of reporting transaction status. When present in the client packet, indicates that the client is aware of servers that support transactions.



Not used.



When set, indicates that the client or the server can authenticate using the new SHA1 method introduced in 4.1.



When set, indicates that the client can send more than one statement in one query, for example:

	res = mysql_query(con,"SELECT a FROM
	t1 WHERE id =1; SELECT b FROM t1
	WHERE id=3");



When set, indicates that the client can receive results from multiple queries in the same statement.



Internal flag used inside the client routines. Never sent to the server.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required