Original code found at “http://iank.org/querysniffer/”. I have stripped much of it and inserted a log of code from “http://www.perlmonks.org/?node_id=170648” to make it a little nicer. I also added a lot of stuff via variables now.
Reference “http://www.redferni.uklinux.net/mysql/MySQL-Protocol.html” for more info on reading the pcap info.
The code will return something like this. Keep in mind the question mark has a value that is passed in with the command. I do not know how to strip out the binary around it so I choose to exclude it so I can at least get the SQL commands executed. For me this told me that the application was doing a nice SQL statement to start with then doing a not so friendly one after. I was able to go back to the developer and ask him to chase this down.
SELECT * FROM sys_log WHERE field1 = ? AND timestamp >= ? AND timestamp <= ? ORDER BY timestamp DESC LIMIT 20000
INSERT INTO log_entry (timestamp,log_type,source,description) VALUES (?, ?, ?, ?)
SELECT * FROM sys_log WHERE field1 = ? ORDER BY timestamp DESC LIMIT 20000
INSERT INTO log_entry (timestamp,log_type,source,description) VALUES (?, ?, ?, ?)
You will need libpcap-devel, libpcap, and perl modules (Net::Pcap, Net::PcapUtils, NetPacket::Ethernet, NetPacket::IP, NetPacket::TCP, Socket). Make sure you run “perl -c” on the script.
#!/usr/bin/perl -w
use strict;
use Net::Pcap;
use Net::PcapUtils;
use NetPacket::Ethernet qw(:strip);
use NetPacket::IP qw(:strip);
use NetPacket::Ethernet;
use NetPacket::TCP;
use Socket;
use constant MYSQL_PORT => 3306;
# Script Version
my $ver = 0.11;
# Remove the numbers for the command type codes you
# do not want to see.
# Beware binary data is not filtered just yet so you
# might end up screwing up ascii display to your terminal
# if the right set of binary characters pass through your screen.
#use constant COM_QUERY_TYPES => qw( 2 3 5 6 11 16 22 23 24 25 );
use constant COM_QUERY_TYPES => qw( 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 );
my @COM_QUERY_TYPES = (COM_QUERY_TYPES);
# If you do not want to filter lines that only contain binary data
# then change the value to 0;
my $filterbinary = 1;
# MySQL Command codes
my %cmdcode = (
0 => 'SLEEP',
1 => 'QUIT',
2 => 'INIT_DB',
3 => 'QUERY',
4 => 'FIELD_LIST',
5 => 'CREATE_DB',
6 => 'DROP_DB',
7 => 'REFRESH',
8 => 'SHUTDOWN',
9 => 'STATISTICS',
10 => 'PROCESS_INFO',
11 => 'CONNECT',
12 => 'PROCESS_KILL',
13 => 'DEBUG',
14 => 'PING',
15 => 'TIME',
16 => 'DELAYED_INSERT',
17 => 'CHANGE_USER',
18 => 'BINLOG_DUMP',
19 => 'TABLE_DUMP',
20 => 'CONNECT_OUT',
21 => 'REGISTER_SLAVE',
22 => 'PREPARE',
23 => 'EXECUTE',
24 => 'LONG_DATA',
25 => 'CLOSE_STMT',
26 => 'RESET_STMT',
27 => 'SET_OPTION',
);
my $err;
# Use network device passed in program arguments or if no
# argument is passed, determine an appropriate network
# device for packet sniffing using the
# Net::Pcap::lookupdev method
my $dev = $ARGV[0];
unless (defined $dev) {
$dev = Net::Pcap::lookupdev(\$err);
if (defined $err) {
die 'Unable to determine network device for monitoring - ', $err;
}
}
# Specify the MTU you want to use on teh interface
my $mtu = 1500;
# Source/Destination filter
my $filter_string = "(dst 127.0.0.1)";
# Do not print these SQL query commands when displaying SQL queries;
# Change $do_not_print to = ''; if you do not want to filter the printing
# of the stuff listed below
my $do_not_print = "^SET NAMES|^SET character|^SHOW VARIABLE|^SHOW COLLATION|^SET autocommit|^SET sql_mode";
# Number of packets to capture
# Zero or a negative number means capture packets indefinitely
# (or until an error occurs if the $to_ms argument of
# the open_live method is set to 0)
# 100 = 100 packets to capture
my $count = -1;
# Optimize the capture filter
# 0 = false (will not optimize)
# 1 = true (will optimize)
my $optimize = 0;
# #
# DO NOT EDIT BELOW THIS LINE #
# #
# Look up network address information about network
# device using Net::Pcap::lookupnet - This also acts as a
# check on bogus network device arguments that may be
# passed to the program as an argument.
#
# Note: The address and netmask are returned as integers
# so you will need to convert them
my ($address, $netmask);
if (Net::Pcap::lookupnet($dev, \$address, \$netmask, \$err)) {
die 'Unable to look up device information for ', $dev, ' - ', $err;
}
# Convert the integer returned for the addresses to decimal ip addr format
my $Address = inet_ntoa(pack "N", $address);
my $Netmask = inet_ntoa(pack "N", $netmask);
# Create packet capture object on device
my $object;
$object = Net::Pcap::open_live($dev, $mtu, 0, 0, \$err);
unless (defined $object) {
die 'Unable to create packet capture on device ', $dev, ' - ', $err;
}
# Compile and set packet filter for packet capture
# object - For the capture of TCP packets with the SYN
# header flag set directed at the external interface of
# the local host, the packet filter of '(dst IP) && (tcp
# [13] & 2 != 0)' is used where IP is the IP address of
# the external interface of the machine. For
# illustrative purposes, the IP address of 127.0.0.1 is
# used in this example.
my $filter_compiled;
Net::Pcap::compile(
$object,
\$filter_compiled,
"$filter_string",
$optimize,
$netmask
) && die 'Unable to compile packet capture filter';
Net::Pcap::setfilter($object, $filter_compiled) &&
die 'Unable to set packet capture filter';
my $mysqlport = MYSQL_PORT;
# Print the header before the output is displayed
print "+------------------------------------------------+\n";
print "| MySQL Live SQL command monitor (Ver. $ver) |\n";
print "+------------------------------------------------+\n";
print "| Filter params:
| Interface: $dev
| IP Address: $Address
| NetMask: $Netmask
| MTU: $mtu
| Src/Dst Filter: $filter_string
| Read # of Packets: $count
| SQL Command Types: @COM_QUERY_TYPES
| MySQL Port: $mysqlport
";
print "+------------------------------------------------+\n";
# Set callback function and initiate packet capture loop
Net::Pcap::loop($object, $count, \&process_packets, '' ) ||
die 'Unable to perform packet capture';
Net::Pcap::close($object);
sub process_packets {
my ($user_data, $header, $packet) = @_;
## Strip the ethernet and IP headers
my $tcp_obj = NetPacket::TCP->decode(ip_strip(eth_strip($packet)));
## Strip ethernet encapsulation of captured packet
my $tcp_obj1 = NetPacket::Ethernet::strip($packet);
## Decode contents of TCP/IP packet contained within
## captured ethernet packet
my $ip = NetPacket::IP->decode($tcp_obj1);
my $tcp = NetPacket::TCP->decode($ip->{'data'});
## 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));
foreach (@COM_QUERY_TYPES)
{
if ($type == $_)
{
my $Data = substr($data,5);
if ( $do_not_print ne '' )
{
printf ( "SQL_CMD from %s to %s: \n %s: %s\n\n", "$ip->{'src_ip'}", "$ip->{'dest_ip'}", "$cmdcode{$type}", "$Data" ) if $Data !~ /$do_not_print/;
}
else
{
printf ( "SQL_CMD from %s to %s: \n %s: %s\n\n", "$ip->{'src_ip'}", "$ip->{'dest_ip'}", "$cmdcode{$type}", "$Data" );
}
}
}
}
}
#
# POD info for perldoc
#
=head1 NAME
mysqlsniff.pl: MySQL query sniffer
=head1 VERSION
0.11 Lance V added support for all command types.
Added filtering
Added exit after n packets
Added Compiled packet filter
Added many features from http://www.perlmonks.org/?node_id=170648
0.10
=head1 USAGE
mysqlsniff.pl [interface]
interface is optional, defaulting to the interface returned by
Net::Pcap::lookupdev()
Note: PCAP will require root/sudo access.
=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
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
Lance Vermilion
Ian Kilgore
=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 TODO
1. Fix the binary + ascii output generated with the EXECUTE query. it should only show ascii ouput and filter the binary output.
2. Find out why the CLOSE_STMT does not show any statment for closing.
3. Inspect the errors that are returned convert them to human readable.
=head1 SEE ALSO
L
=cut
Gheek.net 