def loadCVEs():
db = PSQLDatabase()
db.query("SELECT * FROM cves ORDER BY cve_id ASC;")
data = db.cursor.fetchall()
db.close()
# return json.dumps(data)
return data
def getTweets(cve):
db = PSQLDatabase()
db.query(f"SELECT publised_date from CVES where CVE_id = '{cve}'")
NVD_Published_Date = db.cursor.fetchone()[0]
tweets = []
c = twint.Config()
c.Search = cve
c.Since = NVD_Published_Date
c.Store_object = True
c.Hide_output = True
c.Filter_retweets = True
c.Store_object_tweets_list = tweets
try:
twint.run.Search(c)
for t in tweets:
url_list = ','.join([u for u in t.urls])
santweet = t.tweet.replace("'", "")
sqlquery = f"""INSERT INTO TwitterTweets (CVE_id, tweet_id, tweet, Datestamp, retweet_count, Replies_Count, Likes_Count, URLs, username)
VALUES ('{cve}','{t.id}','{santweet}','{t.datestamp}','{t.retweets_count}','{t.replies_count}','{t.likes_count}','{url_list}','{t.username}')
ON CONFLICT (tweet_id)
DO UPDATE SET
retweet_count = excluded.retweet_count,
Replies_Count = excluded.Replies_Count,
Likes_Count = excluded.Likes_Count
;"""
print(sqlquery)
db.query(sqlquery)
except Exception as e:
print(e)
db.conn.commit()
db.close()
return "OK"
def createCVEdb(new_cve):
db = PSQLDatabase()
db.query("SELECT CVE_id from CVES where CVE_id = '" + new_cve + "';")
if not db.cursor.fetchone():
req = requests.get("https://nvd.nist.gov/vuln/detail/" + new_cve)
soup = BeautifulSoup(req.content, 'html.parser')
NVD_Description = soup.find("p", {
"data-testid": "vuln-description"
}).getText().replace("'", "")
NVD_Published_Date = soup.find("span", {
"data-testid": "vuln-published-on"
}).getText()
NVD_Published_Date = datetime.strptime(NVD_Published_Date,
'%m/%d/%Y').strftime('%Y-%m-%d')
print("Exploit published on: " + str(NVD_Published_Date))
NVD_Updated_Date = soup.find("span", {
"data-testid": "vuln-last-modified-on"
}).getText()
NVD_Updated_Date = datetime.strptime(NVD_Updated_Date,
'%m/%d/%Y').strftime('%Y-%m-%d')
CVSS3 = soup.find("a", {
"data-testid": "vuln-cvss3-panel-score"
}).getText()
CVSS_vector = soup.find("span", {
"data-testid": "vuln-cvss3-nist-vector"
}).getText()
#CVE_References = soup.find("table", {"data-testid" : "vuln-hyperlinks-table"}).getText()
#CWE = soup.find("table", {"data-testid" : "vuln-technical-details-container"}).getText()
#Configurations = soup.find("div", {"id" : "vulnCpeTree"}).getText()
sql = f"""INSERT INTO CVES (
CVE_id,
Description,
Publised_Date,
CVSS,
CVSS_vector)
VALUES (
'{new_cve}',
'{NVD_Description}',
'{NVD_Published_Date}',
'{CVSS3}',
'{CVSS_vector}')
ON CONFLICT DO NOTHING;"""
db.query(sql)
db.conn.commit()
else:
print("El cve ya existe")
db.close()
return "OK"
def getRepos(cve):
config = configparser.ConfigParser()
try:
config.read('./config.ini')
except FileExistsError as err:
print('File exists error: {0}', err)
sys.exit(1)
ACCESS_TOKEN = config['Github']['ACCESS_TOKEN']
g = Github(ACCESS_TOKEN)
db = PSQLDatabase()
rate_limit = g.get_rate_limit()
rate = rate_limit.search
if rate.remaining == 0:
print(
f'You have 0/{rate.limit} API calls remaining. Reset time: {rate.reset}'
)
return
else:
print(f'You have {rate.remaining}/{rate.limit} API calls remaining')
# Search code
query = f'"{cve}" in:file extension:py extension:ps1 extension:md extension:vbs extension:rb extension:pl extension:php extension:go extension:sh extension:bat extension:sql extension:exe extension:sln extension:txt'
result_code = g.search_code(query, order='desc')
for file in result_code:
created_at = datetime.strptime(
str(file.repository.created_at),
'%Y-%m-%d %H:%M:%S').strftime('%Y-%m-%d')
updated_at = datetime.strptime(
str(file.repository.updated_at),
'%Y-%m-%d %H:%M:%S').strftime('%Y-%m-%d')
sqlquery = f"""INSERT INTO GithubRepos (CVE_id, repo_id, owner, created_at, updated_at, forks_count, stargazers_count, repository)
VALUES ('{cve}','{file.repository.id}', '{file.repository.owner.login}','{created_at}','{updated_at}','{file.repository.forks_count}','{file.repository.stargazers_count}','{file.repository.full_name}')
ON CONFLICT (repo_id)
DO UPDATE SET
forks_count = excluded.forks_count,
stargazers_count = excluded.stargazers_count,
updated_at = excluded.updated_at
;"""
db.query(sqlquery)
sqlquery = f"""INSERT INTO GithubReposURLs (CVE_id, repo_id, name, download_url, sha1, date_add, epoch)
VALUES ('{cve}','{file.repository.id}', '{file.name}', '{file.download_url}', '{file.sha}', '{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}', '{int(datetime.now().timestamp())}')
ON CONFLICT (download_url)
DO UPDATE SET
sha1 = excluded.sha1
;"""
db.query(sqlquery)
db.conn.commit()
db.close()
return "OK"
def reposJSON_chart(cve):
db = PSQLDatabase()
sqlquery = f"""SELECT repo.updated_at, repou.name, repou.sha1
FROM GithubReposURLs as repou
INNER JOIN GithubRepos as repo ON repo.repo_id = repou.repo_id
WHERE repou.cve_id = '{cve}'
;"""
db.query(sqlquery)
data_files = db.cursor.fetchall()
df_files = pd.DataFrame(columns=['Date', 'File', 'SHA1'])
for data in data_files:
index = len(df_files)
df_files.loc[index, 'Date'] = data[0]
df_files.loc[index, 'File'] = data[1]
df_files.loc[index, 'SHA1'] = data[2]
# Set Date to index
df_files['datetime'] = pd.to_datetime(df_files['Date'])
df_files = df_files.set_index('datetime')
a = df_files.groupby('Date')['File'].apply(list)
# MAX Files
df_files = df_files.sort_values('Date').drop_duplicates(
['Date'], keep='last').sort_index()
df_files = df_files.sort_index()
df_files['n_files'] = [len(c) for c in a.tolist()]
df_files['all_files'] = [c for c in a.tolist()]
data = {}
# Dates
data['dates'] = []
for date in df_files['Date']:
data['dates'].append(date)
# Files Volume (n_files)
data['files_volume'] = []
for n_files in df_files['n_files']:
data['files_volume'].append(n_files)
print(data)
db.close()
return data
def reposJSON(cve):
db = PSQLDatabase()
sqlquery = f"""SELECT json_agg(to_json(d))
from (
select
repo_id, created_at, download_url, stargazers_count, forks_count
from githubrepos WHERE cve_id = '{cve}'
) as d
;"""
sqlquery = f"""SELECT json_agg(to_json(d))
from (
SELECT repou.CVE_id, repou.download_url, repo.repository, repo.updated_at, repou.name, repou.sha1
FROM GithubReposURLs as repou
INNER JOIN GithubRepos as repo ON repo.repo_id = repou.repo_id
WHERE repou.cve_id = '{cve}'
ORDER BY repo.updated_at DESC
) as d
;"""
db.query(sqlquery)
data_repo = db.cursor.fetchone()
data_repo = data_repo[0]
return json.dumps(data_repo)
def tweetsJSON(cve):
db = PSQLDatabase()
sqlquery = f"""SELECT Datestamp, tweet, tweet_id, retweet_count, replies_count, likes_count, username FROM TwitterTweets where cve_id = '{cve}';"""
db.query(sqlquery)
data_tweet = db.cursor.fetchall()
df_tweet = pd.DataFrame(
columns=['Date', 'Tweet', 'ID', 'RTs', 'Replies', 'Likes', 'Username'])
for data in data_tweet:
index = len(df_tweet)
df_tweet.loc[index, 'Date'] = data[0]
df_tweet.loc[index, 'Tweet'] = data[1]
df_tweet.loc[index, 'ID'] = data[2]
df_tweet.loc[index, 'RTs'] = data[3]
df_tweet.loc[index, 'Replies'] = data[4]
df_tweet.loc[index, 'Likes'] = data[5]
df_tweet.loc[index, 'Username'] = data[6]
# Set Date to index
df_tweet['datetime'] = pd.to_datetime(df_tweet['Date'])
df_tweet = df_tweet.set_index('datetime')
df_tweet.sort_values('Likes')
# SUM
df_tweet['count_sum_likes'] = df_tweet.groupby(['Date'
])['Likes'].transform(sum)
df_tweet['count_sum_rts'] = df_tweet.groupby(['Date'
])['RTs'].transform(sum)
a = df_tweet.groupby('Date')['Tweet'].apply(list)
# MAX Twitter
df_tweet = df_tweet.sort_values('Likes').drop_duplicates(
['Date'], keep='last').sort_index()
df_tweet = df_tweet.sort_index()
df_tweet['n_tweets'] = [len(c) for c in a.tolist()]
df_tweet['all_tweets'] = [c for c in a.tolist()]
df_tweet = df_tweet.reset_index()
# Create JSON data
data = {}
# Dates
data['dates'] = []
for date in df_tweet['Date']:
data['dates'].append(date)
# IDs
data['ids'] = []
for ids in df_tweet['ID']:
ids = str(ids)
data['ids'].append(ids)
# Tweets Volume (n_tweets)
data['tweets_volume'] = []
for n_tweet in df_tweet['n_tweets']:
data['tweets_volume'].append(n_tweet)
# Likes Volume
data['likes_volume'] = []
for count_sum_likes in df_tweet['count_sum_likes']:
data['likes_volume'].append(count_sum_likes)
# RTs Volume
data['retweets_volume'] = []
for count_sum_rts in df_tweet['count_sum_rts']:
data['retweets_volume'].append(count_sum_rts)
# Max Tweets
data['max_tweets'] = []
for tweet in df_tweet['Tweet']:
data['max_tweets'].append(tweet)
# Username max tweet
data['usernames'] = []
for username in df_tweet['Username']:
data['usernames'].append(username)
# Predictions
data['predictions'] = []
pp = TWprocessing()
df_Tweet_Tweet = df_tweet['Tweet']
preds = pp.funcTWprocessing(df_Tweet_Tweet)
for pred in preds:
data['predictions'].append(int(pred))
db.conn.commit()
db.close()
return data
def searchByCVE(cve, column):
db = PSQLDatabase()
db.query("SELECT " + column + " FROM cves WHERE cve_id = '" + cve + "';")
data = db.cursor.fetchone()
db.close()
return data[0]
def deleteCVEdb(cve):
db = PSQLDatabase()
db.query("DELETE from CVES where CVE_id = '" + cve + "';")
db.conn.commit()
db.close()
return "OK"
forks_count BIGINT,
stargazers_count BIGINT,
sha TEXT,
repository TEXT,
download_url TEXT,
created_at TEXT,
updated_at TEXT,
CONSTRAINT fk_cves
FOREIGN KEY(CVE_id)
REFERENCES CVES(CVE_id)
ON DELETE CASCADE);''',
'''Create Table IF NOT EXISTS GithubReposURLs(CVE_id TEXT,
repo_id BIGINT,
name TEXT,
download_url TEXT PRIMARY KEY,
sha1 TEXT,
date_add TEXT,
epoch BIGINT,
CONSTRAINT fk_cves
FOREIGN KEY(CVE_id)
REFERENCES CVES(CVE_id)
ON DELETE CASCADE) ;''')
for command in commands:
db.query(command)
print(command)
db.conn.commit()
db.close()