#!/usr/bin/perl

#First file finish on frame ~803 - after that is some client server communication
#Second file finish on frame ~2347 - after that is some client server communication
#Both files are identical.
#frame #16 contains the file length  00 6a 0b 00                                      .j..


#http://search.cpan.org/~capttofu/DBD-mysql-4.014/lib/DBD/mysql.pm

## TCP Flags
#use constant FIN => 0x01;
#use constant SYN => 0x02;
#use constant RST => 0x04;
#use constant PSH => 0x08;
#use constant ACK => 0x10;
#use constant URG => 0x20;
#use constant ECE => 0x40;
#use constant CWR => 0x80;


use strict;
use warnings;
use Getopt::Long; # read parameters
use Pod::Usage;
use Net::Pcap;
use NetPacket::Ethernet;
use NetPacket::IP;
use NetPacket::TCP;
use NetPacket::UDP;
use Digest::MD5  qw(md5 md5_hex md5_base64);
use DBI;

my $host = "localhost";
my $port = "3306";
my $database = "puzzle6";
my $tablename = "packets";
my $user = "puzzle6";
my $pw = "puzzle6";

my $is_verbose = 0;

my $err;
my $file;
my $index = 0;
my $ref_sec = 0;
my $ref_usec = 0;

my $dsn = "DBI:mysql:database=$database;host=$host;port=$port";
my $dbh = DBI->connect($dsn, $user, $pw);

get_arguments();
print_md5($file);

if ($is_verbose) {print_row_count();}

empty_database();
import_file($file);

my ($start, $end) = get_q7_interval();

q4();
q5();

q7_a($start, $end);
q7_b($start, $end);
q7_c($start, $end);
q8();
q10();

print "Finished\n";

sub q7_a
{
	my $pstart = shift;
	my $pend = shift;
	my $sql = "select 
	p1.SEQ_NUM sequence,round(((select timedelta from packets p2 where max(p1.pindex)<p2.pindex and p2.pindex < $pend and p2.dest_port = 4445 AND p2.flags = 2 order by p2.pindex limit 0,1) - min(p1.timedelta)),6) 'delay', count(p1.SEQ_NUM) 'count', group_concat(p1.pindex) 'pindexes' from packets p1 where p1.pindex >= $pstart AND p1.pindex <= $pend AND p1.dest_port = 4445 AND p1.flags = 2 group by p1.SEQ_NUM order by max(p1.pindex)";
	my $sth = $dbh->prepare($sql);
	$sth->execute();
	if($is_verbose){print "\nISN.\t\tChange delay\tPackets count\tIndexes\n";}
	while (my $ref = $sth->fetchrow_hashref()) 
	{
		if($is_verbose)
		{
			my $pdelay='not availabe';
			if(defined($ref->{delay}))
				{$pdelay = "$ref->{delay}";}
			print "[$ref->{sequence}]\t[$pdelay]\t\t[$ref->{count}]\t[$ref->{pindexes}].\n";
		}
	}

	$sql = "select round(avg(t1.delay),1) av_delay, round(avg(t1.count)) av_count from ($sql) t1";
	$sth = $dbh->prepare($sql);
	$sth->execute();
	my $ref = $sth->fetchrow_hashref();
	print "\n7.a Initial sequence number changed every [$ref->{av_count}] packet(s) and every [$ref->{av_delay}] second(s). \n\n";
}

sub q8
{
	my $sql = "select pindex, timedelta, round(timedelta,1) time from packets where flags = 16 and dest_port = 4445 order by pindex limit 0,1";
	my $sth = $dbh->prepare($sql);
	$sth->execute();
	my $ref = $sth->fetchrow_hashref();
	print "\n8. Port 4445 connection start at index [$ref->{pindex}] and time [$ref->{time}] second(s). \n\n";
}

sub q10
{
	my $sql = "select pindex, timedelta, round(timedelta,1) time from packets where flags = 17 and dest_port = 4445 order by pindex limit 0,1";
	my $sth = $dbh->prepare($sql);
	$sth->execute();
	my $ref = $sth->fetchrow_hashref();
	print "\n10. Port 4445 connection end at index [$ref->{pindex}] and time [$ref->{time}] second(s). \n\n";
}

sub q4
{
	my $sql = "select pindex, timedelta, round(timedelta,1) time from packets where flags = 16 and dest_port = 4444 order by pindex limit 0,1";
	my $sth = $dbh->prepare($sql);
	$sth->execute();
	my $ref = $sth->fetchrow_hashref();
	print "\n4. Port 4444 connection start at index [$ref->{pindex}] and time [$ref->{time}] second(s). \n\n";
}

sub q5
{
	my $sql = "select pindex, timedelta, round(timedelta,1) time from packets WHERE dest_port = 4444 AND (flags &1) = 1 AND (flags &16) = 16 ORDER BY pindex LIMIT 0 , 1";
	my $sth = $dbh->prepare($sql);
	$sth->execute();
	my $ref = $sth->fetchrow_hashref();
	print "\n5. Port 4444 connection end at index [$ref->{pindex}] and time [$ref->{time}] second(s). \n\n";
}

sub q7_b
{
	my $pstart = shift;
	my $pend = shift;
	my $sql = "select p1.IP_ID ip_ident, round(((select timedelta from packets p2 where max(p1.pindex)<p2.pindex and p2.pindex < $pend and p2.dest_port = 4445 AND p2.flags = 2 order by p2.pindex limit 0,1) - min(p1.timedelta)),6) 'delay', count(p1.IP_ID) 'count', group_concat(p1.pindex) 'pindexes' from packets p1 where p1.pindex > $pstart and p1.pindex < $pend and p1.dest_port = 4445 AND p1.flags = 2 group by p1.IP_ID order by max(p1.pindex)";
	my $sth = $dbh->prepare($sql);
	$sth->execute();
	if($is_verbose){print "\nIp Id\tChange delay\tPackets count\tIndexes\n";}
	while (my $ref = $sth->fetchrow_hashref()) 
	{
		if($is_verbose)
		{
			my $pdelay='not availabe';
			if(defined($ref->{delay}))
				{$pdelay = "$ref->{delay}";}
			print "[$ref->{ip_ident}]\t[$pdelay]\t\t[$ref->{count}]\t[$ref->{pindexes}].\n";
		}
	}
	$sql = "select round(avg(t1.delay),1) av_delay, round(avg(t1.count)) av_count from ($sql) t1";
	$sth = $dbh->prepare($sql);
	$sth->execute();
	my $ref = $sth->fetchrow_hashref();
	print "\n7.b Ip Identification changed every [$ref->{av_count}] packet(s) and every [$ref->{av_delay}] second(s).\n\n";
}

sub q7_c
{
	my $pstart = shift;
	my $pend = shift;
	my $sql = "select p1.src_port, round(((select timedelta from packets p2 where max(p1.pindex)<p2.pindex and p2.pindex < $pend and p2.dest_port = 4445 AND p2.flags = 2 order by p2.pindex limit 0,1) - min(p1.timedelta)),6) 'delay', count(p1.src_port) 'count', group_concat(p1.pindex) pindexes from packets p1 where p1.pindex > $pstart and p1.pindex < $pend and p1.dest_port = 4445 AND p1.flags = 2 group by p1.src_port order by max(p1.pindex)";
	my $sth = $dbh->prepare($sql);
	$sth->execute();
	if($is_verbose){print "\nSource port\tChange port\tPackets count\tIndexes\n";}
	while (my $ref = $sth->fetchrow_hashref()) 
	{
		if($is_verbose)
		{
			my $pdelay='not availabe';
			if(defined($ref->{delay}))
				{$pdelay = "$ref->{delay}";}
			print "[$ref->{src_port}]\t\t[$pdelay]\t\t[$ref->{count}]\t[$ref->{pindexes}].\n";
		}
	}
	$sql = "select round(avg(t1.delay),1) av_delay, round(avg(t1.count)) av_count from ($sql) t1";
	$sth = $dbh->prepare($sql);
	$sth->execute();
	my $ref = $sth->fetchrow_hashref();
	print "\n7.c Source port changed every [$ref->{av_count}] packet(s) and every [$ref->{av_delay}] second(s). \n\n";
}

sub get_q7_interval
{
	#Connection end for port 4444
	my $sql="SELECT pindex, timedelta FROM packets WHERE dest_port = 4444 AND (flags &1) = 1 AND (flags &16) = 16 ORDER BY pindex LIMIT 0 , 1";
	my $sth = $dbh->prepare($sql);
	$sth->execute();
	my $ref = $sth->fetchrow_hashref();
	if(not defined($ref)) {die 'ERROR - Cannot found connection end on port 4444.';}
	
	my $con4444_end_pindex = $ref->{pindex};

	if($is_verbose) { print "[$ref->{pindex}] -> Connection on port 4444 ended at second [$ref->{timedelta}].\n"}


	#reconnect start for port 4445 after connection on port 4444 ended
	$sql = "SELECT pindex, timedelta FROM packets WHERE pindex > $con4444_end_pindex AND dest_port = 4445  AND flags = 2  ORDER BY pindex LIMIT 0,1";
	$sth = $dbh->prepare($sql);
	$sth->execute();
	$ref = $sth->fetchrow_hashref();
	if(not defined($ref)) {die 'ERROR - Cannot found connection start on port 4445.';}
	
	my $con4445_start_pindex = $ref->{pindex};

	if($is_verbose) { print "[$ref->{pindex}] -> Connection on 4445 (after 4444 closes) started at second [$ref->{timedelta}].\n"}

	#reconnect succeded for port 4445
	$sql = "SELECT pindex, timedelta FROM packets WHERE dest_port = 4445 AND flags = 2 AND pindex > $con4445_start_pindex ORDER BY pindex desc LIMIT 0 , 1";
	$sth = $dbh->prepare($sql);
	$sth->execute();
	$ref = $sth->fetchrow_hashref();
	if(not defined($ref)) {die 'ERROR - Cannot found connection successfully completed on port 4445.';}
	
	my $con4445_success_pindex = $ref->{pindex};

	if($is_verbose) { print "[$ref->{pindex}] -> Connection successfully completed on port 4445 at second [$ref->{timedelta}].\n"}
	
	return ($con4445_start_pindex, $con4445_success_pindex);
}

sub print_row_count
{
	my $sth = $dbh->prepare("SELECT * FROM packets");
	$sth->execute;
	my $numRows = $sth->rows;
	print "Rows: $numRows of $index.\n";
}

sub empty_database
{
	$dbh->do("TRUNCATE TABLE packets");
	if ($is_verbose) 
	{
		print "Database cleaned.\n";
		print_row_count();
	}
}

sub import_file
{	
	my $source_file = shift;
	my $packets;
	my $filter_c;
	my $filter = '';
	$packets = Net::Pcap::open_offline( $source_file, \$err );
	if( defined $err ) { die 'Unable to read the PCAP file ' . $err . "\n"; }
	# create the filter
	Net::Pcap::compile( $packets, \$filter_c, $filter, 0, -1 );
	Net::Pcap::setfilter( $packets, $filter_c );

	Net::Pcap::loop( $packets, -1, \&insert_packet, '' ); 
	Net::Pcap::close( $packets );
	if ($is_verbose) 
	{
		print "Database filled.\n";
		print_row_count();
	}
}

sub get_arguments
{
	GetOptions(
		"source=s"=>\$file,
		"verbose!"=>\$is_verbose
		);
	if(( not defined($file)) || (not -e $file )) { die 'File expected! Use -s <pcap_file_name>'; }
}

sub print_md5
{
	my $filename = shift;
	my $digest = get_md5($filename);
	print "Filename [$filename], MD5 [$digest]\n";
}

sub get_md5
{
	my $filename = shift;
	my $ctx;
	my $digest;
	open (MYFILE, $filename);
	$ctx = Digest::MD5->new;
	$ctx->addfile(*MYFILE);
	$digest = $ctx->hexdigest;
	close (MYFILE);
	return $digest;
}

sub insert_packet
{
	my $user_data = shift;
	my $header = shift;
	my $pack = shift;
	
	$index +=1;
	
	my $ether = NetPacket::Ethernet->decode($pack);
	my $ip = NetPacket::IP->decode( $ether->{data} );

	if( $ip->{proto} != NetPacket::IP::IP_PROTO_TCP) 
	{
		if ($is_verbose) {print "[$index] Packet ignored. Protocol type [$ip->{proto}].\n";}
		return;
	}

	my $trans = NetPacket::TCP->decode( $ip->{data} );

	

	my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime($header->{tv_sec});
	my $stamp = sprintf("%4d-%02d-%02d %02d:%02d:%02d", $year+1900, $mon+1, $mday, $hour, $min, $sec);

	if($ref_sec == 0)
		{$ref_sec = $header->{tv_sec};}

	if($ref_usec ==0)
		{$ref_usec = $header->{tv_usec};}

	my $delta_ms = $header->{tv_usec} - $ref_usec;
	my $delta_s = $header->{tv_sec} - $ref_sec +  $delta_ms/1000000;

	$dbh->do("INSERT INTO packets VALUES ($index, \'$stamp\', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )", undef, $delta_s, $pack, $ip->{id}, $trans->{seqnum}, $trans->{acknum}, $trans->{src_port}, $trans->{dest_port}, $ip->{src_ip}, $ip->{dest_ip}, $trans->{flags},0,0);
}

0;

__END__