Listing 9 Query translator section of reporting script

# Initialize some variables
my @users = $q->param('users');    # Grab CGI usernames as an array
my $users = scalar @users;        # Count the number of users in the array
my %query = $q->Vars;        # Suck in CGI query parameters 

# Connect to the database
$dbh=DBI->connect( "DBI:Pg: dbname=$dbName; host=$dbHost", $dbUser,
$dbPassword)
    || die "Unable to connect: $DBI::errstr\n";

# Build start and end timestamps in SQL-friendly format
$startstamp = $query{'beg_month'}.' '.$query{'beg_day'}.' '.
    $query{'beg_hour'}.':'.$query{'beg_minute'}.':'.'00 '.
    $query{'beg_year'};
    # Don't forget to add timezone later -- .' '.$beg_tz;
$endstamp = $query{'end_month'}.' '.$query{'end_day'}.' '.
    $query{'end_hour'}.':'.$query{'end_minute'}.':'.'00 '.
    $query{'end_year'};
    # Don't forget to add timezone later -- .' '.$end_tz;

# Build date chunk of query
$date_chunk = "datetime BETWEEN \'$startstamp\' AND \'$endstamp\' ";

# Build username chunk of query
if ( grep /---Everyone---/,@users ) {
    # Make no mention of username in SELECT query
    $user_chunk = ''
    }
else {
    # Make username query chunk in the form of:
    # "AND ( username = $user[0] OR username = $user[1] ... )"
    $user_chunk = 'AND ( ';
    foreach $user ( @users ) {
        $user_chunk .= "username = \'$user\' OR "
        };
    $user_chunk =~ s/OR $//;    # Whack off the last "OR "
    $user_chunk .= ') '
    };

# Build URL filter chunk of query
$domain_filter=$query{'domain_filter'};
$url_filter=$query{'url_filter'};
if ( $domain_filter ) { $like_chunk .= "AND domain LIKE \'%$domain_filter%\' " };
if ( $domain_filter ) { $like_chunk .= "AND url LIKE \'%$url_filter%\' " };

# Figure out which columns the user wants to see
push @headers, 'Count';
if ($query{'show_date'}) { 
    $columns.='datetime,';
    push @headers, 'Date'
};
if ($query{'show_firewall'}) {
    $columns.='firewall,';
    push @headers, 'Firewall'
};
if ($query{'show_user'}) {
    $columns.='username,';
    push @headers, 'User'
};
if ($query{'show_source'}) {
    $columns.='sourceip,';
    push @headers, 'Source IP'
};
if ($query{'show_dest'}) {
    $columns.='domain,';
    push @headers, 'Domain'
};
if ($query{'show_url'}) {
    $columns.='url,';
    push @headers, 'URL Path'
};

# Did the user want ANY columns?  If not, error.  Otherwise, chop off that
# extra comma in the column string.
if (! $columns) { error_report ; return 1 }
else { chop($columns) };

# Build the SELECT and GROUP BY query chunks
$select_chunk='SELECT COUNT(*),'.$columns.' FROM traffic WHERE ';
$groupby_chunk='GROUP BY '.$columns.' ';

# Build the ORDER BY chunk
$valid_columns=$columns."count";    # If it ain't reported, don't sort it.
if ($query{'sort1'} && $valid_columns=~/$query{'sort1'}/) {
    $orderby_chunk.=$query{'sort1'}.' '.$query{'order1'}.','};
if ($query{'sort2'} && $valid_columns=~/$query{'sort2'}/) {
    $orderby_chunk.=$query{'sort2'}.' '.$query{'order2'}.','};
if ($query{'sort3'} && $valid_columns=~/$query{'sort3'}/) {
    $orderby_chunk.=$query{'sort3'}.' '.$query{'order3'}.','};
if ($orderby_chunk) {
    chop($orderby_chunk);    # Whack off that last comma...
    $orderby_chunk='ORDER BY '.$orderby_chunk.' '
};

# Which lines to display?  Build that query chunk
$limit_chunk='LIMIT '.$query{'num_lines'}.' OFFSET '.$query{'offset'}.' ';

# Build a query to count the number of results available
$querycount='SELECT COUNT(*) FROM ( SELECT DISTINCT '.
    $columns.' FROM traffic WHERE '.$date_chunk.$user_chunk.
    $like_chunk.') AS counter';

# Build the actual query...
$querything=$select_chunk.$date_chunk.$user_chunk.$like_chunk.
    $groupby_chunk.$orderby_chunk.$limit_chunk