wt-security / lib / DB.php

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

393 lines 12.8 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	}
80
81	$scan_logs_table = self::add_prefix(self::WTOTEM_TABLE_SCAN_LOGS);
82	if ($wpdb->get_var("show tables like '$scan_logs_table'") != $scan_logs_table) {
83
84	$sql = "CREATE TABLE " . $scan_logs_table . " (
85	id bigint NOT NULL AUTO_INCREMENT,
86	created_at DATETIME NOT NULL,
87	scan_source tinytext,
88	data_type tinytext,
89	source tinytext,
90	content text,
91	is_internal boolean,
92	UNIQUE KEY id (id)
93	)
94	DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
95
96	dbDelta($sql);
97	}
98
99	$dbname = $wpdb->dbname;
100	$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'");
101
102	if (empty($is_had_col)) {
103	$add_status_column = "ALTER TABLE `{$scan_logs_table}` ADD `is_internal` VARCHAR(50) NULL DEFAULT NULL AFTER `content`; ";
104	$wpdb->query($add_status_column);
105	}
106
107	$confidential_files_table = self::add_prefix(self::WTOTEM_TABLE_CONFIDENTIAL_FILES);
108	if ($wpdb->get_var("show tables like '$confidential_files_table'") != $confidential_files_table) {
109
110	$sql = "CREATE TABLE " . $confidential_files_table . " (
111	id bigint NOT NULL AUTO_INCREMENT,
112	created_at DATETIME NOT NULL,
113	path text,
114	name text,
115	size tinytext,
116	modified_at text,
117	url text,
118	UNIQUE KEY id (id)
119	)
120	DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
121
122	dbDelta($sql);
123	}
124
125
126
127	$cve_list_table = self::add_prefix(self::WTOTEM_TABLE_CVE_LIST);
128	if ($wpdb->get_var("show tables like '$cve_list_table'") != $cve_list_table) {
129
130	$sql = "CREATE TABLE " . $cve_list_table . " (
131	id bigint NOT NULL AUTO_INCREMENT,
132	cve_id tinytext NOT NULL,
133	plugin_name tinytext NOT NULL,
134	plugin_version tinytext,
135	slug tinytext,
136	new_version tinytext,
137	cve_data text,
138	UNIQUE KEY id (id)
139	)
140	DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
141
142	dbDelta($sql);
143	}
144
145
146	return true;
147	}
148
149	/**
150	* Add (or update) data to the table.
151	*/
152	public static function setData($options, $table, $where = false)
153	{
154	global $wpdb;
155	$table_name = self::getTable($table);
156
157	if ($wpdb->get_var("show tables like '$table_name'") == $table_name) {
158	if ($where && $current = self::getData($where, $table)) {
159	$options['id'] = $current['id'];
160	}
161
162	$wpdb->replace($table_name, $options);
163	}
164	}
165
166	/**
167	* Delete data from the table.
168	*/
169	public static function deleteData($params, $table)
170	{
171	global $wpdb;
172
173	$table_name = self::getTable($table);
174	if ($params) {
175	$wpdb->delete($table_name, $params);
176	} else {
177	$wpdb->query("DELETE FROM " . $table_name);
178	$wpdb->query("UPDATE " . $table_name . " SET id = 0");
179	$wpdb->query("ALTER TABLE " . $table_name . " AUTO_INCREMENT =0;");
180	}
181	}
182
183	/**
184	* Getting values from the table.
185	*
186	* @param array $options
187	* Option name.
188	*
189	* @return array
190	*/
191	public static function getData($options, $table)
192	{
193	global $wpdb;
194	$table_name = self::getTable($table);
195	$where = '';
196
197	if ($options) {
198	$where = [];
199	foreach ($options as $key => $value) {
200	$where[] = $key . " = '" . $value . "'";
201	}
202	$where = 'WHERE ' . implode(' AND ', $where);
203	}
204
205	$_options = [];
206	if ($wpdb->get_var("show tables like '$table_name'") == $table_name) {
207	$_options = $wpdb->get_row("SELECT * FROM $table_name $where");
208	}
209
210	return (array)$_options ?: [];
211	}
212
213	/**
214	* Check availability.
215	*/
216	public static function checkAvailability($table, $values, $field)
217	{
218	global $wpdb;
219	$table_name = self::getTable($table);
220	$result = [];
221
222	if ($wpdb->get_var("show tables like '$table_name'") == $table_name) {
223	foreach ($values as $value) {
224	$is_exists = $wpdb->get_row("SELECT COUNT(*) as count FROM $table_name WHERE $field = '$value'");
225	if ($is_exists->count) {
226	$result[$value] = __($value, 'wtotem');
227	}
228	}
229	}
230	return $result;
231	}
232
233	/**
234	* Getting rows from the table.
235	*
236	* @param string $table
237	* Table name.
238	* @param string $columns
239	* Columns.
240	* @param string $values
241	* Values.
242	*/
243	public static function setRows($table, $columns, $values)
244	{
245	global $wpdb;
246	$table_name = self::getTable($table);
247
248	if ($wpdb->get_var("show tables like '$table_name'") != $table_name) {
249	WebTotemDB::install();
250	}
251
252	$wpdb->query("INSERT INTO " . $table_name . " " . $columns . " VALUES " . $values);
253	}
254
255	/**
256	* Getting rows from the table.
257	*
258	* @param array $options
259	* Option name.
260	*
261	* @return array
262	*/
263	public static function getRows($options, $table, $group_by = false, $pagination = ['limit' => 10, 'page' => 1], $sort = ['order_by' => 'id', 'direction' => 'DESC'])
264	{
265	global $wpdb;
266	$table_name = self::getTable($table);
267
268	if ($wpdb->get_var("show tables like '$table_name'") != $table_name) {
269	WebTotemDB::install();
270	}
271
272	if ($wpdb->get_var("show tables like '$table_name'") == $table_name) {
273	$where = '';
274	if ($options) {
275	if ($options[0] == 'AND' or $options[0] == 'OR') {
276	$where = [];
277	foreach ($options[1] as $key => $value) {
278	if (is_array($value)) {
279	foreach ($value as $val) {
280	$where[] = $key . " = '" . $val . "'";
281	}
282	} else {
283	$where[] = $key . " = '" . $value . "'";
284	}
285	}
286	$where = 'WHERE ' . implode(' ' . $options[0] . ' ', $where);
287	}
288	if ($options[0] == 'LIKE') {
289	$where = [];
290	foreach ($options[1] as $key => $value) {
291	$where[] = $key . " LIKE '" . $value . "'";
292	}
293	$where = 'WHERE ' . implode(' OR ', $where);
294	}
295	}
296
297	$_pagination = $pagination == 'all' ? '' : 'LIMIT ' . $pagination['limit'] . ' OFFSET ' . $pagination['limit'] * ($pagination['page'] - 1);
298	$_sort = 'ORDER BY `' . $sort['order_by'] . '` ' . $sort['direction'];
299
300	$_group_by = $group_by ? 'GROUP BY ' . $group_by : '';
301
302	$result['data'] = WebTotem::convertObjectToArray($wpdb->get_results("SELECT * FROM $table_name $where $_group_by $_sort $_pagination"));
303
304	if ($pagination != 'all') {
305	if ($group_by) {
306	$count = $wpdb->get_results("SELECT COUNT(DISTINCT $group_by) as count FROM $table_name $where");
307	} else {
308	$count = $wpdb->get_results("SELECT COUNT(*) as count FROM $table_name $where");
309	}
310	}
311
312	$result['count'] = !empty($count) ? $count[0]->count : 0;
313
314	if ($table == 'audit_logs') {
315
316	// Set viewed mark.
317	$ids = implode(",", array_column($result['data'], 'id'));
318	if ($ids) $wpdb->query("UPDATE $table_name SET viewed = 1 WHERE id in ($ids)");
319
320	// Get dates count
321	$created_at = array_column($result['data'], 'created_at');
322	$dates = [];
323	foreach ($created_at as $value) {
324	$dates[] = date_i18n('Y-m-d', strtotime($value));
325	}
326	$dates = array_unique($dates);
327	foreach ($dates as $date) {
328	$count = $wpdb->get_results("SELECT COUNT(*) as count FROM $table_name WHERE created_at BETWEEN '$date 00:00:00' AND '$date 23:59:59'");
329	$dates_count[date_i18n('M j, Y', strtotime($date))] = $count[0]->count;
330	}
331	$result['dates_count'] = $dates_count ?? [];
332	}
333	}
334	return $result ?? ['data' => [], 'count' => 0];
335	}
336
337	/**
338	* Deleting wtotem tables.
339	*/
340	public static function uninstall()
341	{
342	$tables = [
343	self::WTOTEM_TABLE_SETTINGS,
344	self::WTOTEM_TABLE_BLOCKED_LIST,
345	self::WTOTEM_TABLE_AUDIT_LOGS,
346	self::WTOTEM_TABLE_SCAN_LOGS,
347	self::WTOTEM_TABLE_CONFIDENTIAL_FILES,
348	self::WTOTEM_TABLE_CVE_LIST,
349	];
350	foreach ($tables as $table) {
351	global $wpdb;
352	$wpdb->query('DROP TABLE IF EXISTS `' . self::add_prefix($table) . '`');
353	}
354	}
355
356	/**
357	* Returns the table with the site prefix added.
358	*
359	* @param string $table
360	* Table name.
361	* @return string
362	*/
363	public static function add_prefix($table)
364	{
365	global $wpdb;
366	return $wpdb->base_prefix . $table;
367	}
368
369	/**
370	* Get table name.
371	*/
372	private static function getTable($name)
373	{
374	switch ($name) {
375	case 'settings':
376	return self::add_prefix(self::WTOTEM_TABLE_SETTINGS);
377	case 'blocked_list':
378	return self::add_prefix(self::WTOTEM_TABLE_BLOCKED_LIST);
379	case 'audit_logs':
380	return self::add_prefix(self::WTOTEM_TABLE_AUDIT_LOGS);
381	case 'scan_logs':
382	return self::add_prefix(self::WTOTEM_TABLE_SCAN_LOGS);
383	case 'confidential_files':
384	return self::add_prefix(self::WTOTEM_TABLE_CONFIDENTIAL_FILES);
385	case 'plugins_cve_list':
386	return self::add_prefix(self::WTOTEM_TABLE_CVE_LIST);
387
388	}
389
390	throw new \OutOfBoundsException('Unknown key: ' . $name);
391	}
392
393	}