diff options
Diffstat (limited to 'bin/common/srtool_common.py')
| -rwxr-xr-x | bin/common/srtool_common.py | 675 |
1 files changed, 590 insertions, 85 deletions
diff --git a/bin/common/srtool_common.py b/bin/common/srtool_common.py index 13b5893d..d9fbd341 100755 --- a/bin/common/srtool_common.py +++ b/bin/common/srtool_common.py @@ -51,6 +51,7 @@ except: verbose = False cmd_skip = 0 cmd_count = 0 +cmd_test = False srtDbName = 'srt.sqlite' packageKeywordsFile = 'data/package_keywords.csv' @@ -85,6 +86,12 @@ def get_name_sort(cve_name): cve_name_sort = cve_name return cve_name_sort +def get_tag_key(tag,key,default=None): + d = json.loads(tag) + if key in d: + return d[key] + return default + ################################# # Load the package keyword source into the database # @@ -293,7 +300,8 @@ def score_new_cves(cve_filter): # Scan the open CVEs if 'NEW' == cve_filter: - sql = "SELECT * FROM orm_cve WHERE (status='%s' OR status='%s');" % (ORM.STATUS_NEW,ORM.STATUS_NEW_RESERVED) +# sql = "SELECT * FROM orm_cve WHERE (status='%s' OR status='%s') AND score_date IS NULL;" % (ORM.STATUS_NEW,ORM.STATUS_NEW_RESERVED) + sql = "SELECT * FROM orm_cve WHERE status='%s' AND score_date IS NULL;" % (ORM.STATUS_NEW) cur.execute(sql) elif cve_filter.startswith('CVE-'): cur.execute('SELECT * FROM orm_cve WHERE name LIKE "'+cve_filter+'%"') @@ -318,22 +326,24 @@ def score_new_cves(cve_filter): record_count = 0 write_count = 0 ds_count = 0 + is_change = False time_now = datetime.now() for i,cve in enumerate(cur): cve_name = cve[ORM.CVE_NAME] - if cve[ORM.CVE_SCORE_DATE]: - #cve_score_date = datetime.strptime(source[ORM.CVE_SCORE_DATE], '%Y-%m-%d %H:%M:%S') - # If there is any score_date, then nothing to do here - continue - +# if cve[ORM.CVE_SCORE_DATE]: +# #cve_score_date = datetime.strptime(source[ORM.CVE_SCORE_DATE], '%Y-%m-%d %H:%M:%S') +# # If there is any score_date, then nothing to do here +# continue +# # Progress indicator support if 0 == i % 10: print('%04d: %20s\r' % (i,cve_name), end='') - if (0 == i % 200) and not cmd_skip: + if (0 == i % 200) and (not cmd_skip) and is_change: conn.commit() print("%4d: COMMIT" % i) sleep(2) + is_change = False # Development/debug support if cmd_skip: if i < cmd_skip: @@ -351,21 +361,24 @@ def score_new_cves(cve_filter): recommend,recommend_list = compute_recommends(cve) cve_packages = '' if recommend_list: - # Go ahead and create/attach packages to CVEs cve_packages = attach_packages(cur_write, cve, recommend_list) - #cve_packages = cve[ORM.CVE_PACKAGES] + else: + cve_packages = cve[ORM.CVE_PACKAGES] - sql = ''' UPDATE orm_cve - SET recommend = ?, - recommend_list = ?, - packages = ?, - score_date = ? - WHERE id = ?''' - cur_write.execute(sql, (recommend, recommend_list, cve_packages, time_now.strftime(ORM.DATASOURCE_DATETIME_FORMAT), cve[ORM.CVE_ID])) - write_count += 1 + # Always set score_date since it has been evaluated + # NOTE: we do not touch 'cve.srt_updated' for this background change + sql = ''' UPDATE orm_cve + SET recommend = ?, + recommend_list = ?, + packages = ?, + score_date = ? + WHERE id = ?''' + cur_write.execute(sql, (recommend, recommend_list, cve_packages, time_now.strftime(ORM.DATASOURCE_DATETIME_FORMAT), cve[ORM.CVE_ID])) + write_count += 1 + is_change = True - if verbose: print(" %d:%s:%s" % (recommend,recommend_list,cve_packages)) +# if verbose: print(" %d:%s:%s" % (recommend,recommend_list,cve_packages)) # Attach all matching CVE sources for ds_obj in ds_list: @@ -379,8 +392,9 @@ def score_new_cves(cve_filter): ds_count += 1 print("%30sADDED [%4d]: %20s <- %20s\r" % ('',ds_count,ds_obj['key'],cve[ORM.CVE_NAME]),end='') - conn.commit() - print("COMMIT") + if is_change: + conn.commit() + print("COMMIT") print("\nUpdated CVEs=%d, Added alternate sources=%d" % (write_count,ds_count)) ################################# @@ -412,6 +426,430 @@ def init_notify_categories(filename): conn.close() ################################# +# Update cumulative Cve/Vulnerability/Investigation status +# +# * Scan the respective child Vulnerabilities/Investigations/Defects, and +# sum them into cumulative status for parent +# * Rules for Status: +# If any child is VULNERABLE, then the parent is VULNERABLE +# else if any child is INVESTIGATE, then the parent is INVESTIGATE +# else if any child is NEW, then the parent is INVESTIGATE +# else the parent is NOT_VULNERABLE +# * Exceptions: +# Children that are 'ORM.STATUS_HISTORICAL' or 'ORM.STATUS_NEW_RESERVED' have no vote +# If there are no children nor any children with votes, then the status is left unchanged +# * Rules for Priority: +# If any child has a higher priority, that priority is used +# + +def _update_cve_status(cur,cve,srtool_today,update_skip_history): + if verbose: print("Cve:%s:%s" % (cve[ORM.CVE_NAME],ORM.get_orm_string(cve[ORM.CVE_STATUS],ORM.STATUS_STR))) + # Is status locked? + # if cve[ORM.CVE_STATUS_LOCK]: + # return + + # Get the CVE's Vulnerabilities + cve_priority = cve[ORM.CVE_PRIORITY] + cve_status = None + vote_count = 0 + cve2vuls = cur.execute("SELECT * FROM orm_cvetovulnerablility where cve_id = '%s'" % cve[ORM.CVE_ID]).fetchall() + for cve2vul in cve2vuls: + vulnerability_id = cve2vul[ORM.CVETOVULNERABLILITY_VULNERABILITY_ID] + vulnerability = cur.execute("SELECT * FROM orm_vulnerability where id = '%s'" % vulnerability_id).fetchone() + # Compute Status + status = vulnerability[ORM.VULNERABILITY_STATUS] + if verbose: print(" %s,%s" % (vulnerability[ORM.VULNERABILITY_NAME],ORM.get_orm_string(status,ORM.STATUS_STR))) + if ORM.STATUS_VULNERABLE == status: + if verbose: print(" %s => %s" % (ORM.get_orm_string(cve_status,ORM.STATUS_STR),ORM.get_orm_string(status,ORM.STATUS_STR))) + cve_status = ORM.STATUS_VULNERABLE + vote_count += 1 + break + elif status in (ORM.STATUS_INVESTIGATE,ORM.STATUS_NEW) and cve_status in (None,ORM.STATUS_INVESTIGATE): + if verbose: print(" %s => (%s),%s" % (ORM.get_orm_string(cve_status,ORM.STATUS_STR),ORM.get_orm_string(status,ORM.STATUS_STR),ORM.get_orm_string(ORM.STATUS_INVESTIGATE,ORM.STATUS_STR))) + cve_status = ORM.STATUS_INVESTIGATE + vote_count += 1 + elif ORM.STATUS_NOT_VULNERABLE == status: + # tentative not vulnerable + vote_count += 1 + continue + else: + # Non-voting status: Active:Historical,New-Reserved Inactive:(New),(Investigate),(Vulnerable),Vulnerable) + continue + # Compute Priority + if cve_priority < vulnerability[ORM.VULNERABILITY_PRIORITY]: + cve_priority = vulnerability[ORM.VULNERABILITY_PRIORITY] + + # If no votes, skip and leave existing status + if 0 == vote_count: + if verbose: print(" No votes:skip") + return + # if no votes away from 'not vulnerable', defer to 'not vulnerable' + if None == cve_status: + cve_status = ORM.STATUS_NOT_VULNERABLE + if verbose: print(" defer => %s" % (ORM.get_orm_string(cve_status,ORM.STATUS_STR))) + + # Update status + history_update = [] + if cve[ORM.CVE_STATUS] != cve_status: + history_update.append(ORM.UPDATE_STATUS % ( + ORM.get_orm_string(cve[ORM.CVE_STATUS],ORM.STATUS_STR), + ORM.get_orm_string(cve_status,ORM.STATUS_STR))) + if cve[ORM.CVE_PRIORITY] < cve_priority: + history_update.append(ORM.UPDATE_PRIORITY % ( + ORM.get_orm_string(cve[ORM.CVE_PRIORITY],ORM.PRIORITY_STR), + ORM.get_orm_string(cve_priority,ORM.PRIORITY_STR))) + if history_update: + if verbose: print(" Change CVE:%s" % ';'.join(history_update)) + if not cmd_test: + sql = "UPDATE orm_cve SET status=?, priority=?, srt_updated=? WHERE id=?" + cur.execute(sql, (cve_status,cve_priority,srtool_today,cve[ORM.CVE_ID],) ) + if not update_skip_history: + # Add status update in history + update_comment = "%s%s {%s}" % (ORM.UPDATE_UPDATE_STR % ORM.UPDATE_SOURCE_DEFECT,';'.join(history_update),'Cumulative update from vulnerabilities') + sql = '''INSERT INTO orm_cvehistory (cve_id, comment, date, author) VALUES (?,?,?,?)''' + cur.execute(sql, (cve[ORM.CVE_ID],update_comment,srtool_today.strftime(ORM.DATASOURCE_DATE_FORMAT),ORM.USER_SRTOOL_NAME,) ) + + # Create notification + ### TO-DO + pass + else: + if verbose: print(" No status change needed!") + +def update_cve_status(cve_list,update_skip_history): + conn = sqlite3.connect(srtDbName) + cur = conn.cursor() + srtool_today = datetime.today() + + if 'all' == cve_list: + cves = cur.execute("SELECT * FROM orm_cve").fetchall() + else: + cve_paren_list = str(cve_list.split(',')).replace('[','(').replace(']',')') + if verbose: print("SELECT * FROM orm_cve WHERE name IN %s" % cve_paren_list) + cves = cur.execute("SELECT * FROM orm_cve WHERE name IN %s" % cve_paren_list).fetchall() + + if verbose: print("ACTION:update_cve_status:count=%d" % (len(cves))) + + i = 0 + for cve in cves: + + # Leave "New" CVEs to Triage + if ORM.STATUS_NEW == cve[ORM.CVE_STATUS]: + continue + + _update_cve_status(cur,cve,srtool_today,update_skip_history) + i += 1 + if (0 == i % 100): + print("%5d: %-10s\r" % (i,cve[ORM.CVE_NAME]),end='') + if (0 == i % 200): + conn.commit() + # Development/debug support + if cmd_skip and (i < cmd_skip): continue + if cmd_count and ((i - cmd_skip) > cmd_count): break + + print("%5d:" % (i)) + cur.close() + conn.commit() + conn.close() + +# Indexes into the product table cache +PRODUCT_DICT_KEY = 0 +PRODUCT_DICT_TAG = 1 + +def _update_vulnerability_status(cur,vulnerability,srtool_today,product_dict,update_skip_history): + if verbose: print("Vulnerability:%s:%s" % (vulnerability[ORM.VULNERABILITY_NAME],ORM.get_orm_string(vulnerability[ORM.VULNERABILITY_STATUS],ORM.STATUS_STR))) + # Is status locked? + # if vulnerability[ORM.VULNERABILITY_STATUS_LOCK]: + # return + + # Get the Vulnerability's Investigations + vulnerability_priority = vulnerability[ORM.VULNERABILITY_PRIORITY] + vulnerability_status = None + vote_count = 0 + vul2invs = cur.execute("SELECT * FROM orm_vulnerabilitytoinvestigation where vulnerability_id = '%s'" % vulnerability[ORM.VULNERABILITY_ID]).fetchall() + for vul2inv in vul2invs: + investigation_id = vul2inv[ORM.VULNERABILITYTOINVESTIGATION_INVESTIGATION_ID] + investigation = cur.execute("SELECT * FROM orm_investigation where id = '%s'" % investigation_id).fetchone() + + # For now, only calculate the "Public Status", so skip non-supported products + product_mode = get_tag_key(product_dict[investigation[ORM.INVESTIGATION_PRODUCT_ID]][PRODUCT_DICT_TAG],'mode') + if 'support' != product_mode: + if verbose: print(" SKIP:Product %s is mode=%s" % (product_dict[investigation[ORM.INVESTIGATION_PRODUCT_ID]][PRODUCT_DICT_KEY],product_mode)) + continue + + # Compute Status + status = investigation[ORM.INVESTIGATION_STATUS] + if verbose: print(" %s,%s" % (investigation[ORM.INVESTIGATION_NAME],ORM.get_orm_string(status,ORM.STATUS_STR))) + if ORM.STATUS_VULNERABLE == status: + if verbose: print(" %s => %s" % (ORM.get_orm_string(vulnerability_status,ORM.STATUS_STR),ORM.get_orm_string(status,ORM.STATUS_STR))) + vulnerability_status = ORM.STATUS_VULNERABLE + vote_count += 1 + break + elif status in (ORM.STATUS_INVESTIGATE,ORM.STATUS_NEW) and vulnerability_status in (None,ORM.STATUS_INVESTIGATE): + if verbose: print(" %s => (%s),%s" % (ORM.get_orm_string(vulnerability_status,ORM.STATUS_STR),ORM.get_orm_string(status,ORM.STATUS_STR),ORM.get_orm_string(ORM.STATUS_INVESTIGATE,ORM.STATUS_STR))) + vulnerability_status = ORM.STATUS_INVESTIGATE + vote_count += 1 + elif ORM.STATUS_NOT_VULNERABLE == status: + # tentative not vulnerable + vote_count += 1 + continue + else: + # Non-voting status: Active:Historical,New-Reserved Inactive:(New),(Investigate),(Vulnerable),Vulnerable) + continue + # Compute Priority + if vulnerability_priority < investigation[ORM.INVESTIGATION_PRIORITY]: + vulnerability_priority = investigation[ORM.INVESTIGATION_PRIORITY] + + # If no votes, skip and leave existing status + if 0 == vote_count: + if verbose: print(" No votes:skip") + return + # if no votes away from 'not vulnerable', defer to 'not vulnerable' + if None == vulnerability_status: + vulnerability_status = ORM.STATUS_NOT_VULNERABLE + if verbose: print(" defer => %s" % (ORM.get_orm_string(vulnerability_status,ORM.STATUS_STR))) + + # Update status + history_update = [] + if vulnerability[ORM.VULNERABILITY_STATUS] != vulnerability_status: + history_update.append(ORM.UPDATE_STATUS % ( + ORM.get_orm_string(vulnerability[ORM.VULNERABILITY_STATUS],ORM.STATUS_STR), + ORM.get_orm_string(vulnerability_status,ORM.STATUS_STR))) + if vulnerability[ORM.VULNERABILITY_PRIORITY] < vulnerability_priority: + history_update.append(ORM.UPDATE_PRIORITY % ( + ORM.get_orm_string(vulnerability[ORM.VULNERABILITY_PRIORITY],ORM.PRIORITY_STR), + ORM.get_orm_string(vulnerability_priority,ORM.PRIORITY_STR))) + if history_update: + if verbose: print(" Change Vulnerability:%s" % ';'.join(history_update)) + if not cmd_test: + sql = "UPDATE orm_vulnerability SET status=?, priority=?, srt_updated=? WHERE id=?" + cur.execute(sql, (vulnerability_status,vulnerability_priority,srtool_today,vulnerability[ORM.VULNERABILITY_ID],) ) + if not update_skip_history: + # Add status update in history + update_comment = "%s%s {%s}" % (ORM.UPDATE_UPDATE_STR % ORM.UPDATE_SOURCE_DEFECT,';'.join(history_update),'Cumulative update from investigations') + sql = '''INSERT INTO orm_vulnerabilityhistory (vulnerability_id, comment, date, author) VALUES (?,?,?,?)''' + cur.execute(sql, (vulnerability[ORM.VULNERABILITY_ID],update_comment,srtool_today.strftime(ORM.DATASOURCE_DATE_FORMAT),ORM.USER_SRTOOL_NAME,) ) + + # Create notification + ### TO-DO + pass + else: + if verbose: print(" No status change needed!") + +def update_vulnerability_status(vulnerability_list,update_skip_history): + conn = sqlite3.connect(srtDbName) + cur = conn.cursor() + srtool_today = datetime.today() + + # Pre-gather and cache the product information + product_dict = {} + products = cur.execute("SELECT * FROM orm_product").fetchall() + for product in products: + product_dict[ product[ORM.PRODUCT_ID] ] = [product[ORM.PRODUCT_KEY],product[ORM.PRODUCT_PRODUCT_TAGS]] + + if 'all' == vulnerability_list: + vulnerabilities = cur.execute("SELECT * FROM orm_vulnerability").fetchall() + else: + vulnerability_paren_list = str(vulnerability_list.split(',')).replace('[','(').replace(']',')') + if verbose: print("SELECT * FROM orm_vulnerability WHERE name IN %s" % vulnerability_paren_list) + vulnerabilities = cur.execute("SELECT * FROM orm_vulnerability WHERE name IN %s" % vulnerability_paren_list).fetchall() + + i = 0 + for vulnerability in vulnerabilities: + _update_vulnerability_status(cur,vulnerability,srtool_today,product_dict,update_skip_history) + i += 1 + if (0 == i % 100): + print("%5d: %-10s\r" % (i,vulnerability[ORM.VULNERABILITY_NAME]),end='') + if (0 == i % 200): + conn.commit() + # Development/debug support + if cmd_skip and (i < cmd_skip): continue + if cmd_count and ((i - cmd_skip) > cmd_count): break + + print("%5d:" % (i)) + cur.close() + conn.commit() + conn.close() + + +def _update_investigation_status(cur,investigation,srtool_today,update_skip_history): + if verbose: print("Investigation:%s:%s" % (investigation[ORM.INVESTIGATION_NAME],ORM.get_orm_string(investigation[ORM.INVESTIGATION_STATUS],ORM.STATUS_STR))) + # Is status locked? + # if investigation[ORM.INVESTIGATION_STATUS_LOCK]: + # return + + # Get the Investigation's Defects + investigation_priority = investigation[ORM.INVESTIGATION_PRIORITY] + investigation_status = None + vote_count = 0 + inv2defs = cur.execute("SELECT * FROM orm_investigationtodefect where investigation_id = '%s'" % investigation[ORM.INVESTIGATION_ID]).fetchall() + for inv2def in inv2defs: + defect_id = inv2def[ORM.INVESTIGATIONTODEFECT_DEFECT_ID] + defect = cur.execute("SELECT * FROM orm_defect where id = '%s'" % defect_id).fetchone() + # Compute Status + status = defect[ORM.DEFECT_SRT_STATUS] + if verbose: print(" %s,%s" % (defect[ORM.DEFECT_NAME],ORM.get_orm_string(status,ORM.STATUS_STR))) + if ORM.STATUS_VULNERABLE == status: + if verbose: print(" %s => %s" % (ORM.get_orm_string(investigation_status,ORM.STATUS_STR),ORM.get_orm_string(status,ORM.STATUS_STR))) + investigation_status = ORM.STATUS_VULNERABLE + vote_count += 1 + break + elif status in (ORM.STATUS_INVESTIGATE,ORM.STATUS_NEW) and investigation_status in (None,ORM.STATUS_INVESTIGATE): + if verbose: print(" %s => (%s),%s" % (ORM.get_orm_string(investigation_status,ORM.STATUS_STR),ORM.get_orm_string(status,ORM.STATUS_STR),ORM.get_orm_string(ORM.STATUS_INVESTIGATE,ORM.STATUS_STR))) + investigation_status = ORM.STATUS_INVESTIGATE + vote_count += 1 + elif ORM.STATUS_NOT_VULNERABLE == status: + # tentative not vulnerable + vote_count += 1 + continue + else: + # Non-voting status: Active:Historical,New-Reserved Inactive:(New),(Investigate),(Vulnerable),Vulnerable) + continue + # Compute Priority + if investigation_priority < defect[ORM.DEFECT_SRT_PRIORITY]: + investigation_priority = defect[ORM.DEFECT_SRT_PRIORITY] + + # If no votes, skip and leave existing status + if 0 == vote_count: + if verbose: print(" No votes:skip") + return + # if no votes away from 'not vulnerable', defer to 'not vulnerable' + if None == investigation_status: + investigation_status = ORM.STATUS_NOT_VULNERABLE + if verbose: print(" defer => %s" % (ORM.get_orm_string(investigation_status,ORM.STATUS_STR))) + + investigation_outcome = None + for inv2def in inv2defs: + outcome = defect[ORM.DEFECT_SRT_OUTCOME] + if (ORM.OUTCOME_OPEN == outcome) or (ORM.OUTCOME_OPEN == investigation_outcome): + investigation_outcome = ORM.OUTCOME_OPEN + continue + if (ORM.OUTCOME_FIXED == outcome) or (ORM.OUTCOME_FIXED == investigation_outcome): + investigation_outcome = ORM.OUTCOME_FIXED + continue + # ORM.OUTCOME_CLOSED + # ORM.OUTCOME_NOT_FIX + investigation_outcome = outcome + + if not investigation_outcome: + investigation_outcome = investigation[ORM.INVESTIGATION_OUTCOME] + + + ### TO_DO: DOUBLE CHECK + if False: + ### WIND_RIVER_EXTENSION_BEGIN ### + # FIXUP: Status: overwrite if new is Fixed and old isn't "VULNERABLE" + update_fixup = ('Fixed' == jira_resolution) and (ORM.STATUS_VULNERABLE != cve[ORM.CVE_STATUS]) + ### WIND_RIVER_EXTENSION_END ### + + + + # Update status + history_update = [] + if investigation[ORM.INVESTIGATION_STATUS] != investigation_status: + history_update.append(ORM.UPDATE_STATUS % ( + ORM.get_orm_string(investigation[ORM.INVESTIGATION_STATUS],ORM.STATUS_STR), + ORM.get_orm_string(investigation_status,ORM.STATUS_STR))) + if investigation[ORM.INVESTIGATION_OUTCOME] != investigation_outcome: + history_update.append(ORM.UPDATE_OUTCOME % ( + ORM.get_orm_string(investigation[ORM.INVESTIGATION_OUTCOME],ORM.OUTCOME_STR), + ORM.get_orm_string(investigation_outcome,ORM.OUTCOME_STR))) + if investigation[ORM.INVESTIGATION_PRIORITY] < investigation_priority: + history_update.append(ORM.UPDATE_PRIORITY % ( + ORM.get_orm_string(investigation[ORM.INVESTIGATION_PRIORITY],ORM.PRIORITY_STR), + ORM.get_orm_string(investigation_priority,ORM.PRIORITY_STR))) + if history_update: + if verbose: print(" Change Investigation:%s" % ';'.join(history_update)) + if not cmd_test: + sql = "UPDATE orm_investigation SET status=?, outcome=?, priority=?, srt_updated=? WHERE id=?" + cur.execute(sql, (investigation_status,investigation_outcome,investigation_priority,srtool_today,investigation[ORM.INVESTIGATION_ID],) ) + if not update_skip_history: + # Add status update in history + update_comment = "%s%s {%s}" % (ORM.UPDATE_UPDATE_STR % ORM.UPDATE_SOURCE_DEFECT,';'.join(history_update),'Cumulative update from defects') + sql = '''INSERT INTO orm_investigationhistory (investigation_id, comment, date, author) VALUES (?,?,?,?)''' + cur.execute(sql, (investigation[ORM.INVESTIGATION_ID],update_comment,srtool_today.strftime(ORM.DATASOURCE_DATE_FORMAT),ORM.USER_SRTOOL_NAME,) ) + + # Create notification + ### TO-DO + pass + else: + if verbose: print(" No status change needed!") + +def update_investigation_status(investigation_list,update_skip_history): + conn = sqlite3.connect(srtDbName) + cur = conn.cursor() + srtool_today = datetime.today() + + if 'all' == investigation_list: + investigations = cur.execute("SELECT * FROM orm_investigation").fetchall() + else: + investigation_paren_list = str(investigation_list.split(',')).replace('[','(').replace(']',')') + if verbose: print("SELECT * FROM orm_investigation WHERE name IN %s" % investigation_paren_list) + investigations = cur.execute("SELECT * FROM orm_investigation WHERE name IN %s" % investigation_paren_list).fetchall() + + i = 0 + for investigation in investigations: + _update_investigation_status(cur,investigation,srtool_today,update_skip_history) + i += 1 + if (0 == i % 100): + print("%5d: %-10s\r" % (i,investigation[ORM.INVESTIGATION_NAME]),end='') + if (0 == i % 200): + conn.commit() + # Development/debug support + if cmd_skip and (i < cmd_skip): continue + if cmd_count and ((i - cmd_skip) > cmd_count): break + + cur.close() + conn.commit() + conn.close() + +# This routine is intended for incremental cumulative status updates +def update_cve_status_tree(cve_list,update_skip_history): + conn = sqlite3.connect(srtDbName) + cur = conn.cursor() + + if 'all' == cve_list: + # global cumulative update + update_investigation_status('all', update_skip_history) + update_vulnerability_status('all', update_skip_history) + update_cve_status('all', update_skip_history) + return + + # Perform a deep update on the CVEs, their vunerabilities, and their investigations + cve_paren_list = str(cve_list.split(',')).replace('[','(').replace(']',')') + if verbose: print("SELECT * FROM orm_cve WHERE name IN %s" % cve_paren_list) + cves = cur.execute("SELECT * FROM orm_cve WHERE name IN %s" % cve_paren_list).fetchall() + + if verbose: print("ACTION:update_cve_status_tree:count=%d" % (len(cves))) + + i = 0 + cve_list = [] + for cve in cves: + cve_list.append(cve[ORM.CVE_NAME]) + vulnerability_list = [] + investigation_list = [] + + cve2vuls = cur.execute("SELECT * FROM orm_cvetovulnerablility where cve_id = '%s'" % cve[ORM.CVE_ID]).fetchall() + for cve2vul in cve2vuls: + vulnerability_id = cve2vul[ORM.CVETOVULNERABLILITY_VULNERABILITY_ID] + vulnerability = cur.execute("SELECT * FROM orm_vulnerability where id = '%s'" % vulnerability_id).fetchone() + vulnerability_list.append(vulnerability[ORM.VULNERABILITY_NAME]) + + vul2invs = cur.execute("SELECT * FROM orm_vulnerabilitytoinvestigation where vulnerability_id = '%s'" % vulnerability_id).fetchall() + for vul2inv in vul2invs: + investigation_id = vul2inv[ORM.VULNERABILITYTOINVESTIGATION_INVESTIGATION_ID] + investigation = cur.execute("SELECT * FROM orm_investigation where id = '%s'" % investigation_id).fetchone() + investigation_list.append(investigation[ORM.INVESTIGATION_NAME]) + + # Update the CVE's children status + update_investigation_status(','.join(investigation_list), update_skip_history) + update_vulnerability_status(','.join(vulnerability_list), update_skip_history) + + # Childred are updated, now update the CVEs + update_cve_status(','.join(cve_list), update_skip_history) + cur.close() + conn.close() + +################################# # Generate database schema offsets # # @@ -428,12 +866,23 @@ def gen_schema_header(): print("ERROR(%d): %s" % (e.returncode, e.output)) return + # Fetch USER_SRTOOL_ID + conn = sqlite3.connect(srtDbName) + cur = conn.cursor() + USER_SRTOOL_NAME = 'SRTool' + user = cur.execute("SELECT * FROM users_srtuser where username = '%s'" % USER_SRTOOL_NAME).fetchone() + USER_SRTOOL_ID = user[0] # Hardcoded 'ORM.USERS_SRTUSER_ID' + conn.close() + with open(os.path.join(srtool_basepath,'bin/common/srt_schema.py'), 'w') as fd: fd.write("# SRTool database table schema indexes\n") fd.write("# Generated by: './bin/common/srtool_common.py --generate-schema-header'\n") fd.write("# Should be run after any schema changes to sync commandline tools\n") fd.write("\n") fd.write("class ORM():\n") + fd.write(" USER_SRTOOL_NAME = '%s'\n" % USER_SRTOOL_NAME) + fd.write(" USER_SRTOOL_ID = %d\n" % USER_SRTOOL_ID) + for line in output.decode("utf-8").splitlines(): match = create_re.match(line) if not match: @@ -450,14 +899,18 @@ def gen_schema_header(): #print("%s_%s = %d" % (table.upper(),name.upper(),i)) fd.write(" %s_%s = %d\n" % (table.upper(),name.upper(),i)) + # + # Common SRTool Status Mappings + # + fd.write("\n # Shared Constants\n") fd.write(" %s_%s = %d\n" % ('PRIORITY','UNDEFINED',0)) - fd.write(" %s_%s = %d\n" % ('PRIORITY','MINOR' ,1)) - fd.write(" %s_%s = %d\n" % ('PRIORITY','LOW' ,2)) - fd.write(" %s_%s = %d\n" % ('PRIORITY','MEDIUM' ,3)) - fd.write(" %s_%s = %d\n" % ('PRIORITY','HIGH' ,4)) + fd.write(" %s_%s = %d\n" % ('PRIORITY','LOW' ,1)) + fd.write(" %s_%s = %d\n" % ('PRIORITY','MEDIUM' ,2)) + fd.write(" %s_%s = %d\n" % ('PRIORITY','HIGH' ,3)) + fd.write(" %s_%s = %d\n" % ('PRIORITY','CRITICAL' ,4)) fd.write(" %s = '%s'\n" % ('PRIORITY_STR', \ - 'Undefined,Minor,Low,Medium,High' \ + 'UNDEFINED,Low,Medium,High,Critical' \ )) fd.write(" %s_%s = %d\n" % ('STATUS','HISTORICAL' ,0)) @@ -466,8 +919,12 @@ def gen_schema_header(): fd.write(" %s_%s = %d\n" % ('STATUS','INVESTIGATE' ,3)) fd.write(" %s_%s = %d\n" % ('STATUS','VULNERABLE' ,4)) fd.write(" %s_%s = %d\n" % ('STATUS','NOT_VULNERABLE',5)) + fd.write(" %s_%s = %d\n" % ('STATUS','NEW_INACTIVE' ,6)) + fd.write(" %s_%s = %d\n" % ('STATUS','INVESTIGATE_INACTIVE' ,7)) + fd.write(" %s_%s = %d\n" % ('STATUS','VULNERABLE_INACTIVE' ,8)) + fd.write(" %s_%s = %d\n" % ('STATUS','NOT_VULNERABLE_INACTIVE',9)) fd.write(" %s = '%s'\n" % ('STATUS_STR', \ - 'Historical,New,New_Reserved,Investigate,Vulnerable,Not_Vulnerable' \ + 'Historical,New,New_Reserved,Investigate,Vulnerable,Not_Vulnerable,(New),(Investigate),(Vulnerable),(Not Vulnerable)' \ )) fd.write(" %s_%s = %d\n" % ('PUBLISH','UNPUBLISHED',0)) @@ -488,6 +945,10 @@ def gen_schema_header(): 'Open,Closed,Fixed,Not_Fix' \ )) + # + # External Defect Record Mappings + # + fd.write(" %s_%s = %d\n" % ('DEFECT','UNRESOLVED' ,0)) fd.write(" %s_%s = %d\n" % ('DEFECT','RESOLVED' ,1)) fd.write(" %s_%s = %d\n" % ('DEFECT','FIXED' ,2)) @@ -500,12 +961,39 @@ def gen_schema_header(): fd.write(" %s_%s = %d\n" % ('DEFECT','CANNOT_REPRODUCE' ,9)) fd.write(" %s_%s = %d\n" % ('DEFECT','DONE' ,10)) fd.write(" %s_%s = '%s'\n" % ('DEFECT','RESOLUTION_STR', \ - 'Unresolved,Resolved,Fixed,Will_Not_Fix,Withdrawn,Rejected,Duplicate,Not_Applicable,Replaced_By_Requirement,Cannot_Reproduce,Done' \ + 'Unresolved,Resolved,Fixed,Will Not Fix,Withdrawn,Rejected,Duplicate,Not Applicable,Replaced By Requirement,Cannot Reproduce,Done' \ )) + fd.write(" %s_%s = %d\n" % ('DEFECT','UNDEFINED',0)) + fd.write(" %s_%s = %d\n" % ('DEFECT','LOW' ,1)) + fd.write(" %s_%s = %d\n" % ('DEFECT','MEDIUM' ,2)) + fd.write(" %s_%s = %d\n" % ('DEFECT','HIGH' ,3)) + fd.write(" %s_%s = %d\n" % ('DEFECT','CRITICAL' ,4)) + fd.write(" %s_%s = '%s'\n" % ('DEFECT','PRIORITY_STR', \ + 'UNDEFINED,P4,P3,P2,P1' \ + )) + + fd.write(" %s_%s = %d\n" % ('DEFECT','STATUS_OPEN' ,0)) + fd.write(" %s_%s = %d\n" % ('DEFECT','STATUS_IN_PROGRESS' ,1)) + fd.write(" %s_%s = %d\n" % ('DEFECT','STATUS_ON_HOLD' ,2)) + fd.write(" %s_%s = %d\n" % ('DEFECT','STATUS_CHECKED_IN' ,3)) + fd.write(" %s_%s = %d\n" % ('DEFECT','STATUS_RESOLVED' ,4)) + fd.write(" %s_%s = %d\n" % ('DEFECT','STATUS_CLOSED' ,5)) + fd.write(" %s_%s = '%s'\n" % ('DEFECT','STATUS_STR', \ + 'Open,In progress,On Hold,Checked In,Resolved,Closed' \ + )) + + # + # Package Record Mappings + # + fd.write(" %s_%s = %d\n" % ('PACKAGE','FOR' ,0)) fd.write(" %s_%s = %d\n" % ('PACKAGE','AGAINST' ,1)) + # + # Data source Record Mappings + # + fd.write(" %s_%s = %d\n" % ('DATASOURCE','MINUTELY' ,0)) fd.write(" %s_%s = %d\n" % ('DATASOURCE','HOURLY' ,1)) fd.write(" %s_%s = %d\n" % ('DATASOURCE','DAILY' ,2)) @@ -519,6 +1007,55 @@ def gen_schema_header(): fd.write(" %s_%s = '%s'\n" % ('DATASOURCE','DATE_FORMAT','%Y-%m-%d')) fd.write(" %s_%s = '%s'\n" % ('DATASOURCE','DATETIME_FORMAT','%Y-%m-%d %H:%M:%S')) + # + # Update class Mappings + # + + fd.write("\n\n") + fd.write(" %s_%s = '%s'\n" % ('UPDATE','UPDATE_STR','UPDATE(%s):')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','CREATE_STR','CREATE(%s):')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','SOURCE_USER','User')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','SOURCE_TRIAGE','Triage')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','SOURCE_CVE','CVE')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','SOURCE_DEFECT','Defect')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','NEW_NAME','New_Name(%s,%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','PRIORITY','Priority(%s,%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','STATUS','Status(%s,%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','SEVERITY_V3','Severity_V3(%s,%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','SEVERITY_V2','Severity_V2(%s,%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','DESCRIPTION','Description()')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','LASTMODIFIEDDATE','LastModifiedDate(%s,%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','OUTCOME','Outcome(%s,%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','RELEASE','Release(%s,%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','NOTE','User_Note()')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','PRIVATE_NOTE','Private_Note()')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','TAG','Tag()')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','PUBLISH_STATE','Publish_State(%s,%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','PUBLISH_DATE','Publish_Date(%s,%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','ACKNOWLEDGE_DATE','AcknowledgeDate(%s,%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','ATTACH_CVE','Attach_CVE(%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','DETACH_CVE','Detach_CVE(%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','ATTACH_VUL','Attach_Vulnerability(%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','DETACH_VUL','Detach_Vulnerability(%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','ATTACH_INV','Attach_Investigration(%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','DETACH_INV','Detach_Investigration(%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','ATTACH_DEV','Attach_Defect(%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','DETACH_DEV','Detach_Defect(%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','ATTACH_DOC','Attach_Document(%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','DETACH_DOC','Detach_Document(%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','ATTACH_USER_NOTIFY','Attach_User_Notify(%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','DETACH_USER_NOTIFY','Detach_User_Notify(%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','ATTACH_ACCESS','Attach_Access(%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','DETACH_ACCESS','Detach_Access(%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','ATTACH_PRODUCT','Attach_Product(%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','DETACH_PRODUCT','Detach_Product(%s)')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','MARK_NEW','Mark_New()')) + fd.write(" %s_%s = '%s'\n" % ('UPDATE','MARK_UPDATED','Mark_Updated()')) + + # + # Helper routine to map values to string names + # + fd.write("\n\n") fd.write(" # General routine to return string name of a constant (e.g. 'DATASOURCE_FREQUENCY_STR')\n") fd.write(" @staticmethod\n") @@ -536,63 +1073,15 @@ def gen_schema_header(): fd.write("\n") ################################# -# fixups -# - -# 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() - - sql = ''' UPDATE orm_cve - SET name_sort = ? - WHERE id = ?''' - cur_write.execute(sql, (name_sort, cve[ORM.CVE_ID],)) - conn.commit() - -# 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 - 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() - - sql = ''' UPDATE orm_cve - SET recommend = ? - WHERE id = ?''' - cur_write.execute(sql, (0, cve[ORM.CVE_ID],)) - print("CVE RECOMMEND FIX COUNT=%d" % i) - conn.commit() - -################################# # main loop # def main(argv): global verbose + global update_skip_history global cmd_skip global cmd_count + global cmd_test # setup parser = argparse.ArgumentParser(description='srtool_common.py: manage SRTool common source data') @@ -600,15 +1089,24 @@ def main(argv): parser.add_argument('--init-notify-categories', '-n', action='store_const', const='init_notify_categories', dest='command', help='Initialize notify categories') parser.add_argument('--score-new-cves', '-s', dest='score_new_cves', help='Score CVEs for triage [NEW|CVE-1234]') parser.add_argument('--generate-schema-header', '-g', action='store_const', const='gen_schema_header', dest='command', help='Generate database schema header') + + + parser.add_argument('--update-cve-status-tree', '-S', dest='update_cve_status_tree', help="Update CVEs and their children's cumulative status") + parser.add_argument('--update-investigation-status', '-I', dest='update_investigation_status', help='Update Investigation cumulative status') + parser.add_argument('--update-vulnerability-status', '-V', dest='update_vulnerability_status', help='Update Vulnerability cumulative status') + parser.add_argument('--update-cve-status', '-C', dest='update_cve_status', help='Update CVE cumulative status') + parser.add_argument('--update-skip-history', '-H', action='store_true', dest='update_skip_history', help='Skip history updates') + parser.add_argument('--force', '-f', action='store_true', dest='force', help='Force the update') + parser.add_argument('--test', '-t', action='store_true', dest='test', help='Test run') 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-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') args = parser.parse_args() verbose = args.verbose + update_skip_history = args.update_skip_history + cmd_test = args.test cmd_skip = 0 if None != args.skip: cmd_skip = int(args.skip) @@ -618,6 +1116,9 @@ def main(argv): if get_override('SRTDBG_MINIMAL_DB'): cmd_count = 40 + if verbose: + print('srtool_common %s' % args) + if 'init_package_keywords' == args.command: init_package_keywords(packageKeywordsFile) elif 'init_notify_categories' == args.command: @@ -626,12 +1127,16 @@ def main(argv): score_new_cves(args.score_new_cves) elif 'gen_schema_header' == args.command: gen_schema_header() - ### TO-DO: TEMPORARY WORKAROUND - fix_cve_recommend() - elif 'fix_name_sort' == args.command: - fix_name_sort() - elif 'fix_cve_recommend' == args.command: - fix_cve_recommend() + + elif args.update_cve_status_tree: + update_cve_status_tree(args.update_cve_status_tree, update_skip_history) + elif args.update_cve_status: + update_cve_status(args.update_cve_status, update_skip_history) + elif args.update_vulnerability_status: + update_vulnerability_status(args.update_vulnerability_status, update_skip_history) + elif args.update_investigation_status: + update_investigation_status(args.update_investigation_status, update_skip_history) + else: print("Command not found") |