wt-security / lib / DB.php

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

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