ÿþFilterClass

1

filter

0	1

p	n	c

0	0

	Unique Files by Hash with MSSQL	5193 /* Unique Files by Hash with MSSQL Server by Stadtpolizei Zürich (c) 2010

The script is similar to the "Unique Files by Hash" filter provided by Guidance.
Because the script uses an MSSQL server for storing the hashes and not a NameListClass, it is much faster.
In tests it filters about 220'000 entries in 3 minutes. Also the displayed filter applying time is really close
to the total time that the filter would actualy run.

To use this script, you have to had a running insance of MSSQL Server local or in your network.
Please use credentials with enough permission to create and modify databases and tables.

The filter creates a table per dongle id. So you could use this filter simultan on different EnCases in your lab.
Please do not run the filter simultan on 2 or more EnCase instances on the same examiner machine.

The express edition of the MSSQL Server 2008 R2 (free available) could be downloaded from:

http://www.microsoft.com/germany/express/products/database.aspx

*/

typelib STRM "ADODB.Stream"

class MainClass {

  String NewTname;
  int FirstRun;
  long HashesChecked;
  long TotalCount;
  STRM::Connection command;
  String DBserver,DBuser,DBpw;


////////////////////// DatabaseConnect Dialog ////////////////////////
class DialogDBconn: DialogClass {

  StringEditClass     _DBserver;
  StringEditClass     _DBuser;
  StringEditClass     _DBpw;

    DialogDBconn(MainClass main):
    DialogClass(null, "Database Connection to MSSQL-Server"),
    _DBserver(this, "DB Server Name or IP-Adress", START, START, 150, 12, 0, main.DBserver, 255, 0),
    _DBuser(this, "DB Server Username:", START, 30, 150, 12, 0, main.DBuser, 255, 0),
    _DBpw(this, "DB Server Password:", START, 55, 150, 12, WindowClass::PASSWORD, main.DBpw, 255, 0)
    {
        OKButton.SetName("Save");
    }
 }
//////////////////////////////////////////////////////////////////////
//////////////////// DB Storage /////////////////////
 void GetStorageDBConfig(uint opts) {
    StorageClass st("EnCase_MSSQL_Connect_Config", opts);
    st.Value("DBserver", DBserver);
    st.Value("DBuser", DBuser);
    st.Value("DBpw", DBpw);
 }
/////////////////////////////////////////////////////


bool FindHash(const String &HashValue, STRM::Connection &command_temp)
{
  
  STRM::Recordset rs;
  rs.Create();
  
  rs.Open("SELECT * FROM " + NewTname + " where hash='" + HashValue + "'", command_temp, STRM::adOpenForwardOnly, STRM::adLockReadOnly );
  int Counter;
  while (!rs.EOF())
  {
      Counter++;
      rs.MoveNext();
  }
  rs.Close();
  
  if (Counter == 0)
  {
    return false;
  }
  else
  {
    return true;
  }
}


void AddHash(const String &HashValue, STRM::Connection &command_temp)
{
   int dummy;
   command_temp.Execute("INSERT INTO " + NewTname + " (hash) VALUES ('" + HashValue + "')",dummy,0);
}


  bool Main(EntryClass entry) {
  
  
  int dummy;

if (FirstRun == 0)
{

////// Databaseconfig Dialog /////////////////////////////////////////////////
DialogDBconn DBDiag(this);
GetStorageDBConfig(0);
if (DBserver.GetLength() == 0)
{
  //Show Dialog
  if (DBDiag.Execute() == SystemClass::OK)
  {
      GetStorageDBConfig(StorageClass::WRITE);
  }
  else
  {
    SystemClass::Exit();
  }
}
//Connection Test Get Error and abort script if not works
STRM::Connection Conn;
Conn.Create();
Conn.Open("Provider=SQLOLEDB; Data Source=" + DBserver + "; Initial Catalog=master; User ID=" + DBuser + "; Password=" + DBpw + ";");
Conn.Close();
/////////////////////////////////////////////////////////////////////////////////

  //Connect to DB
  command.Create();
  command.SetCommandTimeout(300);
  String NewDbName = "HashListsFromEnCase";
  NewTname = "HashTab_" + SystemClass::DongleId();

  command.Open("Provider=SQLOLEDB; Data Source=" + DBserver + "; Initial Catalog=master; User ID=" + DBuser + "; Password=" + DBpw + ";");

  //Create DB if not exist
  String strSQL = "if not exists(select * from sys.databases where name = '" + NewDbName + "')\n"
                     "create database [" + NewDbName + "]";
  command.Execute(strSQL,dummy,0);
  command.Close();
  
  command.Open("Provider=SQLOLEDB; Data Source=" + DBserver + "; Initial Catalog=" + NewDbName + "; User ID=" + DBuser + "; Password=" + DBpw + ";");
  
  //Delete Temp DB
  strSQL = "if exists(select * from sys.objects where object_id = OBJECT_ID(N'[dbo].[" + NewTname + "]') and type in (N'U'))\n"
                     "DROP TABLE [" + NewTname + "]";
  command.Execute(strSQL,dummy,0);
    
  //Create Temp DB
  command.Execute("CREATE TABLE [" + NewTname + "] (\n"
        "[id] [numeric](18,0) IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,\n"
         "[hash] [nvarchar](32) NULL)",dummy,0);
         
  command.Execute("CREATE UNIQUE INDEX idxHashSingle ON " + NewTname + " (hash)",dummy,0);
 
  FirstRun = 1;
  
}

HashesChecked++;
SystemClass::StatusMessage("Check Hash " + HashesChecked);
 
 
  if (entry.HashValue())
  {
    HashClass hash = entry.HashValue();
    
    if (hash && !FindHash(hash.GetString(),command))
    {
      AddHash(hash.GetString(),command);
    }
    else
    {
      return false;
    }
    
    return true;
  }
  else
  {
    return true;
  }
 
  
  
    
  }
  
  
  
  
  
}