diff options
Diffstat (limited to 'bin/common/srtool_utils.py')
| -rwxr-xr-x | bin/common/srtool_utils.py | 1209 |
1 files changed, 996 insertions, 213 deletions
diff --git a/bin/common/srtool_utils.py b/bin/common/srtool_utils.py index 0ac64713..30ad1e9b 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") |