#!/usr/bin/perl
#
# $Id: secadvmgmt.pl,v 1.1 2002/01/27 15:37:47 adulau Exp $
#
# Basic script to update the security advisory database
#Usage is : secadvmgmt.pl [action] [secadvisory type] [db source id] [filename]
#
# Action could be :  add   add or update advisory in the database
#                    list  show current advisory source supported in the db and their db source id
#  
# currently supported format are :
#
#           - cve		Comma separated value from CVE-Mitre                   
#	    - cert		CERT html advisory 

# Authors : Alexandre Dulaunoy <alex@thinkingsecure.com>
#
# Copyright (C) 2001,2002 Conostix S.A. Luxembourg - Alexandre Dulaunoy
# Copyright (C) 2001,2002 Alexandre Dulaunoy <alex@thinkingsecure.com>

# 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 2 of the License, or (at your
# option) any later version.  See <http://www.fsf.org/copyleft/gpl.txt>.
#
# 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.
#

$db       = "secadvisory";
$username = "alex";
$password = "";

use DBI;

my $connect_string = "dbi:Pg:dbname=$db;host=127.0.0.1;port=5432";

my $dbh = DBI->connect( $connect_string, $username, $password )
  or $m->abort("Database connection failed");

$dbh->{AutoCommit} = 1;

#$dbh->trace(0);

$action   = $ARGV[0];
$type     = $ARGV[1];
$id       = $ARGV[2];
$filename = $ARGV[3];

if ( $action eq "add" ) {
    if ( $id eq "" ) { &PrintUsage; die ("Not db source id specified\n"); }
}

if ( $action eq "add" ) {

    if ( $type eq "cve" ) {
        open( FILECVE, "$filename" )
          or die ("Unable to open $filename for type : $type\n");
        my $cpt = 0;

        while (<FILECVE>) {
            if ( $cpt > 1 ) {

                #print $_;
                @tmp = split (/,/);
                $tmp[0] =~ s/"//g;
                $tmp[1] =~ s/"//g;
                $tmp[2] =~ s/"//g;
                $tmp[0] = $dbh->quote( $tmp[0] );
                $tmp[2] = $tmp[1] . " -- " . $tmp[2];
                $tmp[2] = $dbh->quote( $tmp[2] );

                my $query =
"INSERT INTO advisory (source_id, advisory_text, advisory_id_src) VALUES ($id,$tmp[2],$tmp[0])";
                my $sth = $dbh->prepare($query);
                $sth->execute or print "$tmp[0] is an existing advisory\n";
                $sth->finish;
                $dbh->commit;
            }
            $cpt++;
        }
    }
    else {
        &PrintUsage;
        die "Aborting : Unknown security advisory type";
    }
}
elsif ( $action eq "list" ) {
    my $query =
"SELECT source_id, source_url, source_creation from source order by source_id";
    my $sth = $dbh->prepare($query);
    $sth->execute or die ("Unable to execute query\n");
    while ( my @line = $sth->fetchrow_array ) {
        print
"db-source-id : $line[0] ** source_url : $line[1] ** source_creation : $line[2]\n";
    }
}
else {
    &PrintUsage;
    die "Aborting : Unknown action";
}
$rc = $dbh->disconnect;

sub PrintUsage {
    $rc = $dbh->disconnect;
    print <<USAGE;

Usage is : secadvmgmt.pl [action] [secadvisory type] [db source id] [filename]

 Action could be :  add   add or update advisory in the database
                    list  show current advisory source supported in the db and their db source id
  
  currently supported format are :

           - cve		Comma separated value from CVE-Mitre                   
	   - cert		CERT html advisory 

  example : 
  		Inserting CVE CSV files into the db-source-id 2
		"secadvmgmt.pl add cve 2 full-cve.csv"
		
		
USAGE
}
