def truncateRelevantData():
q = 'delete from npmAlert where toolId=%s'
sql.execute(q, (toolId, ))
truncates = ['truncate table npmAudit', 'truncate table npmAuditFix']
for q in truncates:
sql.execute(q)
def getToolId(name):
selectQ = 'select id from tool where name=%s'
results = sql.execute(selectQ, (name, ))
if not results:
insertQ = 'insert into tool values(%s,%s)'
sql.execute(insertQ, (None, name))
results = sql.execute(selectQ, (name, ))
return results[0]['id']
def getDependencyPathId(path):
h = encrypt_string(path)
selectQ = 'select id from dependencyPath where hash=%s'
results = sql.execute(selectQ, (h, ))
if not results:
insertQ = 'insert into dependencyPath values (%s,%s,%s)'
sql.execute(insertQ, (None, h, path))
results = sql.execute(selectQ, (h, ))
return results[0]['id']
def selectId():
nonlocal cveId, sourceId
if cveId and not sourceId:
selectQ = '''select id from vulnerability where
cveId=%s and sourceId is null'''
return sql.execute(selectQ, (cveId))
else:
selectQ = '''select id from vulnerability where
cveId is null and sourceId=%s'''
return sql.execute(selectQ, (sourceId))
def processVulnMethods(repoId, data):
for vuln in data:
assert len(vuln['calls']) == 1
call = vuln['calls'][0]
callChains = len(call['callChains'])
className = call['method']['className']
descriptor = call['method']['descriptor']
method = call['method']['methodName']
q = 'insert into srcclrCallChains values(%s,%s,%s,%s,%s)'
sql.execute(q, (repoId, className, descriptor, method, callChains))
def addGithubAdvisory(alert):
vuln = alert['securityAdvisory']
ghsa = vuln['ghsaId']
vulnId = common.getVulnerabilityId(None, ghsa)
if vulnId > 0:
return vulnId
description = vuln['description']
publishDate = dt.parse(vuln['publishedAt'])
q = 'insert into vulnerability values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
sql.execute(q, (None, 'GitHub', None, ghsa, publishDate, description, None,
None, None, None))
return common.getVulnerabilityId(None, ghsa)
def addCWEs(vulnerabilityId, cwes):
q = 'insert into vulnerabilityCWE values(%s,%s)'
for cwe in cwes:
if type(cwe) != int:
cwe = -1 #'NVD-CVE-Noinfo or other"
print(cwe, ' cwe does not have an integer id')
try:
sql.execute(q, (vulnerabilityId, cwe))
except sql.pymysql.IntegrityError as error:
if error.args[0] == sql.PYMYSQL_DUPLICATE_ERROR:
return
else:
raise Exception(str(error))
def addSteadyVulnerability(vuln):
#process this vuln
sourceId = vuln['bug']['id']
vulnId = common.getVulnerabilityId(None, sourceId)
if vulnId > 0:
return vulnId
insertQ = 'insert into vulnerability values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
sql.execute(
insertQ,
(None, 'Steady', None, sourceId, None, None, None, None, None, None))
return common.getVulnerabilityId(None, sourceId)
def addAlerts(vuln):
scandate= datetime.now()
for (vulnerabilityId, dependencyId) in vuln.keys():
count= vuln[(vulnerabilityId, dependencyId)]['count']
insertQ = 'insert into mavenAlert values(%s,%s,%s,%s,%s,%s,%s,%s)'
try:
sql.execute(insertQ,(None,scandate,dependencyId,vulnerabilityId,
toolId, None, None, count))
except sql.pymysql.IntegrityError as error:
if error.args[0] == sql.PYMYSQL_DUPLICATE_ERROR:
#TODO update scandate
print('alert exists already in db')
else:
raise Exception(str(error))
def addWhiteSourceVulnerability(id, publishDate, description, cvss):
cvss = cvss.find_all('b')
assert len(cvss) == 1
score = float(cvss[0].text)
insertQ = 'insert into vulnerability values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
try:
sql.execute(insertQ, (None, 'WhiteSource', None, id, publishDate,
description, None, None, score, None))
except sql.pymysql.IntegrityError as error:
if error.args[0] == sql.PYMYSQL_DUPLICATE_ERROR:
print(id, ' already exists')
else:
raise Exception(str(error))
def insertPackage(artifact,version):
assert '%' not in version
q='select * from package where artifact = %s'
results=sql.execute(q,(artifact,))
if results:
group= results[0]['group']
eco=results[0]['ecosystem']
else:
group = 'seeker'
eco='seeker'
q='insert into package values(%s,%s,%s,%s,%s)'
sql.execute(q,(None,group,artifact,version,eco))
def repoAlredyProcessed(repo) -> bool:
query='''select * from repository where
repoName="{}" '''.format(repo)
results=sql.execute(query)
if results:
return True
return False
def addNpmAuditTable(repoId, actions):
for action in actions:
act = action['action']
resolves = len(action['resolves'])
module = action['module']
if 'target' in action:
target = action['target']
else:
target = None
if 'isMajor' in action:
isMajor = action['isMajor']
else:
isMajor = None
q = 'insert into npmAudit values (%s,%s,%s,%s,%s,%s,%s)'
sql.execute(q, (None, repoId, act, isMajor, module, target, resolves))
async def fname_step(message: types.Message, state: FSMContext):
db = sqlite3.connect('server.db')
sql = db.cursor()
for i in sql.execute("SELECT id FROM user_time"):
await message.send_copy(i[0])
await state.finish()
await bot.send_message(message.chat.id, 'Рассылка выполенена!')
def getSrcClrVulnerability(data):
publishDate = dt.parse(data['disclosureDate'])
description = data['title'] + ' ' + data['overview']
cvssScore = data['cvssScore']
source = 'SourceClear'
selectQ = 'select * from vulnerability where source=%s and description=%s'
results = sql.execute(selectQ, (source, description))
if not results:
id = source + str(hash(description))
insertQ = 'insert into vulnerability values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
sql.execute(insertQ, (None, source, None, id, publishDate, description,
None, None, cvssScore, None))
results = sql.execute(selectQ, (source, description))
return results[0]['id']
def addVulnerabilityInfo(vulnId, vuln):
if 'fixedIn' in vuln and vuln['fixedIn']:
fixedVersion = True
else:
fixedVersion = False
exploit = vuln['exploit']
snykId = vuln['id']
insertQ = 'insert into vulnerabilityInfoSnyk values (%s,%s,%s, %s)'
try:
sql.execute(insertQ, (vulnId, fixedVersion, exploit, snykId))
except sql.pymysql.IntegrityError as error:
if error.args[0] == sql.PYMYSQL_DUPLICATE_ERROR:
print('vulnerability info snyk already present')
return
else:
raise Exception(str(error))
def getAlertId(scandate, dependencyId, vulnerabilityId):
print(scandate, dependencyId, vulnerabilityId)
insertQ = 'insert into mavenAlert values(%s,%s,%s,%s,%s,%s,%s,%s)'
try:
sql.execute(insertQ, (None, scandate, dependencyId, vulnerabilityId,
toolId, None, None, 1))
except sql.pymysql.IntegrityError as error:
if error.args[0] == sql.PYMYSQL_DUPLICATE_ERROR:
#TODO update scandate
print('maven alert exists already in db')
else:
raise Exception(str(error))
selectQ = '''select id from mavenAlert where dependencyId=%s
and vulnerabilityId=%s and toolId=%s'''
return sql.execute(selectQ,
(dependencyId, vulnerabilityId, toolId))[0]['id']
def getDependencyId(idrepo, idpackage, idtool=None, insertIfNotExists=False):
selectQ = '''select id from dependency where
repositoryId=%s and packageId=%s'''
results = sql.execute(selectQ, (idrepo, idpackage))
if not results:
logging.info('new dependency found: %s', idpackage)
assert insertIfNotExists
insertQ = 'insert into dependency values(%s,%s,%s)'
sql.execute(insertQ, (None, idrepo, idpackage))
results = sql.execute(selectQ, (idrepo, idpackage))
iddependency = results[0]['id']
#check if this dependency was in deptree (maven or npm)
q = '''select * from
(select packageId
from mavenDependencyTree
union
select packageId from
npmDependencyTree) t
where packageId=%s;'''
check = sql.execute(q, (idpackage, ))
if not check:
q = 'insert into dependencyFoundByTool values(%s,%s)'
assert idtool
sql.execute(q, (iddependency, idtool))
iddependency = results[0]['id']
return iddependency
def readRenvatePr(name, repoId):
g = Github(token)
user = g.get_user()
repo = user.get_repo(name)
issues = repo.get_issues()
for issue in issues:
if issue.user.login != 'renovate[bot]':
continue
if 'Update dependency' not in issue.title:
continue
html = md.markdown(issue.body)
soup = bs(html, 'html.parser')
p = str(soup.find_all('p')[1])
p = p.split('|')
changeType = (p[-3])
q = 'insert into renovate values(%s,%s)'
sql.execute(q, (repoId, changeType))
def getPackageId(group,
artifact,
version,
ecosystem=None,
insertIfNotExists=False):
selectQ = 'select * from package where artifact=%s and version =%s'
results = sql.execute(selectQ, (artifact, version))
if not results:
print(group, artifact, version)
assert ' ' not in artifact and ' ' not in version and version not in artifact
logging.info('new package found: %s, %s, %s', group, artifact, version)
assert insertIfNotExists
assert ecosystem
sql.execute("insert into package values(null,%s,%s,%s,%s)",
(group, artifact, version, ecosystem))
results = sql.execute(selectQ, (artifact, version))
return results[0]['id']
def processMavenAlerts(mavenDf):
df = mavenDf
if len(df) == 0:
return
df['packageId'] = df.apply(lambda row: getMavenPackageId(
row.dependency, row.identifier, insertIfNotExists=True),
axis=1)
df['dependencyId'] = df.apply(lambda row: common.getDependencyId(
row.repoId, row.packageId, row.toolId, insertIfNotExists=True),
axis=1)
df = df[[
'scandate', 'dependencyId', 'vulnerabilityId', 'toolId', 'confidence'
]]
df['id'] = [np.nan] * len(df)
df['severity'] = [np.nan] * len(df)
df['count'] = [1] * len(df)
df = df[[
'scandate', 'dependencyId', 'vulnerabilityId', 'toolId', 'confidence'
]]
assert len(df[df.duplicated()]) == 0
alerts = df.values
insertQ = 'insert into mavenAlert values(%s,%s,%s,%s,%s,%s,%s,%s)'
for alert in alerts:
for i in range(len(alert)):
if pd.isna(alert[i]):
alert[i] = None
scandate, dependencyId, vulnerabilityId, toolId, confidence = alert
try:
sql.execute(insertQ,
(None, scandate, dependencyId, vulnerabilityId, toolId,
confidence, None, 1))
except sql.pymysql.IntegrityError as error:
if error.args[0] == sql.PYMYSQL_DUPLICATE_ERROR:
#TODO update scandate
print('maven alert exists already in db')
else:
raise Exception(str(error))
def processAlerts(repoId, alerts):
mavenHM = {}
npmHM = {}
#process alerts
for alert in alerts:
ecosystem = alert['securityVulnerability']['package']['ecosystem']
if ecosystem == 'MAVEN':
processMavenAlert(repoId, alert, mavenHM)
elif ecosystem == 'NPM':
processNpmAlert(repoId, alert, npmHM)
else:
raise Exception("alert. see this one - ", alert)
#add alerts
for (dependencyId, vulnId, toolId) in mavenHM:
severity = mavenHM[(dependencyId, vulnId, toolId)]['severity']
count = mavenHM[(dependencyId, vulnId, toolId)]['count']
q = 'insert into mavenAlert values(%s,%s,%s,%s,%s,%s,%s,%s)'
try:
sql.execute(
q,
(None, None, dependencyId, vulnId, toolId, None, severity, 1))
except sql.pymysql.IntegrityError as error:
if error.args[0] == sql.PYMYSQL_DUPLICATE_ERROR:
#TODO update scandate
print('maven alert exists already in db')
else:
raise Exception(str(error))
for (dependencyId, vulnId, toolId) in npmHM:
severity = npmHM[(dependencyId, vulnId, toolId)]['severity']
count = npmHM[(dependencyId, vulnId, toolId)]['count']
q = 'insert into npmAlert values(%s,%s,%s,%s,%s,%s,%s,%s,%s)'
try:
sql.execute(q, (None, None, dependencyId, vulnId, None, toolId,
None, severity, 1))
except sql.pymysql.IntegrityError as error:
if error.args[0] == sql.PYMYSQL_DUPLICATE_ERROR:
#TODO update scandate
print('maven alert exists already in db')
else:
raise Exception(str(error))
def readDependabotPR(name, repoId):
g = Github(token)
user = g.get_user()
repo = user.get_repo(name)
issues = repo.get_issues()
for issue in issues:
if issue.user.login != 'dependabot[bot]':
continue
if 'Bump' not in issue.title:
continue
body = issue.body.split('\n')[0]
print(body)
body = body.strip()
body = body.split(' ')
prior = body[-3]
fixed = body[-1]
changeType = getChangeType(prior, fixed)
q = 'insert into dependabotPR values(%s,%s)'
sql.execute(q, (repoId, changeType))
def getNPMVulnerability(data):
sourceId = 'NPM-' + str(data['id'])
vulnId = common.getVulnerabilityId(None, sourceId)
if vulnId > 0:
return vulnId
publishDate = dt.parse(data['created'])
description = data['title'] + data['overview']
insertQ = 'insert into vulnerability values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
try:
sql.execute(insertQ, (None, 'NPM', None, sourceId, publishDate,
description, None, None, None, None))
except sql.pymysql.IntegrityError as error:
if error.args[0] == sql.PYMYSQL_DUPLICATE_ERROR:
print(sourceId, ' already exists')
else:
raise Exception(str(error))
return common.getVulnerabilityId(None, sourceId)
def addSnykInfo(vuln, dependencyPathId, repoId, ecosystem):
snykId = vuln['id']
isUpgradable = vuln['isUpgradable']
isPatchable = vuln['isPatchable']
proprietary = vuln['proprietary']
if 'parentDepType' in vuln:
depType = vuln['parentDepType']
else:
depType = None
insertQ = 'insert into snyk values(%s,%s,%s,%s,%s,%s,%s,%s,%s)'
try:
sql.execute(insertQ,
(None, snykId, repoId, dependencyPathId, isUpgradable,
isPatchable, depType, proprietary, ecosystem))
except sql.pymysql.IntegrityError as error:
if error.args[0] == sql.PYMYSQL_DUPLICATE_ERROR:
print('snykinfo already present')
return
else:
raise Exception(str(error))
def addVulnerabilities(repoId, data, vulnMapping, severity):
hm = {}
for action in data:
for cur in action['resolves']:
vulnIds = vulnMapping[cur['id']]
dependencyId = getNPMDependencyId(cur['path'])
dependencyPathId = common.getDependencyPathId(cur['path'])
for vulnId in vulnIds:
tup = (dependencyId, vulnId, dependencyPathId)
if tup not in hm:
hm[tup] = 1
else:
hm[tup] += 1
for (dependencyId, vulnId, dependencyPathId) in hm.keys():
q = 'insert into npmAlert values(%s,%s,%s,%s,%s,%s,%s,%s,%s)'
sql.execute(
q,
(None, None, dependencyId, vulnId, dependencyPathId, toolId, None,
severity[vulnId], hm[(dependencyId, vulnId, dependencyPathId)]))
def get_packagejson_files():
q='''select * from repoDependencyFiles rDF
join repository r on rDF.repositoryId = r.id
where file like %s;'''
results = sql.execute(q,('%package.json%'))
hm={}
for item in results:
hm[item['repositoryId']]=item['file']
return hm
def process_vulnerabilities(repoId, data, allLibraries):
#get vuln id
srcclrId = True
assert 'cve' in data.keys()
if data['cve'] is not None:
cveId = 'CVE-' + data['cve']
vulnId = common.getVulnerabilityId(cveId, None)
if vulnId > 0:
srcclrId = False
print(cveId)
if srcclrId:
vulnId = getSrcClrVulnerability(data)
depIds = []
for library in data['libraries']:
ref = library['_links']['ref']
assert ref.startswith('/records/0/libraries/')
ref = ref[len('/records/0/libraries/'):]
ref = ref.split('/')
p, v = int(ref[0]), int(ref[-1])
package = allLibraries[p]
group = package['coordinate1']
artifact = package['coordinate2']
version = package['versions'][v]['version']
packageId = common.getPackageId(group, artifact, version, 'maven',
True)
dependencyId = common.getDependencyId(repoId, packageId, toolId, True)
depIds.append(dependencyId)
for dependencyId in depIds:
insertQ = 'insert into mavenAlert values(%s,%s,%s,%s,%s,%s,%s,%s)'
try:
sql.execute(insertQ, (None, None, dependencyId, vulnId, toolId,
None, None, 1 / len(depIds)))
except sql.pymysql.IntegrityError as error:
if error.args[0] == sql.PYMYSQL_DUPLICATE_ERROR:
#TODO update scandate
print('maven alert exists already in db')
else:
raise Exception(str(error))
def npmInstall():
q = '''select * from repoDependencyFiles rDF
join repository r on rDF.repositoryId = r.id
where file like %s'''
results = sql.execute(q, ('%package.json', ))
for item in results:
path = '/Users/nasifimtiaz/openmrs/' + item['repoName']
if '/' in item['file']:
path = path + '/' + item['file']
path = path[:-len('/package.json')]
os.chdir(path)
os.system('npm install')
def addSnykVulenrability(vuln):
id = vuln['id']
#first check if the sourceId was already inserted
selectQ = 'select id from vulnerability where cveId is null and sourceId=%s'
results = sql.execute(selectQ, (id, ))
if not results:
cvssScore = vuln['cvssScore']
severity = vuln['severity']
title = vuln['title']
publishDate = dt.parse(vuln['publicationTime'])
insertQ = 'insert into vulnerability values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
sql.execute(insertQ, (None, 'Snyk', None, id, publishDate, title, None,
None, cvssScore, severity))
results = sql.execute(selectQ, (id, ))
cwes = vuln['identifiers']['CWE']
common.addCWEs(results[0]['id'], cwes)
return results[0]['id']
