--- a/WebBrowser/SafeBrowsing/SafeBrowsingCache.py Tue Jul 25 19:22:36 2017 +0200
+++ b/WebBrowser/SafeBrowsing/SafeBrowsingCache.py Wed Jul 26 19:46:17 2017 +0200
@@ -17,10 +17,13 @@
from __future__ import unicode_literals
import os
+import hashlib
from PyQt5.QtCore import QObject
from PyQt5.QtSql import QSql, QSqlDatabase, QSqlQuery
+from .SafeBrowsingUtilities import toHex
+
class ThreatList(object):
"""
@@ -161,7 +164,7 @@
self.__openCacheDb()
if preparationNeeded:
- self.__prepareCacheDb()
+ self.prepareCacheDb()
def close(self):
"""
@@ -190,9 +193,9 @@
opened = True
return opened
- def __prepareCacheDb(self):
+ def prepareCacheDb(self):
"""
- Private method to prepare the cache database.
+ Public method to prepare the cache database.
"""
db = QSqlDatabase.database(self.__connectionName)
db.transaction()
@@ -332,7 +335,7 @@
expires_at=datetime(current_timestamp, '+{0} SECONDS')
WHERE value=? AND threat_type=? AND platform_type=? AND
threat_entry_type=?
- """
+ """.format(int(cacheDuration))
db = QSqlDatabase.database(self.__connectionName)
if db.isOpen():
@@ -349,7 +352,7 @@
del query
query = QSqlQuery(db)
- query.prepare(updateQueryStr.format(int(cacheDuration)))
+ query.prepare(updateQueryStr)
query.addBindValue(hashValue, QSql.In | QSql.Binary)
query.addBindValue(threatList.threatType)
query.addBindValue(threatList.platformType)
@@ -396,6 +399,155 @@
queryStr = """
DELETE FROM full_hash
WHERE expires_at=datetime(current_timestamp, '{0} SECONDS')
+ """.format(int(keepExpiredFor))
+
+ db = QSqlDatabase.database(self.__connectionName)
+ if db.isOpen():
+ db.transaction()
+ try:
+ query = QSqlQuery(db)
+ query.prepare(queryStr)
+ query.exec_()
+ del query
+ finally:
+ db.commit()
+
+ def updateHashPrefixExpiration(self, threatList, hashPrefix,
+ negativeCacheDuration):
+ """
+ Public method to update the hash prefix expiration time.
+
+ @param threatList threat list info object
+ @type ThreatList
+ @param hashPrefix hash prefix
+ @type bytes
+ @param negativeCacheDuration time in seconds the entry should remain
+ in the cache
+ @type int or float
+ """
+ queryStr = """
+ UPDATE hash_prefix
+ SET negative_expires_at=datetime(current_timestamp, '+{0} SECONDS')
+ WHERE value=? AND threat_type=? AND platform_type=? AND
+ threat_entry_type=?
+ """.format(int(negativeCacheDuration))
+
+ db = QSqlDatabase.database(self.__connectionName)
+ if db.isOpen():
+ db.transaction()
+ try:
+ query = QSqlQuery(db)
+ query.prepare(queryStr)
+ query.addBindValue(hashPrefix, QSql.In | QSql.Binary)
+ query.addBindValue(threatList.threatType)
+ query.addBindValue(threatList.platformType)
+ query.addBindValue(threatList.threatEntryType)
+ query.exec_()
+ del query
+ finally:
+ db.commit()
+
+ def getThreatLists(self):
+ """
+ Public method to get the available threat lists.
+
+ @return list of available threat lists
+ @rtype list of tuples of (ThreatList, str)
+ """
+ queryStr = """
+ SELECT threat_type,platform_type,threat_entry_type,client_state
+ FROM threat_list
+ """
+ output = []
+
+ db = QSqlDatabase.database(self.__connectionName)
+ if db.isOpen():
+ db.transaction()
+ try:
+ query = QSqlQuery(db)
+ query.prepare(queryStr)
+
+ query.exec_()
+
+ while query.next():
+ threatType = query.value(0)
+ platformType = query.value(1)
+ threatEntryType = query.value(2)
+ clientState = query.value(3)
+ threatList = ThreatList(threatType, platformType,
+ threatEntryType)
+ output.append((threatList, clientState))
+ del query
+ finally:
+ db.commit()
+
+ return output
+
+ def addThreatList(self, threatList):
+ """
+ Public method to add a threat list to the cache.
+
+ @param threatList threat list to be added
+ @type ThreatList
+ """
+ queryStr = """
+ INSERT OR IGNORE INTO threat_list
+ (threat_type, platform_type, threat_entry_type, timestamp)
+ VALUES (?, ?, ?, current_timestamp)
+ """
+
+ 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 deleteThreatList(self, threatList):
+ """
+ Public method to delete a threat list from the cache.
+
+ @param threatlist threat list to be deleted
+ @type ThreatList
+ """
+ queryStr = """
+ DELETE FROM threat_list
+ 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 updateThreatListClientState(self, threatList, clientState):
+ """
+ Public method to update the client state of a threat list.
+
+ @param threatList threat list to update the client state for
+ @type ThreatList
+ @param clientState new client state
+ @type str
+ """
+ queryStr = """
+ UPDATE threat_list SET timestamp=current_timestamp, client_state=?
+ WHERE threat_type=? AND platform_type=? AND threat_entry_type=?
"""
db = QSqlDatabase.database(self.__connectionName)
@@ -403,8 +555,170 @@
db.transaction()
try:
query = QSqlQuery(db)
- query.prepare(queryStr.format(int(keepExpiredFor)))
+ query.prepare(queryStr)
+ query.addBindValue(clientState)
+ query.addBindValue(threatList.threatType)
+ query.addBindValue(threatList.platformType)
+ query.addBindValue(threatList.threatEntryType)
query.exec_()
del query
finally:
db.commit()
+
+ def hashPrefixListChecksum(self, threatList):
+ """
+ Public method to calculate the SHA256 checksum for an alphabetically
+ sorted concatenated list of hash prefixes.
+
+ @param threatList threat list to calculate checksum for
+ @type ThreatList
+ @return SHA256 checksum
+ @rtype bytes
+ """
+ queryStr = """
+ SELECT value FROM hash_prefix
+ WHERE threat_type=? AND platform_type=? AND threat_entry_type=?
+ ORDER BY value
+ """
+ checksum = None
+
+ db = QSqlDatabase.database(self.__connectionName)
+ if db.isOpen():
+ db.transaction()
+ allHashes = b""
+ try:
+ query = QSqlQuery(db)
+ query.prepare(queryStr)
+ query.addBindValue(threatList.threatType)
+ query.addBindValue(threatList.platformType)
+ query.addBindValue(threatList.threatEntryType)
+
+ query.exec_()
+
+ while query.next():
+ allHashes += bytes(query.value(0))
+ del query
+ finally:
+ db.commit()
+
+ checksum = hashlib.sha256(allHashes).digest()
+
+ return checksum
+
+ def populateHashPrefixList(self, threatList, prefixes):
+ """
+ Public method to populate the hash prefixes for a threat list.
+
+ @param threatList threat list of the hash prefixes
+ @type ThreatList
+ @param prefixes hash prefixes to be inserted
+ @type bytes
+ """
+ queryStr = """
+ INSERT INTO hash_prefix
+ (value, cue, threat_type, platform_type, threat_entry_type,
+ timestamp)
+ VALUES (?, ?, ?, ?, ?, current_timestamp)
+ """
+
+ db = QSqlDatabase.database(self.__connectionName)
+ if db.isOpen():
+ db.transaction()
+ try:
+ for prefix in prefixes:
+ query = QSqlQuery(db)
+ query.prepare(queryStr)
+ query.addBindValue(prefix, QSql.In | QSql.Binary)
+ query.addBindValue(toHex(prefix[:4]))
+ query.addBindValue(threatList.threatType)
+ query.addBindValue(threatList.platformType)
+ query.addBindValue(threatList.threatEntryType)
+ query.exec_()
+ del query
+ finally:
+ db.commit()
+
+ def getHashPrefixValuesToRemove(self, threatList, indexes):
+ """
+ Public method to get the hash prefix values to be removed from the
+ cache.
+
+ @param threatList threat list to remove prefixes from
+ @type ThreatList
+ @param indexes list of indexes of prefixes to be removed
+ @type list of int
+ @return list of hash prefixes to be removed
+ @rtype list of bytes
+ """
+ queryStr = """
+ SELECT value FROM hash_prefix
+ WHERE threat_type=? AND platform_type=? AND threat_entry_type=?
+ ORDER BY value
+ """
+ indexes = set(indexes)
+ output = []
+
+ 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_()
+
+ index = 0
+ while query.next():
+ if index in indexes:
+ prefix = bytes(query.value(0))
+ output.append(prefix)
+ index += 1
+ del query
+ finally:
+ db.commit()
+
+ return output
+
+ def removeHashPrefixIndices(self, threatList, indexes):
+ """
+ Public method to remove hash prefixes from the cache.
+
+ @param threatList threat list to delete hash prefixes of
+ @type ThreatList
+ @param indexes list of indexes of prefixes to be removed
+ @type list of int
+ """
+ queryStr = """
+ DELETE FROM hash_prefix
+ WHERE threat_type=? AND platform_type=? AND
+ threat_entry_type=? AND value IN ({0})
+ """
+ batchSize = 40
+
+ prefixesToRemove = self.getHashPrefixValuesToRemove(
+ threatList, indexes)
+ if prefixesToRemove:
+ db = QSqlDatabase.database(self.__connectionName)
+ if db.isOpen():
+ db.transaction()
+ try:
+ for index in range(0, len(prefixesToRemove), batchSize):
+ removeBatch = \
+ prefixesToRemove[index:(index + batchSize)]
+
+ query = QSqlQuery(db)
+ query.prepare(
+ queryStr.format(",".join(["?"] * len(removeBatch)))
+ )
+ query.addBindValue(threatList.threatType)
+ query.addBindValue(threatList.platformType)
+ query.addBindValue(threatList.threatEntryType)
+ for prefix in removeBatch:
+ query.addBindValue(prefix, QSql.In | QSql.Binary)
+ query.exec_()
+ del query
+ finally:
+ db.commit()
