wt-security / lib / DB.php

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

326 lines 10.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
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	}
146	}
147
148	/**
149	* Getting values from the table.
150	*
151	* @param array $options
152	* Option name.
153	*
154	* @return array
155	*/
156	public static function getData ($options, $table) {
157	global $wpdb;
158	$table_name = self::getTable($table);
159	$where = '';
160
161	if($options){
162	$where = [];
163	foreach ($options as $key => $value){
164	$where[] = $key . " = '" . $value . "'";
165	}
166	$where = 'WHERE ' . implode(' AND ', $where);
167	}
168
169	$_options = [];
170	if($wpdb->get_var("show tables like '$table_name'") == $table_name) {
171	$_options = $wpdb->get_row("SELECT * FROM $table_name $where");
172	}
173
174	return (array) $_options ?: [];
175	}
176
177	/**
178	* Check availability.
179	*/
180	public static function checkAvailability ($table, $values, $field) {
181	global $wpdb;
182	$table_name = self::getTable($table);
183	$result = [];
184
185	if($wpdb->get_var("show tables like '$table_name'") == $table_name) {
186	foreach ($values as $value){
187	$is_exists = $wpdb->get_row( "SELECT COUNT(*) as count FROM $table_name WHERE $field = '$value'" );
188	if($is_exists->count){
189	$result[$value] = __($value, 'wtotem');
190	}
191	}
192	}
193	return $result;
194	}
195
196	/**
197	* Getting rows from the table.
198	*
199	* @param array $options
200	* Option name.
201	*
202	* @return array
203	*/
204	public static function getRows ($options, $table, $group_by = false, $pagination = ['limit' => 10, 'page' => 1], $sort = ['order_by' => 'id', 'direction' => 'DESC']) {
205	global $wpdb;
206	$table_name = self::getTable($table);
207
208	if($wpdb->get_var("show tables like '$table_name'") != $table_name) {
209	WebTotemDB::install();
210	}
211
212	if($wpdb->get_var("show tables like '$table_name'") == $table_name) {
213	$where = '';
214	if($options){
215	if($options[0] == 'AND' or $options[0] == 'OR'){
216	$where = [];
217	foreach ($options[1] as $key => $value){
218	if(is_array($value)){
219	foreach ($value as $val){
220	$where[] = $key . " = '" . $val . "'";
221	}
222	} else {
223	$where[] = $key . " = '" . $value . "'";
224	}
225	}
226	$where = 'WHERE ' . implode(' '.$options[0].' ', $where);
227	}
228	if($options[0] == 'LIKE'){
229	$where = [];
230	foreach ($options[1] as $key => $value){
231	$where[] = $key . " LIKE '" . $value . "'";
232	}
233	$where = 'WHERE ' . implode(' OR ', $where);
234	}
235	}
236
237	$_pagination = $pagination == 'all' ? '' : 'LIMIT '. $pagination['limit'] .' OFFSET ' . $pagination['limit'] * ($pagination['page'] - 1);
238	$_sort = 'ORDER BY `' . $sort['order_by'] . '` ' . $sort['direction'];
239
240	$_group_by = $group_by ? 'GROUP BY ' . $group_by : '';
241
242	$result['data'] = WebTotem::convertObjectToArray( $wpdb->get_results( "SELECT * FROM $table_name $where $_group_by $_sort $_pagination" ) );
243
244	if($pagination != 'all'){
245	if($group_by){
246	$count = $wpdb->get_results( "SELECT COUNT(DISTINCT $group_by) as count FROM $table_name $where" );
247	} else {
248	$count = $wpdb->get_results( "SELECT COUNT(*) as count FROM $table_name $where" );
249	}
250	}
251
252	$result['count'] = !empty($count) ? $count[0]->count : 0;
253
254	if($table == 'audit_logs'){
255
256	// Set viewed mark.
257	$ids = implode(",", array_column($result['data'], 'id'));
258	if( $ids ) $wpdb->query( "UPDATE $table_name SET viewed = 1 WHERE id in ($ids)" );
259
260	// Get dates count
261	$created_at = array_column($result['data'], 'created_at');
262	$dates = [];
263	foreach ($created_at as $value){
264	$dates[] = date_i18n('Y-m-d', strtotime($value));
265	}
266	$dates = array_unique($dates);
267	foreach ($dates as $date){
268	$count = $wpdb->get_results( "SELECT COUNT(*) as count FROM $table_name WHERE created_at BETWEEN '$date 00:00:00' AND '$date 23:59:59'" );
269	$dates_count[date_i18n('M j, Y', strtotime($date))] = $count[0]->count;
270	}
271	$result['dates_count'] = $dates_count ?? [];
272	}
273	}
274	return $result ?? ['data' => [], 'count' => 0];
275	}
276
277	/**
278	* Deleting wtotem tables.
279	*/
280	public static function uninstall() {
281	$tables = [
282	self::WTOTEM_TABLE_SETTINGS,
283	self::WTOTEM_TABLE_BLOCKED_LIST,
284	self::WTOTEM_TABLE_AUDIT_LOGS,
285	self::WTOTEM_TABLE_SCAN_LOGS,
286	self::WTOTEM_TABLE_CONFIDENTIAL_FILES,
287	];
288	foreach ($tables as $table) {
289	global $wpdb;
290	$wpdb->query('DROP TABLE IF EXISTS `' . self::add_prefix($table) . '`');
291	}
292	}
293
294	/**
295	* Returns the table with the site prefix added.
296	*
297	* @param string $table
298	* Table name.
299	* @return string
300	*/
301	public static function add_prefix($table) {
302	global $wpdb;
303	return $wpdb->base_prefix . $table;
304	}
305
306	/**
307	* Get table name.
308	*/
309	private static function getTable($name) {
310	switch ($name) {
311	case 'settings':
312	return self::add_prefix(self::WTOTEM_TABLE_SETTINGS);
313	case 'blocked_list':
314	return self::add_prefix(self::WTOTEM_TABLE_BLOCKED_LIST);
315	case 'audit_logs':
316	return self::add_prefix(self::WTOTEM_TABLE_AUDIT_LOGS);
317	case 'scan_logs':
318	return self::add_prefix(self::WTOTEM_TABLE_SCAN_LOGS);
319	case 'confidential_files':
320	return self::add_prefix(self::WTOTEM_TABLE_CONFIDENTIAL_FILES);
321	}
322
323	throw new \OutOfBoundsException('Unknown key: ' . $name);
324	}
325
326	}