aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorDavid Reyna <David.Reyna@windriver.com>2020-01-31 02:26:48 -0800
committerDavid Reyna <David.Reyna@windriver.com>2020-01-31 02:26:48 -0800
commit91a0a241372e683b611d5147ab87bd3a9458b2d1 (patch)
treeb6bdfabb0e0c3b10c8d7b9a2b531d72caf50a512
parentfb78272da52429f4db258169e39cfac750b0884c (diff)
downloadsrtool-91a0a241372e683b611d5147ab87bd3a9458b2d1.zip
srtool-91a0a241372e683b611d5147ab87bd3a9458b2d1.tar.gz
srtool-91a0a241372e683b611d5147ab87bd3a9458b2d1.tar.bz2
srtool: enhance database utility validation and repair functions
1. Support analyzing alternate databases (e.g. backups) with potentially older schemas by autogenerating and loading respective "srt_schema.py" files, using the '--database /path/to/srt.sqlite' option. 2. Add the following reports to help analyize the distribution of V3/V2 severity values across the CVE years and across the CVE/VUL/INV records. This helps spot trends and potential translation errors. ./bin/common/srtool_utils.py --report-cve-status-summary ./bin/common/srtool_utils.py --report-db-status-summary 3. Add a report to display VUL/INV/DEF recoords that are unattached to any parent CVE/VUL/INV records respectively. ./bin/common/srtool_utils.py --report-unattached-records 4. Update the repair routines to report issues, but only fix them if the "--force" flag is set. This allows the review of the potential fixes before committing them. 5. General clean and internal documentation. Rename the commands to better distinguish "fix" (one-shot), "repair" (on-going), and "report" functions. Signed-off-by: David Reyna <David.Reyna@windriver.com>
-rwxr-xr-xbin/common/srtool_utils.py1209
1 files changed, 996 insertions, 213 deletions
diff --git a/bin/common/srtool_utils.py b/bin/common/srtool_utils.py
index 0ac6471..30ad1e9 100755
--- a/bin/common/srtool_utils.py
+++ b/bin/common/srtool_utils.py
@@ -5,7 +5,7 @@
#
# Security Response Tool Commandline Tool
#
-# Copyright (C) 2018-2019 Wind River Systems
+# Copyright (C) 2018-2020 Wind River Systems
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License version 2 as
@@ -30,11 +30,12 @@ import time
import re
import subprocess
import json
+import xml.etree.ElementTree as ET
# load the srt.sqlite schema indexes
dir_path = os.path.dirname(os.path.dirname(os.path.realpath(__file__)))
sys.path.insert(0, dir_path)
-from common.srt_schema import ORM
+#from common.srt_schema import ORM
# Setup:
verbose = False
@@ -43,6 +44,7 @@ cmd_count = 0
force = False
srtDbName = 'srt.sqlite'
+srtSchemaName = 'srt_schema.py'
#################################
# Common routines
@@ -86,6 +88,59 @@ def execute_process(*args):
return result.returncode,result.stdout,result.stderr
#################################
+# ORM mapping for the given database file
+#
+
+# ORM mapping for the given database file
+class ORM_Class(object):
+ # Members will be added dynamically
+
+ # General routine to return string name of a constant (e.g. 'DATASOURCE_FREQUENCY_STR')
+ @staticmethod
+ def get_orm_string(value,string_set):
+ if None == value: return('None')
+ string_list = string_set.split(',')
+ string_count = len(string_list)
+ value = int(value)
+ if (value < 0) or (value >= string_count):
+ print("ERROR: value '%d' out of range of '%s'" % (value,string_set))
+ return '<error>'
+ return string_list[value]
+
+# Instantiate the ORM class object
+ORM = ORM_Class()
+
+# Attach the specific database schema attibutes and values
+def import_orm_schema(databaseDir):
+ global ORM
+
+ # Local or generate database schema file
+ if not databaseDir or ('.' == databaseDir):
+ # Normal database and schema locations
+ schema = os.path.join(databaseDir,'bin/common',srtSchemaName)
+ else:
+ # Remote database location (e.g. backups)
+ schema = os.path.join(databaseDir,srtSchemaName)
+ # Generate the schema file if not found
+ if not os.path.isfile(schema):
+ ret = os.system("%s --generate-schema-header-dir %s" % (os.path.join(srtool_basepath,'bin/common/srtool_common.py'),databaseDir))
+
+ with open(schema) as fp:
+ for line in fp:
+ try:
+ name = line[:line.index('=')].strip()
+ value = line[line.index('=')+1:].strip()
+ if '"' == value[0]:
+ value = value[1:-1]
+ elif "'" == value[0]:
+ value = value[1:-1]
+ else:
+ value = int(value)
+ except:
+ continue
+ setattr(ORM, name, value)
+
+#################################
# reset sources
#
@@ -369,7 +424,7 @@ def fix_srt_datetime(scope):
if 0 == i % 10:
print('%05d: %20s\r' % (i,defect[ORM.DEFECT_NAME]), end='')
if (0 == i % 200):
- conn.commit()
+ if force: conn.commit()
# Development/debug support
if cmd_skip:
if i < cmd_skip:
@@ -383,13 +438,14 @@ def fix_srt_datetime(scope):
if defect_srt_updated == defect[ORM.DEFECT_SRT_UPDATED]:
continue
- sql = ''' UPDATE orm_defect
- SET srt_updated = ?
- WHERE id = ?'''
- cur_write.execute(sql, (defect_srt_updated, defect[ORM.DEFECT_ID],))
+ if force:
+ sql = ''' UPDATE orm_defect
+ SET srt_updated = ?
+ WHERE id = ?'''
+ cur_write.execute(sql, (defect_srt_updated, defect[ORM.DEFECT_ID],))
is_change_count += 1
print("DEFECT DATE FIX COUNT=%d/%d" % (is_change_count,i))
- conn.commit()
+ if force: conn.commit()
# INVESTIGATION DATE FIX COUNT=1089363, real 12m20.041s = 1472 recs/sec
if ('i' == scope) or ('all' == scope):
@@ -403,7 +459,7 @@ def fix_srt_datetime(scope):
if 0 == i % 10:
print('%05d: %20s\r' % (i,investigation[ORM.INVESTIGATION_NAME]), end='')
if (0 == i % 200):
- conn.commit()
+ if force: conn.commit()
time.sleep(0.1) # give time for Sqlite to sync
# Development/debug support
if cmd_skip:
@@ -422,13 +478,14 @@ def fix_srt_datetime(scope):
if (srt_updated == investigation[ORM.INVESTIGATION_SRT_UPDATED]) and (srt_created == investigation[ORM.INVESTIGATION_SRT_CREATED]):
continue
- sql = ''' UPDATE orm_investigation
- SET srt_updated = ?, srt_created = ?
- WHERE id = ?'''
- cur_write.execute(sql, (srt_updated, srt_created, investigation[ORM.INVESTIGATION_ID],))
+ if force:
+ sql = ''' UPDATE orm_investigation
+ SET srt_updated = ?, srt_created = ?
+ WHERE id = ?'''
+ cur_write.execute(sql, (srt_updated, srt_created, investigation[ORM.INVESTIGATION_ID],))
is_change_count += 1
print("INVESTIGATION DATE FIX COUNT=%d/%d" % (is_change_count,i))
- conn.commit()
+ if force: conn.commit()
# VULNERABILITY DATE FIX COUNT=86585, real 1m2.969s = 1374 recs/sec
if ('v' == scope) or ('all' == scope):
@@ -442,7 +499,7 @@ def fix_srt_datetime(scope):
if 0 == i % 10:
print('%05d: %20s\r' % (i,vulnerability[ORM.VULNERABILITY_NAME]), end='')
if (0 == i % 200):
- conn.commit()
+ if force: conn.commit()
time.sleep(0.1) # give time for Sqlite to sync
# Development/debug support
if cmd_skip:
@@ -461,13 +518,14 @@ def fix_srt_datetime(scope):
if (srt_updated == vulnerability[ORM.VULNERABILITY_SRT_UPDATED]) and (srt_created == vulnerability[ORM.VULNERABILITY_SRT_CREATED]):
continue
- sql = ''' UPDATE orm_vulnerability
- SET srt_updated = ?, srt_created = ?
- WHERE id = ?'''
- cur_write.execute(sql, (srt_updated, srt_created, vulnerability[ORM.VULNERABILITY_ID],))
+ if force:
+ sql = ''' UPDATE orm_vulnerability
+ SET srt_updated = ?, srt_created = ?
+ WHERE id = ?'''
+ cur_write.execute(sql, (srt_updated, srt_created, vulnerability[ORM.VULNERABILITY_ID],))
is_change_count += 1
print("VULNERABILITY DATE FIX COUNT=%d/%d" % (is_change_count,i))
- conn.commit()
+ if force: conn.commit()
# CVE DATE FIX COUNT=86585, real 1m2.969s = 1374 recs/sec
# NOTE: only ACK dates need fixing, received bad apha content from srtool_mitre
@@ -484,7 +542,7 @@ def fix_srt_datetime(scope):
if 0 == i % 10:
print('%05d: %20s\r' % (i,cve[ORM.CVE_NAME]), end='')
if (0 == i % 200) and is_change:
- conn.commit()
+ if force: conn.commit()
time.sleep(0.1) # give time for Sqlite to sync
is_change = False
@@ -518,14 +576,15 @@ def fix_srt_datetime(scope):
if not is_change:
continue
- is_change_count += 1
- sql = ''' UPDATE orm_cve
- SET srt_updated = ?, srt_created = ?, acknowledge_date = ?
- WHERE id = ?'''
- cur_write.execute(sql, (srt_updated, srt_created, acknowledge_date, cve[ORM.CVE_ID],))
+ if force:
+ sql = ''' UPDATE orm_cve
+ SET srt_updated = ?, srt_created = ?, acknowledge_date = ?
+ WHERE id = ?'''
+ cur_write.execute(sql, (srt_updated, srt_created, acknowledge_date, cve[ORM.CVE_ID],))
+ is_change = True
is_change_count += 1
print("CVE DATE FIX COUNT=%d/%d" % (is_change_count,i))
- conn.commit()
+ if force and is_change: conn.commit()
# Fix CVE History
if scope in ('ch','all','history'):
@@ -541,7 +600,7 @@ def fix_srt_datetime(scope):
if 0 == i % 10:
print('%05d: \r' % (i), end='')
if (0 == i % 200) and is_change:
- conn.commit()
+ if force: conn.commit()
time.sleep(0.1) # give time for Sqlite to sync
is_change = False
@@ -558,16 +617,16 @@ def fix_srt_datetime(scope):
if not updated:
continue
- is_change = True
- sql = ''' UPDATE orm_cvehistory
- SET date = ?
- WHERE id = ?'''
- cur_write.execute(sql, (history_date, cve_history[ORM.CVEHISTORY_ID],))
+ if force:
+ sql = ''' UPDATE orm_cvehistory
+ SET date = ?
+ WHERE id = ?'''
+ cur_write.execute(sql, (history_date, cve_history[ORM.CVEHISTORY_ID],))
+ is_change = True
is_change_count += 1
# Commit all remaining changes
- if is_change:
- conn.commit()
+ if force and is_change: conn.commit()
print("CVE HISTORY DATE FIX COUNT=%d/%d" % (is_change_count,i))
# Fix Vulnerability History
@@ -584,7 +643,7 @@ def fix_srt_datetime(scope):
if 0 == i % 10:
print('%05d: \r' % (i), end='')
if (0 == i % 200) and is_change:
- conn.commit()
+ if force: conn.commit()
time.sleep(0.1) # give time for Sqlite to sync
is_change = False
@@ -601,16 +660,16 @@ def fix_srt_datetime(scope):
if not updated:
continue
- is_change = True
- sql = ''' UPDATE orm_vulnerabilityhistory
- SET date = ?
- WHERE id = ?'''
- cur_write.execute(sql, (history_date, vulnerabilityhistory[ORM.VULNERABILITYHISTORY_ID],))
- is_change_count += 1
+ if force:
+ is_change = True
+ sql = ''' UPDATE orm_vulnerabilityhistory
+ SET date = ?
+ WHERE id = ?'''
+ cur_write.execute(sql, (history_date, vulnerabilityhistory[ORM.VULNERABILITYHISTORY_ID],))
+ is_change_count += 1
# Commit all remaining changes
- if is_change:
- conn.commit()
+ if force and is_change: conn.commit()
print("VULNERABILITY HISTORY DATE FIX COUNT=%d/%d" % (is_change_count,i))
# Fix Investigation History
@@ -627,7 +686,7 @@ def fix_srt_datetime(scope):
if 0 == i % 10:
print('%05d: \r' % (i), end='')
if (0 == i % 200) and is_change:
- conn.commit()
+ if force: conn.commit()
time.sleep(0.1) # give time for Sqlite to sync
is_change = False
@@ -644,16 +703,16 @@ def fix_srt_datetime(scope):
if not updated:
continue
- is_change = True
- sql = ''' UPDATE orm_investigationhistory
- SET date = ?
- WHERE id = ?'''
- cur_write.execute(sql, (history_date, investigation_history[ORM.INVESTIGATIONHISTORY_ID],))
+ if force:
+ is_change = True
+ sql = ''' UPDATE orm_investigationhistory
+ SET date = ?
+ WHERE id = ?'''
+ cur_write.execute(sql, (history_date, investigation_history[ORM.INVESTIGATIONHISTORY_ID],))
is_change_count += 1
# Commit all remaining changes
- if is_change:
- conn.commit()
+ if force and is_change: conn.commit()
print("INVESTIGATION HISTORY DATE FIX COUNT=%d/%d" % (is_change_count,i))
#################################
@@ -806,7 +865,7 @@ def fix_public_reserved():
#
# Remove a specific/accidental set of bulk CVE history updates intended to be background
-def fix_foo():
+def fix_remove_bulk_cve_history():
conn = sqlite3.connect(srtDbName)
cur = conn.cursor()
cur_cve = conn.cursor()
@@ -814,8 +873,6 @@ def fix_foo():
fix_count = 0
- print("FOO=%s\n\n" % ORM.STATUS_NEW_RESERVED)
-
cur.execute('SELECT * FROM orm_cvehistory WHERE date LIKE "2019-03-2%"')
i = 0
@@ -854,11 +911,12 @@ def fix_foo():
conn.commit()
#################################
-# fix_defects_to_products
+# report_defects_to_products
#
-
+# Report all defects without a product link
#
-def fix_defects_to_products():
+
+def report_defects_to_products():
conn = sqlite3.connect(srtDbName)
cur = conn.cursor()
cur_cve = conn.cursor()
@@ -934,8 +992,7 @@ def fix_bad_mitre_init():
print('MITRE DataSource List=[%s]' % mitre_source_list)
# Find all bad MITRE reserved CVEs
- cur.execute('SELECT * FROM orm_cve WHERE description = "" AND status = %d' % ORM.STATUS_NEW)
-# cur.execute('SELECT * FROM orm_cve WHERE description = ""')
+ cur.execute('SELECT * FROM orm_cve WHERE description = ""')
for i,cve in enumerate(cur):
new_count += 1
@@ -975,110 +1032,213 @@ def fix_bad_mitre_init():
# conn.commit()
-
-
#
# Fix MITRE CVEs that are missing a description in the top level CVE
# records due to column ordering issue in the MITRE "Init" routine.
#
-def foo_fix_bad_mitre_init():
+def fix_bad_mitre_descr(datasource_list):
conn = sqlite3.connect(srtDbName)
- cur = conn.cursor()
cur_ds = conn.cursor()
+ cur_cs = conn.cursor()
cur_cve = conn.cursor()
cur_del = conn.cursor()
+ cve_count = 0
fix_count = 0
- reserved_count = 0
- mitre_count = 0
- nosource_count = 0
+ nist_ds_list = {}
+ mitre_ds_list = {}
+ modified_cve_list = []
- mitre_source_list = []
- mitre_lookup = {}
+ DATA_MAP_DESCRIPTION = 0
- # Find MITRE data sources
- cur.execute('SELECT * FROM orm_datasource WHERE source = "mitre"')
- for i,ds in enumerate(cur):
- mitre_source_list.append(ds[ORM.DATASOURCE_ID])
- mitre_lookup[ds[ORM.DATASOURCE_ID]] = ds[ORM.DATASOURCE_LOOKUP]
- print('MITRE DataSource List=[%s]' % mitre_source_list)
+ def description_summary(description):
+ desc_sum = 0
+ for ch in description:
+ desc_sum += ord(ch)
+ if 37 < len(description):
+ description = "%-37s..." % description[:37]
+ return("%-40s [sum=%d]" % (description,desc_sum))
- # Find all bad MITRE reserved CVEs
- cur.execute('SELECT * FROM orm_cve WHERE description = ""')
- for i,cve in enumerate(cur):
- fix_count += 1
+ #
+ # Gather the MITRE and NIST data source lists
+ #
-# reserved_pos = cve[ORM.CVE_DESCRIPTION].find('** RESERVED **')
-# if (0 <= reserved_pos) and (20 > reserved_pos):
-# reserved_count += 1
+ cur_ds.execute('SELECT * FROM orm_datasource WHERE source = "mitre" ORDER BY key DESC;')
+ for i,ds in enumerate(cur_ds):
+ mitre_ds_list[ds[ORM.DATASOURCE_ID]] = ds[ORM.DATASOURCE_DESCRIPTION]
+ cur_ds.execute('SELECT * FROM orm_datasource WHERE source = "nist" ORDER BY key DESC;')
+ for i,ds in enumerate(cur_ds):
+ nist_ds_list[ds[ORM.DATASOURCE_ID]] = ds[ORM.DATASOURCE_DESCRIPTION]
+
+ #
+ # Iterate over the MITRE data sources
+ #
- if ORM.STATUS_NEW == cve[ORM.CVE_STATUS]:
- reserved_count += 1
+ cur_ds.execute('SELECT * FROM orm_datasource WHERE source = "mitre" ORDER BY key DESC;')
+ for i,ds in enumerate(cur_ds):
+ # Development/debug support
+ if cmd_count and ((cve_count - cmd_skip) > cmd_count):
+ break
- cur_ds.execute('SELECT * FROM orm_cvesource WHERE cve_id = %d' % cve[ORM.CVE_ID])
- is_mitre = False
- mitre_ds = 0
- ds_list = []
- ds_count = 0
- for cvesource in cur_ds:
- ds_count += 1
- if cvesource[ORM.CVESOURCE_DATASOURCE_ID] in mitre_source_list:
- is_mitre = True
- mitre_ds = cvesource[ORM.CVESOURCE_DATASOURCE_ID]
+ if "ALL" == datasource_list:
+ pass
+ elif not ds[ORM.DATASOURCE_DESCRIPTION] in datasource_list.split(','):
+ continue
+ print("MITRE Source:%s" % ds[ORM.DATASOURCE_DESCRIPTION])
+
+ # Scan the NIST datasource file and extract required values into a map
+ # bin/mitre/srtool_mitre.py --download-only --source='Mitre 2010' --file=data/allitems-cvrf-year-2010.xml --url-file=allitems-cvrf-year-2010.xml
+ cve_source_file = ''
+ for param in ds[ORM.DATASOURCE_LOOKUP].split(' '):
+ if param.startswith('--file='):
+ cve_source_file = param.replace('--file=','')
+ print(" File:%s" % cve_source_file)
break
- ds_list.append(cvesource[ORM.CVESOURCE_DATASOURCE_ID])
-
- if True:
- print('%05d: %-20s, SourceList=%s' % (i,cve[ORM.CVE_NAME],ds_list))
-
- if False:
- if is_mitre:
- mitre_count += 1
-
- lookup_command = mitre_lookup[ cvesource[ORM.CVESOURCE_DATASOURCE_ID] ].replace('%command%','--cve-detail=%s' % cve[ORM.CVE_NAME])
- result_returncode,result_stdout,result_stderr = execute_process(lookup_command.split(' '))
- if 0 != result_returncode:
- print("ERROR_LOOKUP:%s" % lookup_command)
- return(1)
- description = ''
- for line in result_stdout.decode("utf-8").splitlines():
- try:
- name = line[:line.index('=')]
- value = line[line.index('=')+1:].replace("[EOL]","\n")
- except:
- continue
- if name == 'description':
- description = value
- break
- if description:
- # print("%s='%s'" % (cve[ORM.CVE_NAME],description))
+ mitre_data_map = {}
+ mitre_file = os.path.join(srtool_basepath,cve_source_file)
+
+ #
+ # Gather the Descriptions of all the CVEs in this MITRE data sources
+ #
+ # <Vulnerability Ordinal="158066" xmlns="http://www.icasi.org/CVRF/schema/vuln/1.1">
+ # <Notes>
+ # <Note Ordinal="1" Type="Description">In getProcessRecordLocked ...</Note>
+ # </Notes>
+ # <CVE>CVE-2020-0001</CVE>
+
+ # Find the CVE and extract the description
+ if not os.path.isfile(mitre_file):
+ print("ERROR: no such file '%s'" % mitre_file)
+ exit(1)
+ f = open(mitre_file, 'r')
+ tree = ET.parse(mitre_file)
+ root = tree.getroot()
+ for child in root:
+ cve_name = ''
+ description = ''
+ if 'Vulnerability' in child.tag:
+ for child_v in child:
+ if 'CVE' in child_v.tag:
+ cve_name = child_v.text
+ if 'Notes' in child_v.tag:
+ for child_n in child_v:
+ if 'Note' in child_n.tag:
+ if 'Description' == child_n.attrib['Type']:
+ description = child_n.text.replace('\n','\\r')
+
+# # Debugging support
+# if cve_name != "CVE-2016-0887": #"CVE-2020-7470","CVE-2019-15031"
+# continue
+
+ if cve_name:
+ mitre_data_map[cve_name] = [description]
+
+
+ #
+ # Update the 'description' for all found CVE records in this datasource
+ #
+
+ for cve_name in mitre_data_map:
+ description = mitre_data_map[cve_name][DATA_MAP_DESCRIPTION]
+ cur_cve.execute('SELECT * FROM orm_cve WHERE name = "%s"' % cve_name)
+ cve = cur_cve.fetchone()
+ if not cve:
+ print("WARNING: MISSING CVE in orm : %s" % cve_name)
+ continue
+
+
+ #
+ # Repair the data source mappings
+ # * Add missing MITRE links
+ # * Replace old MITRE links with found links
+ #
+
+ found_mapping = False
+ is_nist = False
+ cur_cs.execute('SELECT * FROM orm_cvesource WHERE cve_id = %d' % cve[ORM.CVE_ID])
+ for cve2ds in cur_cs:
+ if cve2ds[ORM.CVESOURCE_DATASOURCE_ID] in nist_ds_list:
+ is_nist = True
+ if cve2ds[ORM.CVESOURCE_DATASOURCE_ID] in mitre_ds_list:
+ # Do we have an obsolete MITRE mapping?
+ if cve2ds[ORM.CVESOURCE_DATASOURCE_ID] != ds[ORM.DATASOURCE_ID]:
+ # Delete old mapping
+ print("Delete old mapping %s,%s" % (cve_name,mitre_ds_list[cve2ds[ORM.CVESOURCE_DATASOURCE_ID]]))
+ if force:
+ sql = 'DELETE FROM orm_cvesource WHERE id=?'
+ cur_del.execute(sql, (cve2ds[ORM.CVESOURCE_ID],))
+ else:
+ # We are good to go
+ found_mapping = True
+
+ # Disable this feature for now, since we do want to add unneeded pasive MITRE records
+ if False:
+ # Add if missing or deleted as obsolete
+ if not found_mapping:
+ print("Insert new mapping %s,%s" % (cve_name,mitre_ds_list[ds[ORM.DATASOURCE_ID]]))
+ if force:
+ sql = ''' INSERT into orm_cvesource (cve_id, datasource_id) VALUES (?, ?)'''
+ cur_cs.execute(sql, (cve[ORM.CVE_ID],ds[ORM.DATASOURCE_ID],))
+
+ # If this CVE has any NIST data sources, then skip description checking (e.g. NIST Modified preempt)
+ if is_nist:
+ continue
+
+ # Test is desciption needs repair
+ if cve[ORM.CVE_DESCRIPTION] != description:
+ fix_count += 1
+ print("CHANGE: %s DESCRIPTION('%s' to '%s')" % (
+ cve_name, description_summary(cve[ORM.CVE_DESCRIPTION]), description_summary(description)))
+ if force:
sql = ''' UPDATE orm_cve
SET description = ?
WHERE id = ?'''
- cur_ds.execute(sql, (description,cve[ORM.CVE_ID],))
- # conn.commit()
- # return(0)
+ cur_cve.execute(sql, (description,cve[ORM.CVE_ID],))
+# print('%05d: %-20s = %-20s' % (j,cve_name,nist_data_map[cve_name]))
- elif 0 == ds_count:
- nosource_count += 1
- else:
- print('%05d: %-20s, SourceList=%s' % (i,cve[ORM.CVE_NAME],ds_list))
- # Progress indicator support
- if 19 == i % 100:
- print('%05d: %-20s\r' % (i,cve[ORM.CVE_NAME]), end='')
- pass
- if (0 == i % 200):
-# conn.commit()
- #print('')
- pass
- # Development/debug support
- if cmd_skip and (i < cmd_skip): continue
- if cmd_count and ((i - cmd_skip) > cmd_count): break
+ #
+ # Repair the data source mappings
+ # * Add missing MITRE links
+ # * Replace old MITRE links with found links
+ #
- print("CVE RESERVED COUNT=%d of %d, mitre=%d, no_source=%d" % (reserved_count,fix_count,mitre_count,nosource_count))
-# conn.commit()
+ found_mapping = False
+ cur_cs.execute('SELECT * FROM orm_cvesource WHERE cve_id = %d' % cve[ORM.CVE_ID])
+ for j,cve2ds in enumerate(cur_cs):
+ if cve2ds[ORM.CVESOURCE_DATASOURCE_ID] in mitre_ds_list:
+ # Do we have an obsolete MITRE mapping?
+ if cve2ds[ORM.CVESOURCE_DATASOURCE_ID] != ds[ORM.DATASOURCE_ID]:
+ # Delete old mapping
+ print("Delete old mapping %s,%s" % (cve_name,mitre_ds_list[cve2ds[ORM.CVESOURCE_DATASOURCE_ID]]))
+ if force:
+ sql = 'DELETE FROM orm_cvesource WHERE id=?'
+ cur_del.execute(sql, (cve2ds[ORM.CVESOURCE_ID],))
+ else:
+ # We are good to go
+ found_mapping = True
+ # Add if missing or deleted as obsolete
+ if not found_mapping:
+ print("Insert new mapping %s,%s" % (cve_name,mitre_ds_list[ds[ORM.DATASOURCE_ID]]))
+ if force:
+ sql = ''' INSERT into orm_cvesource (cve_id, datasource_id) VALUES (?, ?)'''
+ cur_cs.execute(sql, (cve[ORM.CVE_ID],ds[ORM.DATASOURCE_ID],))
+
+
+ # Development/debug support
+ cve_count += 1
+ if cmd_skip and (cve_count < cmd_skip): continue
+ if cmd_count and ((cve_count - cmd_skip) > cmd_count): break
+
+ # Progress indicator support
+ if (0 == cve_count % 1000):
+ print('%05d: %-20s\r' % (cve_count,cve_name), end='')
+ if force: conn.commit()
+ print('')
+ pass
+ print("CVE COUNT=%d, fix_count=%d" % (cve_count,fix_count))
+ if force: conn.commit()
#
# Fix CVE records with missing 'cvssV2_severity' values in the top-level CVE records, due to
@@ -1203,7 +1363,7 @@ def fix_severity(datasource_list):
cur_cve.execute('SELECT * FROM orm_cve WHERE name = "%s"' % cve_name)
cve = cur_cve.fetchone()
if not cve:
- print("WARNING: MISSING CVE in orm_cvesource : %s" % cve_name)
+ print("WARNING: MISSING CVE in orm_cve : %s" % cve_name)
continue
cve_name = cve[ORM.CVE_NAME]
if cve_name in nist_data_map:
@@ -1350,10 +1510,14 @@ def fix_trim_cve_scores():
#################################
-# find_multiple_defects
+# report_multiple_defects
+#
+# Normally for each CVE there is one defect per
+# product. This report finds all cases of multiple
+# defects per Investigation.
#
-def find_multiple_defects():
+def report_multiple_defects():
conn = sqlite3.connect(srtDbName)
cur_i2d = conn.cursor()
@@ -1373,17 +1537,19 @@ def find_multiple_defects():
for k,i2d in enumerate(i2d_list):
cur_def.execute('SELECT * FROM orm_defect WHERE id = "%s"' % i2d[ORM.INVESTIGATIONTODEFECT_DEFECT_ID])
defect = cur_def.fetchone()
- if defect[ORM.DEFECT_NAME].startswith("LIN10"):
- if 0 == k:
- print("[%02d] Multiple defects for investigation '%s':" % (count,investigation[ORM.INVESTIGATION_NAME]))
- print(" [%02d] %s: %s (%s)" % (k+1,defect[ORM.DEFECT_NAME],defect[ORM.DEFECT_SUMMARY],ORM.get_orm_string(defect[ORM.DEFECT_RESOLUTION],ORM.DEFECT_RESOLUTION_STR)))
+ if 0 == k:
+ print("[%02d] Multiple defects for investigation '%s':" % (count,investigation[ORM.INVESTIGATION_NAME]))
+ print(" [%02d] %s: %s (%s)" % (k+1,defect[ORM.DEFECT_NAME],defect[ORM.DEFECT_SUMMARY],ORM.get_orm_string(defect[ORM.DEFECT_RESOLUTION],ORM.DEFECT_RESOLUTION_STR)))
conn.close()
#################################
-# find_duplicate_names
+# report_duplicate_names
+#
+#
+#
#
-def find_duplicate_names():
+def report_duplicate_names():
conn = sqlite3.connect(srtDbName)
cur = conn.cursor()
@@ -1452,10 +1618,10 @@ def find_duplicate_names():
conn.close()
#################################
-# find_bad_links
+# fix_bad_links
#
-def find_bad_links():
+def fix_bad_links():
conn = sqlite3.connect(srtDbName)
cur = conn.cursor()
@@ -1532,42 +1698,7 @@ def find_bad_links():
conn.close()
#################################
-# find_empty_status
-#
-
-def find_empty_status():
-
- conn = sqlite3.connect(srtDbName)
- cur = conn.cursor()
- cur_del = conn.cursor()
-
- #
- print('\n=== CVE Empty Status Check ===\n')
- #
-
- cur.execute('SELECT * FROM orm_cve')
- empty_count = 0
- date_count = 0
- other_count = 0
- total = 0
- for i,cve in enumerate(cur):
- total += 1
- if not cve[ORM.CVE_STATUS]:
- empty_count += 1
- elif '-' in cve[ORM.CVE_STATUS]:
- date_count += 1
- else:
- try:
- value = int(cve[ORM.CVE_STATUS])
- except:
- other_count += 1
-
-
- print("STATUS: Empty=%d, Date=%d, OtherBad=%d, total=%d of %d" % (empty_count,date_count,other_count,empty_count+date_count+other_count,total))
-
-
-#################################
-# fix_bad_new
+# fix_bad_score_date
#
# Remove the 'score_date' value to repair the migration to '0006_reconcile', allowing
# the field for new CVEs to be regenerated. The schema for this field is 'models.DateField'
@@ -1575,14 +1706,14 @@ def find_empty_status():
# date_time value. That crashes Django-2.2 (but not Django-1.11).
#
-def fix_bad_new():
+def fix_bad_score_date():
conn = sqlite3.connect(srtDbName)
cur = conn.cursor()
cur_fix = conn.cursor()
#
- print('\n=== CVE fix_bad_new Check ===\n')
+ print('\n=== CVE fix_bad_score_date Check ===\n')
#
cur.execute('SELECT * FROM orm_cve WHERE status = %d' % ORM.STATUS_NEW)
@@ -1600,6 +1731,644 @@ def fix_bad_new():
conn.commit()
#################################
+# report_cve_status_summary()
+#
+# Report the distribution of the CVE status and V3/V2
+# severities across the years. Used to track trends, and
+# also to validate the data and its repairs/migrations.
+#
+
+def report_cve_status_summary():
+
+ I_COUNT = 0
+
+ I_NEW = 1
+ I_VULNERABLE = 2
+ I_INVESTIGATE = 3
+ I_NOT_VULNERABLE = 4
+ I_NEW_RESERVED = 5
+ I_HISTORICAL = 6
+ I_STATUS_OTHER = 7
+
+ I_V3_CRITICAL = 8
+ I_V3_HIGH = 9
+ I_V3_MEDIUM = 10
+ I_V3_LOW = 11
+ I_V3_EMPTY = 12
+ I_V3_OTHER = 13
+
+ I_V2_CRITICAL = 14
+ I_V2_HIGH = 15
+ I_V2_MEDIUM = 16
+ I_V2_LOW = 17
+ I_V2_EMPTY = 18
+ I_V2_OTHER = 19
+
+ I_MAX = 20
+
+ YEAR_START = 1999
+ YEAR_STOP = 2020
+
+ label = [
+ 'NEW',
+ 'VUL',
+ 'INV',
+ 'NVUL',
+ 'NEW_R',
+ 'HIST',
+ 'OTHER',
+ ]
+
+ table = {}
+
+ def blank_row():
+ return [0 for i in range(I_MAX)]
+
+ # Prepare the array
+ for year in range(YEAR_START,YEAR_STOP+1):
+ table[year] = blank_row()
+ for status in range(I_NEW,I_STATUS_OTHER+1):
+ table[status] = blank_row()
+ table['total1'] = blank_row()
+ table['total2'] = blank_row()
+
+ conn = sqlite3.connect(srtDbName)
+ cur = conn.cursor()
+ cur.execute('SELECT * FROM orm_cve')
+ i = 0
+ for cve in cur:
+ i += 1
+
+ year = int(cve[ORM.CVE_NAME].split('-')[1])
+ v3_severity = cve[ORM.CVE_CVSSV3_BASESEVERITY].upper().strip()
+ v2_severity = cve[ORM.CVE_CVSSV2_SEVERITY].upper().strip()
+ status = cve[ORM.CVE_STATUS]
+
+ if status == ORM.STATUS_NEW:
+ table[year][I_NEW] += 1
+ status_row = I_NEW
+ elif status == ORM.STATUS_VULNERABLE:
+ table[year][I_VULNERABLE] += 1
+ status_row = I_VULNERABLE
+ elif status == ORM.STATUS_INVESTIGATE:
+ table[year][I_INVESTIGATE] += 1
+ status_row = I_INVESTIGATE
+ elif status == ORM.STATUS_NOT_VULNERABLE:
+ table[year][I_NOT_VULNERABLE] += 1
+ status_row = I_NOT_VULNERABLE
+ elif status == ORM.STATUS_NEW_RESERVED:
+ table[year][I_NEW_RESERVED] += 1
+ status_row = I_NEW_RESERVED
+ elif status == ORM.STATUS_HISTORICAL:
+ table[year][I_HISTORICAL] += 1
+ status_row = I_HISTORICAL
+ else:
+ table[year][I_STATUS_OTHER] += 1
+ status_row = I_STATUS_OTHER
+
+ if v3_severity == 'CRITICAL':
+ table[year][I_V3_CRITICAL] += 1
+ table[status_row][I_V3_CRITICAL] += 1
+ elif v3_severity == 'HIGH':
+ table[year][I_V3_HIGH] += 1
+ table[status_row][I_V3_HIGH] += 1
+ elif v3_severity == 'MEDIUM':
+ table[year][I_V3_MEDIUM] += 1
+ table[status_row][I_V3_MEDIUM] += 1
+ elif v3_severity == 'LOW':
+ table[year][I_V3_LOW] += 1
+ table[status_row][I_V3_LOW] += 1
+ elif v3_severity == '':
+ table[year][I_V3_EMPTY] += 1
+ table[status_row][I_V3_EMPTY] += 1
+ else:
+ table[year][I_V3_OTHER] += 1
+ table[status_row][I_V3_OTHER] += 1
+
+ if v2_severity == 'CRITICAL':
+ table[year][I_V2_CRITICAL] += 1
+ table[status_row][I_V2_CRITICAL] += 1
+ elif v2_severity == 'HIGH':
+ table[year][I_V2_HIGH] += 1
+ table[status_row][I_V2_HIGH] += 1
+ elif v2_severity == 'MEDIUM':
+ table[year][I_V2_MEDIUM] += 1
+ table[status_row][I_V2_MEDIUM] += 1
+ elif v2_severity == 'LOW':
+ table[year][I_V2_LOW] += 1
+ table[status_row][I_V2_LOW] += 1
+ elif v2_severity == '':
+ table[year][I_V2_EMPTY] += 1
+ table[status_row][I_V2_EMPTY] += 1
+ else:
+ table[year][I_V2_OTHER] += 1
+ table[status_row][I_V2_OTHER] += 1
+
+ #
+ # Year Summary
+ #
+
+ print("")
+ print(" |Cve |Status |V3 Severity |V2 Severity |")
+ print(" | COUNT| NEW, VUL, INV, NVUL, NEW_R, HIST, OTHR| CRIT, HIGH, MED, LOW, EMPTY, OTHR| CRIT, HIGH, MED, LOW, EMPTY, OTHR|")
+ print("-----|------|------------------------------------------------|-----------------------------------------|-----------------------------------------|")
+
+ for year in range(YEAR_START,YEAR_STOP+1):
+ # Sum the status counts
+ for index in range(I_COUNT,I_STATUS_OTHER+1):
+ table[year][I_COUNT] += table[year][index]
+ # print the columns
+ print("%05d|" % year,end = '')
+ for index in range(I_COUNT,I_V2_OTHER+1):
+ print("%6d%s" % (table[year][index],'|' if index in [I_STATUS_OTHER,I_V3_OTHER,I_V2_OTHER,I_COUNT] else ','),end = '')
+ print("")
+
+ print(" ====|======|================================================|=========================================|=========================================|")
+ totals = blank_row()
+ # Vertical Totals
+ for year in range(YEAR_START,YEAR_STOP+1):
+ for index in range(I_COUNT,I_V2_OTHER+1):
+ totals[index] += table[year][index]
+ print("%-5s|" % 'Total',end = '')
+ for index in range(I_COUNT,I_V2_OTHER+1):
+ print("%6d%s" % (totals[index],'|' if index in [I_STATUS_OTHER,I_V3_OTHER,I_V2_OTHER,I_COUNT] else ','),end = '')
+ print("")
+
+ #
+ # Status Summary
+ #
+
+ print("-----|-------------------------------------------------------|-----------------------------------------|-----------------------------------------|")
+ for status in range(I_NEW,I_STATUS_OTHER+1):
+ print("%-5s|" % label[status-I_NEW],end = '')
+ print(" |",end = '')
+ for index in range(I_V3_CRITICAL,I_V2_OTHER+1):
+ print("%6d%s" % (table[status][index],'|' if index in [I_COUNT,I_STATUS_OTHER,I_V3_OTHER,I_V2_OTHER] else ','),end = '')
+ print("")
+
+ print(" ====|=======================================================|=========================================|=========================================|")
+ # Calculate Status Totals
+ totals = blank_row()
+ # Vertical Totals
+ for status in range(I_NEW,I_STATUS_OTHER+1):
+ for index in range(I_V3_CRITICAL,I_V2_OTHER+1):
+ totals[index] += table[status][index]
+ print("%-5s|" % 'Total',end = '')
+ print(" |",end = '')
+ for index in range(I_V3_CRITICAL,I_V2_OTHER+1):
+ print("%6d%s" % (totals[index],'|' if index in [I_COUNT,I_STATUS_OTHER,I_V3_OTHER,I_V2_OTHER] else ','),end = '')
+ print("")
+
+
+#################################
+# report_db_status_summary()
+#
+# Report the distribution of the status across the
+# CVE/VUL/INV/DEF by years. Used to track trends, and
+# also to validate the data and its repairs/migrations.
+#
+
+def report_db_status_summary():
+
+ today_str = datetime.today().strftime(ORM.DATASOURCE_DATETIME_FORMAT)
+
+ YEAR_START = 1999
+ YEAR_STOP = 2020
+
+ I_COUNT = 0
+
+ # Object Status indexes
+ I_CVE_NEW = 1; I_CVE_VULNERABLE = 2; I_CVE_INVESTIGATE = 3; I_CVE_NOT_VULNERABLE = 4; I_CVE_NEW_RESERVED = 5; I_CVE_HISTORICAL = 6; I_CVE_STATUS_OTHER = 7
+ I_VUL_NEW = 8; I_VUL_VULNERABLE = 9; I_VUL_INVESTIGATE = 10; I_VUL_NOT_VULNERABLE = 11; I_VUL_NEW_RESERVED = 12; I_VUL_HISTORICAL = 13; I_VUL_STATUS_OTHER = 14
+ I_INV_NEW = 15; I_INV_INVNERABLE = 16; I_INV_INVESTIGATE = 17; I_INV_NOT_VULNERABLE = 18; I_INV_NEW_RESERVED = 19; I_INV_HISTORICAL = 20; I_INV_STATUS_OTHER = 21
+ I_DEF_NEW = 22; I_DEF_DEFNERABLE = 23; I_DEF_DEFESTIGATE = 24; I_DEF_NOT_VULNERABLE = 25; I_DEF_NEW_RESERVED = 26; I_DEF_HISTORICAL = 27; I_DEF_STATUS_OTHER = 28
+ I_STATUS_MAX = 29
+ # Summary Status indexes
+ I_NEW = 1; I_VULNERABLE = 2; I_INVESTIGATE = 3; I_NOT_VULNERABLE = 4; I_NEW_RESERVED = 5; I_HISTORICAL = 6; I_STATUS_OTHER = 7
+
+ # SRTool Priority
+ I_CVE_CRITICAL = 1; I_CVE_HIGH = 2; I_CVE_MEDIUM = 3; I_CVE_LOW = 4; I_CVE_UNDEFINED = 5; I_CVE_ERROR = 6
+ I_VUL_CRITICAL = 7; I_VUL_HIGH = 8; I_VUL_MEDIUM = 9; I_VUL_LOW = 10; I_VUL_UNDEFINED = 11; I_VUL_ERROR = 12
+ I_INV_CRITICAL = 13; I_INV_HIGH = 14; I_INV_MEDIUM = 15; I_INV_LOW = 16; I_INV_UNDEFINED = 17; I_INV_ERROR = 18
+ I_DEF_CRITICAL = 19; I_DEF_HIGH = 20; I_DEF_MEDIUM = 21; I_DEF_LOW = 22; I_DEF_UNDEFINED = 23; I_DEF_ERROR = 24
+ I_PRIORITY_MAX = 25
+
+ cve_idx = 100
+ vul_idx = 101
+ inv_idx = 102
+ def_idx = 103
+ object_label = {
+ I_NEW:'NEW',I_VULNERABLE:'VUL',I_INVESTIGATE:'INV',I_NOT_VULNERABLE:'NVUL',I_NEW_RESERVED:'NEW_R',I_HISTORICAL:'HIST',I_STATUS_OTHER:'OTHER',
+ cve_idx:'CVE',vul_idx:'VUL',inv_idx:'INV',def_idx:'DEF',
+ }
+
+ # Prepare the sum table
+ def blank_row():
+ return [0 for i in range(max(I_STATUS_MAX,I_PRIORITY_MAX)+1)]
+ table_status = {}
+ table_priority = {}
+ for year in range(YEAR_START,YEAR_STOP+1):
+ table_status[year] = blank_row()
+ table_priority[year] = blank_row()
+ for status_idx in range(I_NEW,I_STATUS_OTHER+1):
+ table_status[status_idx] = blank_row()
+
+ def sum_object_status(status,year,obj_offset,merge_inactive=True):
+ offset = obj_offset - I_CVE_NEW
+ if merge_inactive:
+ if status == ORM.STATUS_NEW_INACTIVE:
+ status = ORM.STATUS_NEW
+ elif status == ORM.STATUS_INVESTIGATE_INACTIVE:
+ status = ORM.STATUS_INVESTIGATE
+ elif status == ORM.STATUS_VULNERABLE_INACTIVE:
+ status = ORM.STATUS_VULNERABLE
+ elif status == ORM.STATUS_NOT_VULNERABLE_INACTIVE:
+ status = ORM.STATUS_NOT_VULNERABLE
+ if status == ORM.STATUS_NEW:
+ table_status[year][I_NEW+offset] += 1
+ table_status[I_NEW][I_NEW+offset] += 1
+ elif status == ORM.STATUS_VULNERABLE:
+ table_status[year][I_VULNERABLE+offset] += 1
+ table_status[I_VULNERABLE][I_VULNERABLE+offset] += 1
+ elif status == ORM.STATUS_INVESTIGATE:
+ table_status[year][I_INVESTIGATE+offset] += 1
+ table_status[I_INVESTIGATE][I_INVESTIGATE+offset] += 1
+ elif status == ORM.STATUS_NOT_VULNERABLE:
+ table_status[year][I_NOT_VULNERABLE+offset] += 1
+ table_status[I_NOT_VULNERABLE][I_NOT_VULNERABLE+offset] += 1
+ elif status == ORM.STATUS_NEW_RESERVED:
+ table_status[year][I_NEW_RESERVED+offset] += 1
+ table_status[I_NEW_RESERVED][I_NEW_RESERVED+offset] += 1
+ elif status == ORM.STATUS_HISTORICAL:
+ table_status[year][I_HISTORICAL+offset] += 1
+ table_status[I_HISTORICAL][I_HISTORICAL+offset] += 1
+ else:
+ table_status[year][I_STATUS_OTHER+offset] += 1
+ table_status[I_STATUS_OTHER][I_STATUS_OTHER+offset] += 1
+
+ def sum_object_priority(priority,year,obj_offset):
+ offset = obj_offset - I_CVE_CRITICAL
+ if priority == ORM.PRIORITY_LOW:
+ table_priority[year][I_CVE_LOW+offset] += 1
+ elif priority == ORM.PRIORITY_MEDIUM:
+ table_priority[year][I_CVE_MEDIUM+offset] += 1
+ elif priority == ORM.PRIORITY_HIGH:
+ table_priority[year][I_CVE_HIGH+offset] += 1
+ elif priority == ORM.PRIORITY_CRITICAL:
+ table_priority[year][I_CVE_CRITICAL+offset] += 1
+ else: # priority == ORM.PRIORITY_UNDEFINED:
+ table_priority[year][I_CVE_UNDEFINED+offset] += 1
+
+ conn = sqlite3.connect(srtDbName)
+ cur_cve = conn.cursor()
+ cur_cve2vul = conn.cursor()
+ cur_vul = conn.cursor()
+ cur_vul2inv = conn.cursor()
+ cur_inv = conn.cursor()
+ cur_inv2def = conn.cursor()
+ cur_def = conn.cursor()
+ cur_cve.execute('SELECT * FROM orm_cve')
+
+ #
+ # Year-specific table_status
+ #
+
+ i = 0
+ for count,cve in enumerate(cur_cve):
+ year = int(cve[ORM.CVE_NAME].split('-')[1])
+
+ # Sum the CVE status
+ sum_object_status(cve[ORM.CVE_STATUS],year,I_CVE_NEW)
+ sum_object_priority(cve[ORM.CVE_PRIORITY],year,I_CVE_CRITICAL)
+
+ i += 1
+
+ # Find all related Vulnerabilities
+ cur_cve2vul.execute('SELECT * FROM orm_cvetovulnerablility WHERE cve_id = %d' % cve[ORM.CVE_ID])
+ for cve2vul in cur_cve2vul:
+ # Sum the Vulnerability status
+ cur_vul.execute('SELECT * FROM orm_vulnerability WHERE id = %d' % cve2vul[ORM.CVETOVULNERABLILITY_VULNERABILITY_ID])
+ for vul in cur_vul:
+ sum_object_status(vul[ORM.VULNERABILITY_STATUS],year,I_VUL_NEW)
+ sum_object_priority(vul[ORM.VULNERABILITY_PRIORITY],year,I_VUL_CRITICAL)
+
+ # Find all related Investigations
+ cur_vul2inv.execute('SELECT * FROM orm_vulnerabilitytoinvestigation WHERE vulnerability_id = %d' % vul[ORM.VULNERABILITY_ID])
+ for vul2inv in cur_vul2inv:
+ # Sum the Investigation status
+ cur_inv.execute('SELECT * FROM orm_investigation WHERE id = %d' % vul2inv[ORM.VULNERABILITYTOINVESTIGATION_INVESTIGATION_ID])
+ for inv in cur_inv:
+ sum_object_status(inv[ORM.INVESTIGATION_STATUS],year,I_INV_NEW)
+ sum_object_priority(inv[ORM.INVESTIGATION_PRIORITY],year,I_INV_CRITICAL)
+
+ # Find all related Defects
+ cur_inv2def.execute('SELECT * FROM orm_investigationtodefect WHERE investigation_id = %d' % inv[ORM.INVESTIGATION_ID])
+ for inv2def in cur_inv2def:
+ # Sum the Defect status
+ cur_def.execute('SELECT * FROM orm_defect WHERE id = %d' % inv2def[ORM.INVESTIGATIONTODEFECT_DEFECT_ID])
+ for defect in cur_def:
+ sum_object_status(defect[ORM.DEFECT_SRT_STATUS],year,I_DEF_NEW)
+ sum_object_priority(defect[ORM.DEFECT_SRT_PRIORITY],year,I_DEF_CRITICAL)
+
+ if 1000 == i:
+ print("%7d: %-20s\r" % (count+1,cve[ORM.CVE_NAME]),end='')
+ i = 0
+# if 10000 < count:
+# break
+
+ #
+ # Object-specific table_status
+ #
+
+ for object_idx in range(cve_idx,def_idx+1):
+ table_status[object_idx] = blank_row()
+ table_priority[object_idx] = blank_row()
+ table_status['total'] = blank_row()
+ table_priority['total'] = blank_row()
+
+ print("CVEs...")
+ cur_cve.execute('SELECT * FROM orm_cve')
+ for cve in cur_cve:
+ sum_object_status(cve[ORM.CVE_STATUS],cve_idx,I_CVE_NEW)
+ sum_object_priority(cve[ORM.CVE_PRIORITY],cve_idx,I_CVE_CRITICAL)
+ print("Vulnerabilities...")
+ cur_vul.execute('SELECT * FROM orm_vulnerability')
+ for vul in cur_vul:
+ sum_object_status(vul[ORM.VULNERABILITY_STATUS],vul_idx,I_CVE_NEW)
+ sum_object_priority(vul[ORM.VULNERABILITY_PRIORITY],vul_idx,I_CVE_CRITICAL)
+ cur_inv.execute('SELECT * FROM orm_investigation')
+ print("Investigations...")
+ for inv in cur_inv:
+ sum_object_status(inv[ORM.INVESTIGATION_STATUS],inv_idx,I_CVE_NEW)
+ sum_object_priority(inv[ORM.INVESTIGATION_PRIORITY],inv_idx,I_CVE_CRITICAL)
+ print("Defects...")
+ cur_def.execute('SELECT * FROM orm_defect')
+ for defect in cur_def:
+ sum_object_status(defect[ORM.DEFECT_SRT_STATUS],def_idx,I_CVE_NEW)
+ sum_object_priority(defect[ORM.DEFECT_SRT_PRIORITY],def_idx,I_CVE_CRITICAL)
+ i += 1
+
+ #
+ # Display Status Summary
+ #
+
+ # Year Summary
+ print("\n=== SRTool Status Summary (%s) (%s) ===\n" % (today_str,srtool_basepath))
+ print(" |Cve |CVE Status |Vulnerability Status |Investigation Status |Defect Status |")
+ print(" | COUNT| NEW, VUL, INV, NVUL, NEW_R, HIST, OTHR| NEW, VUL, INV, NVUL, NEW_R, HIST, OTHR| NEW, VUL, INV, NVUL, NEW_R, HIST, OTHR| NEW, VUL, INV, NVUL, NEW_R, HIST, OTHR|")
+ print("-----|-------|-------------------------------------------------|------------------------------------------------|-------------------------------------------------|------------------------------------------------|")
+
+ for year in range(YEAR_START,YEAR_STOP+1):
+ # Sum the status counts
+ for index in range(I_CVE_NEW,I_CVE_STATUS_OTHER+1):
+ table_status[year][I_COUNT] += table_status[year][index]
+ # print the columns
+ print("%5d|" % year,end = '')
+ for index in range(I_COUNT,I_STATUS_MAX):
+ format = "%7d%s" if index in [I_COUNT,I_CVE_NOT_VULNERABLE,I_VUL_NOT_VULNERABLE,I_INV_NOT_VULNERABLE,I_DEF_NOT_VULNERABLE] else "%6d%s"
+ print(format % (table_status[year][index],'|' if index in [I_COUNT,I_CVE_STATUS_OTHER,I_VUL_STATUS_OTHER,I_INV_STATUS_OTHER,I_DEF_STATUS_OTHER] else ','),end = '')
+ print("")
+
+ print("=====|=======|=================================================|================================================|=================================================|================================================|")
+ totals = blank_row()
+ # Vertical Totals
+ for year in range(YEAR_START,YEAR_STOP+1):
+ for index in range(I_COUNT,I_STATUS_MAX):
+ totals[index] += table_status[year][index]
+ print("%-5s|" % 'Total',end = '')
+ for index in range(I_COUNT,I_STATUS_MAX):
+ format = "%7d%s" if index in [I_COUNT,I_CVE_NOT_VULNERABLE,I_VUL_NOT_VULNERABLE,I_INV_NOT_VULNERABLE,I_DEF_NOT_VULNERABLE] else "%6d%s"
+ print(format % (totals[index],'|' if index in [I_COUNT,I_CVE_STATUS_OTHER,I_VUL_STATUS_OTHER,I_INV_STATUS_OTHER,I_DEF_STATUS_OTHER] else ','),end = '')
+ print("")
+
+ # Count Summary
+ print("")
+ print(" | COUNT| NEW, VUL, INV, NVUL, NEW_R, HIST, OTHR|")
+ print("-----|-------|-------------------------------------------------|")
+ for object_idx in range(cve_idx,def_idx+1):
+ # Sum the status counts
+ for index in range(I_CVE_NEW,I_CVE_STATUS_OTHER+1):
+ table_status[object_idx][I_COUNT] += table_status[object_idx][index]
+ # print the columns
+ print("%5s|" % object_label[object_idx],end = '')
+ for index in range(I_COUNT,I_CVE_STATUS_OTHER+1):
+ format = "%7d%s" if index in [I_COUNT,I_CVE_NOT_VULNERABLE,I_VUL_NOT_VULNERABLE,I_INV_NOT_VULNERABLE,I_DEF_NOT_VULNERABLE] else "%6d%s"
+ print(format % (table_status[object_idx][index],'|' if index in [I_COUNT,I_CVE_STATUS_OTHER] else ','),end = '')
+ print("")
+ print("-----|-------|-------------------------------------------------|")
+
+ #
+ # Display Priority Summary
+ #
+
+ # Year Summary
+ print("\n=== SRTool Priority Summary (%s) (%s) ===\n" % (today_str,srtool_basepath))
+ print(" |Cve |CVE Priority |Vulnerability Priority |Investigation Priority |Defect Priority |")
+ print(" | COUNT| CRIT, HIGH, MED, LOW, UNDEF, ERROR| CRIT, HIGH, MED, LOW, UNDEF, ERROR| CRIT, HIGH, MED, LOW, UNDEF, ERROR| CRIT, HIGH, MED, LOW, UNDEF, ERROR|")
+ print("-----|-------|------------------------------------------|------------------------------------------|------------------------------------------|------------------------------------------|")
+
+ for year in range(YEAR_START,YEAR_STOP+1):
+ # Sum the status counts
+ for index in range(I_COUNT,I_PRIORITY_MAX):
+ table_priority[year][I_COUNT] += table_priority[year][index]
+ # print the columns
+ print("%5d|" % year,end = '')
+ for index in range(I_COUNT,I_PRIORITY_MAX):
+ format = "%7d%s" if index in [I_COUNT,I_CVE_UNDEFINED,I_VUL_UNDEFINED,I_INV_UNDEFINED,I_DEF_UNDEFINED] else "%6d%s"
+ print(format % (table_priority[year][index],'|' if index in [I_COUNT,I_CVE_ERROR,I_VUL_ERROR,I_INV_ERROR,I_DEF_ERROR] else ','),end = '')
+ print("")
+
+ print("=====|=======|==========================================|==========================================|==========================================|==========================================|")
+ totals = blank_row()
+ # Vertical Totals
+ for year in range(YEAR_START,YEAR_STOP+1):
+ for index in range(I_COUNT,I_PRIORITY_MAX):
+ totals[index] += table_priority[year][index]
+ print("%-5s|" % 'Total',end = '')
+ for index in range(I_COUNT,I_PRIORITY_MAX):
+ format = "%7d%s" if index in [I_COUNT,I_CVE_UNDEFINED,I_VUL_UNDEFINED,I_INV_UNDEFINED,I_DEF_UNDEFINED] else "%6d%s"
+ print(format % (totals[index],'|' if index in [I_COUNT,I_CVE_ERROR,I_VUL_ERROR,I_INV_ERROR,I_DEF_ERROR] else ','),end = '')
+ print("")
+
+ print("")
+ print(" | COUNT| CRIT, HIGH, MED, LOW, UNDEF, ERROR|")
+ print("-----|-------|------------------------------------------|")
+ for object_idx in range(cve_idx,def_idx+1):
+ # Sum the status counts
+ for index in range(I_COUNT,I_CVE_ERROR+1):
+ table_priority[object_idx][I_COUNT] += table_priority[object_idx][index]
+ # print the columns
+ print("%5s|" % object_label[object_idx],end = '')
+ for index in range(I_COUNT,I_CVE_ERROR+1):
+ format = "%7d%s" if index in [I_COUNT,I_CVE_UNDEFINED] else "%6d%s"
+ print(format % (table_priority[object_idx][index],'|' if index in [I_COUNT,I_CVE_ERROR] else ','),end = '')
+ print("")
+ print("-----|-------|------------------------------------------|")
+
+
+#################################
+# report_unattached_records()
+#
+# Report the VUL unattached to CVE, INV unattached to VUL,
+# and DEF unattached to INV records
+#
+
+def report_unattached_records():
+
+ conn = sqlite3.connect(srtDbName)
+ cur = conn.cursor()
+ cur_cve = conn.cursor()
+ cur_cve2vul = conn.cursor()
+ cur_vul = conn.cursor()
+ cur_vul2inv = conn.cursor()
+ cur_inv = conn.cursor()
+ cur_inv2def = conn.cursor()
+ cur_def = conn.cursor()
+
+ if False:
+ # Add all Vulnerability record IDs
+ unattached_records = {}
+ cur.execute('SELECT * FROM orm_vulnerability')
+ count = 0
+ for vul in cur:
+ count += 1
+ unattached_records[vul[ORM.VULNERABILITY_ID]] = True
+ print("Count = %d" % count)
+ # Remove Vulnerabilities with mapping to CVEs
+ cur.execute('SELECT * FROM orm_cvetovulnerablility')
+ count = 0
+ for cve2vul in cur:
+ count += 1
+ del unattached_records[cve2vul[ORM.CVETOVULNERABLILITY_VULNERABILITY_ID]]
+ print("Count = %d" % count)
+ print("Unattached VUL to CVE = %d" % len(unattached_records))
+ for key in unattached_records:
+ vul = cur.execute('SELECT * FROM orm_vulnerability WHERE id = %d' % key).fetchone()
+ print(" %-10s [%7d] : %s" % (vul[ORM.VULNERABILITY_NAME],vul[ORM.VULNERABILITY_ID],vul[ORM.VULNERABILITY_DESCRIPTION][:60]))
+
+ if False:
+ # Add all Investigation record IDs
+ unattached_records = {}
+ cur.execute('SELECT * FROM orm_investigation')
+ count = 0
+ for inv in cur:
+ count += 1
+ unattached_records[inv[ORM.INVESTIGATION_ID]] = True
+ print("Count = %d" % count)
+ # Remove Investigations with mapping to Vulnerabilities
+ cur.execute('SELECT * FROM orm_vulnerabilitytoinvestigation')
+ count = 0
+ for vul2inv in cur:
+ count += 1
+ del unattached_records[vul2inv[ORM.VULNERABILITYTOINVESTIGATION_INVESTIGATION_ID]]
+ print("Count = %d" % count)
+ print("Unattached INV to VUL = %d" % len(unattached_records))
+ for key in unattached_records:
+ vul = cur.execute('SELECT * FROM orm_investigation WHERE id = %d' % key).fetchone()
+ print(" %-10s [%7d] : %s" % (inv[ORM.INVESTIGATION_NAME],inv[ORM.INVESTIGATION_ID],inv[ORM.INVESTIGATION_DESCRIPTION][:60]))
+
+ # Add all Defect record IDs
+ unattached_records = {}
+ cur.execute('SELECT * FROM orm_defect')
+ count = 0
+ for defect in cur:
+ count += 1
+ unattached_records[defect[ORM.DEFECT_ID]] = True
+ print("Count = %d" % count)
+ # Remove Defects with a mapping Investigations
+ cur.execute('SELECT * FROM orm_investigationtodefect')
+ count = 0
+ for inv2def in cur:
+ count += 1
+ del unattached_records[inv2def[ORM.INVESTIGATIONTODEFECT_DEFECT_ID]]
+ print("Count = %d" % count)
+ print("Unattached DEV to INV = %d" % len(unattached_records))
+ defect_list = []
+ for i,key in enumerate(unattached_records):
+ defect = cur.execute('SELECT * FROM orm_defect WHERE id = %d' % key).fetchone()
+ defect_list.append(defect)
+# print(" %-10s [%7d] : %s" % (defect[ORM.DEFECT_NAME],defect[ORM.DEFECT_ID],defect[ORM.DEFECT_SUMMARY][:60]))
+# if i > 10:
+# break
+
+ def sortDefectRecord(defect):
+ return defect[ORM.DEFECT_NAME]
+ defect_list.sort(key = sortDefectRecord)
+ defect_related = []
+ defect_nocve = []
+ defect_manycve = []
+ test = 0
+ test_match = 0
+ for i,defect in enumerate(defect_list):
+ name = defect[ORM.DEFECT_NAME]
+ summary = defect[ORM.DEFECT_SUMMARY]
+
+ if not defect[ORM.DEFECT_SUMMARY].startswith("Security Advisory - "):
+ defect_related.append('%s,%s' % (name,summary))
+ continue
+
+ if name.startswith('DEFECT-'):
+ name = name.replace('DEFECT-','')
+
+ product = name.split('-')[0]
+ match_set = re.findall(".*(CVE-\d+-\d+).*",summary)
+ if not match_set:
+ defect_nocve.append('%s,%s' % (name,summary))
+# print("ERROR: NO MATCH : %s,%s" % (name,summary))
+ continue
+ elif 1 < len(match_set):
+ defect_manycve.append('%s,%s' % (name,summary))
+# print("ERROR: MULTIPLE MATCHES : %s,%s,%s" % (name,summary,match_set))
+ continue
+ else:
+ cve = match_set[0]
+
+ if not product or not cve:
+ print("ERROR: MISSING : %s,%s,%s,%s" % (name,product,cve,summary))
+ test += 1
+
+ print("%s,%s,%s,%s,%s,%s" % (defect[ORM.DEFECT_NAME],product,cve,
+ ORM.get_orm_string(defect[ORM.DEFECT_STATUS],ORM.DEFECT_STATUS_STR),
+ ORM.get_orm_string(defect[ORM.DEFECT_RESOLUTION],ORM.DEFECT_RESOLUTION_STR),
+ defect[ORM.DEFECT_SUMMARY][:60],))
+
+ match = False
+ cur_cve.execute('SELECT * FROM orm_cve WHERE name = "%s"' % cve)
+ for cve in cur_cve:
+ cur_cve2vul.execute('SELECT * FROM orm_cvetovulnerablility WHERE cve_id = %d' % cve[ORM.CVE_ID])
+ for cve2vul in cur_cve2vul:
+ cur_vul2inv.execute('SELECT * FROM orm_vulnerabilitytoinvestigation WHERE vulnerability_id = %d' % cve2vul[ORM.CVETOVULNERABLILITY_VULNERABILITY_ID])
+ for vul2inv in cur_vul2inv:
+ # Find all related Defects
+ cur_inv2def.execute('SELECT * FROM orm_investigationtodefect WHERE investigation_id = %d' % vul2inv[ORM.VULNERABILITYTOINVESTIGATION_INVESTIGATION_ID])
+ for inv2def in cur_inv2def:
+ cur_def.execute('SELECT * FROM orm_defect WHERE id = %d' % inv2def[ORM.INVESTIGATIONTODEFECT_DEFECT_ID])
+ for defect in cur_def:
+ product_this = defect[ORM.DEFECT_NAME].replace('DEFECT-','').split('-')[0]
+ if product == product_this:
+ match = True
+ print(" DEFECT:%s,%s,%s,%s" % (defect[ORM.DEFECT_NAME],
+ ORM.get_orm_string(defect[ORM.DEFECT_STATUS],ORM.DEFECT_STATUS_STR),
+ ORM.get_orm_string(defect[ORM.DEFECT_RESOLUTION],ORM.DEFECT_RESOLUTION_STR),
+ defect[ORM.DEFECT_SUMMARY][:60],
+ ))
+ if match:
+ test_match += 1
+
+# if test > 10:
+# break
+
+ print("Related = %d" % len(defect_related))
+ print("NoCVE = %d" % len(defect_nocve))
+ print("ManyCVE = %d" % len(defect_manycve))
+ print("Testable = %d" % test)
+ print("TestMatches = %d" % test_match)
+
+# extract product,cve, defect [Defect Status,Defect Resolution]
+# see if CVE has VUL has INV for the product
+
+
+#################################
# main loop
#
@@ -1611,12 +2380,14 @@ def main(argv):
global srtDbName
# setup
- parser = argparse.ArgumentParser(description='srtool.py: manage the SRTool database')
+ parser = argparse.ArgumentParser(description='srtool_utils.py: manage/repair the SRTool database')
parser.add_argument('--sources', '-s', nargs='?', const='display', help='SRTool Sources')
parser.add_argument('--reset-sources', '-r', action='store_const', const='reset_sources', dest='command', help='Reset SRTool Sources')
parser.add_argument('--settings', '-S', action='store_const', const='settings', dest='command', help='Show the SRT Settings')
parser.add_argument('--remove-app-sources', dest='remove_app_sources', help='Remove data sources for a previous app')
+ # One shot database repair routines
+
parser.add_argument('--fix-name-sort', action='store_const', const='fix_name_sort', dest='command', help='Recalulate the CVE name sort values')
parser.add_argument('--fix-cve-recommend', action='store_const', const='fix_cve_recommend', dest='command', help='Fix the empty CVE recommend values')
parser.add_argument('--fix-new-reserved', action='store_const', const='fix_new_reserved', dest='command', help='Reset new reserved CVEs to NEW_RESERVED')
@@ -1625,22 +2396,27 @@ def main(argv):
parser.add_argument('--fix-reset-nist-to-create', dest='fix_reset_nist_to_create', help='Bulk reset CVE [prefix*] srt_create dates to NIST release dates')
parser.add_argument('--fix-missing-create-dates', action='store_const', const='fix_missing_create_dates', dest='command', help='Reset CVE srt_create dates to NIST release dates')
parser.add_argument('--fix-public-reserved', action='store_const', const='fix_public_reserved', dest='command', help='Reset CVE NEW_RESERVED if now public')
- parser.add_argument('--fix-remove-bulk-cve-history', action='store_const', const='fix_remove_bulk_cve_history', dest='command', help='foo')
- parser.add_argument('--fix-bad-mitre-init', action='store_const', const='fix_bad_mitre_init', dest='command', help='foo')
- parser.add_argument('--fix-bad-new', action='store_const', const='fix_bad_new', dest='command', help='foo')
- parser.add_argument('--find-empty-status', action='store_const', const='find_empty_status', dest='command', help='foo')
-
- parser.add_argument('--fix-severity', dest='fix_severity', help='Fix bad score/severity values, broken cve source links')
+ parser.add_argument('--fix-remove-bulk-cve-history', action='store_const', const='fix_remove_bulk_cve_history', dest='command', help='Remove a specific/accidental set of bulk CVE history updates ')
+ parser.add_argument('--fix-bad-mitre-init', action='store_const', const='fix_bad_mitre_init', dest='command', help='Fix MITRE "New" that should be "New-Reserved"')
+ parser.add_argument('--fix-bad-mitre-descr', dest='fix_bad_mitre_descr', help='Fix MITRE that were created with empty descriptions')
+ parser.add_argument('--fix-bad-score-date', action='store_const', const='fix_bad_score_date', dest='command', help='Clear score dates to fix obsolete formats')
parser.add_argument('--fix-trim-cve-scores', action='store_const', const='fix_trim_cve_scores', dest='command', help='Trim V3/V2 scores to one decimal place standard')
- parser.add_argument('--find-multiple-defects', action='store_const', const='find_multiple_defects', dest='command', help='foo')
- parser.add_argument('--find-duplicate-names', action='store_const', const='find_duplicate_names', dest='command', help='foo')
+ # Continuous maintenance validation and repair routines
+
+ parser.add_argument('--fix-bad-links', action='store_const', const='fix_bad_links', dest='command', help='Find bad links, e.g. "orm_cvesource" (add "-f" to fix)')
+ parser.add_argument('--fix-severity', dest='fix_severity', help='Find bad score/severity values, broken cve source links {ALL|"NIST 2020[,...]*"} (add "-f" to fix)')
- parser.add_argument('--fix-defects-to-products', action='store_const', const='fix_defects_to_products', dest='command', help='foo')
- parser.add_argument('--find-bad-links', action='store_const', const='find_bad_links', dest='command', help='Find bad links, e.g. "orm_cvesource" (add "-f" to fix)')
+ parser.add_argument('--report-multiple-defects', action='store_const', const='report_multiple_defects', dest='command', help='Report multiple defects per investigations')
+ parser.add_argument('--report-duplicate-names', action='store_const', const='report_duplicate_names', dest='command', help='Report duplicate names for CVE,VUL,INV,DEF')
+ parser.add_argument('--report-defects-to-products', action='store_const', const='report_defects_to_products', dest='command', help='Report defects without product link')
+ parser.add_argument('--report-cve-status-summary', action='store_const', const='report_cve_status_summary', dest='command', help='Report the CVE status summary')
+ parser.add_argument('--report-db-status-summary', action='store_const', const='report_db_status_summary', dest='command', help='Report the database status summary')
+ parser.add_argument('--report-unattached-records', action='store_const', const='report_unattached_records', dest='command', help='Report VUL/INV/DEF unattached to parent CVE/VUL/INV')
- parser.add_argument('--database', '-D', dest='database', help='Selected database file')
+ # Options
+ parser.add_argument('--database', '-D', dest='database', help='Select specific alternate database file (e.g. a backup)')
parser.add_argument('--force', '-f', action='store_true', dest='force', help='Force the update')
parser.add_argument('--update-skip-history', '-H', action='store_true', dest='update_skip_history', help='Skip history updates')
parser.add_argument('--verbose', '-v', action='store_true', dest='verbose', help='Debugging: verbose output')
@@ -1661,6 +2437,7 @@ def main(argv):
# Test for example the backup databases
if args.database:
srtDbName = args.database
+ import_orm_schema(os.path.dirname(srtDbName) )
if args.sources:
if args.sources.startswith('s'):
@@ -1699,27 +2476,33 @@ def main(argv):
fix_public_reserved()
elif 'fix_remove_bulk_cve_history' == args.command:
fix_remove_bulk_cve_history()
- elif 'fix_defects_to_products' == args.command:
- fix_defects_to_products()
+ elif 'report_defects_to_products' == args.command:
+ report_defects_to_products()
elif 'fix_bad_mitre_init' == args.command:
fix_bad_mitre_init()
- elif 'fix_bad_new' == args.command:
- fix_bad_new()
+ elif args.fix_bad_mitre_descr:
+ fix_bad_mitre_descr(args.fix_bad_mitre_descr)
+ elif 'fix_bad_score_date' == args.command:
+ fix_bad_score_date()
elif args.fix_severity:
fix_severity(args.fix_severity)
elif 'fix_trim_cve_scores' == args.command:
fix_trim_cve_scores()
- elif 'find_multiple_defects' == args.command:
- find_multiple_defects()
- elif 'find_duplicate_names' == args.command:
- find_duplicate_names()
- elif 'find_bad_links' == args.command:
- find_bad_links()
+ elif 'report_multiple_defects' == args.command:
+ report_multiple_defects()
+ elif 'report_duplicate_names' == args.command:
+ report_duplicate_names()
+ elif 'fix_bad_links' == args.command:
+ fix_bad_links()
+ elif 'report_cve_status_summary' == args.command:
+ report_cve_status_summary()
+ elif 'report_db_status_summary' == args.command:
+ report_db_status_summary()
+ elif 'report_unattached_records' == args.command:
+ report_unattached_records()
- elif 'find_empty_status' == args.command:
- find_empty_status()
else:
print("Command not found")