--- a/WebBrowser/SafeBrowsing/SafeBrowsingCache.py Mon Jul 24 18:40:07 2017 +0200
+++ b/WebBrowser/SafeBrowsing/SafeBrowsingCache.py Tue Jul 25 19:22:36 2017 +0200
@@ -19,7 +19,7 @@
import os
from PyQt5.QtCore import QObject
-from PyQt5.QtSql import QSqlDatabase, QSqlQuery
+from PyQt5.QtSql import QSql, QSqlDatabase, QSqlQuery
class ThreatList(object):
@@ -37,9 +37,9 @@
@param threatEntryType threat entry type
@type str
"""
- self.__threatType = threatType
- self.__platformType = platformType
- self.__threatEntryType = threatEntryType
+ self.threatType = threatType
+ self.platformType = platformType
+ self.threatEntryType = threatEntryType
@classmethod
def fromApiEntry(cls, entry):
@@ -62,7 +62,7 @@
@return tuple containing the threat list info
@rtype tuple of (str, str, str)
"""
- return (self.__threatType, self.platformType, self.__threatEntryType)
+ return (self.threatType, self.platformType, self.threatEntryType)
def __repr__(self):
"""
@@ -78,7 +78,6 @@
"""
Class implementing a cache for Google Safe Browsing.
"""
-
create_threat_list_stmt = """
CREATE TABLE threat_list
(threat_type character varying(128) NOT NULL,
@@ -164,6 +163,14 @@
if preparationNeeded:
self.__prepareCacheDb()
+ def close(self):
+ """
+ Public method to close the database.
+ """
+ if QSqlDatabase.database(self.__connectionName).isOpen():
+ QSqlDatabase.database(self.__connectionName).close()
+ QSqlDatabase.removeDatabase(self.__language)
+
def __openCacheDb(self):
"""
Private method to open the cache database.
@@ -225,7 +232,7 @@
queryStr = """
SELECT threat_type, platform_type, threat_entry_type,
expires_at < current_timestamp AS has_expired
- FROM full_hash WHERE value IN ({})
+ FROM full_hash WHERE value IN ({0})
"""
output = []
@@ -237,7 +244,7 @@
query.prepare(
queryStr.format(",".join(["?" * len(hashValues)])))
for hashValue in hashValues:
- query.addBindValue(hashValue)
+ query.addBindValue(hashValue, QSql.In | QSql.Binary)
query.exec_()
@@ -259,7 +266,7 @@
"""
Public method to look up hash prefixes in the local cache.
- @param prefix hash prefix to look up
+ @param prefixes list of hash prefixes to look up
@type list of bytes
@return list of tuples containing the threat list, full hash and
negative cache expiration flag
@@ -268,7 +275,7 @@
queryStr = """
SELECT value,threat_type,platform_type,threat_entry_type,
negative_expires_at < current_timestamp AS negative_cache_expired
- FROM hash_prefix WHERE cue IN ({})
+ FROM hash_prefix WHERE cue IN ({0})
"""
output = []
@@ -298,3 +305,106 @@
db.commit()
return output
+
+ def storeFullHash(self, threatList, hashValue, cacheDuration,
+ malwareThreatType):
+ """
+ Public method to store full hash data in the cache database.
+
+ @param threatList threat list info object
+ @type ThreatList
+ @param hashValue hash to be stored
+ @type bytes
+ @param cacheDuration duration the data should remain in the cache
+ @type int or float
+ @param malwareThreatType threat type of the malware
+ @type str
+ """
+ insertQueryStr = """
+ INSERT OR IGNORE INTO full_hash
+ (value, threat_type, platform_type, threat_entry_type,
+ malware_threat_type, downloaded_at)
+ VALUES
+ (?, ?, ?, ?, ?, current_timestamp)
+ """
+ updateQueryStr = """
+ UPDATE full_hash SET
+ expires_at=datetime(current_timestamp, '+{0} SECONDS')
+ WHERE value=? AND threat_type=? AND platform_type=? AND
+ threat_entry_type=?
+ """
+
+ db = QSqlDatabase.database(self.__connectionName)
+ if db.isOpen():
+ db.transaction()
+ try:
+ query = QSqlQuery(db)
+ query.prepare(insertQueryStr)
+ query.addBindValue(hashValue, QSql.In | QSql.Binary)
+ query.addBindValue(threatList.threatType)
+ query.addBindValue(threatList.platformType)
+ query.addBindValue(threatList.threatEntryType)
+ query.addBindValue(malwareThreatType)
+ query.exec_()
+ del query
+
+ query = QSqlQuery(db)
+ query.prepare(updateQueryStr.format(int(cacheDuration)))
+ query.addBindValue(hashValue, QSql.In | QSql.Binary)
+ query.addBindValue(threatList.threatType)
+ query.addBindValue(threatList.platformType)
+ query.addBindValue(threatList.threatEntryType)
+ query.exec_()
+ del query
+ finally:
+ db.commit()
+
+ def deleteHashPrefixList(self, threatList):
+ """
+ Public method to delete hash prefixes for a given threat list.
+
+ @param threatList threat list info object
+ @type ThreatList
+ """
+ queryStr = """
+ DELETE FROM hash_prefix
+ WHERE threat_type=? AND platform_type=? AND threat_entry_type=?
+ """
+
+ db = QSqlDatabase.database(self.__connectionName)
+ if db.isOpen():
+ db.transaction()
+ try:
+ query = QSqlQuery(db)
+ query.prepare(queryStr)
+ query.addBindValue(threatList.threatType)
+ query.addBindValue(threatList.platformType)
+ query.addBindValue(threatList.threatEntryType)
+ query.exec_()
+ del query
+ finally:
+ db.commit()
+
+ def cleanupFullHashes(self, keepExpiredFor=43200):
+ """
+ Public method to clean up full hash entries expired more than the
+ given time.
+
+ @param keepExpiredFor time period in seconds of entries to be expired
+ @type int or float
+ """
+ queryStr = """
+ DELETE FROM full_hash
+ WHERE expires_at=datetime(current_timestamp, '{0} SECONDS')
+ """
+
+ db = QSqlDatabase.database(self.__connectionName)
+ if db.isOpen():
+ db.transaction()
+ try:
+ query = QSqlQuery(db)
+ query.prepare(queryStr.format(int(keepExpiredFor)))
+ query.exec_()
+ del query
+ finally:
+ db.commit()
