diff options
Diffstat (limited to 'bin/common/srtool_utils.py')
| -rwxr-xr-x | bin/common/srtool_utils.py | 931 |
1 files changed, 924 insertions, 7 deletions
diff --git a/bin/common/srtool_utils.py b/bin/common/srtool_utils.py index 8c13f3a1..ac65d42d 100755 --- a/bin/common/srtool_utils.py +++ b/bin/common/srtool_utils.py @@ -25,6 +25,9 @@ import os import sys import argparse import sqlite3 +from datetime import datetime, date +import time +import re # load the srt.sqlite schema indexes dir_path = os.path.dirname(os.path.dirname(os.path.realpath(__file__))) @@ -35,6 +38,7 @@ from common.srt_schema import ORM verbose = False cmd_skip = 0 cmd_count = 0 +force = False srtDbName = 'srt.sqlite' @@ -141,9 +145,6 @@ def remove_app_sources(master_app): # Is this reserved by Mitre? Is '** RESERVED **' within the first 20 char positions? def fix_new_reserved(): - global cmd_skip - global cmd_count - conn = sqlite3.connect(srtDbName) cur = conn.cursor() cur_write = conn.cursor() @@ -171,6 +172,7 @@ def fix_new_reserved(): reserved_pos = cve[ORM.CVE_DESCRIPTION].find('** RESERVED **') if (0 <= reserved_pos) and (20 > reserved_pos): print("STATUS_NEW_RESERVED:%s:%s:%s" % (cve[ORM.CVE_STATUS],cve[ORM.CVE_NAME],cve[ORM.CVE_DESCRIPTION][:40])) + # NOTE: we do not touch 'cve.srt_updated' for this background change sql = ''' UPDATE orm_cve SET status = ? WHERE id = ?''' @@ -180,12 +182,881 @@ def fix_new_reserved(): conn.commit() ################################# +# fix_new_tags +# + +# Fix the None "cve.tags" fields +def fix_new_tags(): + conn = sqlite3.connect(srtDbName) + cur = conn.cursor() + cur_write = conn.cursor() + + cur.execute('SELECT * FROM orm_cve') + i = 0 + j = 0 + for cve in cur: + i += 1 + + # Progress indicator support + if 0 == i % 10: + print('%05d: %20s\r' % (i,cve[ORM.CVE_NAME]), end='') + if (0 == i % 200): + conn.commit() + # Development/debug support + if cmd_skip: + if i < cmd_skip: + continue + if cmd_count: + if (i - cmd_skip) > cmd_count: + print("Count return: %s,%s" % (i,cmd_count)) + break + + if not cve[ORM.CVE_TAGS]: + # NOTE: we do not touch 'cve.srt_updated' for this background change + sql = ''' UPDATE orm_cve + SET tags = ? + WHERE id = ?''' + cur_write.execute(sql, ('', cve[ORM.CVE_ID],)) + j += 1 + print("\nCVE COUNT=%5d,%5d" % (i,j)) + conn.commit() + +################################# +# fixup fix_name_sort +# + +# Recompute all of the CVE name_sort fields +def fix_name_sort(): + conn = sqlite3.connect(srtDbName) + cur = conn.cursor() + cur_write = conn.cursor() + + cur.execute('SELECT * FROM orm_cve') + for i,cve in enumerate(cur): + name_sort = get_name_sort(cve[ORM.CVE_NAME]) + + # Progress indicator support + if 0 == i % 10: + print('%05d: %20s to %20s\r' % (i,cve[ORM.CVE_NAME],name_sort), end='') + if (0 == i % 200): + conn.commit() + + # NOTE: we do not touch 'cve.srt_updated' for this background change + sql = ''' UPDATE orm_cve + SET name_sort = ? + WHERE id = ?''' + cur_write.execute(sql, (name_sort, cve[ORM.CVE_ID],)) + conn.commit() + +################################# +# fixup fix_cve_recommend +# + +# Reset empty CVE recommend fields to the proper integer zero +def fix_cve_recommend(): + conn = sqlite3.connect(srtDbName) + cur = conn.cursor() + cur_write = conn.cursor() + + cur.execute('SELECT * FROM orm_cve WHERE recommend = ""') + i = 0 + fix_count = 0 + for cve in cur: + i += 1 + + # Progress indicator support + if 0 == i % 10: + print('%05d: %20s\r' % (i,cve[ORM.CVE_NAME]), end='') + + # + # Fix miss-write to lastModifiedDate, missing integer for recommend + # + + fix = False + + lastModifiedDate = cve[ORM.CVE_LASTMODIFIEDDATE] + if '0' == lastModifiedDate: + lastModifiedDate = '' + fix = True + + recommend = cve[ORM.CVE_RECOMMEND] + if not recommend: + recommend = 0 + fix = True + + # NOTE: we do not touch 'cve.srt_updated' for this background change + if fix: + sql = ''' UPDATE orm_cve + SET recommend = ?, lastModifiedDate = ? + WHERE id = ?''' + cur_write.execute(sql, (recommend, lastModifiedDate, cve[ORM.CVE_ID],)) + + fix_count += 1 + if (199 == fix_count % 200): + conn.commit() + + print("CVE RECOMMEND FIX COUNT=%d of %d" % (fix_count,i)) + if fix_count: + conn.commit() + conn.close() + +################################# +# fixup fix_srt_dates +# + +# Reset older 'date' values as 'datetime' values + +def _fix_datetime(value,default): + if (not value) or (not value[0].isdigit()): + return(default) + elif ':' in value: + return(value) + else: + return(datetime.strptime(value, '%Y-%m-%d')) + +def _fix_date(value,default): + if (not value) or (not value[0].isdigit()): + return(False,default) + elif not ':' in value: + return(False,value) + else: + value = re.sub('\..*','',value) + dt = datetime.strptime(value,ORM.DATASOURCE_DATETIME_FORMAT) + return(True,dt.strftime(ORM.DATASOURCE_DATE_FORMAT)) + + +def fix_srt_datetime(scope): + conn = sqlite3.connect(srtDbName) + cur = conn.cursor() + cur_write = conn.cursor() + + if ('d' == scope) or ('all' == scope): + cur.execute('SELECT * FROM orm_defect') + i = 0 + is_change_count = 0 + for defect in cur: + i += 1 + + # Progress indicator support + if 0 == i % 10: + print('%05d: %20s\r' % (i,defect[ORM.DEFECT_NAME]), end='') + if (0 == i % 200): + conn.commit() + # Development/debug support + if cmd_skip: + if i < cmd_skip: + continue + if cmd_count: + if (i - cmd_skip) > cmd_count: + print("Count return: %s,%s" % (i,cmd_count)) + break + + defect_srt_updated = _fix_datetime(defect[ORM.DEFECT_SRT_UPDATED],defect[ORM.DEFECT_DATE_UPDATED]) + 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],)) + is_change_count += 1 + print("DEFECT DATE FIX COUNT=%d/%d" % (is_change_count,i)) + conn.commit() + + # INVESTIGATION DATE FIX COUNT=1089363, real 12m20.041s = 1472 recs/sec + if ('i' == scope) or ('all' == scope): + cur.execute('SELECT * FROM orm_investigation') + i = 0 + is_change_count = 0 + for investigation in cur: + i += 1 + + # Progress indicator support + if 0 == i % 10: + print('%05d: %20s\r' % (i,investigation[ORM.INVESTIGATION_NAME]), end='') + if (0 == i % 200): + conn.commit() + time.sleep(0.1) # give time for Sqlite to sync + # Development/debug support + if cmd_skip: + if i < cmd_skip: + continue + if cmd_count: + if (i - cmd_skip) > cmd_count: + print("Count return: %s,%s" % (i,cmd_count)) + break + + srt_updated = _fix_datetime(investigation[ORM.INVESTIGATION_SRT_UPDATED],None) + srt_created = _fix_datetime(investigation[ORM.INVESTIGATION_SRT_CREATED],None) + if (not srt_updated) or (not srt_created): + print("ERROR[%d]: bad date field at '%s', U=%s,C=%s" % (i,investigation[ORM.INVESTIGATION_ID],investigation[ORM.INVESTIGATION_SRT_UPDATED],investigation[ORM.INVESTIGATION_SRT_CREATED])) + exit(1) + 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],)) + is_change_count += 1 + print("INVESTIGATION DATE FIX COUNT=%d/%d" % (is_change_count,i)) + conn.commit() + + # VULNERABILITY DATE FIX COUNT=86585, real 1m2.969s = 1374 recs/sec + if ('v' == scope) or ('all' == scope): + cur.execute('SELECT * FROM orm_vulnerability') + i = 0 + is_change_count = 0 + for vulnerability in cur: + i += 1 + + # Progress indicator support + if 0 == i % 10: + print('%05d: %20s\r' % (i,vulnerability[ORM.VULNERABILITY_NAME]), end='') + if (0 == i % 200): + conn.commit() + time.sleep(0.1) # give time for Sqlite to sync + # Development/debug support + if cmd_skip: + if i < cmd_skip: + continue + if cmd_count: + if (i - cmd_skip) > cmd_count: + print("Count return: %s,%s" % (i,cmd_count)) + break + + srt_updated = _fix_datetime(vulnerability[ORM.VULNERABILITY_SRT_UPDATED],None) + srt_created = _fix_datetime(vulnerability[ORM.VULNERABILITY_SRT_CREATED],None) + if (not srt_updated) or (not srt_created): + print("ERROR[%d]: bad date field at '%s', U=%s,C=%s" % (i,vulnerability[ORM.VULNERABILITY_ID],vulnerability[ORM.VULNERABILITY_SRT_UPDATED],vulnerability[ORM.VULNERABILITY_SRT_CREATED])) + exit(1) + 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],)) + is_change_count += 1 + print("VULNERABILITY DATE FIX COUNT=%d/%d" % (is_change_count,i)) + 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 + if ('c' == scope) or ('all' == scope): + cur.execute('SELECT * FROM orm_cve') + i = 0 + # Sparse updates + is_change = False + is_change_count = 0 + for cve in cur: + i += 1 + + # Progress indicator support + if 0 == i % 10: + print('%05d: %20s\r' % (i,cve[ORM.CVE_NAME]), end='') + if (0 == i % 200) and is_change: + conn.commit() + time.sleep(0.1) # give time for Sqlite to sync + is_change = False + + # Development/debug support + if cmd_skip: + if i < cmd_skip: + continue + if cmd_count: + if is_change_count > cmd_count: + print("Count return: %s,%s" % (i,cmd_count)) + break + + is_change = False + + if cve[ORM.CVE_ACKNOWLEDGE_DATE]: + acknowledge_date = _fix_datetime(cve[ORM.CVE_ACKNOWLEDGE_DATE],'alpha') + # If the default 'alpha' happens, then date had bad format and must go away + if ('alpha' == acknowledge_date) or (acknowledge_date != cve[ORM.CVE_ACKNOWLEDGE_DATE]): + acknowledge_date = None + is_change = True + + srt_updated = _fix_datetime(cve[ORM.CVE_SRT_UPDATED],None) + srt_created = _fix_datetime(cve[ORM.CVE_SRT_CREATED],None) + if (not srt_updated) or (not srt_created): + print("ERROR[%d]: bad date field at '%s', U=%s,C=%s" % (i,cve[ORM.CVE_ID],cve[ORM.CVE_SRT_UPDATED],cve[ORM.CVE_SRT_CREATED])) + exit(1) + if (srt_updated != cve[ORM.CVE_SRT_UPDATED]) or (srt_created != cve[ORM.CVE_SRT_CREATED]): + is_change = True + + # Anything to do? + 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],)) + is_change_count += 1 + print("CVE DATE FIX COUNT=%d/%d" % (is_change_count,i)) + conn.commit() + + # Fix CVE History + if scope in ('ch','all','history'): + cur.execute('SELECT * FROM orm_cvehistory') + i = 0 + # Sparse updates + is_change = False + is_change_count = 0 + for cve_history in cur: + i += 1 + + # Progress indicator support + if 0 == i % 10: + print('%05d: \r' % (i), end='') + if (0 == i % 200) and is_change: + conn.commit() + time.sleep(0.1) # give time for Sqlite to sync + is_change = False + + # Development/debug support + if cmd_skip: + if i < cmd_skip: + continue + if cmd_count: + if is_change_count > cmd_count: + print("Count return: %s,%s" % (i,cmd_count)) + break + + updated,history_date = _fix_date(cve_history[ORM.CVEHISTORY_DATE],'') + 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],)) + is_change_count += 1 + + # Commit all remaining changes + if is_change: + conn.commit() + print("CVE HISTORY DATE FIX COUNT=%d/%d" % (is_change_count,i)) + + # Fix Vulnerability History + if scope in ('vh','all','history'): + cur.execute('SELECT * FROM orm_vulnerabilityhistory') + i = 0 + # Sparse updates + is_change = False + is_change_count = 0 + for vulnerabilityhistory in cur: + i += 1 + + # Progress indicator support + if 0 == i % 10: + print('%05d: \r' % (i), end='') + if (0 == i % 200) and is_change: + conn.commit() + time.sleep(0.1) # give time for Sqlite to sync + is_change = False + + # Development/debug support + if cmd_skip: + if i < cmd_skip: + continue + if cmd_count: + if is_change_count > cmd_count: + print("Count return: %s,%s" % (i,cmd_count)) + break + + updated,history_date = _fix_date(vulnerabilityhistory[ORM.VULNERABILITYHISTORY_DATE],'') + 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 + + # Commit all remaining changes + if is_change: + conn.commit() + print("VULNERABILITY HISTORY DATE FIX COUNT=%d/%d" % (is_change_count,i)) + + # Fix Investigation History + if scope in ('ih','all','history'): + cur.execute('SELECT * FROM orm_investigationhistory') + i = 0 + # Sparse updates + is_change = False + is_change_count = 0 + for investigation_history in cur: + i += 1 + + # Progress indicator support + if 0 == i % 10: + print('%05d: \r' % (i), end='') + if (0 == i % 200) and is_change: + conn.commit() + time.sleep(0.1) # give time for Sqlite to sync + is_change = False + + # Development/debug support + if cmd_skip: + if i < cmd_skip: + continue + if cmd_count: + if is_change_count > cmd_count: + print("Count return: %s,%s" % (i,cmd_count)) + break + + updated,history_date = _fix_date(investigation_history[ORM.INVESTIGATIONHISTORY_DATE],'') + 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],)) + is_change_count += 1 + + # Commit all remaining changes + if is_change: + conn.commit() + print("INVESTIGATION HISTORY DATE FIX COUNT=%d/%d" % (is_change_count,i)) + +################################# +# fixup fix_cve_srt_create +# + +# Reset CVE srt_create to NIST release dates +def fix_reset_nist_to_create(cve_prefix): + conn = sqlite3.connect(srtDbName) + cur = conn.cursor() + cur_write = conn.cursor() + + def date_nist2srt(nist_date,default,cve_name,i): + if not nist_date or (4 > len(nist_date)): + return default + try: + return(datetime.strptime(nist_date, '%Y-%m-%d')) + except Exception as e: + print("\n\ndate_nist2srt:%s,%s,%s,%s" % (cve_name,e,cve_name,i)) + exit(1) + return default + + cur.execute('SELECT * FROM orm_cve WHERE name LIKE "'+cve_prefix+'%"') + + i = 0 + for cve in cur: + i += 1 + + # Progress indicator support + if 0 == i % 10: + print('%05d: %20s\r' % (i,cve[ORM.CVE_NAME]), end='') + if (0 == i % 200): + conn.commit() + print('') + # Development/debug support + if cmd_skip and (i < cmd_skip): continue + if cmd_count and ((i - cmd_skip) > cmd_count): break + + nist_released = date_nist2srt(cve[ORM.CVE_PUBLISHEDDATE],cve[ORM.CVE_SRT_CREATED],cve[ORM.CVE_NAME],i) + nist_modified = date_nist2srt(cve[ORM.CVE_LASTMODIFIEDDATE],cve[ORM.CVE_SRT_UPDATED],cve[ORM.CVE_NAME],i) + + sql = ''' UPDATE orm_cve + SET srt_created = ?, srt_updated = ? + WHERE id = ?''' + cur_write.execute(sql, (nist_released, nist_modified, cve[ORM.CVE_ID],)) + print("CVE DATE FIX COUNT=%d" % i) + conn.commit() + +################################# +# fixup fix_missing_create_dates +# + +# Reset CVE None creation dates to 2019-01-01, out of the way of reports +def fix_missing_create_dates(): + conn = sqlite3.connect(srtDbName) + cur = conn.cursor() + cur_write = conn.cursor() + + fix_date = datetime.strptime('Jan 1 2019', '%b %d %Y') + fix_count = 0 + + cur.execute('SELECT * FROM orm_cve') + i = 0 + for cve in cur: + i += 1 + + # Progress indicator support + if 0 == i % 10: + print('%05d: %20s\r' % (i,cve[ORM.CVE_NAME]), end='') + if (0 == i % 200): +# conn.commit() + #print('') + pass + # Development/debug support + if cmd_skip: + if i < cmd_skip: + continue + if cmd_count: + if (i - cmd_skip) > cmd_count: + print("Count return: %s,%s" % (i,cmd_count)) + break + + fix = False + if not cve[ORM.CVE_SRT_CREATED] or (0 > cve[ORM.CVE_SRT_CREATED].find(':')): + srt_created = fix_date + fix = True + else: + srt_created = cve[ORM.CVE_SRT_CREATED] + #srt_created = datetime.strptime(cve[ORM.CVE_SRT_CREATED],'%Y-%m-%d') + if not cve[ORM.CVE_SRT_UPDATED] or (0 > cve[ORM.CVE_SRT_UPDATED].find(':')): + srt_updated = fix_date + fix = True + else: + srt_updated = cve[ORM.CVE_SRT_UPDATED] + #srt_updated = datetime.strptime(cve[ORM.CVE_SRT_UPDATED],'%Y-%m-%d') + + if fix: + sql = ''' UPDATE orm_cve + SET srt_created = ?, srt_updated = ? + WHERE id = ?''' + cur_write.execute(sql, (srt_created, srt_updated, cve[ORM.CVE_ID],)) + fix_count += 1 + print("CVE DATE FIX COUNT=%d of %d" % (fix_count,i)) + conn.commit() + +################################# +# fixup fix_public_reserved +# + +# Reset CVE 'New-Reserved' if now public from NIST +def fix_public_reserved(): + conn = sqlite3.connect(srtDbName) + cur = conn.cursor() + cur_write = conn.cursor() + + fix_count = 0 + + cur.execute('SELECT * FROM orm_cve WHERE status = "%s"' % ORM.STATUS_NEW_RESERVED) + i = 0 + for cve in cur: + i += 1 + + # Progress indicator support + if 0 == i % 10: + print('%05d: %20s %d\r' % (i,cve[ORM.CVE_NAME],cve[ORM.CVE_STATUS]), end='') + if (0 == i % 200): + conn.commit() + #print('') + pass + # Development/debug support + if cmd_skip: + if i < cmd_skip: + continue + if cmd_count: + if (i - cmd_skip) > cmd_count: + print("Count return: %s,%s" % (i,cmd_count)) + break + + if cve[ORM.CVE_CVSSV3_BASESCORE] or cve[ORM.CVE_CVSSV2_BASESCORE]: + sql = ''' UPDATE orm_cve + SET status = ? + WHERE id = ?''' + cur_write.execute(sql, (ORM.STATUS_NEW, cve[ORM.CVE_ID],)) + fix_count += 1 + print("CVE DATE FIX COUNT=%d of %d" % (fix_count,i)) + conn.commit() + +################################# +# fix_remove_bulk_cve_history +# + +# Remove a specific/accidental set of bulk CVE history updates intended to be background +def fix_foo(): + conn = sqlite3.connect(srtDbName) + cur = conn.cursor() + cur_cve = conn.cursor() + cur_del = conn.cursor() + + 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 + for cvehistory in cur: + i += 1 + + # Progress indicator support + if 9 == i % 10: +# print('%05d: %20s %s \r' % (i,cvehistory[ORM.CVEHISTORY_COMMENT],cvehistory[ORM.CVEHISTORY_DATE]), 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 + + if not (cvehistory[ORM.CVEHISTORY_DATE] in ('2019-03-28','2019-03-27')): + continue + if not (cvehistory[ORM.CVEHISTORY_COMMENT].startswith("UPDATE(CVE):")): + continue + + cur_cve.execute('SELECT * FROM orm_cve WHERE id = "%s"' % cvehistory[ORM.CVEHISTORY_CVE_ID]) + cve = cur_cve.fetchone() + if not (cve[ORM.CVE_NAME].startswith("CVE-200")): + continue + + if 19 == fix_count % 20: + print("%4d) CVE=%s,CH_Comment=%s,CH_Date=%s" % (fix_count,cve[ORM.CVE_NAME],cvehistory[ORM.CVEHISTORY_COMMENT],cvehistory[ORM.CVEHISTORY_DATE])) + + mydata = cur_del.execute("DELETE FROM orm_cvehistory WHERE id=?", (cvehistory[ORM.CVEHISTORY_ID],)) + fix_count += 1 + + print("CVE DATE FIX COUNT=%d of %d" % (fix_count,i)) + conn.commit() + +################################# +# fix_defects_to_products +# + +# +def fix_defects_to_products(): + conn = sqlite3.connect(srtDbName) + cur = conn.cursor() + cur_cve = conn.cursor() + cur_del = conn.cursor() + + fix_count = 0 + + # Find all products + products = {} + cur.execute('SELECT * FROM orm_product') + for product in cur: + id = product[ORM.PRODUCT_ID] + name = "%s %s %s" % (product[ORM.PRODUCT_NAME],product[ORM.PRODUCT_VERSION],product[ORM.PRODUCT_PROFILE]) + products[id] = name + print("[%2d] %s" % (id,name)) + + # Test product field for all defects + cur.execute('SELECT * FROM orm_defect') + i = 0 + for defect in cur: + i += 1 + + # Progress indicator support + if 99 == i % 100: + print('%05d: %-20s\r' % (i,defect[ORM.DEFECT_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 + + product_id = defect[ORM.DEFECT_PRODUCT_ID] + if not product_id in products: + print("ERROR:[%5d] %-20s => %s" % (defect[ORM.DEFECT_ID],defect[ORM.DEFECT_NAME],product_id)) + +# print("CVE DATE FIX COUNT=%d of %d" % (fix_count,i)) + conn.commit() + +################################# +# find_multiple_defects +# + +def find_multiple_defects(): + + conn = sqlite3.connect(srtDbName) + cur_i2d = conn.cursor() + cur_inv = conn.cursor() + cur_def = conn.cursor() + + cur_inv.execute('SELECT * FROM orm_investigation') + count = 0 + for i,investigation in enumerate(cur_inv): + if 0 == i % 100: + print("%4d) V=%-30s\r" % (i,investigation[ORM.VULNERABILITY_NAME]), end='') + + cur_i2d.execute('SELECT * FROM orm_investigationtodefect WHERE investigation_id = "%s"' % investigation[ORM.INVESTIGATION_ID]) + i2d_list = cur_i2d.fetchall() + if 1 < len(i2d_list): + count += 1 + 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))) + conn.close() + +################################# +# find_duplicate_names +# + +def find_duplicate_names(): + + conn = sqlite3.connect(srtDbName) + cur = conn.cursor() + + + cur.execute('SELECT * FROM orm_cve') + cve_dict = {} + for i,cve in enumerate(cur): + if 0 == i % 100: + print("%4d) C=%-30s\r" % (i,cve[ORM.CVE_NAME]), end='') + + if not cve[ORM.CVE_NAME] in cve_dict: + cve_dict[cve[ORM.CVE_NAME]] = cve[ORM.CVE_ID] + else: + print("\nERROR:Multiple cve names '%s'" % cve[ORM.CVE_NAME]) + print(" a) id=%d" % cve_dict[cve[ORM.CVE_NAME]]) + print(" b) id=%d" % cve[ORM.CVE_ID]) + cve_dict = {} + print('') + + cur.execute('SELECT * FROM orm_vulnerability') + vul_dict = {} + for i,vulnerability in enumerate(cur): + if 0 == i % 100: + print("%4d) V=%-30s\r" % (i,vulnerability[ORM.VULNERABILITY_NAME]), end='') + + if not vulnerability[ORM.VULNERABILITY_NAME] in vul_dict: + vul_dict[vulnerability[ORM.VULNERABILITY_NAME]] = vulnerability[ORM.VULNERABILITY_ID] + else: + print("\nERROR:Multiple vulnerability names '%s'" % vulnerability[ORM.VULNERABILITY_NAME]) + print(" a) id=%d" % vul_dict[vulnerability[ORM.VULNERABILITY_NAME]]) + print(" b) id=%d" % vulnerability[ORM.VULNERABILITY_ID]) + vul_dict = {} + print('') + + cur.execute('SELECT * FROM orm_investigation') + inv_dict = {} + for i,investigation in enumerate(cur): + if 0 == i % 100: + print("%4d) I=%-30s\r" % (i,investigation[ORM.INVESTIGATION_NAME]), end='') + + if not investigation[ORM.INVESTIGATION_NAME] in inv_dict: + inv_dict[investigation[ORM.INVESTIGATION_NAME]] = investigation[ORM.INVESTIGATION_ID] + else: + print("\nERROR:Multiple investigation names '%s'" % investigation[ORM.INVESTIGATION_NAME]) + print(" a) id=%d" % inv_dict[investigation[ORM.INVESTIGATION_NAME]]) + print(" b) id=%d" % investigation[ORM.INVESTIGATION_ID]) + inv_dict = {} + print('') + + cur.execute('SELECT * FROM orm_defect') + dev_dict = {} + for i,defect in enumerate(cur): + if 0 == i % 100: + print("%4d) D=%-30s\r" % (i,defect[ORM.DEFECT_NAME]), end='') + + if not defect[ORM.DEFECT_NAME] in dev_dict: + dev_dict[defect[ORM.DEFECT_NAME]] = defect[ORM.DEFECT_ID] + else: + print("\nERROR:Multiple defect names '%s'" % defect[ORM.DEFECT_NAME]) + print(" a) id=%d" % dev_dict[defect[ORM.DEFECT_NAME]]) + print(" b) id=%d" % defect[ORM.DEFECT_ID]) + dev_dict = {} + print('') + + conn.close() + +################################# +# find_bad_links +# + +def find_bad_links(): + + conn = sqlite3.connect(srtDbName) + cur = conn.cursor() + cur_del = conn.cursor() + + # + print('\n=== CVE Source Check ===\n') + # + + cur.execute('SELECT * FROM orm_cvesource') + is_change = False + for i,cs in enumerate(cur): + cveid = cs[ORM.CVESOURCE_CVE_ID] + srcid = cs[ORM.CVESOURCE_DATASOURCE_ID] + if 0 == i % 100: + print("%4d) CVE=%6d,SRC=%6d\r" % (cs[ORM.CVESOURCE_ID],cveid,srcid), end='') + error = False + if (1 > cveid): error = True + if (1 > srcid): error = True + + if error: + print("ERROR: [%4d] CVE=%6d,SRC=%6d" % (cs[ORM.CVESOURCE_ID],cveid,srcid)) + if force: + sql = 'DELETE FROM orm_cvesource WHERE id=?' + cur_del.execute(sql, (cs[ORM.CVESOURCE_ID],)) + is_change = True + + print('') + if is_change: + conn.commit() + + # + print('\n=== Defect to Product Check ===\n') + # + + # Find all products + products = {} + cur.execute('SELECT * FROM orm_product') + for product in cur: + id = product[ORM.PRODUCT_ID] + name = "%s %s %s" % (product[ORM.PRODUCT_NAME],product[ORM.PRODUCT_VERSION],product[ORM.PRODUCT_PROFILE]) + products[id] = name + print("[%2d] %s" % (id,name)) + + # Test product field for all defects + cur.execute('SELECT * FROM orm_defect') + i = 0 + for defect in cur: + i += 1 + + # Progress indicator support + if 99 == i % 100: + print('%05d: %-20s\r' % (i,defect[ORM.DEFECT_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 + + product_id = defect[ORM.DEFECT_PRODUCT_ID] + if not product_id in products: + print("ERROR:[%5d] %-20s => %s" % (defect[ORM.DEFECT_ID],defect[ORM.DEFECT_NAME],product_id)) + + conn.close() + + +################################# # main loop # + def main(argv): global verbose global cmd_skip global cmd_count + global force # setup parser = argparse.ArgumentParser(description='srtool.py: manage the SRTool database') @@ -194,13 +1065,28 @@ def main(argv): 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') + 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') + parser.add_argument('--fix-new-tags', action='store_const', const='fix_new_tags', dest='command', help='Reset new cve.tags') + parser.add_argument('--fix-srt-datetime', dest='fix_srt_datetime', help='Fix SRT dates to datetimes [all|c|v|i|d|history|ch|vh|ih|dh]') + 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('--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') + + 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" (with "-f" to fix)') + 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') parser.add_argument('--skip', dest='skip', help='Debugging: skip record count') parser.add_argument('--count', dest='count', help='Debugging: short run record count') - parser.add_argument('--fix-new-reserved', action='store_const', const='fix_new_reserved', dest='command', help='Reset new reserved CVEs to NEW_RESERVED') - args = parser.parse_args() master_log = open(os.path.join(script_pathname, "update_logs/master_log.txt"), "a") @@ -210,6 +1096,7 @@ def main(argv): cmd_skip = int(args.skip) if None != args.count: cmd_count = int(args.count) + force = args.force if args.sources: if args.sources.startswith('s'): @@ -226,10 +1113,40 @@ def main(argv): sources('reset') elif 'settings' == args.command: settings() - elif 'fix_new_reserved' == args.command: - fix_new_reserved() + elif args.remove_app_sources: remove_app_sources(args.remove_app_sources) + + elif 'fix_name_sort' == args.command: + fix_name_sort() + elif 'fix_cve_recommend' == args.command: + fix_cve_recommend() + elif 'fix_new_reserved' == args.command: + fix_new_reserved() + elif 'fix_new_tags' == args.command: + fix_new_tags() + elif args.fix_srt_datetime: + fix_srt_datetime(args.fix_srt_datetime) + elif args.fix_reset_nist_to_create: + fix_reset_nist_to_create(args.fix_reset_nist_to_create) + elif 'fix_missing_create_dates' == args.command: + fix_missing_create_dates() + elif 'fix_public_reserved' == args.command: + 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 '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() + + else: print("Command not found") master_log.close() |