#!/usr/bin/perl
#          File: ipchains_dblog.pl
#     Author(s): Dominic J. Eidson and Andrew Pitt (andrew@project9.com)
#       Created: Late at night in a land far, far away... (a.k.a. IRC :)
# Last Modified: March 17th, 2000
#   Description: Small script that will parse stdin for IPCHAINS log
#                messages and dump them into a Database of your choice
#                (assuming the DB has a DBI interface...)
#  Table Schema:
#                CREATE TABLE net_violations (
#                   id serial,
#                   time integer,
#                   interface varchar(10) NOT NULL,
#                   protocol smallint,
#                   source_ip inet,
#                   source_hostname varchar(80),
#                   source_port int4 NOT NULL,
#                   dest_ip inet,
#                   dest_hostname varchar(100) NOT NULL,
#                   dest_port int4 NOT NULL,
#                   len smallint,
#                   node_type varchar(5),
#                   seq bigint,
#                   flags varchar(6),
#                   ttl int2
#               );

use POSIX qw(strftime);
use DBI;
use Time::ParseDate;
use Socket;

my $debug = 0;
my $year = `date +%Y`;
chop $year;
my $machine = `uname -n`;
chop $machine;
my $found;

sub validip ($)
{
        my (@octets)=split(/\./, $_[0]);

        return 0 if $#octets+1 != 4;            # Bail if != 4 octets.
        foreach $octet (@octets) {
                return 0 if $octet+0 ne $octet; # Bail if non-numeric.
                return 0 if $octet+0 > 255 or $octet+0 < 0;
        }

        1;
}

if(defined($ARGV[0]) && $ARGV[0] eq '-d') {
    $debug = 1;
    print STDERR "Debug.\n";
}

# If you use MySQL, uncomment the following and comment out the DBI:Pg line
# $dbh = DBI->connect("DBI:mysql:dbname=security","username","passwd");
$dbh = DBI->connect('DBI:Pg:dbname=sauron','','', { RaiseError => 1});

die "Cannot connect to DB: ".$DBI::errstr."\n" unless defined $dbh;

while($line = <STDIN>) {
    $found = 0;
    if ( ($month, $day, $hour, $iface_in, $proto, $srcip, $srcport, $dstip,
	  $dstport, $len, $node, $seq, $flag, $ttl) = ($line =~ /^(\w+)\s+(\d+) (.{8}) \w+ kernel: Packet log: \w+ \w+ (\w+) PROTO=(\d+) (\d+\.\d+\.\d+\.\d+):(\d+) (\d+\.\d+\.\d+\.\d+):(\d+) L=(\d+) S=(.{4}) I=(\d+) F=(.{6}) T=(\d{1,3})/)) {
      $found = 1;
    }

    if ( ($month, $day, $hour, $iface_in, $iface_out, $mac, $srcip, $dstip,
	  $len, $tos, $prec, $ttl, $id, $proto, $srcport, $dstport, $seq, 
	  $ack, $window, $res, $ugrp) =
	  ($line =~ /^([a-zA-Z]+)\s+(\d+) (.{8}) \w+ kernel: IN=([a-zA-Z0-9]*) OUT=([a-zA-Z0-9]*) MAC=([0-9a-f:]+) SRC=([0-9.]+) DST=([0-9.]+) LEN=(\d+) TOS=([0-9x]+) PREC=([0-9x]+) TTL=(\d+) ID=(\d+).*PROTO=([A-Z]+) SPT=(\d+) DPT=(\d+) SEQ=(\d+) ACK=(\d+) WINDOW=(\d+) RES=([0-9x]+).*URGP=(\d+)/)) {
      $found = 1;
      $proto = getprotobyname($proto);
    }


    if($found) {
	if(!validip($srcip) || !validip($dstip)) {
	    next;
	}

	$time = parsedate("$month $day $year $hour", NO_RELATIVE => 1);

	if(!defined($hosts{$srcip})) {
	    $ip = inet_aton($srcip);
	    $hostname = gethostbyaddr($ip, AF_INET);
	    if (!defined($hostname)) {
		$hostname = $srcip;
	    }
	    $hosts{$srcip} = $hostname;
	} else {
	    $hostname = $hosts{$srcip};
	}

	$query="INSERT INTO net_violations (time, interface, protocol,
		source_ip, source_hostname, source_port, dest_ip,
		dest_hostname, dest_port, len, node_type, seq, flags, ttl)
		VALUES  ($time, '$iface_in', $proto, '$srcip', '$hostname',
		$srcport, '$dstip', '$machine', $dstport, $len, '$node',
		$seq, '$flag', $ttl);";

	if($debug == 1) {
	    print "$query\n";
	} else {
	    $dbh->do($query) || die ("Problem submitting query \"$query\"");
	}
    }
}
$dbh->disconnect();