wt-security / lib / DB.php

DB.php in WebTotem Security 3.0.0, at lib/DB.php

408 lines 13.3 KB

Raw Download Zip

1	<?php
2	if (!defined('WEBTOTEM_INIT') \|\| WEBTOTEM_INIT !== true) {
3	if (!headers_sent()) {
4	/* Report invalid access if possible. */
5	header('HTTP/1.1 403 Forbidden');
6	}
7	exit(1);
8	}
9
10	/**
11	* WebTotem Database class for Wordpress.
12	*/
13	class WebTotemDB
14	{
15
16	const WTOTEM_TABLE_SETTINGS = 'wtotem_settings';
17	const WTOTEM_TABLE_BLOCKED_LIST = 'wtotem_blocked_list';
18	const WTOTEM_TABLE_AUDIT_LOGS = 'wtotem_audit_logs';
19	const WTOTEM_TABLE_SCAN_LOGS = 'wtotem_scan_logs';
20	const WTOTEM_TABLE_CONFIDENTIAL_FILES = 'wtotem_confidential_files';
21	const WTOTEM_TABLE_CVE_LIST = 'wtotem_plugins_cve_list';
22
23	/**
24	* Creating a database with plugin settings.
25	*/
26	public static function install()
27	{
28	global $wpdb;
29
30	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
31
32	$settings_table = self::add_prefix(self::WTOTEM_TABLE_SETTINGS);
33	if ($wpdb->get_var("show tables like '$settings_table'") != $settings_table) {
34
35	$sql = "CREATE TABLE " . $settings_table . " (
36	id bigint NOT NULL AUTO_INCREMENT,
37	name tinytext NOT NULL,
38	value longtext,
39	UNIQUE KEY id (id)
40	)
41	DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
42
43	dbDelta($sql);
44	}
45
46	$blocked_list_table = self::add_prefix(self::WTOTEM_TABLE_BLOCKED_LIST);
47	if ($wpdb->get_var("show tables like '$blocked_list_table'") != $blocked_list_table) {
48
49	$sql = "CREATE TABLE " . $blocked_list_table . " (
50	id bigint NOT NULL AUTO_INCREMENT,
51	ip tinytext NOT NULL,
52	reason tinytext,
53	blockedTime tinytext,
54	UNIQUE KEY id (id)
55	)
56	DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
57
58	dbDelta($sql);
59	}
60
61	$audit_logs_table = self::add_prefix(self::WTOTEM_TABLE_AUDIT_LOGS);
62	if ($wpdb->get_var("show tables like '$audit_logs_table'") != $audit_logs_table) {
63
64	$sql = "CREATE TABLE " . $audit_logs_table . " (
65	id bigint NOT NULL AUTO_INCREMENT,
66	created_at DATETIME NOT NULL,
67	user_name tinytext,
68	status tinytext,
69	event tinytext,
70	title tinytext,
71	description text,
72	ip tinytext,
73	viewed tinytext,
74	UNIQUE KEY id (id)
75	)
76	DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
77
78	dbDelta($sql);
79	} else {
80	$current_collation = $wpdb->get_var("SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '$audit_logs_table'");
81
82	if ($current_collation != 'utf8_general_ci') {
83	$wpdb->query("ALTER TABLE $audit_logs_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci ");
84	}
85	}
86
87	$scan_logs_table = self::add_prefix(self::WTOTEM_TABLE_SCAN_LOGS);
88	if ($wpdb->get_var("show tables like '$scan_logs_table'") != $scan_logs_table) {
89
90	$sql = "CREATE TABLE " . $scan_logs_table . " (
91	id bigint NOT NULL AUTO_INCREMENT,
92	created_at DATETIME NOT NULL,
93	scan_source tinytext,
94	data_type tinytext,
95	source tinytext,
96	content text,
97	is_internal boolean,
98	UNIQUE KEY id (id)
99	)
100	DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
101
102	dbDelta($sql);
103	}
104
105	$dbname = $wpdb->dbname;
106	$is_had_col = $wpdb->get_results("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `table_name` = '{$scan_logs_table}' AND `TABLE_SCHEMA` = '{$dbname}' AND `COLUMN_NAME` = 'is_internal'");
107
108	if (empty($is_had_col)) {
109	$add_status_column = "ALTER TABLE `{$scan_logs_table}` ADD `is_internal` VARCHAR(50) NULL DEFAULT NULL AFTER `content`; ";
110	$wpdb->query($add_status_column);
111	}
112
113	$confidential_files_table = self::add_prefix(self::WTOTEM_TABLE_CONFIDENTIAL_FILES);
114	if ($wpdb->get_var("show tables like '$confidential_files_table'") != $confidential_files_table) {
115
116	$sql = "CREATE TABLE " . $confidential_files_table . " (
117	id bigint NOT NULL AUTO_INCREMENT,
118	created_at DATETIME NOT NULL,
119	path text,
120	name text,
121	size tinytext,
122	modified_at text,
123	url text,
124	UNIQUE KEY id (id)
125	)
126	DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
127
128	dbDelta($sql);
129	}
130
131
132
133	$cve_list_table = self::add_prefix(self::WTOTEM_TABLE_CVE_LIST);
134	if ($wpdb->get_var("show tables like '$cve_list_table'") != $cve_list_table) {
135
136	$sql = "CREATE TABLE " . $cve_list_table . " (
137	id bigint NOT NULL AUTO_INCREMENT,
138	cve_id tinytext NOT NULL,
139	plugin_name tinytext NOT NULL,
140	plugin_version tinytext,
141	slug tinytext,
142	new_version tinytext,
143	cve_data text,
144	UNIQUE KEY id (id)
145	)
146	DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
147
148	dbDelta($sql);
149	}
150
151
152	return true;
153	}
154
155	/**
156	* Add (or update) data to the table.
157	*/
158	public static function setData($options, $table, $where = false)
159	{
160	global $wpdb;
161	$table_name = self::getTable($table);
162
163	if ($wpdb->get_var("show tables like '$table_name'") == $table_name) {
164	if ($where && $current = self::getData($where, $table)) {
165	$options['id'] = $current['id'];
166	}
167
168	$wpdb->replace($table_name, $options);
169	}
170	}
171
172	/**
173	* Delete data from the table.
174	*/
175	public static function deleteData($params, $table)
176	{
177	global $wpdb;
178
179	$table_name = self::getTable($table);
180	if ($params) {
181	$wpdb->delete($table_name, $params);
182	} else {
183	$wpdb->query("DELETE FROM " . $table_name);
184	$wpdb->query("UPDATE " . $table_name . " SET id = 0");
185	$wpdb->query("ALTER TABLE " . $table_name . " AUTO_INCREMENT =0;");
186	}
187	}
188
189	/**
190	* Getting values from the table.
191	*
192	* @param array $options
193	* Option name.
194	*
195	* @return array
196	*/
197	public static function getData($options, $table)
198	{
199	global $wpdb;
200	$table_name = self::getTable($table);
201	$where = '';
202
203	if ($options) {
204	$where = [];
205	foreach ($options as $key => $value) {
206	$where[] = $key . " = '" . $value . "'";
207	}
208	$where = 'WHERE ' . implode(' AND ', $where);
209	}
210
211	$_options = [];
212	if ($wpdb->get_var("show tables like '$table_name'") == $table_name) {
213	$_options = $wpdb->get_row("SELECT * FROM $table_name $where");
214	}
215
216	return (array)$_options ?: [];
217	}
218
219	/**
220	* Check availability.
221	*/
222	public static function checkAvailability($table, $values, $field)
223	{
224	global $wpdb;
225	$table_name = self::getTable($table);
226	$result = [];
227
228	if ($wpdb->get_var("show tables like '$table_name'") == $table_name) {
229	foreach ($values as $value) {
230	$is_exists = $wpdb->get_row("SELECT COUNT(*) as count FROM $table_name WHERE $field = '$value'");
231	if ($is_exists->count) {
232	$result[$value] = __($value, 'wtotem');
233	}
234	}
235	}
236	return $result;
237	}
238
239	/**
240	* Getting rows from the table.
241	*
242	* @param string $table
243	* Table name.
244	* @param string $columns
245	* Columns.
246	* @param string $values
247	* Values.
248	*/
249	public static function setRows($table, $columns, $values)
250	{
251	global $wpdb;
252	$table_name = self::getTable($table);
253
254	if ($wpdb->get_var("show tables like '$table_name'") != $table_name) {
255	WebTotemDB::install();
256	}
257
258	$wpdb->query("INSERT INTO " . $table_name . " " . $columns . " VALUES " . $values);
259	}
260
261	/**
262	* Getting rows from the table.
263	*
264	* @param array $options
265	* Option name.
266	*
267	* @return array
268	*/
269	public static function getRows($options, $table, $group_by = false, $pagination = ['limit' => 10, 'page' => 1], $sort = ['order_by' => 'id', 'direction' => 'DESC'])
270	{
271	global $wpdb;
272	$table_name = self::getTable($table);
273
274	if ($wpdb->get_var("show tables like '$table_name'") != $table_name) {
275	WebTotemDB::install();
276	}
277
278	if ($wpdb->get_var("show tables like '$table_name'") == $table_name) {
279	$where = '';
280	if ($options) {
281	if ($options[0] == 'AND' or $options[0] == 'OR') {
282	$where = [];
283	foreach ($options[1] as $key => $value) {
284	if (is_array($value)) {
285	foreach ($value as $val) {
286	$where[] = $key . " = '" . $val . "'";
287	}
288	} else {
289	$where[] = $key . " = '" . $value . "'";
290	}
291	}
292	$where = 'WHERE ' . implode(' ' . $options[0] . ' ', $where);
293	}
294	if ($options[0] == 'LIKE') {
295	$where = [];
296	foreach ($options[1] as $key => $value) {
297	$where[] = $key . " LIKE '" . $value . "'";
298	}
299	$where = 'WHERE ' . implode(' OR ', $where);
300	}
301	}
302
303	$_pagination = $pagination == 'all' ? '' : 'LIMIT ' . $pagination['limit'] . ' OFFSET ' . $pagination['limit'] * ($pagination['page'] - 1);
304	$_sort = 'ORDER BY `' . $sort['order_by'] . '` ' . $sort['direction'];
305
306	$_group_by = $group_by ? 'GROUP BY ' . $group_by : '';
307
308	$result['data'] = WebTotem::convertObjectToArray($wpdb->get_results("SELECT * FROM $table_name $where $_group_by $_sort $_pagination"));
309
310	if ($pagination != 'all') {
311	if ($group_by) {
312	$count = $wpdb->get_results("SELECT COUNT(DISTINCT $group_by) as count FROM $table_name $where");
313	} else {
314	$count = $wpdb->get_results("SELECT COUNT(*) as count FROM $table_name $where");
315	}
316	}
317
318	$result['count'] = !empty($count) ? $count[0]->count : 0;
319
320	if ($table == 'audit_logs') {
321
322	// Set viewed mark.
323	$ids = implode(",", array_column($result['data'], 'id'));
324	if ($ids) $wpdb->query("UPDATE $table_name SET viewed = 1 WHERE id in ($ids)");
325
326	// Get dates count
327	$created_at = array_column($result['data'], 'created_at');
328	$dates = [];
329	foreach ($created_at as $value) {
330	$dates[] = date_i18n('Y-m-d', strtotime($value));
331	}
332	$dates = array_unique($dates);
333	foreach ($dates as $date) {
334	$count = $wpdb->get_results("SELECT COUNT(*) as count FROM $table_name WHERE created_at BETWEEN '$date 00:00:00' AND '$date 23:59:59'");
335	$dates_count[date_i18n('M j, Y', strtotime($date))] = $count[0]->count;
336	}
337	$result['dates_count'] = $dates_count ?? [];
338	}
339	}
340	return $result ?? ['data' => [], 'count' => 0];
341	}
342
343	/**
344	* Deleting wtotem tables.
345	*/
346	public static function uninstall()
347	{
348	$tables = [
349	self::WTOTEM_TABLE_SETTINGS,
350	self::WTOTEM_TABLE_BLOCKED_LIST,
351	self::WTOTEM_TABLE_AUDIT_LOGS,
352	self::WTOTEM_TABLE_SCAN_LOGS,
353	self::WTOTEM_TABLE_CONFIDENTIAL_FILES,
354	self::WTOTEM_TABLE_CVE_LIST,
355	];
356	foreach ($tables as $table) {
357	global $wpdb;
358	$wpdb->query('DROP TABLE IF EXISTS `' . self::add_prefix($table) . '`');
359	}
360	}
361
362	/**
363	* Deleting wtotem tables.
364	*/
365	public static function clearSettings()
366	{
367	global $wpdb;
368	$wpdb->query('TRUNCATE TABLE `' . self::add_prefix(self::WTOTEM_TABLE_SETTINGS) . '`');
369	}
370
371	/**
372	* Returns the table with the site prefix added.
373	*
374	* @param string $table
375	* Table name.
376	* @return string
377	*/
378	public static function add_prefix($table)
379	{
380	global $wpdb;
381	return $wpdb->base_prefix . $table;
382	}
383
384	/**
385	* Get table name.
386	*/
387	private static function getTable($name)
388	{
389	switch ($name) {
390	case 'settings':
391	return self::add_prefix(self::WTOTEM_TABLE_SETTINGS);
392	case 'blocked_list':
393	return self::add_prefix(self::WTOTEM_TABLE_BLOCKED_LIST);
394	case 'audit_logs':
395	return self::add_prefix(self::WTOTEM_TABLE_AUDIT_LOGS);
396	case 'scan_logs':
397	return self::add_prefix(self::WTOTEM_TABLE_SCAN_LOGS);
398	case 'confidential_files':
399	return self::add_prefix(self::WTOTEM_TABLE_CONFIDENTIAL_FILES);
400	case 'plugins_cve_list':
401	return self::add_prefix(self::WTOTEM_TABLE_CVE_LIST);
402
403	}
404
405	throw new \OutOfBoundsException('Unknown key: ' . $name);
406	}
407
408	}