private bool InsertIntoDB(FileProperties fileProperties, SqlKey key, List <SqlParameter> sqlParameters)
{
string updateText = string.Empty;
if (!string.IsNullOrWhiteSpace(fileProperties.YaraMatchedRules))
{
List <string> newYaraMatchedRules = new List <string>();
string currentYaraRulesMatchedValue = GetExistingYaraRules(key);
if (currentYaraRulesMatchedValue != null)
{
newYaraMatchedRules.AddRange(YaraHelper.ParseDelimitedRulesString(currentYaraRulesMatchedValue));
}
newYaraMatchedRules.AddRange(YaraHelper.ParseDelimitedRulesString(fileProperties.YaraMatchedRules));
string newYaraRulesMatchedValue = YaraHelper.FormatDelimitedRulesString(newYaraMatchedRules);
SqlParameter yaraMatchedValueParameter = ParameterHelper.GetNewStringParameter("YaraRulesMatched", newYaraRulesMatchedValue);
sqlParameters.Add(yaraMatchedValueParameter);
updateText = $"UPDATE [{TableName}] SET [YaraRulesMatched] = {yaraMatchedValueParameter.ParameterName} WHERE {key.GetWhereClause()}";
}
string columnNames = sqlParameters.AsColumnString();
string values = sqlParameters.AsValuesString();
string insertStatement =
$@" INSERT INTO [{TableName}]
({columnNames},[PrevalenceCount],[DateSeen])
VALUES
({values},1,GETDATE())" ;
string commandText =
$@"DECLARE @PREVALENCECOUNT INT;
SET @PREVALENCECOUNT = ( SELECT [PrevalenceCount] FROM [{TableName}] WHERE {key.GetWhereClause()} )
SET @PREVALENCECOUNT = @PREVALENCECOUNT + 1;
IF(@PREVALENCECOUNT IS NOT NULL)
BEGIN
UPDATE [{TableName}] SET [PrevalenceCount] = @PREVALENCECOUNT WHERE {key.GetWhereClause()}
{updateText}
END
ELSE
BEGIN
{insertStatement}
END
";
return(ExecNonQuery(commandText, sqlParameters));
}
public bool PersistFileProperties(FileProperties fileProperties)
{
SqlKey key = new SqlKey(fileProperties.MFTNumber, fileProperties.SequenceNumber, fileProperties.Sha256);
List <SqlParameter> sqlParameters = new List <SqlParameter>();
sqlParameters.AddRange(key.GetSqlParameters());
sqlParameters.AddRange(new List <SqlParameter>
{
ParameterHelper.GetParameter("DriveLetter", fileProperties.DriveLetter),
ParameterHelper.GetParameter("FullPath", fileProperties.FullPath),
ParameterHelper.GetParameter("Filename", fileProperties.FileName),
ParameterHelper.GetParameter("Extension", fileProperties.Extension),
ParameterHelper.GetParameter("DirectoryLocation", fileProperties.DirectoryLocation),
ParameterHelper.GetParameter("Length", fileProperties.Length),
ParameterHelper.GetParameter("MftTimeCreation", fileProperties.MftTimeCreation),
ParameterHelper.GetParameter("MftTimeAccessed", fileProperties.MftTimeAccessed),
ParameterHelper.GetParameter("MftTimeModified", fileProperties.MftTimeModified),
ParameterHelper.GetParameter("MftTimeMftModified", fileProperties.MftTimeMftModified),
ParameterHelper.GetParameter("CreationTime", fileProperties.CreationTime),
ParameterHelper.GetParameter("LastAccessTime", fileProperties.LastAccessTime),
ParameterHelper.GetParameter("LastWriteTime", fileProperties.LastWriteTime),
ParameterHelper.GetParameter("Project", fileProperties.Project),
ParameterHelper.GetParameter("ProviderItemID", fileProperties.ProviderItemID),
ParameterHelper.GetParameter("OriginalFileName", fileProperties.OriginalFileName),
ParameterHelper.GetParameter("FileOwner", fileProperties.FileOwner),
ParameterHelper.GetParameter("FileVersion", fileProperties.FileVersion),
ParameterHelper.GetParameter("FileDescription", fileProperties.FileDescription),
ParameterHelper.GetParameter("Trademarks", fileProperties.Trademarks),
ParameterHelper.GetParameter("Copyright", fileProperties.Copyright),
ParameterHelper.GetParameter("Company", fileProperties.Company),
ParameterHelper.GetParameter("ApplicationName", fileProperties.ApplicationName),
ParameterHelper.GetParameter("Comment", fileProperties.Comment),
ParameterHelper.GetParameter("Title", fileProperties.Title),
ParameterHelper.GetParameter("Link", fileProperties.Link),
ParameterHelper.GetParameter("MimeType", fileProperties.MimeType),
ParameterHelper.GetParameter("InternalName", fileProperties.InternalName),
ParameterHelper.GetParameter("ProductName", fileProperties.ProductName),
ParameterHelper.GetParameter("Language", fileProperties.Language),
ParameterHelper.GetParameter("ComputerName", fileProperties.ComputerName),
ParameterHelper.GetParameter("Attributes", fileProperties.Attributes),
ParameterHelper.GetParameter("SHA1", fileProperties.SHA1),
ParameterHelper.GetParameter("MD5", fileProperties.MD5),
ParameterHelper.GetParameter("ImpHash", fileProperties.ImpHash),
ParameterHelper.GetParameter("IsDll", fileProperties.IsDll),
ParameterHelper.GetParameter("IsExe", fileProperties.IsExe),
ParameterHelper.GetParameter("IsDriver", fileProperties.IsDriver),
ParameterHelper.GetParameter("IsSigned", fileProperties.IsSigned),
ParameterHelper.GetParameter("IsSignatureValid", fileProperties.IsSignatureValid),
ParameterHelper.GetParameter("IsValidCertChain", fileProperties.IsValidCertChain),
ParameterHelper.GetNewParameterByType("BinaryType", fileProperties.BinaryType.GetValueOrDefault(), SqlDbType.Int),
ParameterHelper.GetNewParameterByType("CompileDate", fileProperties.CompileDate.GetValueOrDefault(), SqlDbType.DateTime2),
ParameterHelper.GetParameter("IsTrusted", fileProperties.IsTrusted),
ParameterHelper.GetParameter("CertSubject", fileProperties.CertSubject),
ParameterHelper.GetParameter("CertIssuer", fileProperties.CertIssuer),
ParameterHelper.GetParameter("CertSerialNumber", fileProperties.CertSerialNumber),
ParameterHelper.GetParameter("CertThumbprint", fileProperties.CertThumbprint),
ParameterHelper.GetParameter("CertNotBefore", fileProperties.CertNotBefore),
ParameterHelper.GetParameter("CertNotAfter", fileProperties.CertNotAfter),
ParameterHelper.GetParameter("Entropy", fileProperties.Entropy ?? 0)
});
return(InsertIntoDB(fileProperties, key, sqlParameters));
}
private string GetExistingYaraRules(SqlKey key)
{
string queryText = $"SELECT TOP 1 [YaraRulesMatched] FROM [{TableName}] WHERE {key.GetWhereClause()} AND [YaraRulesMatched] IS NOT NULL";
return((string)ExecuteScalar(queryText, key.GetSqlParameters()));
}
