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