<?php
/*
$Id: sql.php 579 2014-05-17 21:23:44Z bhockney $
(C) 2003-2014 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");

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

// SELECT CLAUSE

$fl = array_flip(db ("listfields", "$data_t[0]", $link));

foreach ($columnorder as $col) {
	if (!isset($FWLOG["$col"])) continue;
	$f = preg_replace('/^c_/','',"$col");
	switch ("$col") {
		case "c_count":
			if(isset($FWLOG["summarize"]))
				$select .= "\tcount(*) AS $q{$shortnames['count']}$q,\n";
			break;
		case "c_local_time":
			if (!$config["have_local_time"]) {
				$select .= "\toob_time_sec as \"{$shortnames["oob_time_sec"]}\",\n";
			} else {
				$select .= "\t$f as \"{$shortnames[$f]}\",\n";
			}
 			break;
		case "c_local_hostname":
		case "c_oob_time_sec":
		case "c_oob_prefix":
		case "c_oob_in":
		case "c_oob_out":
		case "c_oob_time_usec":
		case "c_oob_mark":
		case "c_oob_family":
		case "c_ip_totlen":
		case "c_ip_ihl":
		case "c_ip_csum":
		case "c_ip6_flowlabel":
		case "c_ip6_payloadlen":
		case "c_tcp_seq":
		case "c_tcp_ackseq":
		case "c_tcp_window":
		case "c_tcp_urgp":
		case "c_udp_len":
		case "c_icmp_fragmtu":
		case "c_raw_type":
		case "c_oob_protocol":
		case "c_mac_saddr_str":
		case "c_mac_daddr_str":
		case "c_oob_hook":
		case "c_pwsniff_user":
		case "c_pwsniff_pass":
		case "c_ahesp_spi":
			$select .= "\t$f AS $q{$shortnames[$f]}$q,\n";
			break;
		case "c_sname":
			$select .= "\tt.$f AS $q{$shortnames[$f]}$q,\n";
			break;
		case "c_id":
			$select .= "\tt.{$config["data_id"]} AS $q{$shortnames[$f]}$q,\n";
			break;
		case "c_raw_mac":
			$select .= "\tt.{$config["mac_str"]} AS $q{$shortnames[$f]}$q,\n";
			break;
		case "c_earliest":
			if(isset($FWLOG["summarize"]))
				if (!$config["have_local_time"]) {
					$select .= "\tMIN(oob_time_sec) AS $q{$shortnames['earliest']}$q,\n";
				} else {
					$select .= "\tMIN(local_time) AS $q{$shortnames['earliest']}$q,\n";
				}
			break;
		case "c_latest":
			if(isset($FWLOG["summarize"]))
				if (!$config["have_local_time"]) {
					$select .= "\tMAX(oob_time_sec) AS $q{$shortnames['latest']}$q,\n";
				} else {
					$select .= "\tMAX(local_time) AS $q{$shortnames['latest']}$q,\n";
				}
			break;
		case "c_oob_earliest":
			if(isset($FWLOG["summarize"]))
				$select .= "\tMIN(oob_time_sec) AS $q{$shortnames['oob_earliest']}$q,\n";
			break;
		case "c_oob_latest":
			if(isset($FWLOG["summarize"]))
				$select .= "\tMAX(oob_time_sec) AS $q{$shortnames['oob_latest']}$q,\n";
			break;
		case "c_extra":
			if (isset($config["allow_raw_sql"]) and isset($FWLOG["c_extra_value"])
				and (isset($FWLOG["summarize"]) or isset($FWLOG["g_extra"])))
				$select .= "\t"."{$c_extra_split["key"]} AS $q{$c_extra_split["label"]}$q,\n";
			break;
		case "c_ip_protocol":
			$select .=
			"\tCASE WHEN t.ip_protocol=6  THEN '".getprotobynumber(6)."'\n" .
			"\t     WHEN t.ip_protocol=17 THEN '".getprotobynumber(17)."'\n" .
			"\t     WHEN t.ip_protocol=1  THEN '".getprotobynumber(1)."'\n" .
			"\t     WHEN t.ip_protocol=58 THEN '".getprotobynumber(58)."'\n" .
			"\t     ELSE"; 
		 	if ($config["db"] == "mysql") {
		 		$select .= " t.ip_protocol\n";
			} elseif ($config["db"] == "pgsql") {
				$select .= " text(t.ip_protocol)\n";
			}
			$select .=
			"\tEND\n" .
			"\tAS $q{$shortnames['ip_protocol']}$q,\n";
			break;

		case "c_ip_saddr":
		case "c_ip_daddr":
			if ($config["iptype"] == "int") {
				$select .= "\t$inet_ntoa($f{$config["addr_type"]})" .
				" AS $q{$shortnames[$f]}$q,\n";
			} elseif ($config["iptype"] == "bin") {
				$select .= "\t$inet_ntop(t.oob_family, $f{$config["addr_type"]})" .
				" AS $q{$shortnames[$f]}$q,\n";
			} else {
				$select .= "\t$f{$config["addr_type"]}" .
				" AS $q{$shortnames[$f]}$q,\n";
			}
			break;
		case "c_icmptype":
		case "c_icmpcode":
		case "c_icmpechoid":
		case "c_icmpechoseq":
			$fi = preg_replace('/^icmp/','',"$f");
 			$select .= 
			"\tCASE WHEN t.ip_protocol=1  THEN icmp_$fi\n";
			if (isset($fl["icmpv6_$fi"])) $select .=
			"\t     WHEN t.ip_protocol=58 THEN icmpv6_$fi\n";
			$select .=
			"\t     ELSE NULL\n" .
			"\tEND\n" .
			"\tAS $q{$shortnames[$f]}$q,\n";
			break;
		case "c_icmp_gateway":
			if ($config["iptype_g"] == "int") {
				$select .= "\t$inet_ntoa($f)" .
				" AS $q{$shortnames[$f]}$q,\n";
			} else {
				$select .= "\t$f" .
				" AS $q{$shortnames[$f]}$q,\n";
			}
			break;
		case "c_src_host":
		case "c_dst_host":
			if ($config["have_hostnames"]) {
				$select .=
				"\tCASE WHEN $f[0]_hostnames.hostname IS NULL\n" .
				"\t     THEN '-'\n" .
				"\t     ELSE $f[0]_hostnames.hostname\n" .
				"\tEND\n" .
				"\tAS $q{$shortnames[$f]}$q,\n";
			}
			break;
		case "c_sport":
		case "c_dport":
	 			$select .= 
				"\tCASE WHEN t.ip_protocol=6  THEN tcp_$f\n" .
				"\t     WHEN t.ip_protocol=17 THEN udp_$f\n" .
				"\t     ELSE 65536\n" .
				"\tEND\n" .
				"\tAS $q{$shortnames[$f]}$q,\n";
			break;
		case "c_src_service":
			if ($config["have_services"]) {
				$select .= 
				"\tCASE WHEN t.ip_protocol=6  THEN\n" .
				"\t          CASE WHEN s1.service IS NULL\n" .
				"\t               THEN '-'\n" .
				"\t               ELSE s1.service\n" .
				"\t          END\n" .
				"\t     WHEN t.ip_protocol=17 THEN\n" .
				"\t          CASE WHEN s2.service IS NULL\n" .
				"\t               THEN '-'\n" .
				"\t               ELSE s2.service\n" .
				"\t          END\n" .
				"\t     ELSE '-'\n" .
				"\tEND\n" .
				"\tAS $q{$shortnames['src_service']}$q,\n";
			}
			break;
		case "c_dst_service":
			if ($config["have_services"]) {
				$select .= 
				"\tCASE WHEN t.ip_protocol=6  THEN\n" .
				"\t          CASE WHEN s3.service IS NULL\n" .
				"\t               THEN '-'\n" .
				"\t               ELSE s3.service\n" .
				"\t          END\n" .
				"\t     WHEN t.ip_protocol=17 THEN\n" .
				"\t          CASE WHEN s4.service IS NULL\n" .
				"\t               THEN '-'\n" .
				"\t               ELSE s4.service\n" .
				"\t          END\n" .
				"\t     ELSE '-'\n" .
				"\tEND\n" .
				"\tAS $q{$shortnames['dst_service']}$q,\n";
			}
			break;
		case "c_ip_ttl":
			if (!isset($fl["oob_family"]))
				$select .= "\t$f AS $q{$shortnames[$f]}$q,\n";
			else {
	 			$select .= 
				"\tCASE WHEN t.oob_family=2  THEN $f\n";
				if (isset($fl["ip6_hoplimit"])) $select .=
				"\t     WHEN t.oob_family=10 THEN ip6_hoplimit\n"; 
				$select .= 
				"\t     ELSE NULL\n" .
				"\tEND\n" .
				"\tAS $q{$shortnames[$f]}$q,\n";
			}
			break;
		case "c_ip_tos":
			if (!isset($fl["oob_family"]))
				$select .= "\t$f AS $q{$shortnames[$f]}$q,\n";
			else {
	 			$select .= 
				"\tCASE WHEN t.oob_family=2  THEN $f\n";
				if (isset($fl["ip6_priority"])) $select .=
				"\t     WHEN t.oob_family=10 THEN ip6_priority\n"; 
				$select .= 
				"\t     ELSE NULL\n" .
				"\tEND\n" .
				"\tAS $q{$shortnames[$f]}$q,\n";
			}
			break;
		case "c_ip_id":
			if (!isset($fl["oob_family"]))
				$select .= "\t$f AS $q{$shortnames[$f]}$q,\n";
			else {
	 			$select .= 
				"\tCASE WHEN t.oob_family=2  THEN $f\n";
				if (isset($fl["ip6_fragid"])) $select .=
				"\t     WHEN t.oob_family=10 THEN ip6_fragid\n"; 
				$select .= 
				"\t     ELSE NULL\n" .
				"\tEND\n" .
				"\tAS $q{$shortnames[$f]}$q,\n";
			}
			break;
		case "c_ip_fragoff":
			if (!isset($fl["oob_family"]))
				$select .= "\t$f AS $q{$shortnames[$f]}$q,\n";
			else {
	 			$select .= 
				"\tCASE WHEN t.oob_family=2  THEN $f\n";
				if (isset($fl["ip6_ip6_fragoff"])) $select .=
				"\t     WHEN t.oob_family=10 THEN ip6_ip6_fragoff\n"; 
				$select .= 
				"\t     ELSE NULL\n" .
				"\tEND\n" .
				"\tAS $q{$shortnames[$f]}$q,\n";
			}
			break;
		case "c_tcp_options":
			if ($config['db'] == "mysql") {
				$select .= 
				"\tif(t.ip_protocol<>6,'',\n" .
				"\t  if(tcp_syn AND NOT (tcp_urg OR tcp_psh OR tcp_rst OR tcp_ack OR tcp_fin),' SYN  ',\n" .
				"\t    concat(\n" .
				"\t    if( tcp_syn, 's', '-'),\n" .
				"\t    if( tcp_ack, 'a', '-'),\n" .
				"\t    if( tcp_fin, 'f', '-'),\n" .
				"\t    if( tcp_rst, 'r', '-'),\n" .
				"\t    if( tcp_psh, 'p', '-'),\n" .
				"\t    if( tcp_urg, 'u', '-'))))\n" .
				"\tAS $q{$shortnames['tcp_options']}$q,\n";
			} elseif ($config['db'] == "pgsql") {
				$select .= 
				"\tCASE WHEN t.ip_protocol=6 THEN\n" .
				"\t    CASE WHEN tcp_syn AND NOT (tcp_urg OR tcp_psh OR tcp_rst OR tcp_ack OR tcp_fin) THEN ' SYN  '\n" .
				"\t         ELSE \n" .
				"\t             CASE WHEN tcp_syn THEN 's' ELSE '-' END ||\n" .
				"\t             CASE WHEN tcp_ack THEN 'a' ELSE '-' END ||\n" .
				"\t             CASE WHEN tcp_fin THEN 'f' ELSE '-' END ||\n" .
				"\t             CASE WHEN tcp_rst THEN 'r' ELSE '-' END ||\n" .
				"\t             CASE WHEN tcp_psh THEN 'p' ELSE '-' END ||\n" .
				"\t             CASE WHEN tcp_urg THEN 'u' ELSE '-' END\n" .
				"\t    END\n" .
				"\tEND\n" .
				"\tAS $q{$shortnames['tcp_options']}$q,\n";
			}
			break;
		default:
			break;
	}
}

if (!isset($FWLOG["summarize"]) and !isset($FWLOG["c_sname"]))
	$select .= "\tt.sname AS $q{$shortnames['sname']}$q,\n";

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

// JOINs

if ($config["have_hostnames"])
	if ($config["wfwl_iptype"] == "int") {
		if ($config["iptype"] == "int") {
			if (isset($FWLOG["c_src_host"]))
				$join .= "\tLEFT JOIN $hostnames AS s_hostnames ON t.ip_saddr{$config["addr_type"]} = s_hostnames.ip_addr\n";
			if (isset($FWLOG["c_dst_host"]))
				$join .= "\tLEFT JOIN $hostnames AS d_hostnames ON t.ip_daddr{$config["addr_type"]} = d_hostnames.ip_addr\n";
		} else {
			if (isset($FWLOG["c_src_host"]))
				$join .= "\tLEFT JOIN $hostnames AS s_hostnames ON $inet_aton(t.ip_saddr{$config["addr_type"]}) = s_hostnames.ip_addr\n";
			if (isset($FWLOG["c_dst_host"]))
				$join .= "\tLEFT JOIN $hostnames AS d_hostnames ON $inet_aton(t.ip_daddr{$config["addr_type"]}) = d_hostnames.ip_addr\n";
		}
	} elseif ($config["wfwl_iptype"] == "inet") {
		if ($config["iptype"] == "inet") {
			if (isset($FWLOG["c_src_host"]))
				$join .= "\tLEFT JOIN $hostnames AS s_hostnames ON t.ip_saddr{$config["addr_type"]} = s_hostnames.ip_addr\n";
			if (isset($FWLOG["c_dst_host"]))
				$join .= "\tLEFT JOIN $hostnames AS d_hostnames ON t.ip_daddr{$config["addr_type"]} = d_hostnames.ip_addr\n";
		} elseif ($config["iptype"] == "int") {
			if (isset($FWLOG["c_src_host"]))
				$join .= "\tLEFT JOIN $hostnames AS s_hostnames ON $inet_ntoa(t.ip_saddr{$config["addr_type"]})::inet = s_hostnames.ip_addr\n";
			if (isset($FWLOG["c_dst_host"]))
				$join .= "\tLEFT JOIN $hostnames AS d_hostnames ON $inet_ntoa(t.ip_daddr{$config["addr_type"]})::inet = d_hostnames.ip_addr\n";
		} else { // iptype = text
			if (isset($FWLOG["c_src_host"]))
				$join .= "\tLEFT JOIN $hostnames AS s_hostnames ON trim(t.ip_saddr{$config["addr_type"]})::inet = s_hostnames.ip_addr\n";
			if (isset($FWLOG["c_dst_host"]))
				$join .= "\tLEFT JOIN $hostnames AS d_hostnames ON trim(t.ip_daddr{$config["addr_type"]})::inet = d_hostnames.ip_addr\n";
		}
	} elseif ($config["wfwl_iptype"] == "bin") {
		if ($config["iptype"] == "bin") {
			if (isset($FWLOG["c_src_host"]))
				$join .= "\tLEFT JOIN $hostnames AS s_hostnames ON t.{$q}ip_saddr{$q} = s_hostnames.ip_addr\n";
			if (isset($FWLOG["c_dst_host"]))
				$join .= "\tLEFT JOIN $hostnames AS d_hostnames ON t.{$q}ip_daddr{$q} = d_hostnames.ip_addr\n";
		} elseif ($config["iptype"] == "int")  {
			if (isset($FWLOG["c_src_host"]))
				$join .= "\tLEFT JOIN $hostnames AS s_hostnames ON $inet_pton(".AF_INET6.", $inet_ntoa(t.ip_saddr{$config["addr_type"]})) = s_hostnames.ip_addr\n";
			if (isset($FWLOG["c_dst_host"]))
				$join .= "\tLEFT JOIN $hostnames AS d_hostnames ON $inet_pton(".AF_INET6.", $inet_ntoa(t.ip_daddr{$config["addr_type"]})) = d_hostnames.ip_addr\n";
		} else { // iptype = text
			if (isset($FWLOG["c_src_host"]))
				$join .= "\tLEFT JOIN $hostnames AS s_hostnames ON $inet_pton(".AF_INET6.", t.ip_saddr{$config["addr_type"]}) = s_hostnames.ip_addr\n";
			if (isset($FWLOG["c_dst_host"]))
				$join .= "\tLEFT JOIN $hostnames AS d_hostnames ON $inet_pton(".AF_INET6.", t.ip_daddr{$config["addr_type"]}) = d_hostnames.ip_addr\n";
		}
	}


if ($config["have_services"]) {
	if (isset($FWLOG["c_src_service"]))
		$join .= "\tLEFT JOIN $services AS s1 ON t.ip_protocol = s1.ip_protocol AND t.tcp_sport = s1.port\n" .
				 "\tLEFT JOIN $services AS s2 ON t.ip_protocol = s2.ip_protocol AND t.udp_sport = s2.port\n";
	if (isset($FWLOG["c_dst_service"]))
		$join .= "\tLEFT JOIN $services AS s3 ON t.ip_protocol = s3.ip_protocol AND t.tcp_dport = s3.port\n" .
				 "\tLEFT JOIN $services AS s4 ON t.ip_protocol = s4.ip_protocol AND t.udp_dport = s4.port\n";
}

// WHERE CLAUSE

foreach($FWLOG as $k => $v) {
	if (! preg_match('/^w_/',$k)) continue;
		$f = preg_replace('/^w_/','',"$k");
		if (isset($FWLOG["i_{$f}"])) {$inv=" NOT";} else {$inv="";}
		switch ("$k") {
			case "w_local_hostname":
			case "w_oob_prefix":
			case "w_oob_in":
			case "w_oob_out":
			case "w_mac_saddr_str":
			case "w_mac_daddr_str":
			case "w_raw_mac":
			case "w_pwsniff_user":
			case "w_pwsniff_pass":
				// regex match
				// Escape user-supplied strings
				$v = db("escape", "{$v}", $link);
				if ($f == "raw_mac")
					$f = $config["mac_str"];
				if ($inv) {
					$where .= "\tAND (t.$f IS NULL OR NOT t.$f{$config['tcast']} $regexp '$v')\n";
				} else {
					if ("$v" == '^$') {
						$where .= "\tAND (t.$f IS NULL OR t.$f{$config['tcast']} $regexp '$v')\n";
					} else {
						$where .= "\tAND (t.$f{$config['tcast']} $regexp '$v')\n";
					}
				}
				break;
			case "w_tcp_sport":
			case "w_tcp_dport":
			case "w_tcp_seq":
			case "w_tcp_ackseq":
			case "w_tcp_window":
			case "w_tcp_urgp":
				$where .= where_num("$f", "t.ip_protocol=6");
				break;
			case "w_udp_sport":
			case "w_udp_dport":
			case "w_udp_len":
				$where .= where_num("$f", "t.ip_protocol=17");
				break;
			case "w_icmp_type":
			case "w_icmp_code":
				$where .= where_num("$f", "t.ip_protocol=1");
				break;
			case "w_icmp_echoid":
			case "w_icmp_echoseq":
			case "w_icmp_fragmtu":
				// these fields may be null for icmp packets
				$where .= where_num("$f", "t.ip_protocol=1 AND t.$f IS NOT NULL");
				break;
			case "w_icmpv6_type":
			case "w_icmpv6_code":
				$where .= where_num("$f", "t.ip_protocol=58");
				break;
			case "w_icmpv6_echoid":
			case "w_icmpv6_echoseq":
				// these fields may be null for icmpv6 packets
				$where .= where_num("$f", "t.ip_protocol=58 AND t.$f IS NOT NULL");
				break;

			case "w_ahesp_spi":
				$where .= where_num("$f", "t.ip_protocol BETWEEN 50 AND 51");
				break;
			case "w_ip_tos":
			case "w_ip_ihl":
			case "w_ip_totlen":
			case "w_ip_csum":
			case "w_ip6_flowlabel":
			case "w_ip6_payloadlen":
			case "w_oob_mark":
			case "w_oob_time_usec":
			case "w_oob_family":
			case "w_raw_type":
			case "w_oob_protocol":
			case "w_oob_hook":
				// numeric match
				$where .= where_num("$f", false);
				break;
			case "w_ip_protocol":
				$v = db("escape", "{$v}", $link);
				$values = explode(",", "{$v}");
				$where .= "\tAND$inv (";
				foreach($values as $j => $value) {
					if ($j > 0)
						$where .= " OR ";
					$range = explode(":", "$value");
					if (count($range) > 1) {
						if (!is_numeric(trim($range[0])) or !is_numeric(trim($range[1]))) break;
						$where .= "t.$f BETWEEN ".get_number($range[0])." AND ".get_number($range[1]);
					} else {
						if (!is_numeric(trim($value))) $value = getprotobyname(trim("$value"));
						$where .= "t.$f=".get_number($value);
					}
				}
				$where .= ")\n";
				break;
			case "w_min_date":
				$unix_min_date = strtotime($FWLOG["w_min_date"]);
				if (isset($FWLOG["w_oob_min_date"]) or !$config["have_local_time"]) {
					$where .= "\tAND (t.oob_time_sec IS NOT NULL AND t.oob_time_sec>={$unix_min_date})\n";
				} else {
					$where .= "\tAND (t.local_time>={$unix_min_date})\n";
				}
				break;
			case "w_max_date":
				$unix_max_date = strtotime($FWLOG["w_max_date"]);
				if (isset($FWLOG["w_oob_max_date"]) or !$config["have_local_time"]) {
					$where .= "\tAND (t.oob_time_sec IS NOT NULL AND t.oob_time_sec<={$unix_max_date})\n";
				} else {
					$where .= "\tAND (t.local_time<={$unix_max_date})\n";
				}
				break;
			case "w_ip_saddr":
			case "w_ip_daddr":
				$addrs = explode(",", "$v");
				$where .= "\tAND$inv (";
				foreach($addrs as $j => $addr) {
					if ($j > 0)
						$where .= " OR ";
					if ($config["iptype"] <> "text") {
						$where .= "(".where_ip("t.$f{$config["addr_type"]}", trim($addr), $config["iptype"], $link).")";
					} else {
						$where .= "(".where_ip("$inet_aton(t.$f{$config["addr_type"]})", trim($addr), $config["iptype"], $link).")";
					}
				}
				$where .= ")\n";
				break;
			case "w_icmp_gateway":
				$addrs = explode(",", "$v");
				$where .= "\tAND (NOT (t.ip_protocol=1 AND t.$f IS NOT NULL) OR$inv ";
				foreach($addrs as $j => $addr) {
					if ($j > 0)
						$where .= " OR ";
					if ($config["iptype_g"] <> "text") {
						$where .= "(".where_ip("t.{$f}", trim($addr), $config["iptype_g"], $link).")";
					} else {
						$where .= "(".where_ip("$inet_aton(t.{$f})", trim($addr), $config["iptype_g"], $link).")";
					}
				}
				$where .= ")\n";
				break;
			case "w_extra": 
				if (isset($config["allow_raw_sql"]))
					$where .= "\tAND ($v)\n";
				break;
			case "w_ip_ttl":
				if (!isset($fl["oob_family"]))
					$where .= where_num("$f", false);
				else {
					$where .= where_num("$f", "t.oob_family=2");
					if (isset($fl["ip6_hoplimit"])) {
						$FWLOG["w_ip6_hoplimit"] = $FWLOG["w_ip_ttl"];
						$where .= where_num("ip6_hoplimit", "t.oob_family=10");
					}
				}
				break;
			case "w_ip_tos":
				if (!isset($fl["oob_family"]))
					$where .= where_num("$f", false);
				else {
					$where .= where_num("$f", "t.oob_family=2");
					if (isset($fl["ip6_priority"])) {
						$FWLOG["w_ip6_priority"] = $FWLOG["w_ip_tos"];
						$where .= where_num("ip6_priority", "t.oob_family=10");
					}
				}

			case "w_ip_id":
				if (!isset($fl["oob_family"]))
					$where .= where_num("$f", false);
				else {
					$where .= where_num("$f", "t.oob_family=2");
					if (isset($fl["ip6_fragid"])) {
						$FWLOG["w_ip6_fragid"] = $FWLOG["w_ip_id"];
						$where .= where_num("ip6_fragid", "t.oob_family=10");
					}
				}
				break;
			case "w_ip_fragoff":
				if (isset($fl["oob_family"]) and isset($fl["ip6_fragoff"])) {
					$FWLOG["w_ip6_fragoff"] = $FWLOG["w_ip_fragoff"];
					$where .= where_num("ip6_fragoff", "t.oob_family=10");
				}
				break;
			default:
				break;
		}
}

// tcp flags and fragoff deal with mutiple keys simultaneously

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 .= "\tAND (t.ip_protocol<>6 OR (";
	if (isset($FWLOG["w_tcp_options"])) {
		if (isset($FWLOG["w_tcp_syn"])) {$inv="";} else {$inv="NOT ";} $where .= "{$inv}t.tcp_syn";
		if (isset($FWLOG["w_tcp_fin"])) {$inv="";} else {$inv="NOT ";} $where .= " AND {$inv}t.tcp_fin";
		if (isset($FWLOG["w_tcp_ack"])) {$inv="";} else {$inv="NOT ";} $where .= " AND {$inv}t.tcp_ack";
		if (isset($FWLOG["w_tcp_rst"])) {$inv="";} else {$inv="NOT ";} $where .= " AND {$inv}t.tcp_rst";
		if (isset($FWLOG["w_tcp_psh"])) {$inv="";} else {$inv="NOT ";} $where .= " AND {$inv}t.tcp_psh";
		if (isset($FWLOG["w_tcp_urg"])) {$inv="";} else {$inv="NOT ";} $where .= " AND {$inv}t.tcp_urg";
	} else {
		if (isset($FWLOG["w_tcp_syn"])) $where .= "t.tcp_syn OR ";
		if (isset($FWLOG["w_tcp_fin"])) $where .= "t.tcp_fin OR ";
		if (isset($FWLOG["w_tcp_ack"])) $where .= "t.tcp_ack OR ";
		if (isset($FWLOG["w_tcp_rst"])) $where .= "t.tcp_rst OR ";
		if (isset($FWLOG["w_tcp_psh"])) $where .= "t.tcp_psh OR ";
		if (isset($FWLOG["w_tcp_urg"])) $where .= "t.tcp_urg OR ";
		$where = substr($where, 0, strlen($where) - 4);
	}
	$where = "$where))\n";
} else { // match only when no flags are set
	$where .= "\tAND (t.ip_protocol<>6 OR (";
	$where .= "NOT t.tcp_syn AND ";
	$where .= "NOT t.tcp_fin AND ";
	$where .= "NOT t.tcp_ack AND ";
	$where .= "NOT t.tcp_rst AND ";
	$where .= "NOT t.tcp_psh AND ";
	$where .= "NOT t.tcp_urg))\n";
}
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 .= "\tAND (t.ip_fragoff IS NULL OR$inv (";
	foreach($values as $k => $value) {
		if ($k > 0)
			 $where .= " OR ";
		$range = explode(":", "$value");
		if (count($range) > 1) {
			if (!is_numeric(trim($range[0])) or !is_numeric(trim($range[1]))) return "";
			$where .= "t.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 .= "t.ip_fragoff=".(+$FWLOG["w_ip_df"] | +$FWLOG["w_ip_mf"] | get_number($value));
			} else {
				$where .= "t.ip_fragoff=".(+$FWLOG["w_ip_df"] | +$FWLOG["w_ip_mf"]);
			}
		}
	}
	$where .= "))\n";
}

if ($where) // Remove leading ' AND ' if present
	$where = preg_replace('/^\s*AND\s+(.*)$/si',"\t    \\1","$where");

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

// GROUP BY CLAUSE

if (isset($FWLOG["summarize"])) {
	foreach ($columnorder as $col) {
		if (!isset($FWLOG["$col"])) continue;
		$f = preg_replace('/^c_/','',"$col");
		switch ("$col") {
			case "c_ip_protocol":
				$group .= "\tt.$f,\n";
				break;
			case "c_ip_saddr":
			case "c_ip_daddr":
				$group .= "\t$f{$config["addr_type"]},\n";
				break;
			case "c_icmp_gateway":
				$group .= "\t$f,\n";
				break;
			case "c_extra":
				if (!isset($FWLOG["g_extra"])) break;
				// fall through
			case "c_dport":
			case "c_sport":
			case "c_src_service":
			case "c_dst_service":
			case "c_src_host":
			case "c_dst_host":
			case "c_local_hostname":
			case "c_oob_prefix":
			case "c_tcp_options":
			case "c_oob_in":
			case "c_oob_out":
			case "c_oob_time_usec":
			case "c_oob_mark":
			case "c_oob_family":
			case "c_ip_tos":
			case "c_ip_ttl":
			case "c_ip_totlen":
			case "c_ip_ihl":
			case "c_ip_csum":
			case "c_ip_id":
			case "c_ip_fragoff":
			case "c_ip6_flowlabel":
			case "c_ip6_payloadlen":
			case "c_tcp_seq":
			case "c_tcp_ackseq":
			case "c_tcp_window":
			case "c_tcp_urgp":
			case "c_udp_len":
			case "c_icmptype":
			case "c_icmpcode":
			case "c_icmpechoid":
			case "c_icmpechoseq":
			case "c_icmp_fragmtu":
			case "c_id":
			case "c_sname":
			case "c_raw_type":
			case "c_raw_mac":
			case "c_oob_protocol":
			case "c_mac_saddr_str":
			case "c_mac_daddr_str":
			case "c_oob_hook":
			case "c_pwsniff_user":
			case "c_pwsniff_pass":
			case "c_ahesp_spi":
				$group .= "\t$q{$shortnames[$f]}$q,\n";
				break;
			default:
				break;
		}
	}
	$group{strlen($group)-2}=" ";
}

// HAVING CLAUSE

if (isset($FWLOG["w_min_count"]) and isset($FWLOG["summarize"]))
	$having .= "\tAND (count(*)>={$FWLOG["w_min_count"]})\n";
if (isset($FWLOG["w_max_count"]) and isset($FWLOG["summarize"]))
	$having .= "\tAND (count(*)<={$FWLOG["w_max_count"]})\n";

if (isset($FWLOG["w_max_earliest"]) and isset($FWLOG["summarize"])) {
	$unix_date = strtotime($FWLOG["w_max_earliest"]);
	if (isset($FWLOG["w_oob_max_earliest"]) or !$config["have_local_time"]) {
		$column="oob_time_sec";
	} else {
		$column="local_time";
	}
	$having .= "\tAND (MIN($column)<=$unix_date)\n";
}
if (isset($FWLOG["w_min_latest"]) and isset($FWLOG["summarize"])) {
	$unix_date = strtotime($FWLOG["w_min_latest"]);
	if (isset($FWLOG["w_oob_min_latest"]) or !$config["have_local_time"]) {
		$column="oob_time_sec";
	} else {
		$column="local_time";
	}
	$having .= "\tAND (MAX($column)>=$unix_date)\n";
}

if (isset($config["allow_raw_sql"]) and isset($FWLOG["summarize"]) and isset($FWLOG["h_extra"])) 
	$having .= "\tAND ({$FWLOG["h_extra"]})\n";

if ($having) // Remove leading ' AND ' if present
	$having = preg_replace('/^\s*AND\s+(.*)$/si',"\t    \\1","$having");

// ORDER BY CLAUSE

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

foreach ($sortorder as $sort) {
	$f = preg_replace('/^s_/','',"$sort");
	if (isset($FWLOG["o_{$f}"])) {$ord=" DESC";} else {$ord=" ASC";}
	switch ("$sort") {
		case "s_local_time":
			if (!$config["have_local_time"]) {
				$order .= "\toob_time_sec$ord,\n";
			} else {
				$order .= "\t$f$ord,\n";
			}
 			break;
		case "s_oob_time_sec":
		case "s_oob_time_usec":
		case "s_oob_mark":
		case "s_ip_totlen":
		case "s_ip_ihl":
		case "s_ip_csum":
		case "s_ip6_flowlabel":
		case "s_ip6_payloadlen":
		case "s_tcp_seq":
		case "s_tcp_ackseq":
		case "s_tcp_window":
		case "s_tcp_urgp":
		case "s_udp_len":
		case "s_icmp_fragmtu":
		case "s_pwsniff_user":
		case "s_pwsniff_pass":
		case "s_ahesp_spi":
		case "s_raw_type":
		case "s_oob_protocol":
		case "s_mac_saddr_str":
		case "s_mac_daddr_str":
		case "s_oob_hook":
		case "s_local_hostname":
		case "s_oob_prefix":
		case "s_oob_in":
		case "s_oob_out":
		case "s_oob_family":
			$order .= "\t$f$ord,\n";
			break;
		case "s_ip_protocol":
		case "s_sname":
			$order .= "\tt.$f$ord,\n";
			break;
		case "s_id":
			$order .= "\tt.{$config["data_id"]}$ord,\n";
			break;
		case "s_raw_mac":
			$order .= "\tt.{$config["mac_str"]}$ord,\n";
			break;
		case "s_count":
		case "s_earliest":
		case "s_latest":
		case "s_oob_earliest":
		case "s_oob_latest":
			if (! isset($FWLOG["summarize"])) continue;
			// fall through
		case "s_ip_ttl":
		case "s_ip_tos":
		case "s_ip_id":
		case "s_ip_fragoff":
		case "s_dport":
		case "s_sport":
		case "s_tcp_options":
		case "s_icmptype":
		case "s_icmpcode":
		case "s_icmpechoid":
		case "s_icmpechoseq":
			$order .= "\t$q{$shortnames[$f]}$q$ord,\n";
			break;
		case "s_extra": 
			if (isset($config["allow_raw_sql"]) and isset($shortnames["extra"])
				and (isset($FWLOG["summarize"]) or isset($FWLOG["g_extra"])))
					$order .= "\t$q{$shortnames[$f]}$q$ord,\n";	
			break;
		case "s_ip_saddr":
		case "s_ip_daddr":
			if ($config["iptype"] == "text") {
				$order .= "\t$inet_aton($f{$config["addr_type"]})$ord,\n";
			} else {
				$order .= "\t$f{$config["addr_type"]}$ord,\n";
			}
			break;
		case "s_icmp_gateway":
			if ($config["iptype_g"] == "text") {
				$order .= "\t$inet_aton($f)$ord,\n";
			} else {
				$order .= "\t$f$ord,\n";
			}
			break;
		default:
			break;
	}
}

if (isset($FWLOG["c_icmptype"]) or isset($FWLOG["c_icmpcode"]) or isset($FWLOG["c_icmpechoid"]) or isset($FWLOG["c_icmpechoseq"])) {
	$select = "\tt.oob_family AS {$q}oob_family_t$q,\n$select";
	if (isset($FWLOG["summarize"])) {
		if ($group)
			$group = "\t{$q}oob_family_t$q,\n$group";
		else
			$group = "\t{$q}oob_family_t$q\n";
	}
}

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

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

if (!isset($where))
	$where = "$true\n";

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

if ($order)
	$t_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";
	}
}

// $o_query is the effective query made on logged data
// but is never actually sent to the server; it is output
// when the be verbose option is selected.
$o_query = "SELECT\n$select\nFROM table(s) t\n$join\nWHERE $where\n$t_query\n$limit";

if (isset($FWLOG["upd_hosts"]) or isset($FWLOG["populate_cache"]) or isset($FWLOG["populate_cache_full"])) {
	if (isset($FWLOG["c_src_host"]) or isset($FWLOG["populate_cache_full"])) {
		$select = "\tip_saddr{$config["addr_type"]} AS {$q}ip_saddr_t$q,\n$select";
		if (isset($FWLOG["summarize"])) {
			if ($group)
				$group = "\t{$q}ip_saddr_t$q,\n$group";
			else
				$group = "\t{$q}ip_saddr_t$q\n";
		}
	}
	if (isset($FWLOG["c_dst_host"]) or isset($FWLOG["populate_cache_full"])) {
		$select = "\tip_daddr{$config["addr_type"]} AS {$q}ip_daddr_t$q,\n$select";
		if (isset($FWLOG["summarize"])) {
			if ($group)
				$group = "\t{$q}ip_daddr_t$q,\n$group";
			else
				$group = "\t{$q}ip_daddr_t$q\n";
		}
	}
	if (isset($FWLOG["c_src_service"]) or isset($FWLOG["populate_cache_full"])) {
		$select = "\ttcp_sport AS {$q}tcp_sport_t$q,\n$select";
		$select = "\tudp_sport AS {$q}udp_sport_t$q,\n$select";
		if (isset($FWLOG["summarize"])) {
			if ($group)
				$group = "\t{$q}tcp_sport_t$q,\n\t{$q}udp_sport_t$q,\n$group";
			else
				$group = "\t{$q}tcp_sport_t$q,\n\t{$q}udp_sport_t$q\n";
		}
	}
	if (isset($FWLOG["c_dst_service"]) or isset($FWLOG["populate_cache_full"])) {
		$select = "\ttcp_dport AS {$q}tcp_dport_t$q,\n$select";
		$select = "\tudp_dport AS {$q}udp_dport_t$q,\n$select";
		if (isset($FWLOG["summarize"])) {
			if ($group)
				$group = "\t{$q}tcp_dport_t$q,\n\t{$q}udp_dport_t$q,\n$group";
			else
				$group = "\t{$q}tcp_dport_t$q,\n\t{$q}udp_dport_t$q\n";
		}
	}
	if (isset($FWLOG["c_src_service"]) or isset($FWLOG["c_dst_service"]) or isset($FWLOG["populate_cache_full"])) {
		$select = "\tt.ip_protocol AS {$q}ip_protocol_t$q,\n$select";
		if (isset($FWLOG["summarize"])) {
			if ($group)
				$group = "\t{$q}ip_protocol_t$q,\n$group";
			else
				$group = "\t{$q}ip_protocol_t$q\n";
		}
	}

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

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

$query = "SELECT\n$select\nFROM $allrows t\n$join\n$t_query";

// Run Query

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

	$j = 0;
	$fieldlist = array();
	foreach ($data_t as $table) {
		if (isset($FWLOG["w_sname"]) and !preg_match("/{$FWLOG["w_sname"]}/", "$table")) 
			continue;
		$allrows_select = "SELECT\n\t" . cast("'$table'", "{$config['cast_char']}") . " AS sname";
		if ($config["iptype"] == "bin") {
			$allrows_select .= ",\n\tt.ip_saddr_bin AS ${q}ip_saddr${q}";
			$allrows_select .= ",\n\tt.ip_daddr_bin AS ${q}ip_daddr${q}";
		}
		if ($j++ == 0) {
			$allrows_select .= ",\n\tt.*\nFROM $table AS t\n\nWHERE $where";
			db ("statement", "CREATE TEMPORARY TABLE $allrows AS\n$allrows_select", $link);
			$fieldlist = db ("listfields", "$table", $link);
		} else {
			if ($j == 2 and "{$config["db"]}" == "mysql" and ($my_version[0] < 4
				or ($my_version[0] == 4 and $my_version[1] == 0)))
					db("statement", "ALTER TABLE $allrows CHANGE COLUMN sname sname char(32)", $link);
			$th = array_flip(db("listfields", "$table", $link));
			foreach ($fieldlist as $f) {
				if (isset($th["$f"])) {
					$allrows_select .= ", $f";
				} else {
					$allrows_select .= ", NULL";
				}
			}
			$allrows_select .= "\nFROM $table AS t\n\nWHERE $where";
			db ("statement", "INSERT INTO $allrows\n$allrows_select", $link);
		}
	}

	db("statement", "CREATE TEMPORARY TABLE $tmp_output AS\n$query;", $link);

	if (isset($FWLOG["summarize"])) {
		$rownum = 0;
		$result = db("nextrow",	db("statement", "SELECT count(*) AS {$q}count{$q} FROM $allrows", $link));
		$matched_records = $result["count"];
	}

	$rownum = 0;
	$result = db("nextrow", db("statement", "SELECT count(*) AS {$q}count{$q} FROM $tmp_output", $link));
	$matched_rows = $result["count"];

	if ($limit) {
		db("statement", "CREATE TEMPORARY TABLE $output AS\n\tSELECT * FROM $tmp_output $limit;", $link);
		db("statement", "DROP TABLE $tmp_output;", $link);
	} else
		db("statement", "ALTER TABLE $tmp_output RENAME TO $output;", $link);

	$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"]) or isset($FWLOG["populate_cache_full"])) {
		$sql_update = "yes";
		db("statement", "ALTER TABLE $output RENAME TO $tmp_output;", $link);
		include "include/update_cache.php";
		db("statement", "DROP TABLE $tmp_output", $link);
		$query = preg_replace("/\t(t\\.)?([a-z_]+) AS {$q}\\2_t$q,?\n/", '', $query);
		$query = preg_replace("/\t{$q}[a-z_]+_t$q,?\n/", '', $query);
		db("statement", "CREATE TEMPORARY TABLE $output AS\n$query\n$limit;", $link);
	}

	$result = db("statement", "SELECT * FROM $output", $link);

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

// vim:set ts=4:

?>
