<?php
/*
* SnortCenter
*
* Author: Stefan Dens, Changes for SnortCenter
* Roman Danyliw <hide@address.com>, <hide@address.com>
* MSSQL support by Charles Hand <hide@address.com>
*
* Copyright (C) 2000, 2001 Carnegie Mellon University
* Portions Copyright (C) 2001 Iowa National Guard
* (see the file 'license.txt' for license details)
*
* Purpose: database abstraction library
*
*/
class acidCon {
var $DB;
var $DB_type;
var $DB_name;
var $DB_host;
var $DB_port;
var $DB_username;
var $lastSQL;
var $version;
var $sql_trace;
function acidCon($type)
{
$this->DB_type = $type;
}
function acidDBConnect($method, $database, $host, $port, $username, $password)
{
if ( $method == DB_CONNECT )
$this->acidConnect($database, $host, $port, $username, $password);
else
$this->acidPConnect($database, $host, $port, $username, $password);
}
function acidConnect($database, $host, $port, $username, $password)
{
GLOBAL $sql_trace_mode, $sql_trace_file, $PHP_SELF;
$this->DB = NewADOConnection();
$this->DB_name = $database;
$this->DB_host = $host;
$this->DB_port = $port;
$this->DB_username = $username;
if ( $sql_trace_mode > 0 )
{
$this->sql_trace = fopen($sql_trace_file,"w");
if ( !$this->sql_trace )
{
ErrorMessage("Unable to open SQL trace file '".$sql_trace_file."'");
die();
}
}
$db = $this->DB->Connect( ( ( $port == "") ? $host : ($host.":".$port) ),
$username, $password, $database);
if ( !$db )
{
$tmp_host = ( $port == "") ? $host : ($host.":".$port);
echo '<P><B>Error connecting to DB : </B>'.
$database.'@'. $tmp_host .
'<P>Check the DB connection variables in <I>config.php</I>
<PRE>
= $alert_dbname : MySQL database name where the alerts are stored
= $alert_host : host where the database is stored
= $alert_port : port where the database is stored
= $alert_user : username into the database
= $alert_password : password for the username
</PRE>
<P>';
echo $this->acidErrorMessage();
die();
}
/* Set the database schema version number */
$tblSchema_present = $this->acidTableExists("schema");
if($tblSchema_present) {
$sql = "SELECT vseq FROM schema";
if ($this->DB_type == "mssql") $sql = "SELECT vseq FROM [schema]";
$result = $this->DB->Execute($sql);
if ( $this->acidErrorMessage() != "" )
$this->version = 0;
else
{
$myrow = $result->fields;
$this->version = $myrow[0];
$result->Close();
}
}
if ( $sql_trace_mode > 0 )
{
fwrite($this->sql_trace,
"\n--------------------------------------------------------------------------------\n");
fwrite($this->sql_trace, "Connect [".$this->DB_type."] ".$database."@".$host.":".$port." as ".$username."\n");
fwrite($this->sql_trace, "[".date ("M d Y H:i:s", time())."] ".$PHP_SELF);
fwrite($this->sql_trace,
"\n--------------------------------------------------------------------------------\n\n");
fflush($this->sql_trace);
}
return $db;
}
function acidPConnect($database, $host, $port, $username, $password)
{
GLOBAL $sql_trace_mode, $sql_trace_file, $PHP_SELF;
$this->DB = NewADOConnection();
$this->DB_name = $database;
$this->DB_host = $host;
$this->DB_port = $port;
$this->DB_username = $username;
if ( $sql_trace_mode > 0 )
{
$this->sql_trace = fopen($sql_trace_file,"a");
if ( !$this->sql_trace )
{
ErrorMessage("Unable to open SQL trace file '".$sql_trace_file."'");
die();
}
}
$db = $this->DB->PConnect( ( ( $port == "") ? $host : ($host.":".$port) ),
$username, $password, $database);
if ( !$db )
{
$tmp_host = ( $port == "") ? $host : ($host.":".$port);
echo '<P><B>Error (p)connecting to DB : </B>'.
$database.'@'. $tmp_host .
'<P>Check the DB connection variables in <I>acid_conf.php</I>
<PRE>
= $DB_dbname : MySQL database name where the alerts are stored
= $DB_host : host where the database is stored
= $DB_port : port where the database is stored
= $DB_user : username into the database
= $DB_password : password for the username
</PRE>
<P>';
echo $this->acidErrorMessage();
die();
}
/* Set the database schema version number */
$tblSchema_present = $this->acidTableExists("schema");
if($tblSchema_present) {
$sql = "SELECT vseq FROM schema";
if ($this->DB_type == "mssql") $sql = "SELECT vseq FROM [schema]";
$result = $this->DB->Execute($sql);
if ( $this->acidErrorMessage() != "" )
$this->version = 0;
else
{
$myrow = $result->fields;
$this->version = $myrow[0];
$result->Close();
}
}
if ( $sql_trace_mode > 0 )
{
fwrite($this->sql_trace,
"\n--------------------------------------------------------------------------------\n");
fwrite($this->sql_trace, "PConnect [".$this->DB_type."] ".$database."@".$host.":".$port." as ".$username."\n");
fwrite($this->sql_trace, "[".date ("M d Y H:i:s", time())."] ".$PHP_SELF);
fwrite($this->sql_trace,
"\n--------------------------------------------------------------------------------\n\n");
fflush($this->sql_trace);
}
return $db;
}
function acidClose()
{
$this->DB->Close();
}
function acidExecute($sql, $start_row=0, $num_rows=-1, $die_on_error=true )
{
GLOBAL $debug_mode, $sql_trace_mode;
/* ** Begin DB specific SQL fix-up ** */
if ($this->DB_type == "mssql")
{
$sql = eregi_replace("''", "NULL", $sql);
}
/* ** Begin optimization SQL fix-up ** */
$sql = eregi_replace("acid_event.sid > 0 AND ", "", $sql);
$sql = eregi_replace("WHERE acid_event.sid > 0", "", $sql);
/* ** End SQL fix-up ** */
$this->lastSQL = $sql;
$limit_str = "";
/* Check whether need to add a LIMIT / TOP / ROWNUM clause */
if ( $num_rows == -1 )
$rs = new acidRS($this->DB->Execute($sql), $this->DB_type);
else
{
if ( ($this->DB_type == "mysql") || ($this->DB_type == "mysqlt") ||
($this->DB_type == "maxsql") )
{
$rs = new acidRS($this->DB->Execute($sql." LIMIT ".$start_row.", ".$num_rows),
$this->DB_type);
$limit_str = " LIMIT ".$start_row.", ".$num_rows;
}
else if ( $this->DB_type == "postgres" )
{
$rs = new acidRS($this->DB->Execute($sql." LIMIT ".$num_rows." OFFSET ".$start_row),
$this->DB_type);
$limit_str = " LIMIT ".$num_rows." OFFSET ".$start_row;
}
/* Databases which do not support LIMIT (e.g. MS SQL) natively must emulated it */
else
{
$rs = new acidRS($this->DB->Execute($sql), $this->DB_type);
$i = 0;
while ( ($i < $start_row) && $rs)
{
if ( !$rs->row->EOF )
$rs->row->MoveNext();
$i++;
}
}
}
if ( $sql_trace_mode > 0 )
{
fputs($this->sql_trace, $sql."\n");
fflush($this->sql_trace);
}
if ( (!$rs || $this->acidErrorMessage() != "") && $die_on_error )
{
echo '</TABLE></TABLE></TABLE>
<FONT COLOR="#FF0000"><B>Database ERROR:</B>'.($this->acidErrorMessage()).'</FONT>'.
'<P><PRE>'.( $debug_mode > 0 ? ($this->lastSQL).$limit_str : "" ).'</PRE><P>';
die();
}
else
{
return $rs;
}
}
function acidErrorMessage()
{
GLOBAL $debug_mode;
if ( $this->DB->ErrorMsg() &&
($this->DB_type != 'mssql' || (!strstr($this->DB->ErrorMsg(), 'Changed database context to') &&
!strstr($this->DB->ErrorMsg(), 'Changed language setting to'))))
return '</TABLE></TABLE></TABLE>'.
'<FONT COLOR="#FF0000"><B>Database ERROR:</B>'.($this->DB->ErrorMsg()).'</FONT>'.
'<P><CODE>'.( $debug_mode > 0 ? $this->lastSQL : "" ).'</CODE><P>';
}
function acidTableExists($table)
{
$table_list = $this->DB->MetaTables();
if ( $table_list != "" )
{
for ( $i = 0; $i < sizeof($table_list); $i++ )
{
if ( $table_list[$i] == $table )
return 1;
}
}
return 0;
}
function acidFieldExists($table, $field)
{
$sql = "SELECT ".$field." FROM ".$table;
$result = $this->DB->Execute($sql);
if ( !$result )
return false;
else
return true;
/*
$field_list = $this->DB->MetaColumns($table);
for ( $i = 0; $i < sizeof($field_list); $i++ )
if ( $field_list[$i]->name == $field )
return true;
return false; */
}
function acidIndexExists($table, $field)
{
if ( ($this->DB_type == "mysql") || ($this->DB_type == "mysqlt") || ($this->DB_type == "maxsql") )
{
$sql = "SHOW INDEX FROM ".$table;
$result = $this->DB->Execute($sql);
if ( $this->acidErrorMessage() != "" )
return 0;
else
{
$num = $result->RecordCount();
$done = 0;
for ($i = 0; $i < $num && !$done; $i++)
{
$myrow = $result->fields;
if ( $myrow[4] == $field )
$done = 1;
$result->MoveNext();
}
$result->Close();
}
return $done;
}
else
return -1;
}
function acidInsertID()
{
/* Getting the insert ID fails on certain databases (e.g. postgres), but we may use it on the once it works
* on. This function returns -1 if the dbtype is postgres, then we can run a kludge query to get the insert
* ID. That query may vary depending upon which table you are looking at and what variables you have set at
* the current point, so it can't be here and needs to be in the actual script after calling this function
* -- srh (02/01/2001)
*/
if ( ($this->DB_type == "mysql") || ($this->DB_type == "mysqlt") ||
($this->DB_type == "maxsql") || ($this->DB_type == "mssql") )
return $this->DB->Insert_ID();
else if ($this->DB_type == "postgres" )
return -1;
}
function acidTimestampFmt($timestamp)
{
return $this->DB->DBTimeStamp($timestamp);
}
function acidSQL_YEAR($func_param, $op, $timestamp)
{
if ( ($this->DB_type == "mysql") || ($this->DB_type == "mysqlt") ||
($this->DB_type == "maxsql") || ($this->DB_type == "mssql") )
return " YEAR($func_param) $op $timestamp ";
else if ( $this->DB_type == "postgres" )
return " DATE_PART('year', $func_param) $op $timestamp ";
}
function acidSQL_MONTH($func_param, $op, $timestamp)
{
if ( ($this->DB_type == "mysql") || ($this->DB_type == "mysqlt") ||
($this->DB_type == "maxsql") || ($this->DB_type == "mssql") )
return " MONTH($func_param) $op $timestamp ";
else if ( $this->DB_type == "postgres" )
return " DATE_PART('month', $func_param) $op $timestamp ";
}
function acidSQL_DAY($func_param, $op, $timestamp)
{
if ( ($this->DB_type == "mysql") || ($this->DB_type == "mysqlt") || ($this->DB_type == "maxsql") )
return " DAYOFMONTH($func_param) $op $timestamp ";
else if ( $this->DB_type == "postgres" )
return " DATE_PART('day', $func_param) $op $timestamp ";
else if ( $this->DB_type == "mssql" )
return " DAY($func_param) $op $timestamp ";
}
function acidSQL_HOUR($func_param, $op, $timestamp)
{
if ( ($this->DB_type == "mysql") || ($this->DB_type == "mysqlt") || ($this->DB_type == "maxsql") )
return " HOUR($func_param) $op $timestamp ";
else if ( $this->DB_type == "postgres" )
return " DATE_PART('hour', $func_param) $op $timestamp ";
else if ( $this->DB_type == "mssql" )
return " DATEPART(hh, $func_param) $op $timestamp ";
}
function acidSQL_MINUTE($func_param, $op, $timestamp)
{
if ( ($this->DB_type == "mysql") || ($this->DB_type == "mysqlt") || ($this->DB_type == "maxsql") )
return " MINUTE($func_param) $op $timestamp ";
else if ( $this->DB_type == "postgres" )
return " DATE_PART('minute', $func_param) $op $timestamp ";
else if ( $this->DB_type == "mssql" )
return " DATEPART(mi, $func_param) $op $timestamp ";
}
function acidSQL_SECOND($func_param, $op, $timestamp)
{
if ( ($this->DB_type == "mysql") || ($this->DB_type == "mysqlt") || ($this->DB_type == "maxsql") )
return " SECOND($func_param) $op $timestamp ";
else if ( $this->DB_type == "postgres" )
return " DATE_PART('second', $func_param) $op $timestamp ";
else if ( $this->DB_type == "mssql" )
return " DATEPART(ss, $func_param) $op $timestamp ";
}
function acidSQL_UNIXTIME($func_param, $op, $timestamp)
{
if ( ($this->DB_type == "mysql") || ($this->DB_type == "mysqlt") || ($this->DB_type == "maxsql") )
return " UNIX_TIMESTAMP($func_param) $op $timestamp ";
else if ( $this->DB_type == "postgres" )
{
if ( ($op == "") && ($timestamp == "") )
/* Catches the case where I want to get the UNIXTIME of a constant
* i.e. DATE_PART('epoch', timestamp) > = DATE_PART('epoch', timestamp '20010124')
* (This one /\ )
*/
return " DATE_PART('epoch', $func_param::timestamp) ";
else
return " DATE_PART('epoch', $func_param::timestamp) $op $timestamp ";
}
else if ($this->DB_type == "mssql")
{
return " DATEDIFF(ss, '1970-1-1 00:00:00', $func_param) $op $timestamp ";
}
}
function acidSQL_TIMESEC($func_param, $op, $timestamp)
{
if ( ($this->DB_type == "mysql") || ($this->DB_type == "mysqlt") || ($this->DB_type == "maxsql") )
return " TIME_TO_SEC($func_param) $op $timestamp ";
else if ( $this->DB_type == "postgres" )
{
if ( ($op == "") && ($timestamp == "") )
return " DATE_PART('second', DATE_PART('day', '$func_param') ";
else
return " DATE_PART('second', DATE_PART('day', $func_param) ) $op $timestamp ";
}
else if ( $this->DB_type == "mssql" )
{
if ( ($op == "") && ($timestamp == "") )
return " DATEPART(ss, DATEPART(dd, $func_parm) ";
else
return " DATEPART(ss, DATE_PART(dd, $func_param) ) $op $timestamp ";
}
}
function acidGetDBversion()
{
return $this->version;
}
}
class acidRS {
var $row;
var $DB_type;
function acidRS($id, $type)
{
$this->row = $id;
$this->DB_type = $type;
}
function acidFetchRow()
{
if ( !$this->row->EOF )
{
$temp = $this->row->fields;
$this->row->MoveNext();
return $temp;
}
else
return "";
}
function acidColCount()
{
return $this->row->FieldCount();
}
function acidRecordCount()
{
/* MS SQL Server 7, MySQL, Sybase, and Postgres natively support this function */
if ( ($this->DB_type == "mysql") || ($this->DB_type == "mysqlt") || ($this->DB_type == "maxsql") ||
($this->DB_type == "mssql") || ($this->DB_type == "sybase") || ($this->DB_type == "postgres") )
return $this->row->RecordCount();
/* Otherwise we need to emulate this functionality */
else
{
$i = 0;
while ( !$this->row->EOF )
{
++$i;
$this->row->MoveNext();
}
return $i;
}
}
function acidFreeRows()
{
$this->row->Close();
}
}
function getSafeSQLString($str)
{
$t = str_replace("\\", "\\\\", $str);
if ($this->DB_type != "mssql")
$t = str_replace("'", "\'", $t);
else
$t = str_replace("'", "''", $t);
$t = str_replace("\"", "\\\\\"", $t);
return $t;
}
function VerifyDBAbstractionLib($path)
{
GLOBAL $debug_mode;
if ( $debug_mode > 0 )
echo "Checking for DB abstraction lib in '$path'<BR>";
if ( is_file($path) )
return true;
else
{
echo '<P><B>Error loading the DB Abstraction library: </B> from "'.$path.
'"<P>Check the DB abstraction library variable <CODE>$DBlib_path</CODE>'.
' in <CODE>acid_conf.php</CODE>
<P>
The underlying database library currently used is ADODB, that can be downloaded
at <A HREF="http://php.weblogs.com/adodb">http://php.weblogs.com/adodb</A>';
die();
}
}
function NewACIDDBConnection($path, $type)
{
GLOBAL $debug_mode;
if ( !(($type == "mysql") || ($type == "mysqlt") || ($type == "maxsql") ||
($type == "postgres") || ($type == "mssql")) )
{
echo "<B>Invalid Database Type Specified</B>".
"<P>The variable <CODE>\$DBtype</CODE> in <CODE>config.php</CODE> was set to the ".
"unrecognized database type of <CODE>'$type'</CODE>. Only the following databases are ".
"supported: ".
"<PRE>
MySQL : 'mysql'
PostgreSQL : 'postgres'
</PRE>";
die();
}
/* Export ADODB_DIR for use by ADODB */
define('ADODB_DIR', $path);
$GLOBALS['ADODB_DIR'] = $path;
$last_char = substr($path, strlen($path)-1, 1);
if ( $debug_mode > 1 )
echo "Original path = '".$path."'<BR>";
if ( $last_char == "\\" || $last_char == "/" )
{
if ( $debug_mode > 1 ) echo "Attempting to load: '".$path."adodb.inc.php'<BR>";
VerifyDBAbstractionLib($path."adodb.inc.php");
include($path."adodb.inc.php");
}
else if ( strstr($path,"/") || $path == "" )
{
if ( $debug_mode > 1 ) echo "Attempting to load: '".$path."/adodb.inc.php'<BR>";
VerifyDBAbstractionLib($path."/adodb.inc.php");
include($path."/adodb.inc.php");
}
else if ( strstr($path,"\\") )
{
if ( $debug_mode > 1 ) echo "Attempting to load: '".$path."\\adodb.inc.php'<BR>";
VerifyDBAbstractionLib($path."\\adodb.inc.php");
include($path."\\adodb.inc.php");
}
ADOLoadCode($type);
return new AcidCon($type);
}
function MssqlKludgeValue($text)
{
$mssql_kludge = "";
for ($i = 0 ; $i < strlen($text) ; $i++)
{
$mssql_kludge = $mssql_kludge."[".
substr($text,$i, 1)."]";
}
return $mssql_kludge;
}
?>