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 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: