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