Category: Networking Code
Author/Contact Info Ian <ian at>

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 []

#!/usr/bin/perl -w

=head1 NAME MySQL query sniffer

=head1 VERSION


=head1 USAGE [interface]

interface is optional, defaulting to the interface returned by

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


see: L<>
COM_QUERY packets look like this:

    0f 00 00 00
    "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>


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



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";