We help IT Professionals succeed at work.

Error in MySQL syntax for snortreport page

I have a system that runs snortreport, which basically just pulls information from a database and displays it on screen.

For one of the pages (https://myserver.local.lab/snortreport/sigdetail.php?signature=SHELLCODE+x86+NOOP&sigid=137&sigsid=648&FQDN=yes&beginTime=1320332359&endTime=1320418759) I am getting the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND (event.timestamp > FROM_UNIXTIME(1320332359) AND event.timestamp < FROM_UN' at line 1


I am not a php programmer but I can usually hack my way around things.  I assumed the error was in sigdetail.php since that is the page being loaded.

I was able to find 2 lines of code that looked like they could be the problem:

$DBTimeConstraint = "(event.timestamp > " . $db->timestamp($beginTime) . " AND event.timestamp < " . $db->timestamp($endTime) . ")";

$query = "SELECT event.cid, event.sid, iphdr.ip_src, iphdr.ip_dst, " . $db->timeSinceEpoch("event.timestamp") . " AS timestamp FROM event, iphdr WHERE event.cid = iphdr.cid AND event.sid = iphdr.sid AND event.signature = '".$sigid."' AND $DBTimeConstraint" or die("Error in query");


Since I don't know php well enough I was hoping that someone could help me with this.  The error says on line 1, I can not find anything wrong with line 1 in any file, the all have the basic <?php for line 1.

Comment
Watch Question

CERTIFIED EXPERT

Commented:
> You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND (event.timestamp > FROM_UNIXTIME(1320332359) AND event.timestamp < FROM_UN' at line 1

That query is getting truncated it looks like.

Can you search on FROM_UN in the code and make sure that there's not a newline after it where there shouldn't be?

It will be in the section where $db->timestamp($endTime) is being populated.
it would seem that
$query = "SELECT event.cid, event.sid, iphdr.ip_src, iphdr.ip_dst, " . $db->timeSinceEpoch("event.timestamp") . " AS timestamp FROM event, iphdr WHERE event.cid = iphdr.cid AND event.sid = iphdr.sid AND event.signature = '".$sigid."' AND $DBTimeConstraint" or die("Error in query");
$sigid is somehow adding a closing parenthesis. as you can't see it here but in the error code the first character referenced is a closing parenthesis (with no matching opening)
CERTIFIED EXPERT

Author

Commented:
@xterm

When I do a search is that file there is no line containing the string "FROM_UN"

So what I did was grep every file in that directory and came up with this:

-bash-3.2# grep "FROM_UN" *
DB_mysql.php:                  return "FROM_UNIXTIME($time)";


@Derokorian

I am not sure I am following.  Are you sauying $sigid has a closing parenthesis in the variable?
I'm saying it must, when mysql returns a syntax error near '...' 99% of the time the first character it shows is the problem. Also since you are using only and in the where clause I don't believe there is any reason to group the time. IE:
$DBTimeConstraint = "event.timestamp > " . $db->timestamp($beginTime) . " AND event.timestamp < " . $db->timestamp($endTime);

But I'm unsure. can you show more of the code defining the variables used in the query?
actually looking closer i have no idea where the paren is coming from that shows up in the error, because $sigid is within quotes O_o
CERTIFIED EXPERT

Author

Commented:
Here is all the code....
<?
// Snort Report 1.3.1
// December 21, 2005
// Copyright (C) 2000-2005 Symmetrix Technologies, LLC.
//
// 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.
//
//

//add data conversion functions
require_once("functions.php");

$title = sprintf("SNORT Report - Signature Detail (%s)", htmlspecialchars($signature));
require_once("page_top.php");

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__ . " - before retrieving events");

$signature = $_GET['signature'];
$sigid = $_GET['sigid'];
$FQDN = $_GET['FQDN'];
$beginTime = $_GET['beginTime'];
$endTime = $_GET['endTime'];

$beginTime = intval($beginTime); // A value or zero
$endTime = intval($endTime) or 0xFFFFFFFF; // A value or The End of Time
assert($beginTime < $endTime);

$URLTimeConstraint = "beginTime=$beginTime&endTime=$endTime";
$DBTimeConstraint = "(event.timestamp > " . $db->timestamp($beginTime) . " AND event.timestamp < " . $db->timestamp($endTime) . ")";

$sigid = intval($sigid) or die("Invalid signature ID");

// set up the SQL query
$query = "SELECT event.cid, event.sid, iphdr.ip_src, iphdr.ip_dst, " . $db->timeSinceEpoch("event.timestamp") . " AS timestamp FROM event, iphdr WHERE event.cid = iphdr.cid AND event.sid = iphdr.sid AND event.signature = '".$sigid."' AND $DBTimeConstraint" or die("Error in query");

// run the query on the database
$result = $db->query($query);

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__ . " - after retrieving events");

$earliestAlert = 0xFFFFFFFF;
$latestAlert = 0;

// Keyed by IP
$Sources = array();
$Destinations = array();

// Used to accelerate queries to determine dest counts
$UniqueCIDs = array();

while($myrow =  $db->farray($result)) {
	$ip_src = $myrow["ip_src"];
	$ip_dst = $myrow["ip_dst"];
	$timestamp = $myrow["timestamp"];
	
	$UniqueCIDs[$myrow["cid"]] = 1;
	
	if ($timestamp < $earliestAlert) $earliestAlert = $timestamp;
	if ($timestamp > $latestAlert) $latestAlert = $timestamp;

	if (empty($Sources[$ip_src])) {
		$Sources[$ip_src]['SigAlerts'] = 1;
		$Sources[$ip_src]['SigDests'] = array($ip_dst => 1);
		// These will be found later:
		$Sources[$ip_src]['TotalAlerts'] = 0;
		$Sources[$ip_src]['TotalDests'] = 0;
	} else {
		$Sources[$ip_src]['SigAlerts']++;
		if (empty($Sources[$ip_src]['SigDests'][$ip_dst])) {
			$Sources[$ip_src]['SigDests'][$ip_dst] = 1;
		} else {
			$Sources[$ip_src]['SigDests'][$ip_dst]++;
		}
	}
	
	if (empty($Destinations[$ip_dst])) {
		$Destinations[$ip_dst]["SigAlerts"] = 1;
		$Destinations[$ip_dst]["SigSources"] = array($ip_src => 1);
		// These will be found later:
		$Destinations[$ip_dst]['TotalAlerts'] = 0;
		$Destinations[$ip_dst]['TotalSources'] = 0;
	} else {
		$Destinations[$ip_dst]["SigAlerts"]++;
		if (empty($Destinations[$ip_dst]["SigSources"][$ip_src])) {
			$Destinations[$ip_dst]["SigSources"][$ip_src] = 1;
		} else {
			$Destinations[$ip_dst]["SigSources"][$ip_src]++;
		}
	}
}

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);
// Get the total number of alerts for each source

$query = "SELECT ip_src, COUNT(*) AS src_count FROM event INNER JOIN iphdr ON event.cid = iphdr.cid AND event.sid = iphdr.sid WHERE ip_src IN (" . implode(",", array_keys($Sources)) . ") AND $DBTimeConstraint GROUP BY iphdr.ip_src" or die("Error in query");
$result = $db->query($query);

while ($myrow = $db->farray($result)) {
	$Sources[$myrow["ip_src"]]["TotalAlerts"] = $myrow["src_count"];
}
$db->free($result);

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);
// Get the total number of destinations for each source

$query = "SELECT ip_src FROM event INNER JOIN iphdr ON event.cid = iphdr.cid AND event.sid = iphdr.sid WHERE ip_src IN (" . implode(",", array_keys($Sources)) . ") AND $DBTimeConstraint GROUP BY iphdr.ip_src, iphdr.ip_dst" or die("Error in query");
$result = $db->query($query);

while ($myrow = $db->farray($result)) {
	$Sources[$myrow["ip_src"]]["TotalDests"]++;
}
$db->free($result);

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);
// Get the total number of alerts for each destinations

$query = "SELECT ip_dst, COUNT(*) AS dst_count FROM event INNER JOIN iphdr ON event.cid = iphdr.cid AND event.sid = iphdr.sid WHERE ip_dst IN (" . implode(",", array_keys($Destinations)) . ") AND $DBTimeConstraint GROUP BY iphdr.ip_dst" or die("Error in query");
$result = $db->query($query);

while ($myrow = $db->farray($result)) {
	$Destinations[$myrow["ip_dst"]]["TotalAlerts"] = $myrow["dst_count"];
}
$db->free($result);

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);
// Get the total number of sources for each destination

$query = "SELECT ip_dst FROM event INNER JOIN iphdr ON event.cid = iphdr.cid AND event.sid = iphdr.sid WHERE ip_dst IN (" . implode(",", array_keys($Destinations)) . ") AND $DBTimeConstraint GROUP BY iphdr.ip_dst, iphdr.ip_src" or die("Error in query");
$result = $db->query($query);

while ($myrow = $db->farray($result)) {
	$Destinations[$myrow["ip_dst"]]["TotalSources"]++;
}
$db->free($result);

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);

echo '&nbsp; &nbsp; <span class="sigtitle">Signature: ' . htmlspecialchars($signature) . '</span>';

$SigRefs = $db->query("select ref_system_name, ref_tag from sig_reference inner join reference on sig_reference.ref_id = reference.ref_id inner join reference_system on reference.ref_system_id=reference_system.ref_system_id where sig_id=$sigid");

if (($db->numrows($SigRefs) > 0) or ($sigsid >= 103)) {
	echo "<p><span class=\"sigref\"><b>References:</b> ";

	if ($sigsid >= 103) {
		echo "<a href=\"http://www.snort.org/snort-db/sid.html?id=$sigsid\" target=extwin>[sid $sigsid]</a> ";
	}

	if ($db->numrows($SigRefs) > 0) {
		while (list($RefSystem, $RefTag) = $db->farray($SigRefs)) {
			$url = reference_url($RefSystem, $RefTag);
			if (!empty($url)) {
				echo "<a href=\"$url\" target=extwin>[$RefSystem $RefTag]</a> ";
			} else {
				echo "[$RefSystem $RefTag] ";
			}
		}
	}
	
	echo "</span></p>";
}

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);

?>

<p><span class="timeblock">
<b>Earliest Such Alert:</b> <?=date(FULL_DATETIME_FORMAT, $earliestAlert)?><br>
<b>Latest Such Alert:</b> <?=date(FULL_DATETIME_FORMAT, $latestAlert)?></span></p>

<!-- set up source table -->
<table>
<tr>
<td valign="top">
<table border="1" cellspacing="0" bordercolor="#000000">
<tr>
<td align="center" class="detailsigs" colspan="<? if($FQDN == "yes"){ echo "6"; } else { echo "5";} ?>">Sources Triggering This Attack Signature</td>
</tr>
<tr>
<td class="sigtitles" width=100>Source IP</td>
<? if($FQDN == "yes") { ?>
<td class="sigtitles" width=250>FQDN</td>
<? } ?>
<td class="sigtitles" width=90># Alerts (sig)</td>
<td class="sigtitles" width=90># Alerts (total)</td>
<td class="sigtitles" width=90># Dsts (sig)</td>
<td class="sigtitles" width=90># Dsts (total)</td>
</tr>

<?
	if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);

	// print all sources
	function CustomSrcSort($k1, $k2) {
		global $Sources;

		$n1 = $Sources[$k1]["SigAlerts"];
		$n2 = $Sources[$k2]["SigAlerts"];

		if ($n1 == $n2) {
			$n1b = $Sources[$k1]["TotalAlerts"];
			$n2b = $Sources[$k2]["TotalAlerts"];
			if ($n1b == $n2b) return 0;
			return ($n1b > $n2b) ? -1 : 1;
		}
		return ($n1 > $n2) ? -1 : 1;
	}

	uksort($Sources, "CustomSrcSort");

	foreach ($Sources as $IP => $Source) {
	    echo "<tr>";
		$ipAddress = dec2IP($IP);
		$ipURL = "ipdetail.php?type=src&FQDN=$FQDN&ipAddress=".$IP;

		echo "<td class=\"sigblocks\"><a href=\"$ipURL&$URLTimeConstraint\">$ipAddress</a></td>";
		if ($FQDN == "yes") echo "<td class=\"sigblocks\">" . gethostbyaddr($ipAddress) . "</td>";

		echo "<td class=\"sigblocks\">{$Source['SigAlerts']}</font></td>";
		echo "<td class=\"sigblocks\">{$Source['TotalAlerts']}</font></td>";

		echo "<td class=\"sigblocks\">" . count($Source['SigDests']) . "</font></td>";
		echo "<td class=\"sigblocks\">{$Source['TotalDests']}</font></td>";

		echo "</tr>\n";

		flush(); // Speed apparent load time (this helps a LOT with DNS lookups)
    }

	if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);
?>

</table>
</td>
</tr>
</table>
<br><br>

<!-- set up destination table -->
<table>
<tr>
<td valign="top">
<table border="1" cellspacing="0" bordercolor="#000000">
<tr>
<td align="center" class="detailsigs" colspan="<? if($FQDN == "yes"){ echo "6"; } else { echo "5";} ?>">Destinations Receiving This Attack Signature</td>
</tr>
<tr>
<td class="sigtitles" width=100>Dest IP</td>
<? if($FQDN == "yes") echo '<td class="sigtitles" width=250>FQDN</td>'; ?>
<td class="sigtitles" width=90># Alerts (sig)</td>
<td class="sigtitles" width=90># Alerts (total)</td>
<td class="sigtitles" width=90># Srcs (sig)</td>
<td class="sigtitles" width=90># Srcs (total)</td>
</tr>

<?

	if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);
	
	// Print all destinations
	function CustomDstSort($k1, $k2) {
		global $Destinations;

		$n1 = $Destinations[$k1]["SigAlerts"];
		$n2 = $Destinations[$k2]["SigAlerts"];

		if ($n1 == $n2) {
			$n1b = $Destinations[$k1]["TotalAlerts"];
			$n2b = $Destinations[$k2]["TotalAlerts"];
			if ($n1b == $n2b) return 0;
			return ($n1b > $n2b) ? -1 : 1;
		}
		return ($n1 > $n2) ? -1 : 1;
	}

	uksort($Destinations, "CustomDstSort");

	foreach ($Destinations as $IP => $Destination) {
	    echo "<tr>";
		$ipAddress = dec2IP($IP);
		$ipURL = "ipdetail.php?type=dst&FQDN=$FQDN&ipAddress=" . $IP;

		echo "<td class=\"sigblocks\"><a href=\"$ipURL&$URLTimeConstraint\">$ipAddress</a></td>";

		if ($FQDN == "yes") echo "<td class=\"sigblocks\">" . gethostbyaddr($ipAddress) . "</td>";
		echo "<td class=\"sigblocks\">{$Destination["SigAlerts"]}</td>";
		echo "<td class=\"sigblocks\">{$Destination["TotalAlerts"]}</td>";
		echo "<td class=\"sigblocks\">" . count($Destination["SigSources"]) . "</td>";
		echo "<td class=\"sigblocks\">{$Destination["TotalSources"]}</td>";
		echo "</tr>";
		flush(); // Speed apparent load time (this helps a LOT with DNS lookups)
    }
    
    if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);
?>
</table>
</td>
</tr>
</table>
<br>

<?
//Toggle between showing FQDNs
$signature = str_replace(" ","%20", $signature);			//Make URL browser-friendly
$beginTime = str_replace(" ","%20",$beginTime);
$endTime = str_replace(" ","%20",$endTime);
if ($FQDN == "yes") {
        $anchor = "Show signature without FQDNs";
        $FQDN = "no";
} else {
        $anchor = "Show signature with FQDNs";
        $FQDN = "yes";
}
$qs = "signature=$signature&sigid=$sigid&FQDN=$FQDN&beginTime=$beginTime&endTime=$endTime";
print "<b><a href=\"sigdetail.php?$qs\">$anchor</a></b><br>\n";

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);

require_once("page_bottom.php");

?>

Open in new window

See all your code, I would say the problem is one of these 4 queries:
<?php

$query = "SELECT ip_dst FROM event INNER JOIN iphdr ON event.cid = iphdr.cid AND event.sid = iphdr.sid WHERE ip_dst IN (" . implode(",", array_keys($Destinations)) . ") AND $DBTimeConstraint GROUP BY iphdr.ip_dst, iphdr.ip_src" or die("Error in query");

$query = "SELECT ip_dst, COUNT(*) AS dst_count FROM event INNER JOIN iphdr ON event.cid = iphdr.cid AND event.sid = iphdr.sid WHERE ip_dst IN (" . implode(",", array_keys($Destinations)) . ") AND $DBTimeConstraint GROUP BY iphdr.ip_dst" or die("Error in query");

$query = "SELECT ip_src FROM event INNER JOIN iphdr ON event.cid = iphdr.cid AND event.sid = iphdr.sid WHERE ip_src IN (" . implode(",", array_keys($Sources)) . ") AND $DBTimeConstraint GROUP BY iphdr.ip_src, iphdr.ip_dst" or die("Error in query");

$query = "SELECT ip_src, COUNT(*) AS src_count FROM event INNER JOIN iphdr ON event.cid = iphdr.cid AND event.sid = iphdr.sid WHERE ip_src IN (" . implode(",", array_keys($Sources)) . ") AND $DBTimeConstraint GROUP BY iphdr.ip_src" or die("Error in query");

Open in new window

CERTIFIED EXPERT

Author

Commented:
I am not a php programmer, I need a little more help please.
CERTIFIED EXPERT
Commented:
I think we need to work backwards and find the exact query that is failing, and then we can see where its badly written in the code.

Can you please turn on general query logging on your mysql server, and then reload the page so that we can see exactly what SQL query was run?  (you can probably do this with a packet capture via tcpdump too, whatever is easiest for you)

To turn on mysql logging, you have to add to the startup options in the init script "--log=/path/to/log" or "-l /path/to/log" and then restart mysql.  Are you able to restart your mysql outside of a maintenance window?
Or if that's not an option, you could comment out all the $query = "SELECT.... lines and uncomment one at a time until you get the error again (then you'll know which one did it) i realize this will cause other errors! or you could echo each $query after its defined, so we can see exactly how its sent to the mysql server.

<?php
// DERODEBUG
function derodebug($input) {
   $path = $_SERVER['DOCUMENT_ROOT'] . '/logs';
   if( !is_dir($path) ) mkdir($path);
   $output = fopen($path.'/derodebug.txt','a');
   $input = $input . PHP_EOL;
   fwrite($output,$input);
}

// Snort Report 1.3.1
// December 21, 2005
// Copyright (C) 2000-2005 Symmetrix Technologies, LLC.
//
// 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.
//
//

//add data conversion functions
require_once("functions.php");

$title = sprintf("SNORT Report - Signature Detail (%s)", htmlspecialchars($signature));
require_once("page_top.php");

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__ . " - before retrieving events");

$signature = $_GET['signature'];
$sigid = $_GET['sigid'];
$FQDN = $_GET['FQDN'];
$beginTime = $_GET['beginTime'];
$endTime = $_GET['endTime'];

$beginTime = intval($beginTime); // A value or zero
$endTime = intval($endTime) or 0xFFFFFFFF; // A value or The End of Time
assert($beginTime < $endTime);

$URLTimeConstraint = "beginTime=$beginTime&endTime=$endTime";
$DBTimeConstraint = "(event.timestamp > " . $db->timestamp($beginTime) . " AND event.timestamp < " . $db->timestamp($endTime) . ")";

$sigid = intval($sigid) or die("Invalid signature ID");

// set up the SQL query
$query = "SELECT event.cid, event.sid, iphdr.ip_src, iphdr.ip_dst, " . $db->timeSinceEpoch("event.timestamp") . " AS timestamp FROM event, iphdr WHERE event.cid = iphdr.cid AND event.sid = iphdr.sid AND event.signature = '".$sigid."' AND $DBTimeConstraint" or die("Error in query");
derodebug(__LINE__.' - '.$query);
// run the query on the database
$result = $db->query($query);

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__ . " - after retrieving events");

$earliestAlert = 0xFFFFFFFF;
$latestAlert = 0;

// Keyed by IP
$Sources = array();
$Destinations = array();

// Used to accelerate queries to determine dest counts
$UniqueCIDs = array();

while($myrow =  $db->farray($result)) {
	$ip_src = $myrow["ip_src"];
	$ip_dst = $myrow["ip_dst"];
	$timestamp = $myrow["timestamp"];
	
	$UniqueCIDs[$myrow["cid"]] = 1;
	
	if ($timestamp < $earliestAlert) $earliestAlert = $timestamp;
	if ($timestamp > $latestAlert) $latestAlert = $timestamp;

	if (empty($Sources[$ip_src])) {
		$Sources[$ip_src]['SigAlerts'] = 1;
		$Sources[$ip_src]['SigDests'] = array($ip_dst => 1);
		// These will be found later:
		$Sources[$ip_src]['TotalAlerts'] = 0;
		$Sources[$ip_src]['TotalDests'] = 0;
	} else {
		$Sources[$ip_src]['SigAlerts']++;
		if (empty($Sources[$ip_src]['SigDests'][$ip_dst])) {
			$Sources[$ip_src]['SigDests'][$ip_dst] = 1;
		} else {
			$Sources[$ip_src]['SigDests'][$ip_dst]++;
		}
	}
	
	if (empty($Destinations[$ip_dst])) {
		$Destinations[$ip_dst]["SigAlerts"] = 1;
		$Destinations[$ip_dst]["SigSources"] = array($ip_src => 1);
		// These will be found later:
		$Destinations[$ip_dst]['TotalAlerts'] = 0;
		$Destinations[$ip_dst]['TotalSources'] = 0;
	} else {
		$Destinations[$ip_dst]["SigAlerts"]++;
		if (empty($Destinations[$ip_dst]["SigSources"][$ip_src])) {
			$Destinations[$ip_dst]["SigSources"][$ip_src] = 1;
		} else {
			$Destinations[$ip_dst]["SigSources"][$ip_src]++;
		}
	}
}

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);
// Get the total number of alerts for each source

$query = "SELECT ip_src, COUNT(*) AS src_count FROM event INNER JOIN iphdr ON event.cid = iphdr.cid AND event.sid = iphdr.sid WHERE ip_src IN (" . implode(",", array_keys($Sources)) . ") AND $DBTimeConstraint GROUP BY iphdr.ip_src" or die("Error in query");
$result = $db->query($query);
derodebug(__LINE__.' - '.$query);

while ($myrow = $db->farray($result)) {
	$Sources[$myrow["ip_src"]]["TotalAlerts"] = $myrow["src_count"];
}
$db->free($result);

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);
// Get the total number of destinations for each source

$query = "SELECT ip_src FROM event INNER JOIN iphdr ON event.cid = iphdr.cid AND event.sid = iphdr.sid WHERE ip_src IN (" . implode(",", array_keys($Sources)) . ") AND $DBTimeConstraint GROUP BY iphdr.ip_src, iphdr.ip_dst" or die("Error in query");
$result = $db->query($query);
derodebug(__LINE__.' - '.$query);

while ($myrow = $db->farray($result)) {
	$Sources[$myrow["ip_src"]]["TotalDests"]++;
}
$db->free($result);

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);
// Get the total number of alerts for each destinations

$query = "SELECT ip_dst, COUNT(*) AS dst_count FROM event INNER JOIN iphdr ON event.cid = iphdr.cid AND event.sid = iphdr.sid WHERE ip_dst IN (" . implode(",", array_keys($Destinations)) . ") AND $DBTimeConstraint GROUP BY iphdr.ip_dst" or die("Error in query");
$result = $db->query($query);
derodebug(__LINE__.' - '.$query);

while ($myrow = $db->farray($result)) {
	$Destinations[$myrow["ip_dst"]]["TotalAlerts"] = $myrow["dst_count"];
}
$db->free($result);

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);
// Get the total number of sources for each destination

$query = "SELECT ip_dst FROM event INNER JOIN iphdr ON event.cid = iphdr.cid AND event.sid = iphdr.sid WHERE ip_dst IN (" . implode(",", array_keys($Destinations)) . ") AND $DBTimeConstraint GROUP BY iphdr.ip_dst, iphdr.ip_src" or die("Error in query");
$result = $db->query($query);
derodebug(__LINE__.' - '.$query);

while ($myrow = $db->farray($result)) {
	$Destinations[$myrow["ip_dst"]]["TotalSources"]++;
}
$db->free($result);

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);

echo '&nbsp; &nbsp; <span class="sigtitle">Signature: ' . htmlspecialchars($signature) . '</span>';

$SigRefs = $db->query("select ref_system_name, ref_tag from sig_reference inner join reference on sig_reference.ref_id = reference.ref_id inner join reference_system on reference.ref_system_id=reference_system.ref_system_id where sig_id=$sigid");

if (($db->numrows($SigRefs) > 0) or ($sigsid >= 103)) {
	echo "<p><span class=\"sigref\"><b>References:</b> ";

	if ($sigsid >= 103) {
		echo "<a href=\"http://www.snort.org/snort-db/sid.html?id=$sigsid\" target=extwin>[sid $sigsid]</a> ";
	}

	if ($db->numrows($SigRefs) > 0) {
		while (list($RefSystem, $RefTag) = $db->farray($SigRefs)) {
			$url = reference_url($RefSystem, $RefTag);
			if (!empty($url)) {
				echo "<a href=\"$url\" target=extwin>[$RefSystem $RefTag]</a> ";
			} else {
				echo "[$RefSystem $RefTag] ";
			}
		}
	}
	
	echo "</span></p>";
}

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);

?>

<p><span class="timeblock">
<b>Earliest Such Alert:</b> <?=date(FULL_DATETIME_FORMAT, $earliestAlert)?><br>
<b>Latest Such Alert:</b> <?=date(FULL_DATETIME_FORMAT, $latestAlert)?></span></p>

<!-- set up source table -->
<table>
<tr>
<td valign="top">
<table border="1" cellspacing="0" bordercolor="#000000">
<tr>
<td align="center" class="detailsigs" colspan="<? if($FQDN == "yes"){ echo "6"; } else { echo "5";} ?>">Sources Triggering This Attack Signature</td>
</tr>
<tr>
<td class="sigtitles" width=100>Source IP</td>
<? if($FQDN == "yes") { ?>
<td class="sigtitles" width=250>FQDN</td>
<? } ?>
<td class="sigtitles" width=90># Alerts (sig)</td>
<td class="sigtitles" width=90># Alerts (total)</td>
<td class="sigtitles" width=90># Dsts (sig)</td>
<td class="sigtitles" width=90># Dsts (total)</td>
</tr>

<?
	if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);

	// print all sources
	function CustomSrcSort($k1, $k2) {
		global $Sources;

		$n1 = $Sources[$k1]["SigAlerts"];
		$n2 = $Sources[$k2]["SigAlerts"];

		if ($n1 == $n2) {
			$n1b = $Sources[$k1]["TotalAlerts"];
			$n2b = $Sources[$k2]["TotalAlerts"];
			if ($n1b == $n2b) return 0;
			return ($n1b > $n2b) ? -1 : 1;
		}
		return ($n1 > $n2) ? -1 : 1;
	}

	uksort($Sources, "CustomSrcSort");

	foreach ($Sources as $IP => $Source) {
	    echo "<tr>";
		$ipAddress = dec2IP($IP);
		$ipURL = "ipdetail.php?type=src&FQDN=$FQDN&ipAddress=".$IP;

		echo "<td class=\"sigblocks\"><a href=\"$ipURL&$URLTimeConstraint\">$ipAddress</a></td>";
		if ($FQDN == "yes") echo "<td class=\"sigblocks\">" . gethostbyaddr($ipAddress) . "</td>";

		echo "<td class=\"sigblocks\">{$Source['SigAlerts']}</font></td>";
		echo "<td class=\"sigblocks\">{$Source['TotalAlerts']}</font></td>";

		echo "<td class=\"sigblocks\">" . count($Source['SigDests']) . "</font></td>";
		echo "<td class=\"sigblocks\">{$Source['TotalDests']}</font></td>";

		echo "</tr>\n";

		flush(); // Speed apparent load time (this helps a LOT with DNS lookups)
    }

	if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);
?>

</table>
</td>
</tr>
</table>
<br><br>

<!-- set up destination table -->
<table>
<tr>
<td valign="top">
<table border="1" cellspacing="0" bordercolor="#000000">
<tr>
<td align="center" class="detailsigs" colspan="<? if($FQDN == "yes"){ echo "6"; } else { echo "5";} ?>">Destinations Receiving This Attack Signature</td>
</tr>
<tr>
<td class="sigtitles" width=100>Dest IP</td>
<? if($FQDN == "yes") echo '<td class="sigtitles" width=250>FQDN</td>'; ?>
<td class="sigtitles" width=90># Alerts (sig)</td>
<td class="sigtitles" width=90># Alerts (total)</td>
<td class="sigtitles" width=90># Srcs (sig)</td>
<td class="sigtitles" width=90># Srcs (total)</td>
</tr>

<?

	if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);
	
	// Print all destinations
	function CustomDstSort($k1, $k2) {
		global $Destinations;

		$n1 = $Destinations[$k1]["SigAlerts"];
		$n2 = $Destinations[$k2]["SigAlerts"];

		if ($n1 == $n2) {
			$n1b = $Destinations[$k1]["TotalAlerts"];
			$n2b = $Destinations[$k2]["TotalAlerts"];
			if ($n1b == $n2b) return 0;
			return ($n1b > $n2b) ? -1 : 1;
		}
		return ($n1 > $n2) ? -1 : 1;
	}

	uksort($Destinations, "CustomDstSort");

	foreach ($Destinations as $IP => $Destination) {
	    echo "<tr>";
		$ipAddress = dec2IP($IP);
		$ipURL = "ipdetail.php?type=dst&FQDN=$FQDN&ipAddress=" . $IP;

		echo "<td class=\"sigblocks\"><a href=\"$ipURL&$URLTimeConstraint\">$ipAddress</a></td>";

		if ($FQDN == "yes") echo "<td class=\"sigblocks\">" . gethostbyaddr($ipAddress) . "</td>";
		echo "<td class=\"sigblocks\">{$Destination["SigAlerts"]}</td>";
		echo "<td class=\"sigblocks\">{$Destination["TotalAlerts"]}</td>";
		echo "<td class=\"sigblocks\">" . count($Destination["SigSources"]) . "</td>";
		echo "<td class=\"sigblocks\">{$Destination["TotalSources"]}</td>";
		echo "</tr>";
		flush(); // Speed apparent load time (this helps a LOT with DNS lookups)
    }
    
    if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);
?>
</table>
</td>
</tr>
</table>
<br>

<?
//Toggle between showing FQDNs
$signature = str_replace(" ","%20", $signature);			//Make URL browser-friendly
$beginTime = str_replace(" ","%20",$beginTime);
$endTime = str_replace(" ","%20",$endTime);
if ($FQDN == "yes") {
        $anchor = "Show signature without FQDNs";
        $FQDN = "no";
} else {
        $anchor = "Show signature with FQDNs";
        $FQDN = "yes";
}
$qs = "signature=$signature&sigid=$sigid&FQDN=$FQDN&beginTime=$beginTime&endTime=$endTime";
print "<b><a href=\"sigdetail.php?$qs\">$anchor</a></b><br>\n";

if (PROFILING) elapsedTimer(__FILE__ . ": " . __LINE__);

require_once("page_bottom.php");

?>

Open in new window


Paste that instead of your file, and then run the script you should get a new dir in your webroot called logs with a file called derodebug.txt you could paste the contents of that file here =D
CERTIFIED EXPERT

Commented:
@Derokorian:
I'd change $path to /tmp/logs - I very much doubt his web server will have permission to create a directory in the document root which is likely owned by root or hisusername, and httpd will be running as "apache".
He should very much have the ability to create a directory that's a subdir of the webroot (thats what $_SERVER['DOCUMENT_ROOT'] refers to, not the filesystem root, on wamp it resolves to c:\wamp\www not c:\)
CERTIFIED EXPERT

Commented:
That would be fine if he were running on a WAMP system, but he's not, and in the Unix environment, apache can't write to the web root.
Just verified that it works on two different unix servers that I have access to.
CERTIFIED EXPERT

Commented:
We're getting off topic here, but your debug code will 100% not work on the author's server because it will not have permission to write to the web root, nor will it on ANY Unix server unless the security was intentionally sabotaged - your statement of it working elsewhere is simply erroneous at best.

Now, back to the issue:

@savone:
If you cannot turn on mysql query logging at the server level, Derokorian actually has a good idea of running the script echo'ing debug.  You will however have to change the following line for it to work in your environment:

Line 4:  $path = $_SERVER['DOCUMENT_ROOT'] . '/logs';

Needs to be

$path = "/tmp/logs";

One of the two suggested methods should be able to give us the exact query that is being fed to the MySQL server and failing, and then I think that will help us identify what in the snortreport code is at fault.
Most Valuable Expert 2011
Top Expert 2016
Commented:
Here is what I would do... replace this:

$query = "SELECT event.cid, event.sid, iphdr.ip_src, iphdr.ip_dst, " . $db->timeSinceEpoch("event.timestamp") . " AS timestamp FROM event, iphdr WHERE event.cid = iphdr.cid AND event.sid = iphdr.sid AND event.signature = '".$sigid."' AND $DBTimeConstraint" or die("Error in query");

with something like this:

$query = "SELECT event.cid, event.sid, iphdr.ip_src, iphdr.ip_dst, " . $db->timeSinceEpoch("event.timestamp") . " AS timestamp FROM event, iphdr WHERE event.cid = iphdr.cid AND event.sid = iphdr.sid AND event.signature = '".$sigid."' AND $DBTimeConstraint";

$x = mysql_query($query);
if (!$x)
{
    echo "QUERY FAILED";
    var_dump($query);
    $msg = mysql_errno() . ' ' . mysql_error();
    die ($msg);
}

You might need to cast the things like $db->timeSinceEpoch("event.timestamp") as strings.  Just a thought.

Best to all, ~Ray
Most Valuable Expert 2011
Top Expert 2016

Commented:
One other possibility (which may be revealed by the mysql_error() function) is that you are using the MySQL reserved word "timestamp" incorrectly.  Try changing that to "my_timestamp" instead.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.