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 <SQLiteParameter> sqlParameters = new List <SQLiteParameter>(); sqlParameters.AddRange(key.GetSqlParameters()); sqlParameters.AddRange(new List <SQLiteParameter> { SqlHelper.GetParameter("DriveLetter", fileProperties.DriveLetter), SqlHelper.GetParameter("FullPath", fileProperties.FullPath), SqlHelper.GetParameter("Filename", fileProperties.FileName), SqlHelper.GetParameter("Extension", fileProperties.Extension), SqlHelper.GetParameter("DirectoryLocation", fileProperties.DirectoryLocation), SqlHelper.GetParameter("Length", fileProperties.Length), SqlHelper.GetParameter("MftTimeCreation", fileProperties.MftTimeCreation), SqlHelper.GetParameter("MftTimeAccessed", fileProperties.MftTimeAccessed), SqlHelper.GetParameter("MftTimeModified", fileProperties.MftTimeModified), SqlHelper.GetParameter("MftTimeMftModified", fileProperties.MftTimeMftModified), SqlHelper.GetParameter("CreationTime", fileProperties.CreationTime), SqlHelper.GetParameter("LastAccessTime", fileProperties.LastAccessTime), SqlHelper.GetParameter("LastWriteTime", fileProperties.LastWriteTime), SqlHelper.GetParameter("Project", fileProperties.Project), SqlHelper.GetParameter("ProviderItemID", fileProperties.ProviderItemID), SqlHelper.GetParameter("OriginalFileName", fileProperties.OriginalFileName), SqlHelper.GetParameter("FileOwner", fileProperties.FileOwner), SqlHelper.GetParameter("FileVersion", fileProperties.FileVersion), SqlHelper.GetParameter("FileDescription", fileProperties.FileDescription), SqlHelper.GetParameter("Trademarks", fileProperties.Trademarks), SqlHelper.GetParameter("Copyright", fileProperties.Copyright), SqlHelper.GetParameter("Company", fileProperties.Company), SqlHelper.GetParameter("ApplicationName", fileProperties.ApplicationName), SqlHelper.GetParameter("Comment", fileProperties.Comment), SqlHelper.GetParameter("Title", fileProperties.Title), SqlHelper.GetParameter("Link", fileProperties.Link), SqlHelper.GetParameter("MimeType", fileProperties.MimeType), SqlHelper.GetParameter("InternalName", fileProperties.InternalName), SqlHelper.GetParameter("ProductName", fileProperties.ProductName), SqlHelper.GetParameter("Language", fileProperties.Language), SqlHelper.GetParameter("ComputerName", fileProperties.ComputerName), SqlHelper.GetParameter("Attributes", fileProperties.Attributes), SqlHelper.GetParameter("SHA1", fileProperties.SHA1), SqlHelper.GetParameter("MD5", fileProperties.MD5), SqlHelper.GetParameter("ImpHash", fileProperties.ImpHash), SqlHelper.GetParameter("IsDll", fileProperties.IsDll), SqlHelper.GetParameter("IsExe", fileProperties.IsExe), SqlHelper.GetParameter("IsDriver", fileProperties.IsDriver), SqlHelper.GetParameter("IsSigned", fileProperties.IsSigned), SqlHelper.GetParameter("IsSignatureValid", fileProperties.IsSignatureValid), SqlHelper.GetParameter("IsValidCertChain", fileProperties.IsValidCertChain), SqlHelper.GetNewParameterByType("BinaryType", fileProperties.BinaryType.GetValueOrDefault(), DbType.Int32), SqlHelper.GetNewParameterByType("CompileDate", fileProperties.CompileDate.GetValueOrDefault(), DbType.DateTime2), SqlHelper.GetParameter("IsTrusted", fileProperties.IsTrusted), SqlHelper.GetParameter("CertSubject", fileProperties.CertSubject), SqlHelper.GetParameter("CertIssuer", fileProperties.CertIssuer), SqlHelper.GetParameter("CertSerialNumber", fileProperties.CertSerialNumber), SqlHelper.GetParameter("CertThumbprint", fileProperties.CertThumbprint), SqlHelper.GetParameter("CertNotBefore", fileProperties.CertNotBefore), SqlHelper.GetParameter("CertNotAfter", fileProperties.CertNotAfter), SqlHelper.GetParameter("Entropy", fileProperties.Entropy ?? 0) }); int count = _dataClient.GetPrevalenceCount(key); if (count == -1) { _dataClient.InsertRow(sqlParameters); return(true); } count += 1; if (!string.IsNullOrWhiteSpace(fileProperties.YaraMatchedRules)) { List <string> newYaraMatchedRules = new List <string>(); string currentYaraRulesMatchedValue = _dataClient.GetExistingYaraRules(key); if (currentYaraRulesMatchedValue != null) { newYaraMatchedRules.AddRange(YaraHelper.ParseDelimitedRulesString(currentYaraRulesMatchedValue)); } newYaraMatchedRules.AddRange(YaraHelper.ParseDelimitedRulesString(fileProperties.YaraMatchedRules)); _dataClient.UpdateExistingYaraRule(key, newYaraMatchedRules); } _dataClient.UpdatePrevalenceCount(key, count); return(true); }