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