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())); }