How to get information from log files and add them to database table using Perl DBI sqlite

Question

I have this assignment which requires me to take the source ip and destination port from this log file and add them to a database table i created using Perl dbi sqlite. i have tried to write a script that does that but it does not seem to work. i would appreciate any help. The log file is available at http://fleming0.flemingc.on.ca/~chbaker/COMP234-Perl/sample.log

here is the code i have so far.

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my %ip2port;
my $IPCount = keys %ip2port;
my $portCount = 0;
my $filename = "./sample.log";
open my $LOG, "<", $filename or die "Can't open $filename: $!";
LINE: while (my $line = <$LOG>) {
my ($src_id) = $line =~ m!SRC=([.\d]+)!gis; my ($dst_port) = $line =~ m!DPT=([.\d]+)!gis;
my $dbh = DBI->connect(          
    "dbi:SQLite:dbname=test.db", 
    "",                          
    "",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;


$dbh->do("INSERT INTO probes VALUES($src_id, $dst_port )");
$dbh->do("INSERT INTO probes VALUES(2,'$dst_port',57127)");
my $sth = $dbh->prepare("SELECT SQLITE_VERSION()");
$sth->execute();

my $ver = $sth->fetch();

print @$ver;
print "\n";

$sth->finish();
$dbh->disconnect();
}

What doesn't work? What have you done to diagnose the problem? — friedo, Apr 05 '13 at 20:20
the do statements don't work, i would like help on verifying weather i have the script in a correct state , or are there errors — user218001, Apr 05 '13 at 20:45
Oh there are errors all right. But I still want to know what you mean by "don't work." — friedo, Apr 05 '13 at 20:46

Miguel Prz · Accepted Answer · 2013-04-06T06:42:53.147

0

1) Change your regular expression:

my ($src_id) = $line =~ m!SRC=([\.\d]+)!g; 
my ($dst_port) = $line =~ m!DPT=([\d]+)!g;

2) Change your SQL's

$dbh->do("INSERT INTO probes VALUES('$src_id', $dst_port )");

UPDATE In any case, it's better to build sql sentences with parameter binding and avoid the SQL-injection problems:

$dbh->do("INSERT INTO probes VALUES(?,?)", undef, $src_id, $dst_port);

edited Apr 06 '13 at 06:42

answered Apr 05 '13 at 21:04

Miguel Prz

13,718
29
42

How to get information from log files and add them to database table using Perl DBI sqlite

1 Answers1