def getspammeremails():
mainDb = shivadbconfig.dbconnectmain()
notify = server.shivaconf.getboolean('notification', 'enabled')
whitelist = "SELECT `recipients` from `whitelist`"
try:
mainDb.execute(whitelist)
record = mainDb.fetchone()
if ((record is None) or (record[0] is None)):
server.whitelist_ids['spammers_email'] = []
else:
server.whitelist_ids['spammers_email'] = (
record[0].encode('utf-8')).split(",")[-100:]
server.whitelist_ids['spammers_email'] = list(
set(server.whitelist_ids['spammers_email']))
logging.info("[+] Pushtodb Module: whitelist recipients:")
for key, value in server.whitelist_ids.items():
logging.info("key: %s, value: %s" % (key, value))
mainDb.close()
except mdb.Error, e:
logging.critical(
"[-] Error (Module shivapushtodb.py) - some issue obtaining whitelist: %s"
% e)
if notify is True:
shivanotifyerrors.notifydeveloper(
"[-] Error (Module shivapushtodb.py) - getspammeremails %s" %
e)
def getspammeremails():
mainDb = shivadbconfig.dbconnectmain()
notify = server.shivaconf.getboolean('notification', 'enabled')
whitelist = "SELECT `recipients` from `whitelist`"
try:
mainDb.execute(whitelist)
record = mainDb.fetchone()
if ((record is None) or (record[0] is None)):
server.whitelist_ids['spammers_email'] = []
else:
server.whitelist_ids['spammers_email'] = (record[0].encode('utf-8')).split(",")[-100:]
server.whitelist_ids['spammers_email'] = list(set(server.whitelist_ids['spammers_email']))
logging.info("[+] Pushtodb Module: whitelist recipients:")
for key, value in server.whitelist_ids.items():
logging.info("key: %s, value: %s" % (key, value))
mainDb.close()
except mdb.Error, e:
logging.critical("[-] Error (Module shivapushtodb.py) - some issue obtaining whitelist: %s" % e)
if notify is True:
shivanotifyerrors.notifydeveloper("[-] Error (Module shivapushtodb.py) - getspammeremails %s" % e)
def main():
fetchfromtempdb = "SELECT `id`, `ssdeep`, `length` FROM `spam` WHERE 1"
fetchfrommaindb = "SELECT `id`, `ssdeep`, `length` FROM `spam` WHERE 1"
try:
tempDb.execute(fetchfromtempdb)
mainDb.execute(fetchfrommaindb)
except mdb.Error, e:
logging.error("[-] Error (Module shivamaindb.py) - executing fetchfromdb %s" % e)
if notify is True:
shivanotifyerrors.notifydeveloper("[-] Error (Module shivamaindb.py) - executing fetchfromdb %s" % e)
def update(tempid, mainid):
mailFields = {'sourceIP':'', 'sensorID':'', 'firstSeen':'', 'relayCounter':'', 'relayTime':'', 'count':0, 'inlineFileName':[], 'inlineFilePath':[], 'inlineFileMd5':[], 'attachmentFileName':[], 'attachmentFilePath':[], 'attachmentFileMd5':[], 'links':[], 'date': '', 'to': ''}
tempurls = "SELECT `hyperlink` FROM `links` WHERE `spam_id` = '" + str(tempid) + "'"
tempattachs = "SELECT `file_name`, `attachment_file_path`, `attach_type`, `attachmentFileMd5` FROM `attachments` WHERE `spam_id` = '" + str(tempid) + "'"
tempsensors = "SELECT `sensorID` FROM `sensors` WHERE `spam_id` = '" + str(tempid) + "'"
tempspam = "SELECT `firstSeen`, `relayCounter`, `relayTime`, `sourceIP`, `totalCounter`, `to` FROM `spam` WHERE `id` = '" + str(tempid) + "'"
try:
tempDb.execute(tempurls)
records = tempDb.fetchall()
for record in records:
mailFields['links'].append(record[0])
tempDb.execute(tempattachs)
records = None # To make sure that in case following query fails, we don't end up updating values from last query.
records = tempDb.fetchall()
for record in records:
if record[2] == 'attach': # Note: record[2] denotes 'attach_type' field in table. Could be either 'attach' or 'inline'
mailFields['attachmentFileName'].append(record[0])
mailFields['attachmentFileMd5'].append(record[3])
mailFields['attachmentFilePath'].append(record[1])
elif record[2] == 'inline':
mailFields['inlineFileName'].append(record[0])
mailFields['inlineFileMd5'].append(record[3])
mailFields['inlineFilePath'].append(record[1])
tempDb.execute(tempsensors)
record = tempDb.fetchone()
mailFields['sensorID'] = record[0]
tempDb.execute(tempspam)
record = tempDb.fetchone()
mailFields['firstSeen'] = str(record[0])
mailFields['date'] = str(record[0]).split(' ')[0]
mailFields['relayCounter'] = record[1]
mailFields['relayTime'] = str(record[2])
mailFields['sourceIP'] = record[3]
mailFields['count'] = record[4]
mailFields['to'] = record[5]
except mdb.Error, e:
logging.error("[-] Error (Module shivamaindb.py) - executing temprecords %s" % e)
if notify is True:
shivanotifyerrors.notifydeveloper("[-] Error (Module shivamaindb.py) - executing temprecords %s" % e)
def movebadsample(key, msg):
"""Copies the troublesome spam to different folder and removes it from
queue.
"""
queuepath = server.shivaconf.get('global', 'queuepath')
undeliverable_path = server.shivaconf.get('analyzer', 'undeliverable_path')
notify = server.shivaconf.getboolean('notification', 'enabled')
logging.critical("\n**** [-] Error!!! ****")
logging.critical("Copying spam file to distortedSamples directory before \
moving it out of queue")
shutil.copyfile(queuepath + 'new/' + key, undeliverable_path + key)
if notify is True:
shivanotifyerrors.notifydeveloper(msg)
def movebadsample(key, msg):
"""Copies the troublesome spam to different folder and removes it from
queue.
"""
queuepath = server.shivaconf.get('global', 'queuepath')
undeliverable_path = server.shivaconf.get('analyzer', 'undeliverable_path')
notify = server.shivaconf.getboolean('notification', 'enabled')
logging.critical("\n**** [-] Error!!! ****")
logging.critical("Copying spam file to distortedSamples directory before \
moving it out of queue")
shutil.copyfile(queuepath + 'new/' + key, undeliverable_path + key)
if notify is True:
shivanotifyerrors.notifydeveloper(msg)
def push():
logging.info("[+]Inside shivapushtodb Module")
notify = server.shivaconf.getboolean('notification', 'enabled')
exeSql = shivadbconfig.dbconnect()
attachpath = server.shivaconf.get('analyzer', 'attachpath')
inlinepath = server.shivaconf.get('analyzer', 'inlinepath')
truncate = ['truncate attachments','truncate links', 'truncate sensors', 'truncate spam']
for query in truncate:
try:
exeSql.execute(query)
except Exception, e:
logging.critical("[-] Error (shivapushtodb) truncate %s" % str(e))
if notify is True:
shivanotifyerrors.notifydeveloper("[-] Error (Module shivapushtodb.py) - truncate %s" % e)
def push():
logging.info("[+]Inside shivapushtodb Module")
notify = server.shivaconf.getboolean('notification', 'enabled')
exeSql = shivadbconfig.dbconnect()
attachpath = server.shivaconf.get('analyzer', 'attachpath')
inlinepath = server.shivaconf.get('analyzer', 'inlinepath')
truncate = [
'truncate attachments', 'truncate links', 'truncate sensors',
'truncate spam'
]
for query in truncate:
try:
exeSql.execute(query)
except Exception, e:
logging.critical("[-] Error (shivapushtodb) truncate %s" % str(e))
if notify is True:
shivanotifyerrors.notifydeveloper(
"[-] Error (Module shivapushtodb.py) - truncate %s" % e)
record['html']), str(record['subject']), str(
record['headers']), str(record['sourceIP']), str(
record['sensorID']), str(record['firstSeen']), str(
record['relayed']), str(record['counter']), str(
record['len']), str(
record['firstRelayed']), str(
record['user'])
insertSpam = "INSERT INTO `spam`(`id`, `ssdeep`, `to`, `from`, `textMessage`, `htmlMessage`, `subject`, `headers`, `sourceIP`, `sensorID`, `firstSeen`, `relayCounter`, `totalCounter`, `length`, `relayTime`, `user`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
try:
exeSql.execute(insertSpam, values)
except mdb.Error, e:
logging.critical("[-] Error (shivapushtodb insert_spam) - %d: %s" %
(e.args[0], e.args[1]))
if notify is True:
shivanotifyerrors.notifydeveloper(
"[-] Error (Module shivapushtodb.py) - insertSpam %s" % e)
# Checking for attachments and dumping into directory, if any. Also storing information in database.
if len(record['attachmentFile']) > 0:
i = 0
while i < len(record['attachmentFile']):
fileName = str(record['s_id']) + "-a-" + str(
record['attachmentFileName'][i])
path = attachpath + fileName
attachFile = open(path, 'wb')
attachFile.write(record['attachmentFile'][i])
attachFile.close()
#record['attachmentFile'][i] = path
values = str(record['s_id']), str(
mdb.escape_string(
record['attachmentFileName'][i])), 'attach', str(
def insert(spam_id):
mailFields = {'s_id':'', 'ssdeep':'', 'to':'', 'from':'', 'text':'', 'html':'', 'subject':'', 'headers':'', 'sourceIP':'', 'sensorID':'', 'firstSeen':'', 'relayCounter':'', 'relayTime':'', 'count':0, 'len':'', 'inlineFileName':[], 'inlineFilePath':[], 'inlineFileMd5':[], 'attachmentFileName':[], 'attachmentFilePath':[], 'attachmentFileMd5':[], 'links':[], 'date': '' , 'phishingHumanCheck' : '', 'shivaScore' : -1.0, 'spamassassinScore' : -1.0 }
spam = "SELECT `id`, `ssdeep`, `to`, `from`, `textMessage`, `htmlMessage`, `subject`, `headers`, `sourceIP`, `sensorID`, `firstSeen`, `relayCounter`, `relayTime`, `totalCounter`, `length`, `shivaScore`, `spamassassinScore` , `derivedPhishingStatus`, `phishingHumanCheck`, `urlPhishing` FROM `spam` WHERE `id` = '" + str(spam_id) + "'"
attachments = "SELECT `id`, `spam_id`, `file_name`, `attach_type`, `attachmentFileMd5`, `date`, `attachment_file_path` FROM `attachments` WHERE `spam_id` = '" + str(spam_id) + "'"
url = "SELECT `id`, `spam_id`, `hyperlink` `date` FROM `links` WHERE `spam_id` = '" + str(spam_id) + "'"
sensor = "SELECT `id`, `sensorID` FROM `spam` WHERE `id` = '" + str(spam_id) + "'"
try:
# Saving 'spam' table's data
tempDb.execute(spam)
spamrecord = tempDb.fetchone()
if spamrecord:
mailFields['s_id'], mailFields['ssdeep'], mailFields['to'], mailFields['from'], mailFields['text'], mailFields['html'], mailFields['subject'], mailFields['headers'], mailFields['sourceIP'], mailFields['sensorID'], mailFields['firstSeen'], mailFields['relayCounter'], mailFields['relayTime'], mailFields['count'], mailFields['len'], mailFields['shivaScore'], mailFields['spamassassinScore'], mailFields['derivedPhishingStatus'], mailFields['phishingHumanCheck'], mailFields['urlPhishing'] = spamrecord
mailFields['date'] = str(mailFields['firstSeen']).split(' ')[0]
# Saving 'attachments' table's data
tempDb.execute(attachments)
attachrecords = tempDb.fetchall()
for record in attachrecords:
if str(record[3]) == 'attach': # Note: record[3] denotes 'attach_type' field in table. Could be 'attach' or 'inline'
mailFields['attachmentFileName'].append(record[2])
mailFields['attachmentFileMd5'].append(record[4])
mailFields['attachmentFilePath'].append(record[6])
elif str(record[3]) == 'inline':
mailFields['inlineFileName'].append(record[2])
mailFields['inlineFileMd5'].append(record[4])
mailFields['inlineFilePath'].append(record[6])
# Saving 'links' table's data
tempDb.execute(url)
urlrecords = tempDb.fetchall()
for record in urlrecords:
mailFields['links'].append(record[2])
# Saving 'sensor' table's data
tempDb.execute(sensor)
sensorrecords = tempDb.fetchone()
mailFields['sensorID'] = sensorrecords[1]
# Inserting data in main db
phishingHumanCheck = 'NULL'
if mailFields['phishingHumanCheck'] == 1:
phishingHumanCheck = 'TRUE'
elif mailFields['phishingHumanCheck'] == 0:
phishingHumanCheck = 'FALSE'
derivedPhishingStatus = 'NULL'
if mailFields['derivedPhishingStatus'] == 1:
derivedPhishingStatus = 'TRUE'
elif mailFields['derivedPhishingStatus'] == 0:
derivedPhishingStatus = 'FALSE'
insert_spam = "INSERT INTO `spam`(`headers`, `to`, `from`, `subject`, `textMessage`, `htmlMessage`, `totalCounter`, `id`, `ssdeep`, `length`, `shivaScore`, `spamassassinScore`, `derivedPhishingStatus`, `phishingHumanCheck`, `urlPhishing`) VALUES('" + mailFields['headers'] + "', '" + mailFields['to'] + "', '" + mailFields['from'] + "', '" + mailFields['subject'] + "', '" + mailFields['text'] + "', '" + mailFields['html'] + "', '" + str(mailFields['count']) + "', '" + mailFields['s_id'] + "', '" + mailFields['ssdeep'] + "', '" + str(mailFields['len']) + "', '" + str(mailFields['shivaScore']) + "', '" + str(mailFields['spamassassinScore']) + "', " + derivedPhishingStatus + ', ' + phishingHumanCheck + ', ' + str(mailFields['urlPhishing']) + ")"
try:
mainDb.execute(insert_spam)
except mdb.Error, e:
logging.error("[-] Error (Module shivamaindb.py) - executing insert_spam %s" % e)
if notify is True:
shivanotifyerrors.notifydeveloper("[-] Error (Module shivamaindb.py) - executing insert_spam %s" % e)
insert_sdate = "INSERT INTO sdate (`date`, `firstSeen`, `lastSeen`, `todaysCounter`) VALUES('" + str(mailFields['date']) + "', '" + str(mailFields['firstSeen']) + "', '" + str(mailFields['firstSeen']) + "', '" + str(mailFields['count']) + "')"
try:
mainDb.execute(insert_sdate)
except mdb.Error, e:
logging.error("[-] Error (Module shivamaindb.py) - executing insert_sdate %s" % e)
if notify is True:
shivanotifyerrors.notifydeveloper("[-] Error (Module shivamaindb.py) - executing insert_sdate %s" % e)
insert_sdate_spam = "INSERT INTO sdate_spam (`spam_id`, `date_id`) VALUES('" + mailFields['s_id'] + "', '" + str(mainDb.lastrowid) + "')"
try:
mainDb.execute(insert_sdate_spam)
except mdb.Error, e:
logging.error("[-] Error (Module shivamaindb.py) - executing insert_sdate_spam %s" % e)
if notify is True:
shivanotifyerrors.notifydeveloper("[-] Error (Module shivamaindb.py) - executing insert_sdate_spam %s" % e)
insert(t_record[0])
# At last update whitelist recipients
group_concat_max_len = "SET SESSION group_concat_max_len = 20000"
#whitelist = "INSERT INTO `whitelist` (`id`, `recipients`) VALUES ('1', (SELECT GROUP_CONCAT(DISTINCT `to`) FROM `spam` WHERE `totalCounter` < 30)) ON DUPLICATE KEY UPDATE `recipients` = (SELECT GROUP_CONCAT(DISTINCT `to`) FROM `spam` WHERE `totalCounter` < 30)"
whitelist = "INSERT INTO `whitelist` (`id`, `recipients`) VALUES ('1', (SELECT GROUP_CONCAT(`to`) FROM `spam` RIGHT JOIN `sdate_spam` INNER JOIN `sdate` ON (sdate.id = sdate_spam.date_id) ON (spam.id = sdate_spam.spam_id) WHERE spam.id IN (SELECT id FROM `spam` WHERE totalCounter < 100))) ON DUPLICATE KEY UPDATE `recipients` = (SELECT GROUP_CONCAT(`to`) FROM `spam` RIGHT JOIN `sdate_spam` INNER JOIN `sdate` ON (sdate.id = sdate_spam.date_id) ON (spam.id = sdate_spam.spam_id) WHERE spam.id IN (SELECT id FROM `spam` WHERE totalCounter < 100))"
try:
mainDb.execute(group_concat_max_len)
mainDb.execute(whitelist)
except mdb.Error, e:
logging.error("[-] Error (Module shivamaindb.py) - executing mainDb %s" % e)
if notify is True:
shivanotifyerrors.notifydeveloper("[-] Error (Module shivamaindb.py) - executing mainDb %s" % e)
def insert(spam_id):
mailFields = {'s_id':'', 'ssdeep':'', 'to':'', 'from':'', 'text':'', 'html':'', 'subject':'', 'headers':'', 'sourceIP':'', 'sensorID':'', 'firstSeen':'', 'relayCounter':'', 'relayTime':'', 'count':0, 'len':'', 'inlineFileName':[], 'inlineFilePath':[], 'inlineFileMd5':[], 'attachmentFileName':[], 'attachmentFilePath':[], 'attachmentFileMd5':[], 'links':[], 'date': '' , 'phishingHumanCheck' : '', 'shivaScore' : -1.0, 'spamassassinScore' : -1.0 }
spam = "SELECT `id`, `ssdeep`, `to`, `from`, `textMessage`, `htmlMessage`, `subject`, `headers`, `sourceIP`, `sensorID`, `firstSeen`, `relayCounter`, `relayTime`, `totalCounter`, `length`, `shivaScore`, `spamassassinScore` , `derivedPhishingStatus`, `phishingHumanCheck`, `urlPhishing` FROM `spam` WHERE `id` = '" + str(spam_id) + "'"
attachments = "SELECT `id`, `spam_id`, `file_name`, `attach_type`, `attachmentFileMd5`, `date`, `attachment_file_path` FROM `attachments` WHERE `spam_id` = '" + str(spam_id) + "'"
url = "SELECT `id`, `spam_id`, `hyperlink` `date` FROM `links` WHERE `spam_id` = '" + str(spam_id) + "'"
sensor = "SELECT `id`, `sensorID` FROM `spam` WHERE `id` = '" + str(spam_id) + "'"
try:
# Saving 'spam' table's data
if notify is True:
shivanotifyerrors.notifydeveloper("[-] Error (Module shivapushtodb.py) - truncate %s" % e)
for record in server.QueueReceiver.deep_records:
logging.info("Records are %d" % len(server.QueueReceiver.deep_records))
values = str(record['s_id']), str(record['ssdeep']), str(record['to']), str(record['from']), str(record['text']), str(record['html']), str(record['subject']), str(record['headers']), str(record['sourceIP']), str(record['sensorID']), str(record['firstSeen']), str(record['relayed']), str(record['counter']), str(record['len']), str(record['firstRelayed']), str(record['user'])
insertSpam = "INSERT INTO `spam`(`id`, `ssdeep`, `to`, `from`, `textMessage`, `htmlMessage`, `subject`, `headers`, `sourceIP`, `sensorID`, `firstSeen`, `relayCounter`, `totalCounter`, `length`, `relayTime`, `user`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
try:
exeSql.execute(insertSpam, values)
except mdb.Error, e:
logging.critical("[-] Error (shivapushtodb insert_spam) - %d: %s" % (e.args[0], e.args[1]))
if notify is True:
shivanotifyerrors.notifydeveloper("[-] Error (Module shivapushtodb.py) - insertSpam %s" % e)
# Checking for attachments and dumping into directory, if any. Also storing information in database.
if len(record['attachmentFile']) > 0:
i = 0
while i < len(record['attachmentFile']):
fileName = str(record['s_id']) + "-a-" + str(record['attachmentFileName'][i])
path = attachpath + fileName
attachFile = open(path, 'wb')
attachFile.write(record['attachmentFile'][i])
attachFile.close()
#record['attachmentFile'][i] = path
values = str(record['s_id']), str(mdb.escape_string(record['attachmentFileName'][i])), 'attach', str(record['attachmentFileMd5'][i]), str(record['date']), str(mdb.escape_string(path))
insertAttachment = "INSERT INTO `attachments`(`spam_id`, `file_name`, `attach_type`, `attachmentFileMd5`, `date`, `attachment_file_path`) VALUES (%s, %s, %s, %s, %s, %s)"
try:
