Trap2sql.pl

From Federal Burro of Information
Revision as of 03:28, 26 February 2012 by David (talk | contribs) (Created page with "<pre> #!/usr/bin/perl -w #CREATE TABLE `traps` ( # `host_name` varchar(75) character set utf8 NOT NULL default '', # `source` varchar(75) character set utf8 NOT NULL default '...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigationJump to search
#!/usr/bin/perl -w

#CREATE TABLE `traps` (
#  `host_name` varchar(75) character set utf8 NOT NULL default ,
#  `source` varchar(75) character set utf8 NOT NULL default ,
#  `insert_timestamp` datetime default ,
#  `oid_type` varchar(255) default ,
#  `oid_value` varchar(255) default 
#);


use strict;
use DBI qw(:sql_types);
use Digest::MD5 qw(md5 md5_hex md5_base64);

my $database = "mydb";
my $hostname = "localhost";
my $port = "3306";
my $user = "user";
my $password = "password";
my $debug  = "1";
my @my_time = localtime(time());
my $year = $my_time[5]+1900;
my $host_name = "";
my $source = "";
my $null = "";

my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
my $dbh = DBI->connect($dsn, $user, $password)
 or die "Couldn't connect to database: " . DBI->errstr;

my $myinsert = $dbh->prepare("INSERT INTO traps ( host_name, source, insert_timestamp, oid_type, oid_value ) VALUES ( ?,?,?,?,? )")
 or die "Couldn't prepare myinsert, dbh\-\>errstr: $dbh->errstr\n";

open (OUTFILE , ">>/home/david/outfile")
 or die "open failed $!\n";

if ( $debug) { print OUTFILE "---\nstart of loop\n---\n"; }

#read STDIN,$host_name,1024;
#read STDIN,$source,1024;

chomp ( $host_name = <STDIN> );
chomp ( $source = <STDIN> );

while (<>){
        print OUTFILE $_;
        my ($oid_type , $oid_value)  = split ;
        print OUTFILE "\$oid_type = $oid_type  // \$oid_value = $oid_value\n";
        $myinsert->bind_param( 1,  $host_name, SQL_VARCHAR);
        $myinsert->bind_param( 2,  $source, SQL_VARCHAR);
        $myinsert->bind_param( 3,  $null, SQL_VARCHAR);
        $myinsert->bind_param( 4,  $oid_type, SQL_VARCHAR);
        $myinsert->bind_param( 5,  $oid_value, SQL_VARCHAR);
        my $result = $myinsert->execute();
        print $result . "\n";
}

if ( $debug ) {print OUTFILE "---\nend of loop\n---\n";}

close OUTFILE;