From 65f62cadf687ce77c98a46f85f1a6d05af99d98a Mon Sep 17 00:00:00 2001
From: Alexander Ebert <ebert@woltlab.com>
Date: Fri, 8 Mar 2019 12:43:29 +0100
Subject: [PATCH] Stores hashes as binary data to save space

---
 .../blacklist/entry/BlacklistEntry.class.php     | 16 ++++++++++------
 .../entry/BlacklistEntryAction.class.php         |  2 +-
 wcfsetup/setup/db/install.sql                    |  5 +++--
 3 files changed, 14 insertions(+), 9 deletions(-)

diff --git a/wcfsetup/install/files/lib/data/blacklist/entry/BlacklistEntry.class.php b/wcfsetup/install/files/lib/data/blacklist/entry/BlacklistEntry.class.php
index 468f1a319c..d005b20b6c 100644
--- a/wcfsetup/install/files/lib/data/blacklist/entry/BlacklistEntry.class.php
+++ b/wcfsetup/install/files/lib/data/blacklist/entry/BlacklistEntry.class.php
@@ -33,24 +33,24 @@ class BlacklistEntry extends DatabaseObject {
 		
 		$conditions = new PreparedStatementConditionBuilder(true, 'OR');
 		if (BLACKLIST_SFS_USERNAME) {
-			$conditions->add('(type = ? AND hash = ?)', ['username', hash('sha256', $username)]);
+			$conditions->add('(type = ? AND hash = ?)', ['username', self::getHash($username)]);
 		}
 		if (BLACKLIST_SFS_EMAIL_ADDRESS) {
-			$conditions->add('(type = ? AND hash = ?)', ['email', hash('sha256', $email)]);
+			$conditions->add('(type = ? AND hash = ?)', ['email', self::getHash($email)]);
 		}
 		if (BLACKLIST_SFS_IP_ADDRESS) {
 			UserUtil::convertIPv6To4($ipAddress);
 			if ($ipAddress) {
 				if (filter_var($ipAddress, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4) !== false) {
-					$conditions->add('(type = ? AND hash = ?)', ['ipv4', hash('sha256', $ipAddress)]);
+					$conditions->add('(type = ? AND hash = ?)', ['ipv4', self::getHash($ipAddress)]);
 				}
 				else {
 					$parts = explode(':', $ipAddress);
 					
 					// StopForumSpam uses the first two to four segments of an IPv6 address.
-					$ipv6TwoParts = hash('sha256', "{$parts[0]}:{$parts[1]}::");
-					$ipv6ThreeParts = hash('sha256', "{$parts[0]}:{$parts[1]}:{$parts[2]}::");
-					$ipv6FourParts = hash('sha256', "{$parts[0]}:{$parts[1]}:{$parts[2]}:{$parts[3]}::");
+					$ipv6TwoParts = self::getHash("{$parts[0]}:{$parts[1]}::");
+					$ipv6ThreeParts = self::getHash("{$parts[0]}:{$parts[1]}:{$parts[2]}::");
+					$ipv6FourParts = self::getHash("{$parts[0]}:{$parts[1]}:{$parts[2]}:{$parts[3]}::");
 					
 					$conditions->add('(type = ? AND hash IN (?))', ['ipv6', [$ipv6TwoParts, $ipv6ThreeParts, $ipv6FourParts]]);
 				}
@@ -71,6 +71,10 @@ class BlacklistEntry extends DatabaseObject {
 		return false;
 	}
 	
+	protected static function getHash($string) {
+		return hex2bin(hash('sha256', $string));
+	}
+	
 	protected static function isMatch($type, $occurrences) {
 		$setting = [
 			'email' => BLACKLIST_SFS_EMAIL_ADDRESS,
diff --git a/wcfsetup/install/files/lib/data/blacklist/entry/BlacklistEntryAction.class.php b/wcfsetup/install/files/lib/data/blacklist/entry/BlacklistEntryAction.class.php
index 3f352135c2..e17e843e94 100644
--- a/wcfsetup/install/files/lib/data/blacklist/entry/BlacklistEntryAction.class.php
+++ b/wcfsetup/install/files/lib/data/blacklist/entry/BlacklistEntryAction.class.php
@@ -75,7 +75,7 @@ class BlacklistEntryAction extends AbstractDatabaseObjectAction {
 					foreach ($data[$type] as $hash => $occurrences) {
 						$statement->execute([
 							$type,
-							$hash,
+							hex2bin($hash),
 							$lastSeen,
 							min($occurrences, 32767),
 						]);
diff --git a/wcfsetup/setup/db/install.sql b/wcfsetup/setup/db/install.sql
index 645dc3b79d..b33f66e96c 100644
--- a/wcfsetup/setup/db/install.sql
+++ b/wcfsetup/setup/db/install.sql
@@ -302,11 +302,12 @@ CREATE TABLE wcf1_blacklist_status (
 DROP TABLE IF EXISTS wcf1_blacklist_entry;
 CREATE TABLE wcf1_blacklist_entry (
 	type ENUM('email', 'ipv4','ipv6','username'),
-	hash CHAR(64),
+	hash BINARY(32),
 	lastSeen DATETIME NOT NULL,
 	occurrences SMALLINT(5) NOT NULL,
 	
-	UNIQUE KEY entry (type, hash)
+	UNIQUE KEY entry (type, hash),
+	KEY numberOfReports (type, occurrences)
 );
 
 DROP TABLE IF EXISTS wcf1_box;
-- 
2.20.1