<?php
/*  
 * Analysis Console for Incident Databases (ACID)
 *
 * Author: Roman Danyliw <rdd@cert.org>, <roman@danyliw.com>
 *         MSSQL support by Charles Hand <charlieh@silicondefense.com>
 *
 * Copyright (C) 2000, 2001 Carnegie Mellon University
 * Portions Copyright (C) 2001 Iowa National Guard
 * (see the file 'acid_main.php' 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>acid_conf.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 */
     $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." - db version ".$this->version);
        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>
               = $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 */
     $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." - db version ".$this->version);
        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', timestamp '$func_param') ";
           return " DATE_PART('epoch', timestamp $func_param) ";
        else
           return " DATE_PART('epoch', $func_param) $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>acid_conf.php</CODE> was set to the ".
            "unrecognized database type of <CODE>'$type'</CODE>.  Only the following databases are ".
            "supported: ".
            "<PRE>
                MySQL         : 'mysql'
                PostgreSQL    : 'postgres'
                MS SQL Server : 'mssql'
             </PRE>";
     die();
  }

   $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;
}

?>