awk script for custom SQL output

[Luca Andreucci]

maybe someone will care about this quick hack to get "custom" SQL output 
from nessus -qp output.

the script will also be available at 
http://andrew.org/hacks/nessus-inserts.awk

#
# nessus-inserts.awk
#
# creates customizable SQL insert commands from table dumps
#
# example: nessus -qp | awk -f nessus-inserts.awk
#
# (c) 2002 Luca Andreucci <[EMAIL PROTECTED]>
# (c) 2002 Vem Sistemi SpA, http://www.vem.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.
#
# 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.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#
#
# This software is distributed under the GPL license, please
# read the license www.gnu.org
#
# $Id: nessus-inserts.awk,v 1.4 2002/06/14 11:30:22 andrew Exp andrew $
#

BEGIN {

        # configurable section

        # separator regex for input data
        # (may also be provided to awk via -F command-line switch)
        FS = "\|+";

        # name of target table
        tabname = "tablename";

        # target table field names, separated by "|"
        # please include heading and trailing separators
        fieldsstr = "|field_one|field_two|field_three|field_four|field_five|";

        # destination field mappings
        # for each index [i], 1 <= i <= <number of target fields>, provide
        #
        # EITHER
        #
        # fieldmap[i] = n
        #   (n = integer value)
        #   meaning that the i-th target field will be assigned
        #   the value of the n-th field of the input data
        #   (first field = 1)
        #
        # OR
        #
        # const[i] = "constant"
        #   meaning that the i-th target field will be assigned
        #   the constant value.
        #
        # if you provide both directives for the same index,
        #   the const[] will take precedence.
        #
        # if you provide none, the target field will be set to NULL.
        #

        fieldmap[1] = 1;
        # fieldmap[2] = 2;
        const[3]    = "const_val_one";
        const[4]    = "const_val_two";
        fieldmap[5] = 1;

        # create fields array and calculate fields number
        # please leave this alone
        numfields = split (fieldsstr, fields, "\|+") - 1;

        # header stuff
        #   (e.g. table drop/creation commands)
        # you may use any awk construct to assign a value to this var,
        # including references to variables defined above
        # example: header_stuff = sprintf("DROP TABLE %s;\nCREATE TABLE %s 
(...);", tabname, tabname);
        header_stuff = "";

        # footer stuff
        #   (e.g. primary key/index creation commands)
        # same rules as header_stuff apply
        footer_stuff = "";

        # --- end of configuration --- do not edit below


        print header_stuff;
}

END {
        print footer_stuff;
}

{
        printf "INSERT INTO %s ", tabname;

        # field names
        # may eventually be stripped
        printf "(";
        for ( i = 2; i < numfields; i++ ) printf "%s, ", fields[i];
        printf "%s) ", fields[i];

        # field values
        valuesstr = "";
        for ( i = 1; i < numfields; i++ )
                if (const[i])
                        valuesstr = valuesstr "'" const[i] "', "
                else if (fieldmap[i])
                        valuesstr = valuesstr "'" $fieldmap[i] "', "
                else
                        valuesstr = valuesstr "NULL, ";
        # strip the last comma and space
        valuesstr = substr(valuesstr, 0, (length(valuesstr) - 1));
        printf "VALUES (%s);\n", valuesstr;
}