private bool GetUseGlobalValue(SQLiteCommand sqliteCommand, string columnName) { try { sqliteCommand.CommandText = _ddlReader.ReadDdl(_storedProcedureBase + "Select.ReportUseGlobalValueUserSettings.dml", assembly); sqliteCommand.CommandText = sqliteCommand.CommandText.Replace("[COLUMN_NAME]", columnName); using (SQLiteDataReader sqliteDataReader = sqliteCommand.ExecuteReader()) { if (sqliteDataReader.HasRows) { while (sqliteDataReader.Read()) { if (bool.Parse(sqliteDataReader[columnName].ToString())) { return true; } return false; } } return false; } } catch (Exception exception) { LogWriter.LogError($"Unable to obtain user global filter requirements for '{columnName}'."); throw exception; } }
public void CreateCklFromHardware(Hardware hardware, VulnerabilitySource vulnerabilitySource, string saveDirectory) { try { if (!DatabaseBuilder.sqliteConnection.State.ToString().Equals("Open")) { DatabaseBuilder.sqliteConnection.Open(); } using (SQLiteCommand sqliteCommand = DatabaseBuilder.sqliteConnection.CreateCommand()) { sqliteCommand.Parameters.Add(new SQLiteParameter("Hardware_ID", hardware.Hardware_ID)); sqliteCommand.Parameters.Add(new SQLiteParameter("VulnerabilitySource_ID", vulnerabilitySource.VulnerabilitySource_ID)); sqliteCommand.CommandText = sqliteCommand.CommandText = _ddlReader.ReadDdl(_storedProcedureBase + "Select.HardwareCklCreationData.dml", assembly);; using (SQLiteDataReader sqliteDataReader = sqliteCommand.ExecuteReader()) { string stigName = vulnerabilitySource.SourceName .Replace(" ", string.Empty) .Replace("Security Technical Implentation Guide", "_STIG"); string saveFile = $"{saveDirectory}\\U_{stigName}_v{vulnerabilitySource.SourceVersion}_r{vulnerabilitySource.SourceRelease}_{hardware.DisplayedHostName}.ckl"; using (XmlWriter xmlWriter = XmlWriter.Create(saveFile, GenerateXmlWriterSettings())) { xmlWriter.WriteStartDocument(true); xmlWriter.WriteStartElement("CHECKLIST"); int i = 0; while (sqliteDataReader.Read()) { if (i == 0) { WriteAssetInformation(sqliteDataReader, xmlWriter, "Computing"); WriteStigInformation(sqliteDataReader, xmlWriter); i++; } WriteVulnerabilityInformation(sqliteDataReader, xmlWriter); } xmlWriter.WriteEndElement(); xmlWriter.WriteEndElement(); xmlWriter.WriteEndElement(); } } } } catch (Exception exception) { LogWriter.LogError( $"Unable to generate a CKL for '{hardware.DisplayedHostName}' - '{vulnerabilitySource.SourceName}'."); throw exception; } finally { DatabaseBuilder.sqliteConnection.Close(); } }
private void WriteFindingsToReport() { try { using (SQLiteCommand sqliteCommand = DatabaseBuilder.sqliteConnection.CreateCommand()) { sqliteCommand.Parameters.Add(new SQLiteParameter("UserName", Properties.Settings.Default.ActiveUser)); FilterTextCreator filterTextCreator = new FilterTextCreator(); string groupFilter = filterTextCreator.Group(sqliteCommand, "SCAP & STIG Discrepancies"); string severityFilter = filterTextCreator.Severity(sqliteCommand, "SCAP & STIG Discrepancies"); string statusFilter = filterTextCreator.Status(sqliteCommand, "SCAP & STIG Discrepancies"); sqliteCommand.CommandText = _ddlReader.ReadDdl(_storedProcedureBase + "Select.StigDiscrepanciesVulnerabilities.dml", assembly); if (!string.IsNullOrWhiteSpace(groupFilter) || !string.IsNullOrWhiteSpace(severityFilter) || !string.IsNullOrWhiteSpace(statusFilter)) { Regex regex = new Regex(Properties.Resources.RegexSqlGroupBy); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"{Environment.NewLine}"); if (!string.IsNullOrWhiteSpace(groupFilter)) { sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"AND {Environment.NewLine}"); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, groupFilter); } if (!string.IsNullOrWhiteSpace(severityFilter)) { sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"AND {Environment.NewLine}"); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"(PrimaryRawRiskIndicator {severityFilter}) "); } sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, Environment.NewLine); if (!string.IsNullOrWhiteSpace(statusFilter)) { regex = new Regex(Properties.Resources.RegexStigDiscrepanciesStatus); MatchCollection matches = regex.Matches(sqliteCommand.CommandText); for (int i = matches.Count - 1; i >= 0; i--) { sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(matches[i].Index, "AND "); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(matches[i].Index, matches[i].ToString().Contains("Mitigated") ? $"MitigatedStatus {statusFilter}" : $"Status {statusFilter} "); } } } using (SQLiteDataReader sqliteDataReader = sqliteCommand.ExecuteReader()) { while (sqliteDataReader.Read()) { if (string.IsNullOrWhiteSpace(sqliteDataReader["XccdfStatus"].ToString()) || string.IsNullOrWhiteSpace(sqliteDataReader["CklStatus"].ToString()) || sqliteDataReader["XccdfStatus"].Equals(sqliteDataReader["CklStatus"])) { continue; } WriteFindingToReport(sqliteDataReader); } } } } catch (Exception exception) { LogWriter.LogError("Unable to write findings to the 'STIG Discrepancies' workbook."); throw exception; } }
private void CreateDatabase() { try { LogWriter.LogStatusUpdate( $"Begin verifying and, if needed, creating and / or updating database '{Properties.Settings.Default.Database}'."); if (!File.Exists(Properties.Settings.Default.Database)) { SQLiteConnection.CreateFile(Properties.Settings.Default.Database); } if (!sqliteConnection.State.ToString().Equals("Open")) { sqliteConnection.Open(); } using (SQLiteTransaction sqliteTransaction = sqliteConnection.BeginTransaction()) { using (SQLiteCommand sqliteCommand = sqliteConnection.CreateCommand()) { string[] resourceNames = Assembly.GetExecutingAssembly().GetManifestResourceNames(); foreach (string name in resourceNames.Where(x => x.Contains("Vulnerator.Resources.DdlFiles.Tables.Create."))) { Messenger.Default.Send( $"Verifying table '{name.Replace("Vulnerator.Resources.DdlFiles.Tables.Create.", "").Replace(".ddl", "")}'", MessengerToken.LoadingTextUpdated); LogWriter.LogStatusUpdate( $"Verifying table '{name.Replace("Vulnerator.Resources.DdlFiles.Tables.Create.", "").Replace(".ddl", "")}'."); sqliteCommand.CommandText = _ddlReader.ReadDdl(name, assembly); sqliteCommand.ExecuteNonQuery(); } foreach (string name in resourceNames .Where(x => x.Contains("Vulnerator.Resources.DdlFiles.Tables.Insert.Data.")).AsEnumerable()) { LogWriter.LogStatusUpdate( $"Verifying table '{name.Replace("Vulnerator.Resources.DdlFiles.Tables.Insert.Data.", "").Replace(".ddl", "")}' base data exists."); sqliteCommand.CommandText = _ddlReader.ReadDdl(name, assembly); sqliteCommand.ExecuteNonQuery(); } sqliteCommand.ExecuteNonQuery(); PopulateIaControlData(sqliteCommand); PopulateCciData(sqliteCommand); PopulateNistControls(sqliteCommand); MapControlsToCci(sqliteCommand); MapControlsToCnssOverlays(sqliteCommand); MapControlsToMonitoringFrequency(sqliteCommand); InsertUserReportSettings(sqliteCommand); } sqliteTransaction.Commit(); } LogWriter.LogStatusUpdate($"Database '{Properties.Settings.Default.Database}' verified successfully."); } catch (Exception exception) { LogWriter.LogError($"Database '{Properties.Settings.Default.Database}' verification failed."); throw exception; } }
private void WriteFindingsToReport() { try { using (SQLiteCommand sqliteCommand = DatabaseBuilder.sqliteConnection.CreateCommand()) { sqliteCommand.Parameters.Add(new SQLiteParameter("UserName", Properties.Settings.Default.ActiveUser)); FilterTextCreator filterTextCreator = new FilterTextCreator(); string findingTypeFilter = filterTextCreator.FindingType(sqliteCommand, "Navy RAR"); string groupFilter = filterTextCreator.Group(sqliteCommand, "Navy RAR"); string severityFilter = filterTextCreator.Severity(sqliteCommand, "Navy RAR"); string statusFilter = filterTextCreator.Status(sqliteCommand, "Navy RAR"); string rmfOverrideFilter = filterTextCreator.RmfOverride(sqliteCommand, "Navy RAR"); sqliteCommand.CommandText = _ddlReader.ReadDdl(_storedProcedureBase + "Select.NavyRarVulnerabilities.dml", assembly); if (!string.IsNullOrWhiteSpace(findingTypeFilter) || !string.IsNullOrWhiteSpace(groupFilter) || !string.IsNullOrWhiteSpace(severityFilter) || !string.IsNullOrWhiteSpace(statusFilter)) { Regex regex = new Regex(Properties.Resources.RegexSqlGroupBy); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, "WHERE "); if (!string.IsNullOrWhiteSpace(findingTypeFilter)) { sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, findingTypeFilter); } if (!string.IsNullOrWhiteSpace(groupFilter)) { if (!string.IsNullOrWhiteSpace(findingTypeFilter)) { sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"AND {Environment.NewLine}"); } sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, groupFilter); } if (!string.IsNullOrWhiteSpace(severityFilter)) { if (!string.IsNullOrWhiteSpace(findingTypeFilter) || !string.IsNullOrWhiteSpace(groupFilter)) { sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"AND {Environment.NewLine}"); } sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"(PrimaryRawRiskIndicator {severityFilter}) "); } if (!string.IsNullOrWhiteSpace(statusFilter)) { if (!string.IsNullOrWhiteSpace(findingTypeFilter) || !string.IsNullOrWhiteSpace(groupFilter) || !string.IsNullOrWhiteSpace(severityFilter)) { sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"AND {Environment.NewLine}"); } sqliteCommand.CommandText = string.IsNullOrWhiteSpace(rmfOverrideFilter) ? sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"(RawStatus {statusFilter} OR UniqueMitigatedStatus {statusFilter}) ") : sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"(RawStatus {statusFilter} OR UniqueMitigatedStatus {statusFilter} OR GroupMitigatedStatus {statusFilter}) "); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, Environment.NewLine); regex = new Regex(Properties.Resources.RegexGroupsMitigationsOrConditionsVulnerabilities); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"{Environment.NewLine}WHERE (MitigatedStatus {statusFilter}) "); } else { sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, Environment.NewLine); } } if (!string.IsNullOrWhiteSpace(rmfOverrideFilter)) { Regex regex = new Regex(Properties.Resources.RegexSqlFindingTypes); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, rmfOverrideFilter); regex = new Regex(Properties.Resources.RegexSqlFromUniqueFindings); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, Properties.Resources.StringGroupRmfFields); } using (SQLiteDataReader sqliteDataReader = sqliteCommand.ExecuteReader()) { while (sqliteDataReader.Read()) { if (sqliteDataReader["UniqueVulnerabilityIdentifier"].ToString().Equals("Plugin")) { continue; } WriteFindingToReport(sqliteDataReader); } } } } catch (Exception exception) { LogWriter.LogError("Unable to write findings to the 'Navy RAR' workbook."); throw exception; } }