http://www.perlmonks.org?node_id=626924
Category: Networking Code
Author/Contact Info Ian <ian at icontact.com>
Description:

A MySQL query sniffer. It sniffs the network with Net::Pcap, extracts queries from MySQL packets, and prints them on standard output.

The documentation, as well as any future versions, lives at [http://iank.org/querysniffer/]

#!/usr/bin/perl -w

=head1 NAME

mysqlsniff.pl: MySQL query sniffer

=head1 VERSION

0.10

=head1 USAGE

    mysqlsniff.pl [interface]

interface is optional, defaulting to the interface returned by
Net::Pcap::lookupdev()

=head1 DESCRIPTION

mysqlsniff.pl is a query sniffer for mysql.  It sniffs the network
with pcap, extracts queries from mysql packets, and prints them on
standard output.

=head1 PROTOCOL

see: L<http://www.redferni.uklinux.net/mysql/MySQL-Protocol.html>
COM_QUERY packets look like this:

    0f 00 00 00
    03
    "show databases"

The first three bytes are length, the fourth is the packet number for
this transaction.  I'm ignoring the packet number and only looking at
the length, to make sure it's nonzero before continuing.

The fifth byte is the command type.  QUERY is 03.  (A complete list
can be found in mysql header files).

The rest (in the case of QUERY packets) is the query string.

=head1 AUTHOR

Ian Kilgore <ian AT icontact.com>

=head1 COPYRIGHT & LICENSE

Copyright 2007 iContact, all rights reserved.

This program is free software; you can redistribute it and/or modify 
it under the same terms as Perl itself.

=head1 SEE ALSO

L<Net::Pcap>

=cut

use strict;
use Net::PcapUtils;
use NetPacket::Ethernet qw(:strip);
use NetPacket::IP qw(:strip);
use NetPacket::TCP;

use constant MYSQL_PORT => 3306;
use constant COM_QUERY  => 3;

sub process_pkt {
    my($arg, $hdr, $pkt) = @_;

    ## Strip the ethernet and IP headers
    my $tcp_obj = NetPacket::TCP->decode(ip_strip(eth_strip($pkt)));

    ## If dest_port (mysql port), grab the payload and parse it
    if ($tcp_obj->{dest_port} == MYSQL_PORT) {
        my $data = $tcp_obj->{data};
        return unless $data;

        my $len = unpack('C3',$data);
        return unless $len > 0;

        my $type = unpack('C',substr($data,4));
        if ($type == COM_QUERY) {
            print(substr($data,5) . "\n");
        }
    }
}

my $dev = (shift @ARGV || '');
print Net::PcapUtils::loop(\&process_pkt, FILTER => 'tcp',
    SNAPLEN => 1024, DEV => $dev) . "\n";