#!/usr/bin/perl

###############################################################################
#	 Program: pkts2db.pl v.01
#	 Description: Reads a pcap file and puts header fields in a SQLite database
#    Dependencies: Perl, Net::Pcap, NetPacket, and SQLite
#    Development Platform:  Windows 7 with Perl v5.10.1 and SQLite v3.6.22
#    Release Date: 2010-03-05
#
#	 Copyright 2010 Adam Bray
#
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
###############################################################################

# Modules
use warnings;
use strict;
use Getopt::Long;
use File::Basename;

use Net::Pcap;
use NetPacket::Ethernet;
use NetPacket::IP;
use NetPacket::TCP;
use NetPacket::UDP;
use NetPacket::ICMP;

# Declare variables
my $pcap;
my $help;
my $db;
my $id = 1;
my $err;
my $filter_c;
my $filter = "ip and tcp or udp or icmp";

# Get options
GetOptions ("read|r=s" => \$pcap,
			"database|d=s" => \$db,
           "help|h" => \$help);

&Help if $help || !defined($pcap && $db);

# Open files to store header fields
my $basename = fileparse($db, qr/\.[^.]+$/);
open (IPOUT, ">${basename}_ip.csv");
open (TCPOUT, ">${basename}_tcp.csv");
open (UDPOUT, ">${basename}_udp.csv");
open (ICMPOUT, ">${basename}_icmp.csv");

# Read PCAP file
my $packets = Net::Pcap::open_offline( $pcap, \$err ) || die "Error reading file: $err\n";

# Compilie and set filter
Net::Pcap::compile( $packets, \$filter_c, $filter, 1, 0 );
Net::Pcap::setfilter( $packets, $filter_c );

# Process and then close $packets
Net::Pcap::loop( $packets, -1, \&read_packets, '' );
Net::Pcap::close( $packets );

# Subroutine to process each packet
sub read_packets {
   my ($user_data, $header, $packet) = @_;
	my $time = $header->{tv_sec};
	my $timeu = $header->{tv_usec};
   my $eth = NetPacket::Ethernet->decode($packet);
   my $ip = NetPacket::IP->decode($eth->{'data'});
   my $tcp = NetPacket::TCP->decode($ip->{'data'});
	my $udp = NetPacket::UDP->decode($ip->{'data'});
	my $icmp = NetPacket::ICMP->decode($ip->{'data'});
	
	# Format time for SQLite
	my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($time);
	$mon++;
	$sec = "0$sec" if ($sec < 10);
	$min= "0$min" if ($min < 10);
	$hour = "0$hour" if ($hour < 10);
	$mday = "0$mday" if ($mday < 10);
	$mon = "0$mon" if ($mon < 10);
	$time = $year + 1900 . "-$mon-${mday}T$hour:$min:$sec.$timeu";
	
	# Declare variables to store header fields
	# Ethernet
	my ( $smac, $dmac, $ethertype );
	# IP
	my ( $ipver, $iphlen, $ipflags, $ipfoffset, $iptos, $iplen, $ipid, $ipttl, $ipproto, $ipchksum, $ipsaddr, $ipdaddr, $ipopts );
	# TCP
	my ( $sport, $dport, $tcpseqnum, $tcpacknum, $tcphlen, $tcpreserved, $tcpflags, $tcpwindow, $tcpurg, $tcpopts );
	# UDP
	my ( $udplen );
	
	# Ethernet and IP
	$smac = $eth->{'src_mac'};
	$dmac = $eth->{'dest_mac'};
	$ethertype = $eth->{'type'};
	
	if ($ethertype == 0x0800) {
		$ipver = $ip->{'ver'};
		$iphlen = $ip->{'hlen'} * 4;
		$ipflags = $ip->{'flags'};
		$ipfoffset = $ip->{'foffset'};
		$iptos = $ip->{'tos'};
		$iplen = $ip->{'len'};
		$ipid = $ip->{'id'};
		$ipttl = $ip->{'ttl'};
		$ipproto = $ip->{'proto'};
		$ipchksum = $ip->{'cksum'};
		$ipsaddr = $ip->{'src_ip'};
		$ipdaddr = $ip->{'dest_ip'};
		$ipopts = "1" if $ip->{'options'};
		
		# No nulls
		foreach my $field ( $smac, $dmac, $ethertype, $ipver, $iphlen, $ipflags, $ipfoffset,
			$iptos, $iplen, $ipid, $ipttl, $ipproto, $ipchksum, $ipsaddr, $ipdaddr, $ipopts ) {
				$field = "0" if !defined $field;
			}
			
		# Create CSV lines and print to IP file
		my $line = "$id,$time,$smac,$dmac,$ethertype,$ipver,$iphlen,$iptos,$iplen,";
		$line .= "$ipid,$ipflags,$ipfoffset,$ipttl,$ipproto,$ipchksum,$ipsaddr,$ipdaddr,$ipopts\n";
		print IPOUT $line;
	}
	
	if (defined $ipproto && $ipproto == 6) { # TCP
		$sport = $tcp->{'src_port'};
		$dport = $tcp->{'dest_port'};
		$tcpseqnum = $tcp->{'seqnum'};
		$tcpacknum = $tcp->{'acknum'};
		$tcphlen = $tcp->{'hlen'} * 4;
		$tcpreserved = $tcp->{'reserved'};
		$tcpflags = $tcp->{'flags'};
		$tcpwindow = $tcp->{'winsize'};
		$tcpurg = $tcp->{'urg'};
		$tcpopts = "1" if $tcp->{'options'};
		
		# No nulls
		foreach my $field ( $sport, $dport, $tcpseqnum, $tcpacknum, $tcphlen, $tcpreserved, $tcpflags,
			$tcpwindow, $tcpurg, $tcpopts) {
			$field = "0" if !defined $field;
			}
			
		# Create CSV lines and print to TCP file
		my $line = "$id,$sport,$dport,$tcpseqnum,$tcpacknum,$tcphlen,$tcpreserved,";
		$line .= "$tcpflags,$tcpwindow,$tcpurg,$tcpopts\n";
		print TCPOUT $line;
		
	} elsif (defined $ipproto && $ipproto == 17) { # UDP
		$sport = $udp->{'src_port'};
		$dport = $udp->{'dest_port'};
		$udplen = $udp->{'len'};
		
		# No nulls
		foreach my $field ($sport, $dport, $udplen) {
			$field = "0" if !defined $field;
			}
			
		# Create CSV lines and print to UDP file
		my $line = "$id,$sport,$dport,$udplen\n";
		print UDPOUT $line;
		
	} elsif (defined $ipproto && $ipproto == 1) { # ICMP
		my $type = $icmp->{'type'};
		my $code = $icmp->{'code'};
		
		# No nulls
		foreach my $field ($type, $code) {
			$field = "0" if !defined $field;
			}
			
		# Create CSV lines and print to ICMP file
		my $line = "$id,$type,$code\n";
		print ICMPOUT $line;
	}
	$id++;
}

close IPOUT;
close TCPOUT;
close UDPOUT;
close ICMPOUT;

# Create IP table and then delete IP CSV file
system("sqlite3 $db \"CREATE TABLE ip (id INTEGER PRIMARY KEY, time TEXT, smac TEXT, dmac TEXT, ethtype INTEGER,
	ver INTEGER, hlen INTEGER, tos INTEGER, len INTEGER, ipid INTEGER, flags INTEGER, foffset INTEGER,
	ttl INTEGER, proto INTEGER, chksum INTEGER, saddr TEXT, daddr TEXT, opts INTEGER);\"");
system("sqlite3 -separator , $db \".import ${basename}_ip.csv ip\"");
unlink("${basename}_ip.csv"); 

# Create TCP table and then delete TCP CSV file
system("sqlite3 $db \"CREATE TABLE tcp (id INTEGER, sport INTEGER, dport INTEGER, seq INTEGER, ack INTEGER, hlen INTEGER,
	reserved INTEGER, flags INTEGER, window INTEGER, urgptr INTEGER,
	opts INTEGER, FOREIGN KEY (id) REFERENCES ip(id));\"");
system("sqlite3 -separator , $db \".import ${basename}_tcp.csv tcp\"");
unlink("${basename}_tcp.csv"); 

# Create UDP table and then delete UDP CSV file
system("sqlite3 $db \"CREATE TABLE udp (id INTEGER, sport INTEGER, dport INTEGER, len INTEGER,
	FOREIGN KEY (id) REFERENCES ip(id));\"");
system("sqlite3 -separator , $db \".import ${basename}_udp.csv udp\"");
unlink("${basename}_udp.csv"); 

# Create ICMP table and then delete ICMP CSV file
system("sqlite3 $db \"CREATE TABLE icmp (id INTEGER, type INTEGER, code INTEGER,
	FOREIGN KEY (id) REFERENCES ip(id));\"");
system("sqlite3 -separator , $db \".import ${basename}_icmp.csv icmp\"");
unlink("${basename}_icmp.csv");

# Help subroutine
sub Help {

print <<HELP;

pkts2db version .01

Usage: $0 [-d] [-r] [-h]

  -d\tDatabase to create
  -r\tRead PCAP file
  -h\tHelp

Example: $0 -r evidence04.pcap -d evidence04.db
HELP

exit 1;
}