MySQL Credentials Packet in PurePerl

Writing MySQL Protocol Packets in PurePerl – Part 5

Writing a Perl Module

Foreword: In this part of the series, I explain how to produce the MySQL Credentials Packet in PurePerl.

By: Chrysanthus Date Published: 28 Jan 2015

Introduction

This is part 5 of my series, Writing MySQL Protocol Packets in PurePerl. PurePerl stands for Pure Perl. It is Perl software without any C software underneath. In this part of the series, I explain how to produce the MySQL Credentials Packet in PurePerl. You should have read the previous parts of the series before reaching here, as this is a continuation.

In the MySQL authentication process, you have the socket connection from the client first. If the socket connection is successful, the server will send a greeting packet to the client. The greeting packet has a scramble string. The client combines the scramble string in a special way with the password of the user. The client software then sends this combination, the user name and other data in a packet called the Credentials packet, to the server. This credentials packet is a string in bytes. It begins with a header segment followed immediately by the body segment. All packets consist of the header segment and the body segment.

Packet Body Byte Arrangement
In the body segment of the Credentials packet string, the consecutive byte sequences, their lengths and their purposes are as in the following table. Each byte sequence is called a field.

**Table 5.1. Fields of the Client’s Credentials Packet**
Offset in the body	Length	Description
0	4	Protocol capabilities bit mask of the client, low-byte first.
4	4	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.
8	1	Default character set (or more precisely, collation) code of the client.
9	23	Reserved space; currently zeroed out.
32	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.

Zero-terminated means the extra byte is 00000000 in bits, that is 0x00 in hex. Zero byte means the same value. The character is '\0', where 0 is zero and not letter, O.

The PurePerl Code
The Perl package I have designed to produce the packet is called, Credentials in the file, Credentials.pm. The package is part of the client software. It has one function. The function takes as arguments, the user name without the terminating zero, the token (combination of password and scramble) and optionally the database name without the terminating zero. The function adds the termination zero to the user name and database name. The function does a few other things and returns a string of bytes (characters) consisting of the header segment immediately followed by the body segment.

The 4 byte bit mask used is 0x 84870000, where the extra zeroes were just added to make 4 bytes. The bit mask was derived from the following table:

**Table 5.2. Protocol Capability Bits**
Hex Value	Binary Value	Description
0x0004	0000000000000100	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.
0x0080	0000000010000000	When set, indicates that the client is capable of uploading local files with LOAD DATA LOCAL INFILE.
0x0100	0000000100000000	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_nameor length (str)which would normally be illegal.
0x0200	0000001000000000	When set, indicates that the client or the server is capable of using the new protocol that was introduced in version 4.1.
0x0400	0000010000000000	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.
0x8000	1000000000000000	When set, indicates that the client or the server can authenticate using the new SHA1 method introduced in 4.1.

Bitwise Oring all the above gives 1000011110000100 = 1000 0111 1000 0100 = 0x8784. Putting low-byte first gives, 0x8487. Appending high byte zeroes gives 84870000 .

The Protocol Capability Bits Table is long; you will consult some other document for the full table.

The Perl code for the package designed by me is:

package Credentials;
our $VERSION = "1.01";

use strict;

    sub credentials
        {
            # 1st argument is user without \0, 2nd is token, 3rd if present, is database without \0

            my $packet_body_str;
            #pack the bit mask
            $packet_body_str = pack('H8', "84870000");
            #Maximum packet length
            $packet_body_str .= pack('H8', "00000000");
            #Default character set collation = 33 decimal for utf8
            $packet_body_str .= pack('H2', "21");
            $packet_body_str .= pack('H46', "0000000000000000000000000000000000000000000000");

            #add \0 to user name
            $_[0] .= pack('H2', "00");
            my $sha1_len = pack('H2', "14");  #decimal 20

            #add credentials proper
            $packet_body_str .= $_[0];
            $packet_body_str .= $sha1_len;
            $packet_body_str .= $_[1];
            if (length($_[2]) != 0)
                {
                    $_[2] .= pack('H2', "00");
                    $packet_body_str .= $_[2];
                }

            my $body_len = length($packet_body_str);
            #convert decimal $body_len to 3 bytes with low-byte first
            my $body_len_16 = pack( 'S<', $body_len);
            my $body_len_24 = $body_len_16 . pack( 'H2', "00");
            my $seq_no = pack('H2', "01");
            my $header = $body_len_24 . $seq_no;


            my $packet_string = $header . $packet_body_str;
            return $packet_string;
        }

When the credentials packet is sent, if the server identifies the user (user name and password), it sends back an OK packet. If the user does not exists or password is fake, it sends an Error packet back to the client.

If you are not using Aciveperl, then you should precede the package with something like, #!/usr/bin/perl .

That is it for this part of the series. We stop here and continue in the next part.

Chrys

Broad Network

Related Articles

MySQL Credentials Packet in PurePerl

Writing MySQL Protocol Packets in PurePerl – Part 5

Writing a Perl Module

Introduction

Related Links

Comments