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;


