<?php
/*
$Id: sql.php,v 1.39 2009/10/12 19:31:11 bhockney Exp $
(C) 2003-2009 by Bob Hockney <zeus@ix.netcom.com>

sql formulation for webfwlog

Webfwlog 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
*/

$link = db("open", "$database");
$have_hostnames = db("exists", "SELECT count(*) FROM $hostnames LIMIT 1");
$have_services  = db("exists", "SELECT count(*) FROM $services LIMIT 1");
$have_local_time = db("exists", "SELECT local_time FROM $ulog LIMIT 1");

$select = "";
$where  = "";
$group  = "";
$having = "";
$order  = "";
$join   = "";
$limit  = "";

// SELECT CLAUSE

foreach ($columnorder as $col) {
	if (!isset($FWLOG["$col"])) continue;
	switch ("$col") {
case "c_count": if(!isset($FWLOG["nocount"])) $select = $select."\tcount(*)" .
	" AS \"{$shortnames['count']}\",\n"; break;
case "c_local_time": $select = $select."\tlocal_time" . 
	" AS \"{$shortnames['local_time']}\",\n"; break;
case "c_local_hostname": $select = $select."\tlocal_hostname" .
	" AS \"{$shortnames['local_hostname']}\",\n"; break;
case "c_oob_time_sec": $select = $select."\toob_time_sec" .
	" AS \"{$shortnames['oob_time_sec']}\",\n"; break;
case "c_oob_prefix": $select = $select."\toob_prefix" .
	" AS \"{$shortnames['oob_prefix']}\",\n"; break;
case "c_oob_in": $select = $select."\toob_in" .
	" AS \"{$shortnames['oob_in']}\",\n"; break;
case "c_oob_out": $select = $select."\toob_out" .
	" AS \"{$shortnames['oob_out']}\",\n"; break;
case "c_ip_protocol": $select = $select.
"	CASE WHEN $ulog.ip_protocol=6  THEN 'tcp'
	     WHEN $ulog.ip_protocol=17 THEN 'udp'
	     WHEN $ulog.ip_protocol=1  THEN 'icmp'
	     ELSE"; 
		 	if ($config["db"] == "mysql") {
		 		$select = $select . " $ulog.ip_protocol";
			} elseif ($config["db"] == "pgsql") {
				$select = $select . " text($ulog.ip_protocol)";
			}
			$select = $select . "
	END\n" .
	"\tAS \"{$shortnames['ip_protocol']}\",\n"; break;
case "c_ip_saddr":
			if ($config["iptype"] == "int") {
				$select = $select."\t$inet_ntoa(ip_saddr)" .
				" AS \"{$shortnames['ip_saddr']}\",\n";
			} else {
				$select = $select."\tip_saddr" .
				" AS \"{$shortnames['ip_saddr']}\",\n";
			}
		 break;
case "c_src_host":
		if ($have_hostnames) {
			$select = $select."\tCASE WHEN s_hostnames.hostname IS NULL\n\t\tTHEN '-'\n\t\tELSE s_hostnames.hostname\n\tEND\n" .
			"\tAS \"{$shortnames['src_host']}\",\n";
		}
		break;
case "c_ip_daddr":
			if ($config["iptype"] == "int") {
				$select = $select."\t$inet_ntoa(ip_daddr)" .
				" AS \"{$shortnames['ip_daddr']}\",\n";
			} else {
				$select = $select."\tip_daddr" .
				" AS \"{$shortnames['ip_daddr']}\",\n";
			}
		break;
case "c_dst_host":
		if ($have_hostnames) {
			$select = $select."\tCASE WHEN d_hostnames.hostname IS NULL\n\t\tTHEN '-'\n\t\tELSE d_hostnames.hostname\n\tEND\n" .
			"\tAS \"{$shortnames['dst_host']}\",\n";
		}
		break;
case "c_sport": $select = $select .
"	CASE WHEN $ulog.ip_protocol=6  THEN tcp_sport
	     WHEN $ulog.ip_protocol=17 THEN udp_sport
	     ELSE 65536
	END
	AS \"{$shortnames['sport']}\",\n"; break;
case "c_src_service":
		if ($have_services) {
			$select = $select.
"	CASE WHEN $ulog.ip_protocol=1  THEN '-'
	     WHEN $ulog.ip_protocol=6  THEN s1.service
	     WHEN $ulog.ip_protocol=17 THEN s3.service
	END
	AS \"{$shortnames['src_service']}\",\n";
		}
		break;
case "c_dport": $select = $select.
"	CASE WHEN $ulog.ip_protocol=6  THEN tcp_dport
	     WHEN $ulog.ip_protocol=17 THEN udp_dport
	     ELSE 65536
	END
	AS \"{$shortnames['dport']}\",\n"; break;
case "c_dst_service":
		if ($have_services) {
			$select = $select.
"	CASE WHEN $ulog.ip_protocol=1  THEN '-'
	     WHEN $ulog.ip_protocol=6  THEN s2.service
	     WHEN $ulog.ip_protocol=17 THEN s4.service
	END
	AS \"{$shortnames['dst_service']}\",\n";
		}
		break;
case "c_tcp_options":
	if ($config['db'] == "mysql") {
	 $select = $select.
"	if($ulog.ip_protocol<>6,'',
	     IF(tcp_syn AND NOT (tcp_urg OR tcp_psh OR tcp_rst OR tcp_ack OR tcp_fin),
		' SYN  ', concat(
		if( tcp_syn, 's', '-'),
		if( tcp_ack, 'a', '-'),
		if( tcp_fin, 'f', '-'),
		if( tcp_rst, 'r', '-'),
		if( tcp_psh, 'p', '-'),
		if( tcp_urg, 'u', '-'))))
	AS \"{$shortnames['tcp_options']}\",\n";
	} elseif ($config['db'] == "pgsql") {
	 $select = $select.
"	CASE WHEN $ulog.ip_protocol=6 THEN
		CASE WHEN tcp_syn AND NOT (tcp_urg OR tcp_psh OR tcp_rst OR tcp_ack OR tcp_fin) THEN ' SYN  '
		     ELSE 
		         CASE WHEN tcp_syn THEN 's' ELSE '-' END ||
		         CASE WHEN tcp_ack THEN 'a' ELSE '-' END ||
		         CASE WHEN tcp_fin THEN 'f' ELSE '-' END ||
		         CASE WHEN tcp_rst THEN 'r' ELSE '-' END ||
		         CASE WHEN tcp_psh THEN 'p' ELSE '-' END ||
		         CASE WHEN tcp_urg THEN 'u' ELSE '-' END
		 END
	END
	AS \"{$shortnames['tcp_options']}\",\n";
	}
	break;
case "c_oob_time_usec": $select = $select."\toob_time_usec AS \"{$shortnames['oob_time_usec']}\",\n"; break;
case "c_oob_mark": $select = $select."\toob_mark AS \"{$shortnames['oob_mark']}\",\n"; break;
case "c_ip_tos": $select = $select."\tip_tos AS \"{$shortnames['ip_tos']}\",\n"; break;
case "c_ip_ttl": $select = $select."\tip_ttl AS \"{$shortnames['ip_ttl']}\",\n"; break;
case "c_ip_totlen": $select = $select."\tip_totlen AS \"{$shortnames['ip_totlen']}\",\n"; break;
case "c_ip_ihl": $select = $select."\tip_ihl AS \"{$shortnames['ip_ihl']}\",\n"; break;
case "c_ip_csum": $select = $select."\tip_csum AS \"{$shortnames['ip_csum']}\",\n"; break;
case "c_ip_id": $select = $select."\tip_id AS \"{$shortnames['ip_id']}\",\n"; break;
case "c_ip_fragoff": $select = $select."\tip_fragoff AS \"{$shortnames['ip_fragoff']}\",\n"; break;
case "c_tcp_seq": $select = $select."\ttcp_seq AS \"{$shortnames['tcp_seq']}\",\n"; break;
case "c_tcp_ackseq": $select = $select."\ttcp_ackseq AS \"{$shortnames['tcp_ackseq']}\",\n"; break;
case "c_tcp_window": $select = $select."\ttcp_window AS \"{$shortnames['tcp_window']}\",\n"; break;
case "c_tcp_urgp": $select = $select."\ttcp_urgp AS \"{$shortnames['tcp_urgp']}\",\n"; break;
case "c_udp_len": $select = $select."\tudp_len AS \"{$shortnames['udp_len']}\",\n"; break;
case "c_icmp_type": $select = $select."\ticmp_type AS \"{$shortnames['icmp_type']}\",\n"; break;
case "c_icmp_code": $select = $select."\ticmp_code AS \"{$shortnames['icmp_code']}\",\n"; break;
case "c_icmp_echoid": $select = $select."\ticmp_echoid AS \"{$shortnames['icmp_echoid']}\",\n"; break;
case "c_icmp_echoseq": $select = $select."\ticmp_echoseq AS \"{$shortnames['icmp_echoseq']}\",\n"; break;
case "c_icmp_gateway": $select = $select."\ticmp_gateway AS \"{$shortnames['icmp_gateway']}\",\n"; break;
case "c_icmp_fragmtu": $select = $select."\ticmp_fragmtu AS \"{$shortnames['icmp_fragmtu']}\",\n"; break;
case "c_id": $select = $select."\t$ulog.id AS \"{$shortnames['id']}\",\n"; break;
case "c_raw_mac": $select = $select."\traw_mac AS \"{$shortnames['raw_mac']}\",\n"; break;
case "c_pwsniff_user": $select = $select."\tpwsniff_user AS \"{$shortnames['pwsniff_user']}\",\n"; break;
case "c_pwsniff_pass": $select = $select."\tpwsniff_pass AS \"{$shortnames['pwsniff_pass']}\",\n"; break;
case "c_ahesp_spi": $select = $select."\tahesp_spi AS \"{$shortnames['ahesp_spi']}\",\n"; break;
case "c_earliest": if(!isset($FWLOG["nocount"])) $select = $select."\tMIN(local_time) AS \"{$shortnames['earliest']}\",\n"; break;
case "c_latest": if(!isset($FWLOG["nocount"])) $select = $select . "\tMAX(local_time) AS \"{$shortnames['latest']}\",\n"; break;
case "c_oob_earliest": if(!isset($FWLOG["nocount"])) $select = $select."\tMIN(oob_time_sec) AS \"{$shortnames['oob_earliest']}\",\n"; break;
case "c_oob_latest": if(!isset($FWLOG["nocount"])) $select = $select . "\tMAX(oob_time_sec) AS \"{$shortnames['oob_latest']}\",\n"; break;
case "c_extra":
	if (isset($config["allow_raw_sql"]) and isset($FWLOG["c_extra_value"]))
		$select = $select."\t"."{$FWLOG["c_extra_value"]}$label,\n";
	break;
	}
}

if ($select) {
	$select{strlen($select)-2}=" ";
}

// JOINs

if ($have_hostnames)
	if ($config["iptype"] == "int") {
		$join =  $join .
"          LEFT JOIN $hostnames AS s_hostnames ON $ulog.ip_saddr = s_hostnames.ip_addr
          LEFT JOIN $hostnames AS d_hostnames ON $ulog.ip_daddr = d_hostnames.ip_addr\n";
	} else {
		$join =  $join .
"          LEFT JOIN $hostnames AS s_hostnames ON $inet_aton($ulog.ip_saddr) = s_hostnames.ip_addr
          LEFT JOIN $hostnames AS d_hostnames ON $inet_aton($ulog.ip_daddr) = d_hostnames.ip_addr\n";
	}


if ($have_services)
	$join =  $join .
"          LEFT JOIN $services AS s1 ON $ulog.ip_protocol = s1.ip_protocol AND $ulog.tcp_sport = s1.port
          LEFT JOIN $services AS s2 ON $ulog.ip_protocol = s2.ip_protocol AND $ulog.tcp_dport = s2.port
          LEFT JOIN $services AS s3 ON $ulog.ip_protocol = s3.ip_protocol AND $ulog.udp_sport = s3.port
          LEFT JOIN $services AS s4 ON $ulog.ip_protocol = s4.ip_protocol AND $ulog.udp_dport = s4.port\n";

// WHERE CLAUSE

// Escape user-supplied strings

if (isset($FWLOG["w_local_hostname"])) $FWLOG["w_local_hostname"] = db("escape", $FWLOG["w_local_hostname"]);
if (isset($FWLOG["w_oob_prefix"])) $FWLOG["w_oob_prefix"] = db("escape", "{$FWLOG["w_oob_prefix"]}");
if (isset($FWLOG["w_oob_in"])) $FWLOG["w_oob_in"] = db("escape", "{$FWLOG["w_oob_in"]}");
if (isset($FWLOG["w_oob_out"])) $FWLOG["w_oob_out"] = db("escape", "{$FWLOG["w_oob_out"]}");
if (isset($FWLOG["w_ip_protocol"])) $FWLOG["w_ip_protocol"] = db("escape", "{$FWLOG["w_ip_protocol"]}");
if (isset($FWLOG["w_raw_mac"])) $FWLOG["w_raw_mac"] = db("escape", "{$FWLOG["w_raw_mac"]}");

if (isset($FWLOG["w_min_date"])) {
	$unix_min_date = strtotime($FWLOG["w_min_date"]);
	if (isset($FWLOG["w_oob_min_date"]) or !$have_local_time) {
		$where = $where . "\tAND ($ulog.oob_time_sec IS NOT NULL AND $ulog.oob_time_sec>={$unix_min_date})\n";
	} else {
		$where = $where . "\tAND ($ulog.local_time>={$unix_min_date})\n";
	}
}
if (isset($FWLOG["w_max_date"])) {
	$unix_max_date = strtotime($FWLOG["w_max_date"]);
	if (isset($FWLOG["w_oob_max_date"]) or !$have_local_time) {
		$where = $where . "\tAND ($ulog.oob_time_sec IS NOT NULL AND $ulog.oob_time_sec<={$unix_max_date})\n";
	} else {
		$where = $where . "\tAND ($ulog.local_time<={$unix_max_date})\n";
	}
}
if (isset($FWLOG["w_local_hostname"])) {
	if (isset($FWLOG["i_local_hostname"])) {$inv=" NOT";} else {$inv="";}
	$where = $where . "\tAND$inv ($ulog.local_hostname $regexp '{$FWLOG["w_local_hostname"]}')\n";
}
if (isset($FWLOG["w_oob_prefix"])) {
	if (isset($FWLOG["i_oob_prefix"])) {$inv=" NOT";} else {$inv="";}
	$where = $where . "\tAND$inv ($ulog.oob_prefix $regexp '{$FWLOG["w_oob_prefix"]}')\n";
}
if (isset($FWLOG["w_oob_in"])) {
	if (isset($FWLOG["i_oob_in"])) {$inv=" NOT";} else {$inv="";}
	$where = $where . "\tAND$inv ($ulog.oob_in $regexp '{$FWLOG["w_oob_in"]}')\n";
}
if (isset($FWLOG["w_oob_out"])) {
	if (isset($FWLOG["i_oob_out"])) {$inv=" NOT";} else {$inv="";}
	$where = $where . "\tAND$inv ($ulog.oob_out $regexp '{$FWLOG["w_oob_out"]}')\n";
}
if (isset($FWLOG["w_ip_protocol"])) {
	if (isset($FWLOG["i_ip_protocol"])) {$inv=" NOT";} else {$inv="";}
	$values = explode(",", "{$FWLOG["w_ip_protocol"]}");
	$where = $where . "\tAND$inv (";
	foreach($values as $k => $value) {
		if ($k > 0)
			 $where = $where . " OR ";
		$range = explode(":", "$value");
		if (count($range) > 1) {
			if (!is_numeric(trim($range[0])) or !is_numeric(trim($range[1]))) break;
			$where = $where . "$ulog.ip_protocol BETWEEN ".get_number($range[0])." AND ".get_number($range[1]);
		} else {
			if (!is_numeric(trim($value))) $value = getprotobyname(trim("$value"));
			$where = $where . "$ulog.ip_protocol=".get_number($value);
		}
	}
	$where = $where . ")\n";
}
if (isset($FWLOG["w_ip_saddr"])) {
	if (isset($FWLOG["i_ip_saddr"])) {$inv=" NOT";} else {$inv="";}
	$addrs = explode(",", "{$FWLOG["w_ip_saddr"]}");
	$where = $where . "\tAND$inv (";
	foreach($addrs as $k => $addr) {
		if ($k > 0)
			$where = $where . " OR ";
		if ($config["iptype"] == "int") {
			$where = $where . "($ulog.ip_saddr" . where_ip(trim($addr)) . ")";
		} else {
			$where = $where . "($inet_aton($ulog.ip_saddr)" . where_ip(trim($addr)) . ")";
		}
	}
	$where = $where . ")\n";
}
if (isset($FWLOG["w_ip_daddr"])) {
	if (isset($FWLOG["i_ip_daddr"])) {$inv=" NOT";} else {$inv="";}
	$addrs = explode(",", "{$FWLOG["w_ip_daddr"]}");
	$where = $where . "\tAND$inv (";
	foreach($addrs as $k => $addr) {
		if ($k > 0)
			$where = $where . " OR ";
		if ($config["iptype"] == "int") {
			$where = $where . "($ulog.ip_daddr" . where_ip(trim($addr)) . ")";
		} else {
			$where = $where . "($inet_aton($ulog.ip_daddr)" . where_ip(trim($addr)) . ")";
		}
	}
	$where = $where . ")\n";
}
if (isset($FWLOG["w_tcp_sport"])) $where = $where . where_num("tcp_sport");
if (isset($FWLOG["w_tcp_dport"])) $where = $where . where_num("tcp_dport");
if (isset($FWLOG["w_udp_sport"])) $where = $where . where_num("udp_sport");
if (isset($FWLOG["w_udp_dport"])) $where = $where . where_num("udp_dport");
if (isset($FWLOG["w_icmp_type"])) $where = $where . where_num("icmp_type");
if (isset($FWLOG["w_icmp_code"])) $where = $where . where_num("icmp_code");
if (isset($FWLOG["w_tcp_syn"]) or isset($FWLOG["w_tcp_fin"]) or isset($FWLOG["w_tcp_ack"])
 or isset($FWLOG["w_tcp_rst"]) or isset($FWLOG["w_tcp_psh"]) or isset($FWLOG["w_tcp_urg"])) {
	$where = $where . "\tAND ($ulog.ip_protocol<>6 OR (";
	if (isset($FWLOG["w_tcp_options"])) {
		if (isset($FWLOG["w_tcp_syn"])) {$inv="";} else {$inv="NOT ";} $where = $where . "{$inv}$ulog.tcp_syn";
		if (isset($FWLOG["w_tcp_fin"])) {$inv="";} else {$inv="NOT ";} $where = $where . " AND {$inv}$ulog.tcp_fin";
		if (isset($FWLOG["w_tcp_ack"])) {$inv="";} else {$inv="NOT ";} $where = $where . " AND {$inv}$ulog.tcp_ack";
		if (isset($FWLOG["w_tcp_rst"])) {$inv="";} else {$inv="NOT ";} $where = $where . " AND {$inv}$ulog.tcp_rst";
		if (isset($FWLOG["w_tcp_psh"])) {$inv="";} else {$inv="NOT ";} $where = $where . " AND {$inv}$ulog.tcp_psh";
		if (isset($FWLOG["w_tcp_urg"])) {$inv="";} else {$inv="NOT ";} $where = $where . " AND {$inv}$ulog.tcp_urg";
	} else {
		if (isset($FWLOG["w_tcp_syn"])) $where = $where . "$ulog.tcp_syn OR ";
		if (isset($FWLOG["w_tcp_fin"])) $where = $where . "$ulog.tcp_fin OR ";
		if (isset($FWLOG["w_tcp_ack"])) $where = $where . "$ulog.tcp_ack OR ";
		if (isset($FWLOG["w_tcp_rst"])) $where = $where . "$ulog.tcp_rst OR ";
		if (isset($FWLOG["w_tcp_psh"])) $where = $where . "$ulog.tcp_psh OR ";
		if (isset($FWLOG["w_tcp_urg"])) $where = $where . "$ulog.tcp_urg OR ";
		$where = substr($where, 0, strlen($where) - 4);
	}
	$where = "$where))\n";
} else { // match only when no flags are set
	$where = $where . "\tAND ($ulog.ip_protocol<>6 OR (";
	$where = $where . "NOT $ulog.tcp_syn AND ";
	$where = $where . "NOT $ulog.tcp_fin AND ";
	$where = $where . "NOT $ulog.tcp_ack AND ";
	$where = $where . "NOT $ulog.tcp_rst AND ";
	$where = $where . "NOT $ulog.tcp_psh AND ";
	$where = $where . "NOT $ulog.tcp_urg))\n";
}
if (isset($FWLOG["w_ip_tos"])) $where = $where . where_num("ip_tos");
if (isset($FWLOG["w_ip_ttl"])) $where = $where . where_num("ip_ttl");
if (isset($FWLOG["w_ip_ihl"])) $where = $where . where_num("ip_ihl");
if (isset($FWLOG["w_ip_totlen"])) $where = $where . where_num("ip_totlen");
if (isset($FWLOG["w_ip_id"])) $where = $where . where_num("ip_id");
if (isset($FWLOG["w_ip_csum"])) $where = $where . where_num("ip_csum");
if (isset($FWLOG["w_oob_mark"])) $where = $where . where_num("oob_mark");
if (isset($FWLOG["w_ip_fragoff"]) or isset($FWLOG["w_ip_df"]) or isset($FWLOG["w_ip_df"])) {
	if (isset($FWLOG["i_ip_fragoff"])) {$inv=" NOT";} else {$inv="";}
	$values = explode(",", "{$FWLOG["w_ip_fragoff"]}");
	$where = $where . "\tAND ($ulog.ip_fragoff IS NULL OR$inv (";
	foreach($values as $k => $value) {
		if ($k > 0)
			 $where = $where . " OR ";
		$range = explode(":", "$value");
		if (count($range) > 1) {
			if (!is_numeric(trim($range[0])) or !is_numeric(trim($range[1]))) return "";
			$where = $where . "$ulog.ip_fragoff BETWEEN "
				.(+$FWLOG["w_ip_df"] | +$FWLOG["w_ip_mf"] | get_number($range[0]))
				." AND ".(+$FWLOG["w_ip_df"] | +$FWLOG["w_ip_mf"] | get_number($range[1]));
		} else {
			if (is_numeric(trim($value))) {
				$where = $where . "$ulog.ip_fragoff=".(+$FWLOG["w_ip_df"] | +$FWLOG["w_ip_mf"] | get_number($value));
			} else {
				$where = $where . "$ulog.ip_fragoff=".(+$FWLOG["w_ip_df"] | +$FWLOG["w_ip_mf"]);
			}
		}
	}
	$where = $where . "))\n";
}
if (isset($FWLOG["w_tcp_seq"])) $where = $where . where_num("tcp_seq");
if (isset($FWLOG["w_tcp_ackseq"])) $where = $where . where_num("tcp_ackseq");
if (isset($FWLOG["w_tcp_window"])) $where = $where . where_num("tcp_window");
if (isset($FWLOG["w_tcp_urgp"])) $where = $where . where_num("tcp_urgp");
if (isset($FWLOG["w_icmp_echoid"])) $where = $where . where_num("icmp_echoid");
if (isset($FWLOG["w_icmp_echoseq"])) $where = $where . where_num("icmp_echoseq");
if (isset($FWLOG["w_icmp_fragmtu"])) $where = $where . where_num("icmp_fragmtu");
if (isset($FWLOG["w_udp_len"])) $where = $where . where_num("udp_len");
if (isset($FWLOG["w_ahesp_spi"])) $where = $where . where_num("ahesp_spi");
if (isset($FWLOG["w_raw_mac"])) {
	if (isset($FWLOG["i_raw_mac"])) {$inv=" NOT";} else {$inv="";}
	$where = $where . "\tAND$inv ($ulog.raw_mac $regexp '{$FWLOG["w_raw_mac"]}')\n";
}
if (isset($FWLOG["w_oob_time_usec"])) $where = $where . where_num("oob_time_usec");
if (isset($FWLOG["w_icmp_gateway"])) {
	if (isset($FWLOG["i_icmp_gateway"])) {$inv=" NOT";} else {$inv="";}
	$addrs = explode(",", "{$FWLOG["w_icmp_gateway"]}");
	$where = $where . "\tAND$inv (";
	foreach($addrs as $k => $addr) {
		if ($k > 0)
			$where = $where . " OR ";
		if ($config["iptype"] == "int") {
			$where = $where . "($ulog.icmp_gateway" . where_ip(trim($addr)) . ")";
		} else {
			$where = $where . "($inet_aton($ulog.icmp_gateway)" . where_ip(trim($addr)) . ")";
		}
	}
	$where = $where . ")\n";
}

if ($where) // Remove leading ' AND '
	$where = "\t    " . substr($where,5);
 
if (isset($config["allow_raw_sql"]) and isset($FWLOG["w_extra"])) {
	$FWLOG["w_extra"] = $FWLOG["w_extra"]."\n";
	if (!$where) $where = "\t1\n";
	$where = $where . "\t{$FWLOG["w_extra"]}";
}

if (!$where) 
	unset ($where); // If no criteria are specified, unset $where

// GROUP BY CLAUSE

if (!isset($FWLOG["nocount"]) and isset($FWLOG["summarize"])) {
	foreach ($columnorder as $col) {
		if (!isset($FWLOG["$col"])) continue;
		switch ("$col") {
		case "c_ip_protocol": $group = $group . "\t$ulog.ip_protocol,\n"; break;
		case "c_dport": $group = $group . "\t$q{$shortnames['dport']}$q,\n"; break;
		case "c_sport": $group = $group . "\t$q{$shortnames['sport']}$q,\n"; break;
		case "c_src_service": if ($have_services) $group = $group . "\t$q{$shortnames['src_service']}$q,\n"; break;
		case "c_dst_service": if ($have_services) $group = $group . "\t$q{$shortnames['dst_service']}$q,\n"; break;
		case "c_ip_saddr": $group = $group . "\tip_saddr,\n"; break;
		case "c_ip_daddr": $group = $group . "\tip_daddr,\n"; break;
		case "c_src_host": if ($have_hostnames) $group = $group . "\t$q{$shortnames['src_host']}$q,\n"; break;
		case "c_dst_host": if ($have_hostnames) $group = $group . "\t$q{$shortnames['dst_host']}$q,\n"; break;
		case "c_local_hostname": $group = $group . "\t$q{$shortnames['local_hostname']}$q,\n"; break;
		case "c_oob_prefix": $group = $group . "\t$q{$shortnames['oob_prefix']}$q,\n"; break;
		case "c_tcp_options": $group = $group . "\t$q{$shortnames['tcp_options']}$q,\n"; break;
		case "c_oob_in": $group = $group . "\t$q{$shortnames['oob_in']}$q,\n"; break;
		case "c_oob_out": $group = $group . "\t$q{$shortnames['oob_out']}$q,\n"; break;
		case "c_oob_time_usec": $group = $group . "\t$q{$shortnames['oob_time_usec']}$q,\n"; break;
		case "c_oob_mark": $group = $group . "\t$q{$shortnames['oob_mark']}$q,\n"; break;
		case "c_ip_tos": $group = $group . "\t$q{$shortnames['ip_tos']}$q,\n"; break;
		case "c_ip_ttl": $group = $group . "\t$q{$shortnames['ip_ttl']}$q,\n"; break;
		case "c_ip_totlen": $group = $group . "\t$q{$shortnames['ip_totlen']}$q,\n"; break;
		case "c_ip_ihl": $group = $group . "\t$q{$shortnames['ip_ihl']}$q,\n"; break;
		case "c_ip_csum": $group = $group . "\t$q{$shortnames['ip_csum']}$q,\n"; break;
		case "c_ip_id": $group = $group . "\t$q{$shortnames['ip_id']}$q,\n"; break;
		case "c_ip_fragoff": $group = $group . "\t$q{$shortnames['ip_fragoff']}$q,\n"; break;
		case "c_tcp_seq": $group = $group . "\t$q{$shortnames['tcp_seq']}$q,\n"; break;
		case "c_tcp_ackseq": $group = $group . "\t$q{$shortnames['tcp_ackseq']}$q,\n"; break;
		case "c_tcp_window": $group = $group . "\t$q{$shortnames['tcp_window']}$q,\n"; break;
		case "c_tcp_urgp": $group = $group . "\t$q{$shortnames['tcp_urgp']}$q,\n"; break;
		case "c_udp_len": $group = $group . "\t$q{$shortnames['udp_len']}$q,\n"; break;
		case "c_icmp_type": $group = $group . "\t$q{$shortnames['icmp_type']}$q,\n"; break;
		case "c_icmp_code": $group = $group . "\t$q{$shortnames['icmp_code']}$q,\n"; break;
		case "c_icmp_echoid": $group = $group . "\t$q{$shortnames['icmp_echoid']}$q,\n"; break;
		case "c_icmp_echoseq": $group = $group . "\t$q{$shortnames['icmp_echoseq']}$q,\n"; break;
		case "c_icmp_gateway": $group = $group . "\t$q{$shortnames['icmp_gateway']}$q,\n"; break;
		case "c_icmp_fragmtu": $group = $group . "\t$q{$shortnames['icmp_fragmtu']}$q,\n"; break;
		case "c_id": $group = $group . "\t$q{$shortnames['id']}$q,\n"; break;
		case "c_raw_mac": $group = $group . "\t$q{$shortnames['raw_mac']}$q,\n"; break;
		case "c_pwsniff_user": $group = $group . "\t$q{$shortnames['pwsniff_user']}$q,\n"; break;
		case "c_pwsniff_pass": $group = $group . "\t$q{$shortnames['pwsniff_pass']}$q,\n"; break;
		case "c_ahesp_spi": $group = $group . "\t$q{$shortnames['ahesp_spi']}$q,\n"; break;
		case "c_extra": if (isset($FWLOG["g_extra"])) $group = $group . "\t$q{$shortnames['extra']}$q,\n"; break;
		default: break;
		}
	}
	$group{strlen($group)-2}=" ";
}

// HAVING CLAUSE

if (isset($FWLOG["w_min_count"]) and !isset($FWLOG["nocount"]))
	$having = $having . "\tAND (count(*)>={$FWLOG["w_min_count"]})\n";
if (isset($FWLOG["w_max_count"]) and !isset($FWLOG["nocount"]))
	$having = $having . "\tAND (count(*)<={$FWLOG["w_max_count"]})\n";
if (isset($FWLOG["w_max_earliest"]) and !isset($FWLOG["nocount"])) {
	$unix_date = strtotime($FWLOG["w_max_earliest"]);
	if (isset($FWLOG["w_oob_max_earliest"])) {$column="oob_time_sec";} else {$column="local_time";}
	$having = $having . "\tAND (MIN($column)<=$unix_date)\n";
}
if (isset($FWLOG["w_min_latest"]) and !isset($FWLOG["nocount"])) {
	$unix_date = strtotime($FWLOG["w_min_latest"]);
	if (isset($FWLOG["w_oob_min_latest"])) {$column="oob_time_sec";} else {$column="local_time";}
	$having = $having . "\tAND (MAX($column)>=$unix_date)\n";
}


if (isset($config["allow_raw_sql"]) and isset($FWLOG["h_extra"])) 
	$having = $having . "\tAND ({$FWLOG["h_extra"]})\n";
if (isset($config["allow_raw_sql"]) and isset($FWLOG["h_extra_value"])) {
	$key = eregi_replace("^(.+) as ['\"]?[^'\"]*['\"]?$","\\1","{$FWLOG["c_extra_value"]}");
	$having= $having . "\tAND $key={$FWLOG["h_extra_value"]}\n";
}

if ($having) // Remove leading ' AND '
	$having = "\t    " . substr($having,5);

// ORDER BY CLAUSE

foreach ($sortorder as $a => $b) {
	if (!isset($FWLOG["$b"])) {
		unset ($sortorder["$a"]);
	}
}

foreach ($sortorder as $sort) {
	switch ("$sort") {
	case "s_count": if (!isset($FWLOG["nocount"]) and isset($FWLOG["summarize"])) {
		if (isset($FWLOG["o_count"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\t$q{$shortnames['count']}$q$ord,\n"; break;
		}
	case "s_ip_protocol": if (isset($FWLOG["o_ip_protocol"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\t$ulog.ip_protocol$ord,\n"; break;
	case "s_ip_saddr": if (isset($FWLOG["o_ip_saddr"])) {$ord=" DESC";} else {$ord=" ASC";}
		if ($config["iptype"] == "int" or $config["iptype"] == "inet") {
			$order = $order . "\tip_saddr$ord,\n";
		} else {
			$order = $order . "\t$inet_aton(ip_saddr)$ord,\n";
		}
		break;
	case "s_ip_daddr": if (isset($FWLOG["o_ip_daddr"])) {$ord=" DESC";} else {$ord=" ASC";}
		if ($config["iptype"] == "int" or $config["iptype"] == "inet") {
			$order = $order . "\tip_daddr$ord,\n";
		} else {
			$order = $order . "\t$inet_aton(ip_daddr)$ord,\n";
		}
		break;
	case "s_dport": if (isset($FWLOG["o_dport"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\t$q{$shortnames['dport']}$q$ord,\n"; break;
	case "s_sport": if (isset($FWLOG["o_sport"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\t$q{$shortnames['sport']}$q$ord,\n"; break;
	case "s_local_hostname": if (isset($FWLOG["o_local_hostname"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\tlocal_hostname$ord,\n"; break;
	case "s_oob_prefix": if (isset($FWLOG["o_oob_prefix"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\toob_prefix$ord,\n"; break;
	case "s_tcp_options": if (isset($FWLOG["o_tcp_options"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\t$q{$shortnames['tcp_options']}$q$ord,\n"; break;
	case "s_oob_in": if (isset($FWLOG["o_oob_in"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\toob_in$ord,\n"; break;
	case "s_oob_out": if (isset($FWLOG["o_oob_out"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\toob_out$ord,\n"; break;
	case "s_earliest": if (isset($FWLOG["summarize"])) {
		if (isset($FWLOG["o_earliest"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\t$q{$shortnames['earliest']}$q$ord,\n"; break;
		}
	case "s_latest": if (isset($FWLOG["summarize"])) {
		if (isset($FWLOG["o_latest"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\t$q{$shortnames['latest']}$q$ord,\n"; break;
		}
	case "s_oob_earliest": if (isset($FWLOG["summarize"])) {
		if (isset($FWLOG["o_oob_earliest"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\t$q{$shortnames['oob_earliest']}$q$ord,\n"; break;
		}
	case "s_oob_latest": if (isset($FWLOG["summarize"])) {
		if (isset($FWLOG["o_oob_latest"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\t$q{$shortnames['oob_latest']}$q$ord,\n"; break;
		}
	case "s_local_time": if (isset($FWLOG["o_local_time"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\tlocal_time$ord,\n"; break;
	case "s_oob_time_sec": if (isset($FWLOG["o_oob_time_sec"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\toob_time_sec$ord,\n"; break;
	case "s_oob_time_usec": if (isset($FWLOG["o_oob_time_usec"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\toob_time_usec$ord,\n"; break;
	case "s_oob_mark": if (isset($FWLOG["o_oob_mark"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\toob_mark$ord,\n"; break;
	case "s_ip_tos": if (isset($FWLOG["o_ip_tos"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\tip_tos$ord,\n"; break;
	case "s_ip_ttl": if (isset($FWLOG["o_ip_ttl"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\tip_ttl$ord,\n"; break;
	case "s_ip_totlen": if (isset($FWLOG["o_ip_totlen"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\tip_totlen$ord,\n"; break;
	case "s_ip_ihl": if (isset($FWLOG["o_ip_ihl"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\tip_ihl$ord,\n"; break;
	case "s_ip_csum": if (isset($FWLOG["o_ip_csum"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\tip_csum$ord,\n"; break;
	case "s_ip_id": if (isset($FWLOG["o_ip_id"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\tip_id$ord,\n"; break;
	case "s_ip_fragoff": if (isset($FWLOG["o_ip_fragoff"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\tip_fragoff$ord,\n"; break;
	case "s_tcp_seq": if (isset($FWLOG["o_tcp_seq"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\ttcp_seq$ord,\n"; break;
	case "s_tcp_ackseq": if (isset($FWLOG["o_tcp_ackseq"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\ttcp_ackseq$ord,\n"; break;
	case "s_tcp_window": if (isset($FWLOG["o_tcp_window"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\ttcp_window$ord,\n"; break;
	case "s_tcp_urgp": if (isset($FWLOG["o_tcp_urgp"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\ttcp_urgp$ord,\n"; break;
	case "s_udp_len": if (isset($FWLOG["o_udp_len"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\tudp_len$ord,\n"; break;
	case "s_icmp_type": if (isset($FWLOG["o_icmp_type"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\ticmp_type$ord,\n"; break;
	case "s_icmp_code": if (isset($FWLOG["o_icmp_code"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\ticmp_code$ord,\n"; break;
	case "s_icmp_echoid": if (isset($FWLOG["o_icmp_echoid"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\ticmp_echoid$ord,\n"; break;
	case "s_icmp_echoseq": if (isset($FWLOG["o_icmp_echoseq"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\ticmp_echoseq$ord,\n"; break;
	case "s_icmp_gateway": if (isset($FWLOG["o_icmp_gateway"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\ticmp_gateway$ord,\n"; break;
	case "s_icmp_fragmtu": if (isset($FWLOG["o_icmp_fragmtu"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\ticmp_fragmtu$ord,\n"; break;
	case "s_id": if (isset($FWLOG["o_id"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\t$ulog.id$ord,\n"; break;
	case "s_raw_mac": if (isset($FWLOG["o_raw_mac"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\traw_mac$ord,\n"; break;
	case "s_pwsniff_user": if (isset($FWLOG["o_pwsniff_user"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\tpwsniff_user$ord,\n"; break;
	case "s_pwsniff_pass": if (isset($FWLOG["o_pwsniff_pass"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\tpwsniff_pass$ord,\n"; break;
	case "s_ahesp_spi": if (isset($FWLOG["o_ahesp_spi"])) {$ord=" DESC";} else {$ord=" ASC";}
		$order = $order . "\tahesp_spi$ord,\n"; break;
	case "s_extra": 
		if (isset($config["allow_raw_sql"]) and isset($shortnames["extra"])) {
			if (isset($FWLOG["o_extra"])) {$ord=" DESC";} else {$ord=" ASC";}
			$order = $order . "\t$q{$shortnames['extra']}$q$ord,\n";	
		}
		break;
	}
}

if ($order) {
	$order{strlen($order)-2}=" ";
} else {
	unset($order);
}

if (!$select) {
	die ("\n<font size='+3' color='red'>" .
			"<strong>No Columns Selected for Report {$FWLOG["report"]}!!</strong>" .
			"</font>");
}

$query = "FROM $ulog\n$join";

if ($where)
	$query = $query . "\nWHERE $where";
 
if (isset($FWLOG["summarize"])) {
	if ($group) $query = $query . "\nGROUP BY $group";
	if ($having) $query = $query . "\nHAVING $having";
}

if ($order)
	$query = $query . "\nORDER BY $order";

if (isset($FWLOG["page_length"]) and $FWLOG["page_length"] > 0) {
	if (!isset($FWLOG["start"])) {$start = 0;} else {$start = $FWLOG["start"];}
	if ($config["db"] == "mysql") {
		$limit = "LIMIT $start, {$FWLOG["page_length"]}";
	} elseif ($config["db"] == "pgsql") {
		$limit = "LIMIT {$FWLOG["page_length"]} OFFSET $start";
	}
}

$allrows_query = "SELECT count(*) AS recordcount,\n$select\n$query";
$query = "/* EXPLAIN */ SELECT\n$select\n$query";

// Run Query

if ($query) {
	
	$source = "sql";

	db("statement", "CREATE TEMPORARY TABLE $allrows AS $query");
	db("statement", "CREATE TEMPORARY TABLE $output AS SELECT * FROM $allrows $limit;");

	$querytime = time() - $time;

	$hostupdatetime = 0;
	$servicesupdatetime = 0;
	$cachepopulatetime = 0;

	// Update hostnames and services at runtime if specified.
	if (isset($FWLOG["upd_hosts"]) or isset($FWLOG["populate_cache"])) {
		$sql_update = "yes";
		include "include/update_cache.php";
	}

	if (isset($FWLOG["upd_hosts"])) {
		$result = db("statement", "$query\n$limit");
	} else {
		$result = db("statement", "SELECT * FROM $output");
	}

	$querytime = time() - $time - $hostupdatetime - $servicesupdatetime - $cachepopulatetime;
}

// vim:set ts=4:

?>
