wfSchema.php — Wordfence Security – Firewall, Malware Scan, and Login Security 7.2.4

wordfence / lib / wfSchema.php

wordfence / lib Last commit date

wfSchema.php

317 lines

1	<?php
2	require_once('wfDB.php');
3	class wfSchema {
4	const TABLE_CASE_OPTION = 'wordfence_case'; //false is camel case, true is lower
5
6	private static $_usingLowercase = null;
7	private static $deprecatedTables = array(
8	'wfBlocks',
9	'wfBlocksAdv',
10	'wfLockedOut',
11	'wfThrottleLog',
12	'wfNet404s',
13	'wfBlockedCommentLog',
14	'wfVulnScanners',
15	'wfBadLeechers',
16	'wfLeechers',
17	'wfScanners',
18	);
19
20	private static $tables = array(
21	"wfBlocks7" => "(
22	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
23	`type` int(10) unsigned NOT NULL DEFAULT '0',
24	`IP` binary(16) NOT NULL DEFAULT '\\\0\\\0\\\0\\\0\\\0\\\0\\\0\\\0\\\0\\\0\\\0\\\0\\\0\\\0\\\0\\\0',
25	`blockedTime` bigint(20) NOT NULL,
26	`reason` varchar(255) NOT NULL,
27	`lastAttempt` int(10) unsigned DEFAULT '0',
28	`blockedHits` int(10) unsigned DEFAULT '0',
29	`expiration` bigint(20) unsigned NOT NULL DEFAULT '0',
30	`parameters` text,
31	PRIMARY KEY (`id`),
32	KEY `type` (`type`),
33	KEY `IP` (`IP`),
34	KEY `expiration` (`expiration`)
35	) DEFAULT CHARSET=utf8",
36	"wfConfig" => "(
37	`name` varchar(100) NOT NULL,
38	`val` longblob,
39	`autoload` enum('no','yes') NOT NULL DEFAULT 'yes',
40	PRIMARY KEY (`name`)
41	) DEFAULT CHARSET=utf8",
42	"wfCrawlers" => "(
43	`IP` binary(16) NOT NULL DEFAULT '\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0',
44	`patternSig` binary(16) NOT NULL,
45	`status` char(8) NOT NULL,
46	`lastUpdate` int(10) unsigned NOT NULL,
47	`PTR` varchar(255) DEFAULT '',
48	PRIMARY KEY (`IP`,`patternSig`)
49	) DEFAULT CHARSET=utf8",
50	"wfFileChanges" => "(
51	`filenameHash` char(64) NOT NULL,
52	`file` varchar(1000) NOT NULL,
53	`md5` char(32) NOT NULL,
54	PRIMARY KEY (`filenameHash`)
55	) CHARSET=utf8",
56	"wfHits" => "(
57	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
58	`attackLogTime` double(17,6) unsigned NOT NULL,
59	`ctime` double(17,6) unsigned NOT NULL,
60	`IP` binary(16) DEFAULT NULL,
61	`jsRun` tinyint(4) DEFAULT '0',
62	`statusCode` int(11) NOT NULL DEFAULT '200',
63	`isGoogle` tinyint(4) NOT NULL,
64	`userID` int(10) unsigned NOT NULL,
65	`newVisit` tinyint(3) unsigned NOT NULL,
66	`URL` text,
67	`referer` text,
68	`UA` text,
69	`action` varchar(64) NOT NULL DEFAULT '',
70	`actionDescription` text,
71	`actionData` text,
72	PRIMARY KEY (`id`),
73	KEY `k1` (`ctime`),
74	KEY `k2` (`IP`,`ctime`),
75	KEY `attackLogTime` (`attackLogTime`)
76	) DEFAULT CHARSET=utf8",
77	"wfIssues" => "(
78	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
79	`time` int(10) unsigned NOT NULL,
80	`lastUpdated` int(10) unsigned NOT NULL,
81	`status` varchar(10) NOT NULL,
82	`type` varchar(20) NOT NULL,
83	`severity` tinyint(3) unsigned NOT NULL,
84	`ignoreP` char(32) NOT NULL,
85	`ignoreC` char(32) NOT NULL,
86	`shortMsg` varchar(255) NOT NULL,
87	`longMsg` text,
88	`data` text,
89	PRIMARY KEY (`id`),
90	KEY `lastUpdated` (`lastUpdated`),
91	KEY `status` (`status`),
92	KEY `ignoreP` (`ignoreP`),
93	KEY `ignoreC` (`ignoreC`)
94	) DEFAULT CHARSET=utf8",
95	"wfPendingIssues" => "(
96	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
97	`time` int(10) unsigned NOT NULL,
98	`lastUpdated` int(10) unsigned NOT NULL,
99	`status` varchar(10) NOT NULL,
100	`type` varchar(20) NOT NULL,
101	`severity` tinyint(3) unsigned NOT NULL,
102	`ignoreP` char(32) NOT NULL,
103	`ignoreC` char(32) NOT NULL,
104	`shortMsg` varchar(255) NOT NULL,
105	`longMsg` text,
106	`data` text,
107	PRIMARY KEY (`id`),
108	KEY `lastUpdated` (`lastUpdated`),
109	KEY `status` (`status`),
110	KEY `ignoreP` (`ignoreP`),
111	KEY `ignoreC` (`ignoreC`)
112	) DEFAULT CHARSET=utf8",
113	"wfTrafficRates" => "(
114	`eMin` int(10) unsigned NOT NULL,
115	`IP` binary(16) NOT NULL DEFAULT '\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0',
116	`hitType` enum('hit','404') NOT NULL DEFAULT 'hit',
117	`hits` int(10) unsigned NOT NULL,
118	PRIMARY KEY (`eMin`,`IP`,`hitType`)
119	) DEFAULT CHARSET=utf8",
120	"wfLocs" => "(
121	`IP` binary(16) NOT NULL DEFAULT '\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0',
122	`ctime` int(10) unsigned NOT NULL,
123	`failed` tinyint(3) unsigned NOT NULL,
124	`city` varchar(255) DEFAULT '',
125	`region` varchar(255) DEFAULT '',
126	`countryName` varchar(255) DEFAULT '',
127	`countryCode` char(2) DEFAULT '',
128	`lat` float(10,7) DEFAULT '0.0000000',
129	`lon` float(10,7) DEFAULT '0.0000000',
130	PRIMARY KEY (`IP`)
131	) DEFAULT CHARSET=utf8",
132	"wfLogins" => "(
133	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
134	`hitID` int(11) DEFAULT NULL,
135	`ctime` double(17,6) unsigned NOT NULL,
136	`fail` tinyint(3) unsigned NOT NULL,
137	`action` varchar(40) NOT NULL,
138	`username` varchar(255) NOT NULL,
139	`userID` int(10) unsigned NOT NULL,
140	`IP` binary(16) DEFAULT NULL,
141	`UA` text,
142	PRIMARY KEY (`id`),
143	KEY `k1` (`IP`,`fail`),
144	KEY `hitID` (`hitID`)
145	) DEFAULT CHARSET=utf8",
146	"wfReverseCache" => "(
147	`IP` binary(16) NOT NULL DEFAULT '\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0',
148	`host` varchar(255) NOT NULL,
149	`lastUpdate` int(10) unsigned NOT NULL,
150	PRIMARY KEY (`IP`)
151	) DEFAULT CHARSET=utf8",
152	"wfStatus" => "(
153	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
154	`ctime` double(17,6) unsigned NOT NULL,
155	`level` tinyint(3) unsigned NOT NULL,
156	`type` char(5) NOT NULL,
157	`msg` varchar(1000) NOT NULL,
158	PRIMARY KEY (`id`),
159	KEY `k1` (`ctime`),
160	KEY `k2` (`type`)
161	) DEFAULT CHARSET=utf8",
162	'wfHoover' => "(
163	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
164	`owner` text,
165	`host` text,
166	`path` text,
167	`hostKey` varbinary(124) DEFAULT NULL,
168	PRIMARY KEY (`id`),
169	KEY `k2` (`hostKey`)
170	) DEFAULT CHARSET=utf8",
171	'wfFileMods' => "(
172	`filenameMD5` binary(16) NOT NULL,
173	`filename` varchar(1000) NOT NULL,
174	`knownFile` tinyint(3) unsigned NOT NULL,
175	`oldMD5` binary(16) NOT NULL,
176	`newMD5` binary(16) NOT NULL,
177	`SHAC` binary(32) NOT NULL DEFAULT '\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0',
178	`stoppedOnSignature` varchar(255) NOT NULL DEFAULT '',
179	`stoppedOnPosition` int(10) unsigned NOT NULL DEFAULT '0',
180	`isSafeFile` varchar(1) NOT NULL DEFAULT '?',
181	PRIMARY KEY (`filenameMD5`)
182	) DEFAULT CHARSET=utf8",
183	'wfBlockedIPLog' => "(
184	`IP` binary(16) NOT NULL DEFAULT '\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0',
185	`countryCode` varchar(2) NOT NULL,
186	`blockCount` int(10) unsigned NOT NULL DEFAULT '0',
187	`unixday` int(10) unsigned NOT NULL,
188	`blockType` varchar(50) NOT NULL DEFAULT 'generic',
189	PRIMARY KEY (`IP`,`unixday`,`blockType`)
190	) DEFAULT CHARSET=utf8",
191	'wfSNIPCache' => "(
192	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
193	`IP` varchar(45) NOT NULL DEFAULT '',
194	`expiration` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
195	`body` varchar(255) NOT NULL DEFAULT '',
196	`count` int(10) unsigned NOT NULL DEFAULT '0',
197	`type` int(10) unsigned NOT NULL DEFAULT '0',
198	PRIMARY KEY (`id`),
199	KEY `expiration` (`expiration`),
200	KEY `IP` (`IP`),
201	KEY `type` (`type`)
202	) DEFAULT CHARSET=utf8",
203	'wfKnownFileList' => "(
204	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
205	`path` text NOT NULL,
206	PRIMARY KEY (`id`)
207	) DEFAULT CHARSET=utf8",
208	'wfNotifications' => "(
209	`id` varchar(32) NOT NULL DEFAULT '',
210	`new` tinyint(3) unsigned NOT NULL DEFAULT '1',
211	`category` varchar(255) NOT NULL,
212	`priority` int(11) NOT NULL DEFAULT '1000',
213	`ctime` int(10) unsigned NOT NULL,
214	`html` text NOT NULL,
215	`links` text NOT NULL,
216	PRIMARY KEY (`id`)
217	) DEFAULT CHARSET=utf8;",
218	'wfLiveTrafficHuman' => "(
219	`IP` binary(16) NOT NULL DEFAULT '\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0',
220	`identifier` binary(32) NOT NULL DEFAULT '\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0',
221	`expiration` int(10) unsigned NOT NULL,
222	PRIMARY KEY (`IP`,`identifier`),
223	KEY `expiration` (`expiration`)
224	) DEFAULT CHARSET=utf8;",
225	/*
226	'wfPerfLog' => "(
227	id int UNSIGNED NOT NULL auto_increment PRIMARY KEY,
228	IP int UNSIGNED NOT NULL,
229	userID int UNSIGNED NOT NULL,
230	UA varchar(1000) NOT NULL,
231	URL varchar(1000) NOT NULL,
232	ctime int UNSIGNED NOT NULL,
233	fetchStart int UNSIGNED NOT NULL,
234	domainLookupStart int UNSIGNED NOT NULL,
235	domainLookupEnd int UNSIGNED NOT NULL,
236	connectStart int UNSIGNED NOT NULL,
237	connectEnd int UNSIGNED NOT NULL,
238	requestStart int UNSIGNED NOT NULL,
239	responseStart int UNSIGNED NOT NULL,
240	responseEnd int UNSIGNED NOT NULL,
241	domReady int UNSIGNED NOT NULL,
242	loaded int UNSIGNED NOT NULL,
243	KEY k1(ctime)
244	) default charset=utf8"
245	*/
246	);
247	private $db = false;
248	public function __construct($dbhost = false, $dbuser = false, $dbpassword = false, $dbname = false){
249	$this->db = new wfDB();
250	}
251	public function dropAll(){
252	foreach(self::$tables as $table => $def) {
253	$originalTable = wfDB::networkPrefix() . $table;
254	$convertedTable = wfDB::networkPrefix() . strtolower($table);
255
256	$this->db->queryWrite("DROP TABLE IF EXISTS {$convertedTable}");
257	$this->db->queryWrite("DROP TABLE IF EXISTS {$originalTable}");
258	}
259
260	foreach (self::$deprecatedTables as $table) {
261	$originalTable = wfDB::networkTable($table, false);
262	$convertedTable = wfDB::networkTable($table);
263
264	$this->db->queryWrite("DROP TABLE IF EXISTS {$convertedTable}");
265	if ($originalTable !== $convertedTable) {
266	$this->db->queryWrite("DROP TABLE IF EXISTS {$originalTable}");
267	}
268	}
269	}
270	public function createAll() {
271	foreach(self::$tables as $table => $def){
272	$this->db->queryWrite("CREATE TABLE IF NOT EXISTS " . wfDB::networkTable($table) . " " . $def);
273	}
274	}
275	public function create($table) {
276	$this->db->queryWrite("CREATE TABLE IF NOT EXISTS " . wfDB::networkTable($table) . " " . self::$tables[$table]);
277	}
278	public function drop($table) {
279	$originalTable = wfDB::networkTable($table, false);
280	$convertedTable = wfDB::networkTable($table);
281
282	$this->db->queryWrite("DROP TABLE IF EXISTS {$convertedTable}");
283	if ($originalTable !== $convertedTable) {
284	$this->db->queryWrite("DROP TABLE IF EXISTS {$originalTable}");
285	}
286	}
287
288	public static function tableList() {
289	return array_keys(self::$tables);
290	}
291
292	public static function updateTableCase() {
293	global $wpdb;
294	$hasCamelCaseTable = !!$wpdb->get_var($wpdb->prepare('SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=%s', wfDB::networkTable('wfConfig', false)));
295	if (is_multisite() && function_exists('update_network_option')) {
296	update_network_option(null, self::TABLE_CASE_OPTION, !$hasCamelCaseTable);
297	self::$_usingLowercase = !$hasCamelCaseTable;
298	}
299	else {
300	update_option(self::TABLE_CASE_OPTION, !$hasCamelCaseTable);
301	self::$_usingLowercase = !$hasCamelCaseTable;
302	}
303	}
304
305	public static function usingLowercase() {
306	if (self::$_usingLowercase === null) {
307	if (is_multisite() && function_exists('update_network_option')) {
308	self::$_usingLowercase = !!get_network_option(null, self::TABLE_CASE_OPTION);
309	}
310	else {
311	self::$_usingLowercase = !!get_option(self::TABLE_CASE_OPTION);
312	}
313	}
314	return self::$_usingLowercase;
315	}
316	}
317