NAME

MySQL::Packet - encode and decode the MySQL binary protocol

VERSION

Version 0.2007054

SYNOPSIS

Sorry for the absurdly verbose synopsis. I don't have a proper example script for you at the moment.

use MySQL::Packet qw(:debug);           # dumping packet contents etc.
use MySQL::Packet qw(:test :decode);    # decoding subs
use MySQL::Packet qw(:encode);          # encoding subs

use MySQL::Packet qw(:COM :CLIENT :SERVER);     # constants

my $packet;
my $greeting;
my $result;
my $field_end;

my $mysql_socket = whatever_i_do_to_connect();

while (read $mysql_socket, $_, 1000, length) {
    if (not $packet) {
        my $_packet = {};
        my $rc = mysql_decode_header $_packet;
        if ($rc < 0) {
            die 'bad header';
        }
        elsif ($rc > 0) {
            $packet = $_packet;
            redo;
        }
    }
    elsif (not $greeting) {
        my $rc = mysql_decode_greeting $_packet;
        if ($rc < 0) {
            die 'bad greeting';
        }
        elsif ($rc > 0) {
            mysql_debug_packet $packet;
            $greeting = $packet;
            undef $packet;
            send_client_auth();
            redo;
        }
    }
    elsif (not $result) {
        my $rc = mysql_decode_result $packet;
        if ($rc < 0) {
            die 'bad result';
        }
        elsif ($rc > 0) {
            mysql_debug_packet $packet;
            if ($packet->{error}) {
                die 'the server hates me';
            }
            elsif ($packet->{end}) {
                die 'this should never happen';
            }
            else {
                if ($packet->{field_count}) {
                    $result = $packet;
                    # fields and rows to come
                }
                elsif (not $packet->{server_status} & SERVER_MORE_RESULTS_EXISTS) {
                    # that's that..
                    send_some_query();
                }
            }
            undef $packet;
            redo;
        }
    }
    elsif (not $field_end) {
        my $rc = do {
            (mysql_test_var $packet}) ? (mysql_decode_field $packet)
                                      : (mysql_decode_result $packet)
        };
        if ($rc < 0) {
            die 'bad field packet';
        }
        elsif ($rc > 0) {
            mysql_debug_packet $packet;
            if ($packet->{error}) {
                die 'the server hates me';
            }
            elsif ($packet->{end}) {
                $field_end = $packet;
            }
            else {
                do_something_with_field_metadata($packet);
            }
            undef $packet;
            redo;
        }
    }
    else {
        my $rc = do {
            (mysql_test_var $packet ? (mysql_decode_row $packet)
                                    : (mysql_decode_result $packet)
        };
        if ($rc < 0) {
            die 'bad row packet';
        }
        elsif ($rc > 0) {
            mysql_debug_packet $packet;
            if ($packet->{error}) {
                die 'the server hates me';
            }
            elsif ($packet->{end}) {
                undef $result;
                undef $field_end;
                unless ($packet->{server_status} & SERVER_MORE_RESULTS_EXISTS) {
                    # that's that..
                    send_some_query();
                }
            }
            else {
                my @row = @{ $packet->{row} };
                do_something_with_row_data(@row);
            }
            undef $packet;
            redo;
        }
    }
}

sub send_client_auth {
    my $flags = CLIENT_LONG_PASSWORD | CLIENT_LONG_FLAG | CLIENT_PROTOCOL_41 | CLIENT_TRANSACTIONS | CLIENT_SECURE_CONNECTION;
    $flags |= CLIENT_CONNECT_WITH_DB if $i_want_to;
    my $pw_crypt = mysql_crypt 'my_password', $greeting->{crypt_seed};
    my $packet_body = mysql_encode_client_auth (
        $flags,                                 # $client_flags
        0x01000000,                             # $max_packet_size
        $greeting->{server_lang},               # $charset_no
        'my_username',                          # $username
        $pw_crypt,                              # $pw_crypt
        'my_database',                          # $database
    );
    my $packet_head = mysql_encode_header $packet_body, 1;
    print $mysql_socket $packet_head, $packet_body;
}

sub send_some_query {
    my $packet_body = mysql_encode_com_query 'SELECT * FROM foo';
    my $packet_head = mysql_encode_header $packet_body;
    print $mysql_socket $packet_head, $packet_body;
}

DESCRIPTION

This module exports various functions for encoding and decoding binary packets pertinent to the MySQL client/server protocol. It also exports some useful constants. It does NOT wrap an IO::Socket handle for you.

This is ALPHA code. It currently groks only the new v4.1+ protocol. It currently handles only authentication, the COM_QUERY and COM_QUIT commands, and the associated server responses. In other words, just enough to send plain SQL and get the results.

For what it does, it seems to be quite stable, by my own yardstick.

This module should eventually grow to support statement prepare and execute, the pre-v4.1 protocol, compression, and so on.

EXPORTABLE CONSTANTS

Commands (Tag :COM)

COM_SLEEP
COM_QUIT
COM_INIT_DB
COM_QUERY
COM_FIELD_LIST
COM_CREATE_DB
COM_DROP_DB
COM_REFRESH
COM_SHUTDOWN
COM_STATISTICS
COM_PROCESS_INFO
COM_CONNECT
COM_PROCESS_KILL
COM_DEBUG
COM_PING
COM_TIME
COM_DELAYED_INSERT
COM_CHANGE_USER
COM_BINLOG_DUMP
COM_TABLE_DUMP
COM_CONNECT_OUT
COM_REGISTER_SLAVE
COM_STMT_PREPARE
COM_STMT_EXECUTE
COM_STMT_SEND_LONG_DATA
COM_STMT_CLOSE
COM_STMT_RESET
COM_SET_OPTION
COM_STMT_FETCH

Client Flags / Server Capabilities (Tag :CLIENT)

CLIENT_LONG_PASSWORD
CLIENT_FOUND_ROWS
CLIENT_LONG_FLAG
CLIENT_CONNECT_WITH_DB
CLIENT_NO_SCHEMA
CLIENT_COMPRESS
CLIENT_ODBC
CLIENT_LOCAL_FILES
CLIENT_IGNORE_SPACE
CLIENT_PROTOCOL_41
CLIENT_INTERACTIVE
CLIENT_SSL
CLIENT_IGNORE_SIGPIPE
CLIENT_TRANSACTIONS
CLIENT_RESERVED
CLIENT_SECURE_CONNECTION
CLIENT_MULTI_STATEMENTS
CLIENT_MULTI_RESULTS

Server Status Flags (Tag :SERVER)

SERVER_STATUS_IN_TRANS
SERVER_STATUS_AUTOCOMMIT
SERVER_MORE_RESULTS_EXISTS
SERVER_QUERY_NO_GOOD_INDEX_USED
SERVER_QUERY_NO_INDEX_USED
SERVER_STATUS_CURSOR_EXISTS
SERVER_STATUS_LAST_ROW_SENT
SERVER_STATUS_DB_DROPPED
SERVER_STATUS_NO_BACKSLASH_ESCAPES

EXPORTABLE FUNCTIONS

Debugging (Tag :debug)

mysql_debug_packet \%packet
mysql_debug_packet \%packet, $file_handle: Dumps a textual representation of the packet to STDERR or the given handle.

Packet Type Tests (Tag :test)

These functions operate on $_ if no data argument is given. They must be used only after "mysql_decode_header" has succeeded.

$bool = mysql_test_var \%packet;
$bool = mysql_test_var \%packet, $data: Returns true if the data encodes a variable-length binary number or string.
$bool = mysql_test_end \%packet
$bool = mysql_test_end \%packet, $data: Returns true if the data is an end packet (often called EOF packet).
$bool = mysql_test_error \%packet
$bool = mysql_test_error \%packet, $data: Returns true if the data is an error packet.

Decoding Packets (Tag :decode)

These functions take either a hash reference (to be populated with packet information), or a scalar (to receive a number or string). The optional second argument is always the data to decode. If omitted, $_ is used instead, and bytes are consumed from the beginning of $_ as it is processed.

All except "mysql_decode_skip" return the number of bytes consumed, -1 if the data is invalid, or 0 if processing cannot continue until there is more data available. If the return is -1 there is no way to continue, and an unknown number of bytes may have been consumed.

$rc = mysql_decode_header \%packet

$rc = mysql_decode_header \%packet, $data

Populates %packet with header information. This always has to be done before any other decoding subs, or any testing subs, are used.

packet_size         => size of packet body
packet_serial       => packet serial number from 0 to 255

$rc = mysql_decode_skip \%packet

$rc = mysql_decode_skip \%packet, $data

If the number of available bytes is equal to or greater than the packet size, consumes that many bytes and returns them. Otherwise, returns undef.

$rc = mysql_decode_varnum $number

$rc = mysql_decode_varnum $number, $data

Consumes a variable-length binary number and stores it in $number. Note that $number is NOT passed as a reference.

$rc = mysql_decode_varstr $string

$rc = mysql_decode_varstr $string, $data

Consumes a variable-length string and stores it in $string. Note that $string is NOT passed as a reference.

$rc = mysql_decode_greeting \%packet

$rc = mysql_decode_greeting \%packet, $data

Consumes the greeting packet (also called handshake initialization packet) sent by the server upon connection, and populates %packet. After this the client authentication may be encoded and sent.

protocol_version    => equal to 10 for modern MySQL servers
server_version      => e.g. "5.0.26-log"
thread_id           => unique to each active client connection
crypt_seed          => some random bytes for challenge/response auth
server_capa         => flags the client may specify during auth
server_lang         => server's charset number
server_status       => server status flags

$rc = mysql_decode_result \%packet

$rc = mysql_decode_result \%packet, $data

Consumes a result packet and populates %packet. Handles OK packets, error packets, end packets, and result-set header packets.

Error Packet:

error       => 1
errno       => MySQL's errno
message     => description of error
sqlstate    => some sort of official 5-digit code

End Packet:

end             => 1
warning_count   => a number
server_status   => bitwise flags

OK Packet:

field_count     => 0
affected_rows   => a number
last_insert_id  => a number
server_status   => bitwise flags
warning_count   => a number
message         => some text

Result Header Packet:

field_count     => a number greater than zero

$rc = mysql_decode_field \%packet

$rc = mysql_decode_field \%packet, $data

Consumes a field packet and populates %packet.

catalog         => catalog name
db              => database name
table           => table name after aliasing
org_table       => original table name
name            => field name after aliasing
org_name        => original field name
charset_no      => field character set number
display_length  => suggested field display width
field_type      => a number from 0 to 255
flags           => bitwise flags
scale           => number of digits after decimal point

$rc = mysql_decode_row \%packet

$rc = mysql_decode_row \%packet, $data

Consumes a row packet and populates %packet.

row => ref to array of (stringified) values

Encoding Packets (Tag :encode)

These functions all return the encoded binary data.

$header_data = mysql_encode_header $packet_data
$header_data = mysql_encode_header $packet_data, $packet_serial: Returns the header for the already encoded packet. The serial number defaults to 0 which is fine except for the authentication packet, for which it must be 1.
$data = mysql_encode_varnum $number: Returns the variable-length binary encoding for $number.
$data = mysql_encode_varnum $string: Returns the variable-length binary encoding for $string.
$data = mysql_encode_client_auth @args: Returns the payload for an authentication packet where @args = ($flags, $max_packet_size, $charset_no, $username, $crypt_pw, $database). The $database is optional.
$data = mysql_encode_com_quit: Encodes the QUIT command. Takes no arguments.
$data = mysql_encode_com_query @sql: Encodes the QUERY command, using the concatenation of the arguments as the SQL string.

Password Cryption (Tag :crypt)

$crypt_pw = mysql_crypt $password, $crypt_seed: Implements MySQL's crypt algorithm to crypt a plaintext $password using the $crypt_seed from the greeting packet. Returns a binary string suitable for passing to "mysql_encode_client_auth". Requires either Digest::SHA or Digest::SHA1.

BUGS

Most client commands are unimplemented. Does not handle the pre-v4.1 protocol and could mess up in unpredictable ways (even fatal exceptions) if you try. It's possible to get a fatal exception calling a decode function on the wrong data, since Perl's unpack can barf fatally (this got me by surprise after the code was written, so all the unpack calls need to be audited now).

And so forth.

ACKNOWLEDGEMENTS

Thanks to those on #poe for their help with packaging and CPAN.

Thanks to Rob for giving me a good reason to write this!

LICENSE

MySQL::Packet is free software and is licensed under the same terms as Perl itself.

It's interesting to read this licensing notice: http://dev.mysql.com/doc/internals/en/licensing-notice.html

MySQL AB seems to think that any software which communicates with a MySQL server must be GPL'd, because the protocol is GPL. However, they have been quoted in interviews saying that isn't true after all, and that in any case they don't really care.

To install MySQL::Packet, copy and paste the appropriate command in to your terminal.

cpanm

cpanm MySQL::Packet

CPAN shell

perl -MCPAN -e shell
install MySQL::Packet

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)