// ==========================================================================
// Author: Yee Hsu
// Date: 8/5/2008
// File: Database.cpp
//
// Desc: Demonstrates use of databases and SQL queries in C# MySQL API
// ==========================================================================
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using CommonLib;
using MySql.Data;
using MySql.Data.MySqlClient;
namespace Controller
{
public class Database
{
private MySQL mysql = null;
public bool Connect(DBInformation dbinfo)
{
this.mysql = new MySQL();
return this.mysql.Connect(dbinfo);
}
public void Disconnect()
{
this.mysql.Disconnect();
}
public Dictionary<int, Dictionary<int, UpdateBlock>> GetMapUpdates()
{
MySqlDataReader myResultSet = null;
try
{
if (this.mysql != null)
{
string sSQL = "SELECT updateid FROM uidtable GROUP BY updateid;";
myResultSet = this.mysql.ReturnExecutedQuery(sSQL);
List<int> lUpdateId = new List<int>();
Dictionary<int, Dictionary<int, UpdateBlock>> mapUpdates = new Dictionary<int, Dictionary<int, UpdateBlock>>();
// add updates to process in list
while (myResultSet.Read())
{
int nUpdateId = int.Parse(myResultSet.GetValue(0).ToString());
lUpdateId.Add(nUpdateId);
}
myResultSet.Close();
// now lets process list of update ids
foreach (int nId in lUpdateId)
{
Dictionary<int, UpdateBlock> upBlock = this.GetUpdateHistIdBlock(nId);
if (upBlock != null)
mapUpdates.Add(nId, upBlock);
}
return mapUpdates;
}
}
catch (Exception e)
{
Common.SendEmail("Exception!", e.ToString());
myResultSet.Close();
}
return null;
}
public Dictionary<int, UpdateBlock> GetUpdateHistIdBlock(int nUpdateId)
{
MySqlDataReader myResultSet = null;
try
{
if (this.mysql != null)
{
string sSQL = String.Format("SELECT updatehistid, idcount FROM uidtable WHERE updateid = '{0}' ORDER BY idcount DESC LIMIT {1};",
nUpdateId.ToString(), Configuration.nNumHistories);
myResultSet = this.mysql.ReturnExecutedQuery(sSQL);
List<string> lUpdateHistId = new List<string>();
Dictionary<int, UpdateBlock> mUpdateBlock = new Dictionary<int, UpdateBlock>();
// add updatehistid to process in list
while (myResultSet.Read())
{
string sUpdateHistId = myResultSet.GetValue(0).ToString();
lUpdateHistId.Add(sUpdateHistId);
}
myResultSet.Close();
// now lets process list of updatehist ids
int i = 1;
foreach (string sUpHistId in lUpdateHistId)
{
UpdateBlock ub = this.GetUpdateBlock(sUpHistId);
if (ub != null)
mUpdateBlock.Add(i++, ub);
}
return mUpdateBlock;
}
}
catch (Exception e)
{
Common.SendEmail("Exception!", e.ToString());
myResultSet.Close();
}
return null;
}
public UpdateBlock GetUpdateBlock(string sUpdateHistId)
{
MySqlDataReader myResultSet = null;
try
{
if (this.mysql != null)
{
string sSQL = String.Format("SELECT UNIX_TIMESTAMP(timestamp), defdate, defversion, defsignature, engineversion FROM deftable WHERE updatehistid = '{0}';", sUpdateHistId);
myResultSet = this.mysql.ReturnExecutedQuery(sSQL);
UpdateBlock upBlock = new UpdateBlock();
upBlock.diDefinitionInfo = null;
upBlock.fiFixInfo = null;
bool bHasInfo = false;
// add updatehistid to process in list
if (myResultSet.Read())
{
upBlock.diDefinitionInfo = new DefInformation();
upBlock.diDefinitionInfo.nTimeStamp = int.Parse(myResultSet.GetValue(0).ToString());
upBlock.diDefinitionInfo.sDefVersion = myResultSet.GetValue(2).ToString();
upBlock.diDefinitionInfo.sDefSignature = myResultSet.GetValue(3).ToString();
upBlock.diDefinitionInfo.sEngineVersion = myResultSet.GetValue(4).ToString();
upBlock.diDefinitionInfo.sUpdateHistId = sUpdateHistId;
// for some reason, defdate value is kinda different, lets get the correct one
string[] sDefDate = myResultSet.GetValue(1).ToString().Split(' ');
upBlock.diDefinitionInfo.sDefDate = sDefDate[0];
if (upBlock.diDefinitionInfo.sDefDate.Length > 0)
bHasInfo = true;
}
myResultSet.Close();
if (bHasInfo)
{
// now get patch links
sSQL = String.Format("SELECT * FROM urllinktable WHERE updatehistid = '{0}' LIMIT 1", sUpdateHistId);
myResultSet = this.mysql.ReturnExecutedQuery(sSQL);
upBlock.plPatchLinkInfo = null;
if (myResultSet.Read())
{
upBlock.plPatchLinkInfo = new UrlInformation();
upBlock.plPatchLinkInfo.lUrlInfos = null;
upBlock.plPatchLinkInfo.sPatchLink = myResultSet.GetValue(0).ToString();
upBlock.plPatchLinkInfo.sType = myResultSet.GetValue(1).ToString();
upBlock.plPatchLinkInfo.sPatchId = myResultSet.GetValue(2).ToString();
upBlock.plPatchLinkInfo.sUpdateHistId = sUpdateHistId;
}
myResultSet.Close();
// now get patch link instr url
if (upBlock.plPatchLinkInfo != null)
{
upBlock.plPatchLinkInfo.lUrlInfos = new List<string>();
sSQL = String.Format("SELECT * FROM urlinstrtable WHERE patchid = '{0}' GROUP BY urlinstr;", upBlock.plPatchLinkInfo.sPatchId);
myResultSet = this.mysql.ReturnExecutedQuery(sSQL);
while (myResultSet.Read())
{
string sInstrUrl = myResultSet.GetValue(0).ToString();
upBlock.plPatchLinkInfo.lUrlInfos.Add(sInstrUrl);
}
myResultSet.Close();
}
// now get virusnames/vulner
upBlock.fiFixInfo = new List<FixInformation>();
sSQL = String.Format("SELECT fixhistid FROM fixidtable WHERE updatehistid = '{0}' LIMIT 1", sUpdateHistId);
sSQL = String.Format("SELECT * FROM fixnametable WHERE fixhistid = ({0});", sSQL);
myResultSet = this.mysql.ReturnExecutedQuery(sSQL);
while (myResultSet.Read())
{
FixInformation fixinfo = new FixInformation();
fixinfo.sFixHistId = myResultSet.GetValue(0).ToString();
fixinfo.sFixName = myResultSet.GetValue(1).ToString();
fixinfo.nType = int.Parse(myResultSet.GetValue(2).ToString());
fixinfo.nSeverity = int.Parse(myResultSet.GetValue(3).ToString());
fixinfo.sUrl = myResultSet.GetValue(4).ToString();
upBlock.fiFixInfo.Add(fixinfo);
}
myResultSet.Close();
return upBlock;
}
}
}
catch (Exception e)
{
Common.SendEmail("Exception!", e.ToString());
myResultSet.Close();
}
return null;
}
public string GetVendorNameByUpdateId(int nUpdateId)
{
MySqlDataReader myResultSet = null;
try
{
if (this.mysql != null)
{
string sSQL = String.Format("SELECT vendorname FROM vendortable WHERE updateid = '{0}' GROUP BY vendorname;", nUpdateId);
myResultSet = this.mysql.ReturnExecutedQuery(sSQL);
myResultSet.Read();
string sVendorName = myResultSet.GetValue(0).ToString();
myResultSet.Close();
return sVendorName;
}
}
catch (Exception e)
{
Common.SendEmail("Exception!", e.ToString());
myResultSet.Close();
}
return null;
}
public Dictionary<int, string> GetMapVendors()
{
MySqlDataReader myResultSet = null;
try
{
if (this.mysql != null)
{
string sSQL = String.Format("SELECT * FROM vendortable;");
myResultSet = this.mysql.ReturnExecutedQuery(sSQL);
Dictionary<int, string> mapVendors = new Dictionary<int, string>();
while (myResultSet.Read())
{
int nUpdateId = int.Parse(myResultSet.GetValue(0).ToString());
string sVendorName = myResultSet.GetValue(1).ToString();
mapVendors.Add(nUpdateId, sVendorName);
}
myResultSet.Close();
return mapVendors;
}
}
catch (Exception e)
{
Common.SendEmail("Exception!", e.ToString());
myResultSet.Close();
}
return null;
}
public Dictionary<int, List<ProdInformation>> GetKeyMaps()
{
try
{
Dictionary<int, string> mapVendors = this.GetMapVendors();
Dictionary<int, List<ProdInformation>> mapKeyMaps = new Dictionary<int, List<ProdInformation>>();
List<ProdInformation> lProd = null;
foreach (KeyValuePair<int, string> kv in mapVendors)
{
lProd = this.GetProductList(kv);
mapKeyMaps.Add(kv.Key, lProd);
}
return mapKeyMaps;
}
catch (Exception e)
{
Common.SendEmail("Exception!", e.ToString());
return null;
}
}
public List<ProdInformation> GetProductList(KeyValuePair<int, string> kv)
{
MySqlDataReader myResultSet = null;
try
{
if (this.mysql != null)
{
string sSQL = String.Format("SELECT * FROM producttable WHERE updateid = {0};", kv.Key);
myResultSet = this.mysql.ReturnExecutedQuery(sSQL);
List<ProdInformation> lProd = new List<ProdInformation>();
while (myResultSet.Read())
{
ProdInformation newprod = new ProdInformation();
newprod.nUpdateId = kv.Key;
newprod.sVendorName = kv.Value;
newprod.sProductName = myResultSet.GetValue(1).ToString();
newprod.sProductVers = myResultSet.GetValue(2).ToString();
newprod.nOS = int.Parse(myResultSet.GetValue(3).ToString());
lProd.Add(newprod);
}
myResultSet.Close();
return lProd;
}
}
catch (Exception e)
{
Common.SendEmail("Exception!", e.ToString());
myResultSet.Close();
}
return null;
}
}
}