wt-security / lib / DB.php

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

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