Continued implementing the SafeBrowsingCache class. safe_browsing

Tue, 25 Jul 2017 19:22:36 +0200

author
Detlev Offenbach <detlev@die-offenbachs.de>
date
Tue, 25 Jul 2017 19:22:36 +0200
branch
safe_browsing
changeset 5818
cae9956be67e
parent 5817
a5f6c9128500
child 5819
69fa45e95673

Continued implementing the SafeBrowsingCache class.

WebBrowser/SafeBrowsing/SafeBrowsingCache.py file | annotate | diff | comparison | revisions
--- 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()

eric ide

mercurial