Authentication Protocol Security - Result Set Packets

(Page 7 of 7 )

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.

The result set consists of a sequence of packets. First, the server sends information about the fields with a call to Protocol::send_fields() in sql/protocol.cc in version 4.1 and later. In the older versions, the function is called send_fields() and is found in sql/net_pkg.cc. This stage produces the following sequence of packets:

• A packet with the body consisting of the standard field-length specifier sequence. However, this time, the meaning of the number is different. It indicates the number of fields in the result set.
  
• A group of field description packets (see the upcoming explanation for the format description), one for each field, in the field order of the result set.
  
• A terminating EOF packet.

The format of the field description packet body is shown in Tables 4-11 and 4-12. Table 4-11 shows the format for versions 4.0 and earlier, while Table 4-12 shows the format for versions 4.1 and later. Because most of the packet elements have variable lengths, the offsets are dependent on the content of the previous fields. I will, therefore, omit the offset column in the format descriptions. Finally, Table 4-13 explains the different field option flags.

Table 4-11. Format of server’s result set sequence, versions 4.0 and earlier

 

Length	Description
Varies	Table name of the field in the data field format. If the table was aliased in the query, contains the name of the alias.
Varies	Column name of the field in the data field format. If the column was aliased in the query, contains the name of the alias.
4	Data field-formatted value of field length, low byte first.
2	Data field-formatted field-type code according to enum field_typesin include/mysql_com.h.
1	Decimal value 3, meaning the next 3 bytes contain data. The idea is to make the sequence look like a standard data field.
2	Bit mask of field option flags (low byte first). See Table 4-12 for the explanation of the bits.
1	Decimal point precision of the field.
Varies	Optional element. If present, contains the default value of the field in the standard field data format.

Table 4-12. Format of server’s result set sequence, versions 4.1 and later

Length	Description
4	Data field (see the section “Data Field,” earlier in this chapter) containing the ASCII string def .
Varies	Database name of the field in the data field format.
Varies	Table name of the field in the data field format. If the table was aliased in the query, contains the name of the alias.
Varies	Table name of the field in the data field format. If the table was aliased in the query, contains the original name of the table.
Varies	Column name of the field in the data field format. If the column was aliased in the query, contains the name of the alias.
Varies	Column name of the field in the data field format. If the column was aliased in the query, contains the original name of the table.
1	Byte containing decimal 12, meaning that 12 bytes of data follow. The idea is to make the sequence look like a standard data field.

Table 4-12. Format of server’s result set sequence, versions 4.1 and later (continued)

Length	Description
2	Character set code of the field (low byte first).
4	Field length (low byte first).
1	Type code of the field according to enum field_types in include/mysql_com.h.
2	Bit mask of field option flags (low byte first). See Table 4-13 for the explanation of the bits.
1	Decimal-point precision of field values.
2	Reserved.
Varies	Optional element. If present, contains the default value of the field in the standard field data format.

Table 4-13. Option flags in server’s result set packets

 

Bit macro	Hexadecimal bit value	Description
NOT_NULL_FLAG	0x0001	The field value cannot be NULL(it is declared with the NOT NULL attribute).
PRI_KEY_FLAG	0x0002	The field is a part of the primary key.
UNIQUE_KEY_FLAG	0x0004	The field is a part of a unique key.
MULTIPLE_KEY_ FLAG	0x0008	The field is a part of some non-unique key.
BLOB_FLAG	0x0010	The field is a BLOBor TEXT.
UNSIGNED_FLAG	0x0020	The field was declared with the UNSIGNEDattribute, which has the same meaning as the unsignedkeyword in C.
ZEROFILL_FLAG	0x0040	The field has been declared with the ZEROFILLattribute, which tells the server to pad the numeric types with leading zeros in the output to fit the specified field length.
BINARY_FLAG	0x0080	The field has been declared with the BINARYattribute, which tells the server to compare strings byte-for-byte in a case-sensitive manner.
ENUM_FLAG	0x0100	The field is an ENUM.
AUTO_INCREMENT_ FLAG	0x0200	The field has been declared with the AUTO_INCREMENTattribute, which enables the automatic generation of primary key values when a new record is inserted.
TIMESTAMP_FLAG	0x0400	The field is a timestamp.
SET_FLAG	0x0800	The field is a SET.
NUM_FLAG	0x8000	Used with cursors in version 4.1 to indicate that the field is numeric.

Following the field definition sequence of packets, the server sends the actual rows of data, one packet per row. Each row data packet consists of a sequence of values stored in the standard field data format. 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.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: Authentication Protocol Security

>>> Be the FIRST to comment on this article!

Buy this book...

This article is excerpted from the book Understanding MySQL Internals, written by Sasha Pachev (O'Reilly, 2007; ISBN: 0596009577). Check it out today at your favorite bookstore. Buy this book now.