public void UpdateExistingYaraRule(SqlKey key, List <string> newYaraMatchedRules)
{
string newYaraRulesMatchedValue = YaraHelper.FormatDelimitedRulesString(newYaraMatchedRules);
SQLiteParameter yaraMatchedRulesParameter = SqlHelper.GetParameter("YaraRulesMatched", newYaraRulesMatchedValue);
List <SQLiteParameter> parameters = key.GetSqlParameters().ToList();
parameters.Add(yaraMatchedRulesParameter);
string commandText = string.Format(SqlStrings.UpdateYaraRules, yaraMatchedRulesParameter.ParameterName);
ExecuteNonQuery(commandText, parameters);
}
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));
}
