vigilante / includes / class-database.php

class-database.php in Vigilant – 100% Free Security Suite: Firewall, 2FA, Login, Headers, Scanner… 2.10.0, at includes/class-database.php

1,553 lines 56.1 KB

Raw Download Zip

1	<?php
2	/**
3	* Database Class
4	*
5	* Handles database operations for activity log and login attempts
6	*
7	* @package Vigilante
8	*/
9
10	// Prevent direct access
11	if ( ! defined( 'ABSPATH' ) ) {
12	exit;
13	}
14
15	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- All queries use $wpdb->prepare() with validated parameters
16
17	/**
18	* Class Vigilante_Database
19	*
20	* Manages custom database tables
21	*/
22	class Vigilante_Database {
23
24	/**
25	* Database version for migrations
26	*/
27	const DB_VERSION = '1.4.0';
28
29	/**
30	* Option name for storing DB version
31	*/
32	const DB_VERSION_OPTION = 'vigilante_db_version';
33
34	/**
35	* Activity log table name (without prefix)
36	*
37	* @var string
38	*/
39	private $activity_log_table = 'vigilante_activity_log';
40
41	/**
42	* Login attempts table name (without prefix)
43	*
44	* @var string
45	*/
46	private $login_attempts_table = 'vigilante_login_attempts';
47
48	/**
49	* File integrity table name (without prefix)
50	*
51	* @var string
52	*/
53	private $file_integrity_table = 'vigilante_file_integrity';
54
55	/**
56	* 2FA codes table name (without prefix)
57	*
58	* @var string
59	*/
60	private $two_factor_codes_table = 'vigilante_2fa_codes';
61
62	/**
63	* 2FA trusted devices table name (without prefix)
64	*
65	* @var string
66	*/
67	private $two_factor_devices_table = 'vigilante_2fa_trusted_devices';
68
69	/**
70	* 2FA notifications table name (without prefix)
71	*
72	* @var string
73	*/
74	private $two_factor_notifications_table = 'vigilante_2fa_notifications';
75
76	/**
77	* 2FA TOTP secrets table name (without prefix)
78	*
79	* @var string
80	*/
81	private $two_factor_totp_table = 'vigilante_2fa_totp';
82
83	/**
84	* WordPress database instance
85	*
86	* @var wpdb
87	*/
88	private $wpdb;
89
90	/**
91	* Constructor
92	*/
93	public function __construct() {
94	global $wpdb;
95	$this->wpdb = $wpdb;
96	}
97
98	/**
99	* Get full table name with prefix
100	*
101	* @param string $table Table name without prefix.
102	* @return string Full table name.
103	*/
104	public function get_table_name( $table ) {
105	return $this->wpdb->prefix . $table;
106	}
107
108	/**
109	* Get escaped table name for use in SQL queries
110	*
111	* @param string $table Full table name.
112	* @return string Escaped table name with backticks.
113	*/
114	private function esc_table( $table ) {
115	return '`' . esc_sql( $table ) . '`';
116	}
117
118	/**
119	* Get activity log table name
120	*
121	* @return string
122	*/
123	public function get_activity_log_table() {
124	return $this->get_table_name( $this->activity_log_table );
125	}
126
127	/**
128	* Get login attempts table name
129	*
130	* @return string
131	*/
132	public function get_login_attempts_table() {
133	return $this->get_table_name( $this->login_attempts_table );
134	}
135
136	/**
137	* Get file integrity table name
138	*
139	* @return string
140	*/
141	public function get_file_integrity_table() {
142	return $this->get_table_name( $this->file_integrity_table );
143	}
144
145	/**
146	* Get 2FA codes table name
147	*
148	* @return string
149	*/
150	public function get_2fa_codes_table() {
151	return $this->get_table_name( $this->two_factor_codes_table );
152	}
153
154	/**
155	* Get 2FA trusted devices table name
156	*
157	* @return string
158	*/
159	public function get_2fa_devices_table() {
160	return $this->get_table_name( $this->two_factor_devices_table );
161	}
162
163	/**
164	* Get 2FA notifications table name
165	*
166	* @return string
167	*/
168	public function get_2fa_notifications_table() {
169	return $this->get_table_name( $this->two_factor_notifications_table );
170	}
171
172	/**
173	* Get TOTP secrets table name with prefix
174	*
175	* @return string
176	*/
177	public function get_totp_table() {
178	return $this->get_table_name( $this->two_factor_totp_table );
179	}
180
181	/**
182	* Create all required database tables
183	*
184	* @return bool True on success.
185	*/
186	public function create_tables() {
187	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
188
189	$charset_collate = $this->wpdb->get_charset_collate();
190	$result = true;
191
192	// Activity Log table
193	$activity_log_sql = "CREATE TABLE {$this->get_activity_log_table()} (
194	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
195	event_type varchar(50) NOT NULL,
196	event_action varchar(100) NOT NULL,
197	event_message text NOT NULL,
198	user_id bigint(20) unsigned DEFAULT 0,
199	user_login varchar(60) DEFAULT '',
200	ip_address varchar(45) DEFAULT '',
201	user_agent text,
202	request_method varchar(10) DEFAULT '',
203	object_type varchar(50) DEFAULT '',
204	object_id bigint(20) unsigned DEFAULT 0,
205	object_name varchar(255) DEFAULT '',
206	severity varchar(20) DEFAULT 'info',
207	extra_data longtext,
208	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
209	PRIMARY KEY (id),
210	KEY event_type (event_type),
211	KEY event_action (event_action),
212	KEY user_id (user_id),
213	KEY ip_address (ip_address),
214	KEY severity (severity),
215	KEY request_method (request_method),
216	KEY created_at (created_at)
217	) $charset_collate;";
218
219	dbDelta( $activity_log_sql );
220
221	// Login Attempts table
222	$login_attempts_sql = "CREATE TABLE {$this->get_login_attempts_table()} (
223	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
224	ip_address varchar(45) NOT NULL,
225	username varchar(60) NOT NULL,
226	attempt_type varchar(20) NOT NULL DEFAULT 'login',
227	status varchar(20) NOT NULL DEFAULT 'failed',
228	user_agent text,
229	lockout_until datetime DEFAULT NULL,
230	attempt_count int(11) unsigned DEFAULT 1,
231	last_attempt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
232	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
233	PRIMARY KEY (id),
234	KEY ip_address (ip_address),
235	KEY username (username),
236	KEY status (status),
237	KEY lockout_until (lockout_until),
238	KEY last_attempt (last_attempt),
239	UNIQUE KEY ip_username (ip_address, username)
240	) $charset_collate;";
241
242	dbDelta( $login_attempts_sql );
243
244	// File Integrity table
245	$file_integrity_sql = "CREATE TABLE {$this->get_file_integrity_table()} (
246	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
247	file_path varchar(500) NOT NULL,
248	file_hash varchar(64) NOT NULL,
249	file_size bigint(20) unsigned NOT NULL DEFAULT 0,
250	file_type varchar(50) NOT NULL DEFAULT 'core',
251	status varchar(20) NOT NULL DEFAULT 'ok',
252	last_checked datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
253	last_modified datetime DEFAULT NULL,
254	extra_data text,
255	PRIMARY KEY (id),
256	KEY file_type (file_type),
257	KEY status (status),
258	KEY last_checked (last_checked),
259	UNIQUE KEY file_path (file_path(255))
260	) $charset_collate;";
261
262	dbDelta( $file_integrity_sql );
263
264	// 2FA Codes table
265	$two_factor_codes_sql = "CREATE TABLE {$this->get_2fa_codes_table()} (
266	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
267	user_id bigint(20) unsigned NOT NULL,
268	code varchar(6) NOT NULL,
269	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
270	expires_at datetime NOT NULL,
271	attempts int(11) unsigned DEFAULT 0,
272	used tinyint(1) DEFAULT 0,
273	PRIMARY KEY (id),
274	KEY user_id (user_id),
275	KEY expires_at (expires_at)
276	) $charset_collate;";
277
278	dbDelta( $two_factor_codes_sql );
279
280	// 2FA Trusted Devices table
281	$two_factor_devices_sql = "CREATE TABLE {$this->get_2fa_devices_table()} (
282	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
283	user_id bigint(20) unsigned NOT NULL,
284	device_hash varchar(64) NOT NULL,
285	user_agent text,
286	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
287	expires_at datetime NOT NULL,
288	PRIMARY KEY (id),
289	KEY user_id (user_id),
290	KEY device_hash (device_hash),
291	KEY expires_at (expires_at)
292	) $charset_collate;";
293
294	dbDelta( $two_factor_devices_sql );
295
296	// 2FA Notifications table (tracks which users have been notified)
297	$two_factor_notifications_sql = "CREATE TABLE {$this->get_2fa_notifications_table()} (
298	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
299	user_id bigint(20) unsigned NOT NULL,
300	sent_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
301	PRIMARY KEY (id),
302	UNIQUE KEY user_id (user_id)
303	) $charset_collate;";
304
305	dbDelta( $two_factor_notifications_sql );
306
307	// 2FA TOTP secrets table
308	$two_factor_totp_sql = "CREATE TABLE {$this->get_totp_table()} (
309	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
310	user_id bigint(20) unsigned NOT NULL,
311	secret text NOT NULL,
312	backup_codes text,
313	is_configured tinyint(1) DEFAULT 0,
314	configured_at datetime DEFAULT NULL,
315	last_used_at datetime DEFAULT NULL,
316	grace_period_expires datetime DEFAULT NULL,
317	PRIMARY KEY (id),
318	UNIQUE KEY user_id (user_id)
319	) $charset_collate;";
320
321	dbDelta( $two_factor_totp_sql );
322
323	// Store database version
324	update_option( self::DB_VERSION_OPTION, self::DB_VERSION );
325
326	return $result;
327	}
328
329	/**
330	* Check if tables need to be updated
331	*
332	* @return bool True if update needed.
333	*/
334	public function needs_update() {
335	$current_version = get_option( self::DB_VERSION_OPTION, '0' );
336	return version_compare( $current_version, self::DB_VERSION, '<' );
337	}
338
339	/**
340	* Run database migrations
341	*
342	* Handles schema changes between versions.
343	*/
344	public function run_migrations() {
345	$current_version = get_option( self::DB_VERSION_OPTION, '0' );
346
347	// v1.3.0: Add request_method column to activity log
348	if ( version_compare( $current_version, '1.3.0', '<' ) ) {
349	$table = $this->get_activity_log_table();
350
351	// Check if column already exists
352	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
353	$column_exists = $this->wpdb->get_results(
354	$this->wpdb->prepare(
355	'SHOW COLUMNS FROM %i LIKE %s',
356	$table,
357	'request_method'
358	)
359	);
360
361	if ( empty( $column_exists ) ) {
362	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
363	$this->wpdb->query(
364	$this->wpdb->prepare(
365	'ALTER TABLE %i ADD COLUMN request_method varchar(10) DEFAULT %s AFTER user_agent',
366	$table,
367	''
368	)
369	);
370
371	// Add index
372	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
373	$this->wpdb->query(
374	$this->wpdb->prepare(
375	'ALTER TABLE %i ADD KEY request_method (request_method)',
376	$table
377	)
378	);
379	}
380	}
381
382	// Update stored version
383	update_option( self::DB_VERSION_OPTION, self::DB_VERSION );
384	}
385
386	/**
387	* Drop all plugin tables
388	*
389	* @return bool
390	*/
391	public function drop_tables() {
392	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange
393	$tables = array(
394	$this->get_activity_log_table(),
395	$this->get_login_attempts_table(),
396	$this->get_file_integrity_table(),
397	$this->get_2fa_codes_table(),
398	$this->get_2fa_devices_table(),
399	$this->get_2fa_notifications_table(),
400	$this->get_totp_table(),
401	);
402
403	foreach ( $tables as $table ) {
404	$this->wpdb->query( $this->wpdb->prepare( 'DROP TABLE IF EXISTS %i', $table ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
405	}
406
407	delete_option( self::DB_VERSION_OPTION );
408
409	return true;
410	// phpcs:enable
411	}
412
413	// =========================================================================
414	// ACTIVITY LOG METHODS
415	// =========================================================================
416
417	/**
418	* Check if activity log table exists
419	*
420	* @return bool
421	*/
422	private function activity_log_table_exists() {
423	$table = $this->get_activity_log_table();
424	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
425	$result = $this->wpdb->get_var( $this->wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
426	return $result === $table;
427	}
428
429	/**
430	* Insert activity log entry
431	*
432	* @param array $data Log data.
433	* @return int\|false Insert ID or false on failure.
434	*/
435	public function insert_activity_log( $data ) {
436	// Verify table exists before inserting (prevents errors in Plugin Check environment)
437	if ( ! $this->activity_log_table_exists() ) {
438	return false;
439	}
440
441	$defaults = array(
442	'event_type' => 'general',
443	'event_action' => '',
444	'event_message' => '',
445	'user_id' => get_current_user_id(),
446	'user_login' => '',
447	'ip_address' => $this->get_client_ip(),
448	'user_agent' => isset( $_SERVER['HTTP_USER_AGENT'] ) ? sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : '',
449	'request_method' => isset( $_SERVER['REQUEST_METHOD'] ) ? sanitize_text_field( wp_unslash( $_SERVER['REQUEST_METHOD'] ) ) : '',
450	'object_type' => '',
451	'object_id' => 0,
452	'object_name' => '',
453	'severity' => 'info',
454	'extra_data' => '',
455	'created_at' => current_time( 'mysql' ),
456	);
457
458	$data = wp_parse_args( $data, $defaults );
459
460	// Get username if not provided
461	if ( empty( $data['user_login'] ) && $data['user_id'] > 0 ) {
462	$user = get_userdata( $data['user_id'] );
463	if ( $user ) {
464	$data['user_login'] = $user->user_login;
465	}
466	}
467
468	// Serialize extra data if array
469	if ( is_array( $data['extra_data'] ) ) {
470	$data['extra_data'] = wp_json_encode( $data['extra_data'] );
471	}
472
473	// Sanitize data
474	$data = array(
475	'event_type' => sanitize_key( $data['event_type'] ),
476	'event_action' => sanitize_text_field( $data['event_action'] ),
477	'event_message' => sanitize_textarea_field( $data['event_message'] ),
478	'user_id' => absint( $data['user_id'] ),
479	'user_login' => sanitize_user( $data['user_login'] ),
480	'ip_address' => sanitize_text_field( $data['ip_address'] ),
481	'user_agent' => sanitize_textarea_field( substr( $data['user_agent'], 0, 500 ) ),
482	'request_method' => sanitize_text_field( strtoupper( substr( $data['request_method'], 0, 10 ) ) ),
483	'object_type' => sanitize_key( $data['object_type'] ),
484	'object_id' => absint( $data['object_id'] ),
485	'object_name' => sanitize_text_field( $data['object_name'] ),
486	'severity' => sanitize_key( $data['severity'] ),
487	'extra_data' => $data['extra_data'],
488	'created_at' => $data['created_at'],
489	);
490
491	$result = $this->wpdb->insert(
492	$this->get_activity_log_table(),
493	$data,
494	array( '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s' )
495	);
496
497	return $result ? $this->wpdb->insert_id : false;
498	}
499
500	/**
501	* Get activity log entries
502	*
503	* @param array $args Query arguments.
504	* @return array
505	*/
506	public function get_activity_logs( $args = array() ) {
507	$defaults = array(
508	'per_page' => 50,
509	'page' => 1,
510	'event_type' => '',
511	'severity' => '',
512	'request_method' => '',
513	'search' => '',
514	'date_from' => '',
515	'date_to' => '',
516	);
517
518	$args = wp_parse_args( $args, $defaults );
519	$table = $this->get_activity_log_table();
520
521	// Sanitize inputs
522	$event_type = sanitize_key( $args['event_type'] );
523	$severity = sanitize_key( $args['severity'] );
524	$request_method = sanitize_text_field( $args['request_method'] );
525	$search = sanitize_text_field( $args['search'] );
526
527	// Use default dates for empty values (MySQL requires valid DATETIME)
528	$date_from = ! empty( $args['date_from'] ) ? sanitize_text_field( $args['date_from'] ) : '1970-01-01 00:00:00';
529	$date_to = ! empty( $args['date_to'] ) ? sanitize_text_field( $args['date_to'] ) : '9999-12-31 23:59:59';
530
531	// Calculate pagination
532	$per_page = absint( $args['per_page'] );
533	$offset = ( absint( $args['page'] ) - 1 ) * $per_page;
534
535	if ( ! empty( $search ) ) {
536	$like = '%' . $this->wpdb->esc_like( $search ) . '%';
537	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
538	$results = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT * FROM %i WHERE (event_type = %s OR %s = '') AND (severity = %s OR %s = '') AND (request_method = %s OR %s = '') AND created_at >= %s AND created_at <= %s AND (event_message LIKE %s OR user_login LIKE %s OR ip_address LIKE %s OR user_agent LIKE %s OR object_name LIKE %s OR extra_data LIKE %s) ORDER BY created_at DESC LIMIT %d OFFSET %d", $table, $event_type, $event_type, $severity, $severity, $request_method, $request_method, $date_from, $date_to, $like, $like, $like, $like, $like, $like, $per_page, $offset ) );
539	} else {
540	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
541	$results = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT * FROM %i WHERE (event_type = %s OR %s = '') AND (severity = %s OR %s = '') AND (request_method = %s OR %s = '') AND created_at >= %s AND created_at <= %s ORDER BY created_at DESC LIMIT %d OFFSET %d", $table, $event_type, $event_type, $severity, $severity, $request_method, $request_method, $date_from, $date_to, $per_page, $offset ) );
542	}
543
544	return $results ? $results : array();
545	}
546
547	/**
548	* Get total count of activity logs
549	*
550	* @param array $args Query arguments (same as get_activity_logs).
551	* @return int
552	*/
553	public function get_activity_logs_count( $args = array() ) {
554	$table = $this->get_activity_log_table();
555
556	// Sanitize inputs
557	$event_type = isset( $args['event_type'] ) ? sanitize_key( $args['event_type'] ) : '';
558	$severity = isset( $args['severity'] ) ? sanitize_key( $args['severity'] ) : '';
559	$request_method = isset( $args['request_method'] ) ? sanitize_text_field( $args['request_method'] ) : '';
560	$search = isset( $args['search'] ) ? sanitize_text_field( $args['search'] ) : '';
561
562	// Use default dates for empty values (MySQL requires valid DATETIME)
563	$date_from = ! empty( $args['date_from'] ) ? sanitize_text_field( $args['date_from'] ) : '1970-01-01 00:00:00';
564	$date_to = ! empty( $args['date_to'] ) ? sanitize_text_field( $args['date_to'] ) : '9999-12-31 23:59:59';
565
566	if ( ! empty( $search ) ) {
567	$like = '%' . $this->wpdb->esc_like( $search ) . '%';
568	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
569	$count = $this->wpdb->get_var( $this->wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE (event_type = %s OR %s = '') AND (severity = %s OR %s = '') AND (request_method = %s OR %s = '') AND created_at >= %s AND created_at <= %s AND (event_message LIKE %s OR user_login LIKE %s OR ip_address LIKE %s OR user_agent LIKE %s OR object_name LIKE %s OR extra_data LIKE %s)", $table, $event_type, $event_type, $severity, $severity, $request_method, $request_method, $date_from, $date_to, $like, $like, $like, $like, $like, $like ) );
570	} else {
571	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
572	$count = $this->wpdb->get_var( $this->wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE (event_type = %s OR %s = '') AND (severity = %s OR %s = '') AND (request_method = %s OR %s = '') AND created_at >= %s AND created_at <= %s", $table, $event_type, $event_type, $severity, $severity, $request_method, $request_method, $date_from, $date_to ) );
573	}
574
575	return absint( $count );
576	}
577
578	/**
579	* Delete old activity logs
580	*
581	* @param int $days Days to keep.
582	* @return int Number of deleted rows.
583	*/
584	public function cleanup_old_activity_logs( $days = 30 ) {
585	$table = $this->get_activity_log_table();
586	$date = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );
587
588	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
589	$deleted = $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM %i WHERE created_at < %s', $table, $date ) );
590
591	return $deleted ? $deleted : 0;
592	}
593
594	/**
595	* Truncate activity log table
596	*
597	* @return bool
598	*/
599	public function truncate_activity_log() {
600	$table = $this->get_activity_log_table();
601	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
602	return false !== $this->wpdb->query( $this->wpdb->prepare( 'TRUNCATE TABLE %i', $table ) );
603	}
604
605	// =========================================================================
606	// LOGIN ATTEMPTS METHODS
607	// =========================================================================
608
609	/**
610	* Check if login attempts table exists
611	*
612	* @return bool
613	*/
614	private function login_attempts_table_exists() {
615	$table = $this->get_login_attempts_table();
616	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
617	$result = $this->wpdb->get_var( $this->wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
618	return $result === $table;
619	}
620
621	/**
622	* Record a login attempt
623	*
624	* @param string $ip_address IP address.
625	* @param string $username Username attempted.
626	* @param string $status Status: 'failed', 'success', 'lockout'.
627	* @return int\|false
628	*/
629	public function record_login_attempt( $ip_address, $username, $status = 'failed' ) {
630	// Verify table exists before inserting
631	if ( ! $this->login_attempts_table_exists() ) {
632	return false;
633	}
634
635	$table = $this->get_login_attempts_table();
636	$ip_address = sanitize_text_field( $ip_address );
637	$username = sanitize_user( $username );
638	$status = sanitize_key( $status );
639	$user_agent = isset( $_SERVER['HTTP_USER_AGENT'] ) ? sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : '';
640	$now = current_time( 'mysql' );
641
642	// Check if record exists for this IP + username
643	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
644	$existing = $this->wpdb->get_row( $this->wpdb->prepare( 'SELECT * FROM %i WHERE ip_address = %s AND username = %s', $table, $ip_address, $username ), ARRAY_A );
645
646	if ( $existing ) {
647	// Update existing record
648	$data = array(
649	'status' => $status,
650	'attempt_count' => $existing['attempt_count'] + 1,
651	'last_attempt' => $now,
652	'user_agent' => $user_agent,
653	);
654
655	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
656	$this->wpdb->update(
657	$table,
658	$data,
659	array(
660	'ip_address' => $ip_address,
661	'username' => $username,
662	),
663	array( '%s', '%d', '%s', '%s' ),
664	array( '%s', '%s' )
665	);
666
667	return $existing['id'];
668	} else {
669	// Insert new record
670	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
671	$this->wpdb->insert(
672	$table,
673	array(
674	'ip_address' => $ip_address,
675	'username' => $username,
676	'status' => $status,
677	'user_agent' => $user_agent,
678	'attempt_count' => 1,
679	'last_attempt' => $now,
680	'created_at' => $now,
681	),
682	array( '%s', '%s', '%s', '%s', '%d', '%s', '%s' )
683	);
684
685	return $this->wpdb->insert_id;
686	}
687	}
688
689	/**
690	* Get login attempts for an IP
691	*
692	* @param string $ip_address IP address.
693	* @param int $minutes Minutes to look back.
694	* @return array
695	*/
696	public function get_login_attempts( $ip_address, $minutes = 30 ) {
697	$table = $this->get_login_attempts_table();
698	$since = gmdate( 'Y-m-d H:i:s', strtotime( "-{$minutes} minutes" ) );
699
700	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
701	$results = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT * FROM %i WHERE ip_address = %s AND last_attempt >= %s ORDER BY last_attempt DESC', $table, $ip_address, $since ), ARRAY_A );
702
703	return $results ? $results : array();
704	}
705
706	/**
707	* Get failed attempt count for an IP
708	*
709	* @param string $ip_address IP address.
710	* @param int $minutes Minutes to look back.
711	* @return int
712	*/
713	public function get_failed_attempt_count( $ip_address, $minutes = 30 ) {
714	$table = $this->get_login_attempts_table();
715	$since = gmdate( 'Y-m-d H:i:s', strtotime( "-{$minutes} minutes" ) );
716
717	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
718	$count = $this->wpdb->get_var( $this->wpdb->prepare( "SELECT SUM(attempt_count) FROM %i WHERE ip_address = %s AND status = 'failed' AND last_attempt >= %s", $table, $ip_address, $since ) );
719
720	return absint( $count );
721	}
722
723	/**
724	* Set lockout for an IP
725	*
726	* @param string $ip_address IP address.
727	* @param int $seconds Lockout duration in seconds.
728	* @return bool
729	*/
730	public function set_lockout( $ip_address, $seconds ) {
731	$table = $this->get_login_attempts_table();
732	$lockout_until = gmdate( 'Y-m-d H:i:s', time() + $seconds );
733
734	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
735	return false !== $this->wpdb->query( $this->wpdb->prepare( "UPDATE %i SET lockout_until = %s, status = 'lockout' WHERE ip_address = %s", $table, $lockout_until, $ip_address ) );
736	}
737
738	/**
739	* Check if an IP is locked out
740	*
741	* @param string $ip_address IP address.
742	* @return array\|false Lockout data or false if not locked.
743	*/
744	public function is_locked_out( $ip_address ) {
745	$table = $this->get_login_attempts_table();
746	$now = current_time( 'mysql' );
747
748	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
749	$lockout = $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM %i WHERE ip_address = %s AND lockout_until > %s AND status = 'lockout' ORDER BY lockout_until DESC LIMIT 1", $table, $ip_address, $now ), ARRAY_A );
750
751	return $lockout ? $lockout : false;
752	}
753
754	/**
755	* Clear lockout for an IP
756	*
757	* @param string $ip_address IP address.
758	* @return bool
759	*/
760	public function clear_lockout( $ip_address ) {
761	$table = $this->get_login_attempts_table();
762
763	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
764	return false !== $this->wpdb->query( $this->wpdb->prepare( "UPDATE %i SET lockout_until = NULL, status = 'cleared', attempt_count = 0 WHERE ip_address = %s", $table, $ip_address ) );
765	}
766
767	/**
768	* Get all active lockouts
769	*
770	* @return array List of locked IPs with their data.
771	*/
772	public function get_active_lockouts() {
773	$table = $this->get_login_attempts_table();
774	$now = current_time( 'mysql' );
775
776	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
777	$lockouts = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT ip_address, username, attempt_count as attempts, lockout_until as locked_until, last_attempt FROM %i WHERE lockout_until > %s AND status = 'lockout' ORDER BY lockout_until DESC", $table, $now ) );
778
779	return $lockouts ? $lockouts : array();
780	}
781
782	/**
783	* Clear all lockouts
784	*
785	* @return bool
786	*/
787	public function clear_all_lockouts() {
788	$table = $this->get_login_attempts_table();
789
790	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
791	return false !== $this->wpdb->query( $this->wpdb->prepare( "UPDATE %i SET lockout_until = NULL, status = 'cleared', attempt_count = 0 WHERE status = 'lockout'", $table ) );
792	}
793
794	/**
795	* Reset login attempts for an IP
796	*
797	* @param string $ip_address IP address.
798	* @return bool
799	*/
800	public function reset_login_attempts( $ip_address ) {
801	$table = $this->get_login_attempts_table();
802
803	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
804	return false !== $this->wpdb->delete(
805	$table,
806	array( 'ip_address' => $ip_address ),
807	array( '%s' )
808	);
809	}
810
811	/**
812	* Clean up old login attempts
813	*
814	* @param int $hours Hours to keep.
815	* @return int Number of deleted rows.
816	*/
817	public function cleanup_old_login_attempts( $hours = 24 ) {
818	$table = $this->get_login_attempts_table();
819	$date = gmdate( 'Y-m-d H:i:s', strtotime( "-{$hours} hours" ) );
820
821	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
822	$deleted = $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM %i WHERE last_attempt < %s AND (lockout_until IS NULL OR lockout_until < %s)', $table, $date, current_time( 'mysql' ) ) );
823
824	return $deleted ? $deleted : 0;
825	}
826
827	/**
828	* Get all currently locked out IPs
829	*
830	* @return array
831	*/
832	public function get_locked_out_ips() {
833	$table = $this->get_login_attempts_table();
834	$now = current_time( 'mysql' );
835
836	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
837	$results = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT DISTINCT ip_address, lockout_until, attempt_count, last_attempt FROM %i WHERE lockout_until > %s AND status = 'lockout' ORDER BY lockout_until DESC", $table, $now ), ARRAY_A );
838
839	return $results ? $results : array();
840	}
841
842	// =========================================================================
843	// FILE INTEGRITY METHODS
844	// =========================================================================
845
846	/**
847	* Check if file integrity table exists
848	*
849	* @return bool
850	*/
851	private function file_integrity_table_exists() {
852	$table = $this->get_file_integrity_table();
853	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
854	$result = $this->wpdb->get_var( $this->wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
855	return $result === $table;
856	}
857
858	/**
859	* Store file hash
860	*
861	* @param string $file_path File path.
862	* @param string $hash File hash.
863	* @param int $size File size.
864	* @param string $type File type: 'core', 'plugin', 'theme'.
865	* @return int\|false
866	*/
867	public function store_file_hash( $file_path, $hash, $size = 0, $type = 'core' ) {
868	// Verify table exists before inserting
869	if ( ! $this->file_integrity_table_exists() ) {
870	return false;
871	}
872
873	$table = $this->get_file_integrity_table();
874	$now = current_time( 'mysql' );
875
876	// Check if exists
877	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
878	$existing = $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT id FROM %i WHERE file_path = %s', $table, $file_path ) );
879
880	if ( $existing ) {
881	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
882	$this->wpdb->update(
883	$table,
884	array(
885	'file_hash' => $hash,
886	'file_size' => $size,
887	'file_type' => $type,
888	'status' => 'ok',
889	'last_checked' => $now,
890	),
891	array( 'id' => $existing ),
892	array( '%s', '%d', '%s', '%s', '%s' ),
893	array( '%d' )
894	);
895	return $existing;
896	}
897
898	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
899	$this->wpdb->insert(
900	$table,
901	array(
902	'file_path' => $file_path,
903	'file_hash' => $hash,
904	'file_size' => $size,
905	'file_type' => $type,
906	'status' => 'ok',
907	'last_checked' => $now,
908	),
909	array( '%s', '%s', '%d', '%s', '%s', '%s' )
910	);
911
912	return $this->wpdb->insert_id;
913	}
914
915	/**
916	* Get stored file hash
917	*
918	* @param string $file_path File path.
919	* @return array\|null
920	*/
921	public function get_file_hash( $file_path ) {
922	$table = $this->get_file_integrity_table();
923
924	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
925	$result = $this->wpdb->get_row( $this->wpdb->prepare( 'SELECT * FROM %i WHERE file_path = %s', $table, $file_path ), ARRAY_A );
926
927	return $result;
928	}
929
930	/**
931	* Update file status
932	*
933	* @param string $file_path File path.
934	* @param string $status Status: 'ok', 'modified', 'deleted', 'new'.
935	* @param string $new_hash New hash if modified.
936	* @return bool
937	*/
938	public function update_file_status( $file_path, $status, $new_hash = '' ) {
939	$table = $this->get_file_integrity_table();
940
941	$data = array(
942	'status' => $status,
943	'last_checked' => current_time( 'mysql' ),
944	);
945
946	if ( ! empty( $new_hash ) ) {
947	$data['file_hash'] = $new_hash;
948	}
949
950	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
951	return false !== $this->wpdb->update(
952	$table,
953	$data,
954	array( 'file_path' => $file_path ),
955	array_fill( 0, count( $data ), '%s' ),
956	array( '%s' )
957	);
958	}
959
960	/**
961	* Get files by status
962	*
963	* @param string $status File status.
964	* @param string $type File type (optional).
965	* @return array
966	*/
967	public function get_files_by_status( $status, $type = '' ) {
968	$table = $this->get_file_integrity_table();
969	$status = sanitize_key( $status );
970	$type = sanitize_key( $type );
971
972	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
973	$results = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT * FROM %i WHERE status = %s AND (file_type = %s OR %s = '') ORDER BY file_path ASC", $table, $status, $type, $type ), ARRAY_A );
974
975	return $results ? $results : array();
976	}
977
978	/**
979	* Clear all file hashes
980	*
981	* @param string $type Optional file type to clear.
982	* @return bool
983	*/
984	public function clear_file_hashes( $type = '' ) {
985	$table = $this->get_file_integrity_table();
986
987	if ( ! empty( $type ) ) {
988	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
989	return false !== $this->wpdb->delete(
990	$table,
991	array( 'file_type' => $type ),
992	array( '%s' )
993	);
994	}
995
996	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
997	return false !== $this->wpdb->query( $this->wpdb->prepare( 'TRUNCATE TABLE %i', $table ) );
998	}
999
1000	// =========================================================================
1001	// UTILITY METHODS
1002	// =========================================================================
1003
1004	/**
1005	* Get client IP address
1006	*
1007	* Delegates to the shared resolver, which only trusts REMOTE_ADDR unless a
1008	* proxy header has been explicitly declared in settings.
1009	*
1010	* @return string
1011	*/
1012	public function get_client_ip() {
1013	return Vigilante_IP_Utils::get_client_ip();
1014	}
1015
1016	/**
1017	* Get database statistics
1018	*
1019	* @return array
1020	*/
1021	public function get_stats() {
1022	$stats = array(
1023	'activity_log_count' => $this->get_activity_logs_count(),
1024	'locked_out_ips_count' => count( $this->get_locked_out_ips() ),
1025	'file_integrity_count' => 0,
1026	'modified_files_count' => 0,
1027	);
1028
1029	$table = $this->get_file_integrity_table();
1030	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
1031	$stats['file_integrity_count'] = absint( $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) ) );
1032	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
1033	$stats['modified_files_count'] = absint( $this->wpdb->get_var( $this->wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE status != 'ok'", $table ) ) );
1034
1035	return $stats;
1036	}
1037
1038	// =========================================================================
1039	// TWO-FACTOR AUTHENTICATION METHODS
1040	// =========================================================================
1041
1042	/**
1043	* Store 2FA verification code
1044	*
1045	* @param int $user_id User ID.
1046	* @param string $code Verification code.
1047	* @param string $expires_at Expiration datetime.
1048	* @return int\|false Insert ID or false on failure.
1049	*/
1050	public function store_2fa_code( $user_id, $code, $expires_at ) {
1051	$table = $this->get_2fa_codes_table();
1052
1053	// Delete any existing codes for this user
1054	$this->delete_2fa_code( $user_id );
1055
1056	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
1057	$result = $this->wpdb->insert(
1058	$table,
1059	array(
1060	'user_id' => $user_id,
1061	'code' => $code,
1062	'expires_at' => $expires_at,
1063	'attempts' => 0,
1064	'used' => 0,
1065	),
1066	array( '%d', '%s', '%s', '%d', '%d' )
1067	);
1068
1069	return $result ? $this->wpdb->insert_id : false;
1070	}
1071
1072	/**
1073	* Get 2FA code for user
1074	*
1075	* @param int $user_id User ID.
1076	* @return array\|null Code data or null if not found.
1077	*/
1078	public function get_2fa_code( $user_id ) {
1079	$table = $this->get_2fa_codes_table();
1080
1081	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i placeholder requires WP 6.2+.
1082	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1083	return $this->wpdb->get_row(
1084	$this->wpdb->prepare(
1085	'SELECT * FROM %i WHERE user_id = %d AND used = 0 ORDER BY created_at DESC LIMIT 1',
1086	$table,
1087	$user_id
1088	),
1089	ARRAY_A
1090	);
1091	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
1092	}
1093
1094	/**
1095	* Increment 2FA code attempts
1096	*
1097	* @param int $user_id User ID.
1098	* @return bool
1099	*/
1100	public function increment_2fa_attempts( $user_id ) {
1101	$table = $this->get_2fa_codes_table();
1102
1103	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i placeholder requires WP 6.2+.
1104	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1105	return false !== $this->wpdb->query(
1106	$this->wpdb->prepare(
1107	'UPDATE %i SET attempts = attempts + 1 WHERE user_id = %d AND used = 0',
1108	$table,
1109	$user_id
1110	)
1111	);
1112	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
1113	}
1114
1115	/**
1116	* Mark 2FA code as used
1117	*
1118	* @param int $user_id User ID.
1119	* @return bool
1120	*/
1121	public function mark_2fa_code_used( $user_id ) {
1122	$table = $this->get_2fa_codes_table();
1123
1124	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1125	return false !== $this->wpdb->update(
1126	$table,
1127	array( 'used' => 1 ),
1128	array( 'user_id' => $user_id ),
1129	array( '%d' ),
1130	array( '%d' )
1131	);
1132	}
1133
1134	/**
1135	* Delete 2FA code for user
1136	*
1137	* @param int $user_id User ID.
1138	* @return bool
1139	*/
1140	public function delete_2fa_code( $user_id ) {
1141	$table = $this->get_2fa_codes_table();
1142
1143	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1144	return false !== $this->wpdb->delete(
1145	$table,
1146	array( 'user_id' => $user_id ),
1147	array( '%d' )
1148	);
1149	}
1150
1151	/**
1152	* Cleanup expired 2FA codes
1153	*
1154	* @return int Number of deleted rows.
1155	*/
1156	public function cleanup_expired_2fa_codes() {
1157	$table = $this->get_2fa_codes_table();
1158	$now = current_time( 'mysql', true );
1159
1160	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i placeholder requires WP 6.2+.
1161	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1162	$this->wpdb->query(
1163	$this->wpdb->prepare(
1164	'DELETE FROM %i WHERE expires_at < %s OR used = 1',
1165	$table,
1166	$now
1167	)
1168	);
1169	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
1170
1171	return $this->wpdb->rows_affected;
1172	}
1173
1174	/**
1175	* Trust a device for 2FA
1176	*
1177	* @param int $user_id User ID.
1178	* @param string $device_hash Device hash.
1179	* @param string $user_agent User agent.
1180	* @param string $expires_at Expiration datetime.
1181	* @return int\|false Insert ID or false on failure.
1182	*/
1183	public function trust_device( $user_id, $device_hash, $user_agent, $expires_at ) {
1184	$table = $this->get_2fa_devices_table();
1185
1186	// Delete existing entry for this device
1187	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1188	$this->wpdb->delete(
1189	$table,
1190	array(
1191	'user_id' => $user_id,
1192	'device_hash' => $device_hash,
1193	),
1194	array( '%d', '%s' )
1195	);
1196
1197	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
1198	$result = $this->wpdb->insert(
1199	$table,
1200	array(
1201	'user_id' => $user_id,
1202	'device_hash' => $device_hash,
1203	'user_agent' => $user_agent,
1204	'expires_at' => $expires_at,
1205	),
1206	array( '%d', '%s', '%s', '%s' )
1207	);
1208
1209	return $result ? $this->wpdb->insert_id : false;
1210	}
1211
1212	/**
1213	* Check if device is trusted
1214	*
1215	* @param int $user_id User ID.
1216	* @param string $device_hash Device hash.
1217	* @return bool
1218	*/
1219	public function is_device_trusted( $user_id, $device_hash ) {
1220	$table = $this->get_2fa_devices_table();
1221	$now = current_time( 'mysql', true );
1222
1223	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i placeholder requires WP 6.2+.
1224	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1225	$result = $this->wpdb->get_var(
1226	$this->wpdb->prepare(
1227	'SELECT id FROM %i WHERE user_id = %d AND device_hash = %s AND expires_at > %s LIMIT 1',
1228	$table,
1229	$user_id,
1230	$device_hash,
1231	$now
1232	)
1233	);
1234	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
1235
1236	return ! empty( $result );
1237	}
1238
1239	/**
1240	* Get trusted devices for user
1241	*
1242	* @param int $user_id User ID.
1243	* @return array
1244	*/
1245	public function get_trusted_devices( $user_id ) {
1246	$table = $this->get_2fa_devices_table();
1247	$now = current_time( 'mysql', true );
1248
1249	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i placeholder requires WP 6.2+.
1250	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1251	$results = $this->wpdb->get_results(
1252	$this->wpdb->prepare(
1253	'SELECT * FROM %i WHERE user_id = %d AND expires_at > %s ORDER BY created_at DESC',
1254	$table,
1255	$user_id,
1256	$now
1257	),
1258	ARRAY_A
1259	);
1260	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
1261
1262	return $results ? $results : array();
1263	}
1264
1265	/**
1266	* Revoke all trusted devices for user
1267	*
1268	* @param int $user_id User ID.
1269	* @return bool
1270	*/
1271	public function revoke_trusted_devices( $user_id ) {
1272	$table = $this->get_2fa_devices_table();
1273
1274	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1275	return false !== $this->wpdb->delete(
1276	$table,
1277	array( 'user_id' => $user_id ),
1278	array( '%d' )
1279	);
1280	}
1281
1282	/**
1283	* Cleanup expired trusted devices
1284	*
1285	* @return int Number of deleted rows.
1286	*/
1287	public function cleanup_expired_trusted_devices() {
1288	$table = $this->get_2fa_devices_table();
1289	$now = current_time( 'mysql', true );
1290
1291	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i placeholder requires WP 6.2+.
1292	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1293	$this->wpdb->query(
1294	$this->wpdb->prepare(
1295	'DELETE FROM %i WHERE expires_at < %s',
1296	$table,
1297	$now
1298	)
1299	);
1300	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
1301
1302	return $this->wpdb->rows_affected;
1303	}
1304
1305	/**
1306	* Mark user as notified about 2FA
1307	*
1308	* @param int $user_id User ID.
1309	* @return bool
1310	*/
1311	public function mark_2fa_notified( $user_id ) {
1312	$table = $this->get_2fa_notifications_table();
1313
1314	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
1315	$result = $this->wpdb->replace(
1316	$table,
1317	array(
1318	'user_id' => $user_id,
1319	'sent_at' => current_time( 'mysql', true ),
1320	),
1321	array( '%d', '%s' )
1322	);
1323
1324	return false !== $result;
1325	}
1326
1327	/**
1328	* Check if user was notified about 2FA
1329	*
1330	* @param int $user_id User ID.
1331	* @return bool
1332	*/
1333	public function user_was_2fa_notified( $user_id ) {
1334	$table = $this->get_2fa_notifications_table();
1335
1336	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i placeholder requires WP 6.2+.
1337	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1338	$result = $this->wpdb->get_var(
1339	$this->wpdb->prepare(
1340	'SELECT id FROM %i WHERE user_id = %d LIMIT 1',
1341	$table,
1342	$user_id
1343	)
1344	);
1345	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
1346
1347	return ! empty( $result );
1348	}
1349
1350	/**
1351	* Clear 2FA notification records
1352	*
1353	* @return bool
1354	*/
1355	public function clear_2fa_notifications() {
1356	$table = $this->get_2fa_notifications_table();
1357
1358	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i placeholder requires WP 6.2+.
1359	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1360	return false !== $this->wpdb->query(
1361	$this->wpdb->prepare( 'TRUNCATE TABLE %i', $table )
1362	);
1363	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
1364	}
1365
1366	// =========================================================================
1367	// TOTP METHODS
1368	// =========================================================================
1369
1370	/**
1371	* Get TOTP data for a user
1372	*
1373	* @param int $user_id User ID.
1374	* @return array\|null
1375	*/
1376	public function get_totp_data( $user_id ) {
1377	$table = $this->get_totp_table();
1378
1379	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i placeholder requires WP 6.2+.
1380	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1381	return $this->wpdb->get_row(
1382	$this->wpdb->prepare(
1383	'SELECT * FROM %i WHERE user_id = %d LIMIT 1',
1384	$table,
1385	$user_id
1386	),
1387	ARRAY_A
1388	);
1389	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
1390	}
1391
1392	/**
1393	* Create TOTP placeholder row (grace period tracking)
1394	*
1395	* @param int $user_id User ID.
1396	* @param string $grace_expires Grace period expiry datetime.
1397	* @return bool
1398	*/
1399	public function create_totp_placeholder( $user_id, $grace_expires ) {
1400	$table = $this->get_totp_table();
1401
1402	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
1403	return false !== $this->wpdb->replace(
1404	$table,
1405	array(
1406	'user_id' => $user_id,
1407	'secret' => '',
1408	'is_configured' => 0,
1409	'grace_period_expires' => $grace_expires,
1410	),
1411	array( '%d', '%s', '%d', '%s' )
1412	);
1413	}
1414
1415	/**
1416	* Save TOTP data after successful setup
1417	*
1418	* @param int $user_id User ID.
1419	* @param string $encrypted Encrypted secret.
1420	* @return bool
1421	*/
1422	public function save_totp_data( $user_id, $encrypted ) {
1423	$table = $this->get_totp_table();
1424	$now = current_time( 'mysql', true );
1425
1426	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
1427	return false !== $this->wpdb->replace(
1428	$table,
1429	array(
1430	'user_id' => $user_id,
1431	'secret' => $encrypted,
1432	'is_configured' => 1,
1433	'configured_at' => $now,
1434	'grace_period_expires' => null,
1435	),
1436	array( '%d', '%s', '%d', '%s', '%s' )
1437	);
1438	}
1439
1440	/**
1441	* Store backup codes for a user
1442	*
1443	* @param int $user_id User ID.
1444	* @param string $hashed_codes JSON-encoded hashed codes.
1445	* @return bool
1446	*/
1447	public function store_totp_backup_codes( $user_id, $hashed_codes ) {
1448	$table = $this->get_totp_table();
1449
1450	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1451	return false !== $this->wpdb->update(
1452	$table,
1453	array( 'backup_codes' => $hashed_codes ),
1454	array( 'user_id' => $user_id ),
1455	array( '%s' ),
1456	array( '%d' )
1457	);
1458	}
1459
1460	/**
1461	* Update TOTP last used timestamp
1462	*
1463	* @param int $user_id User ID.
1464	* @return bool
1465	*/
1466	public function update_totp_last_used( $user_id ) {
1467	$table = $this->get_totp_table();
1468	$now = current_time( 'mysql', true );
1469
1470	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1471	return false !== $this->wpdb->update(
1472	$table,
1473	array( 'last_used_at' => $now ),
1474	array( 'user_id' => $user_id ),
1475	array( '%s' ),
1476	array( '%d' )
1477	);
1478	}
1479
1480	/**
1481	* Reset TOTP data for a user (admin reset)
1482	*
1483	* @param int $user_id User ID.
1484	* @return bool
1485	*/
1486	public function reset_totp_data( $user_id ) {
1487	$table = $this->get_totp_table();
1488
1489	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1490	return false !== $this->wpdb->delete(
1491	$table,
1492	array( 'user_id' => $user_id ),
1493	array( '%d' )
1494	);
1495	}
1496
1497	/**
1498	* Get all users with TOTP configured
1499	*
1500	* @return array
1501	*/
1502	public function get_totp_configured_users() {
1503	$table = $this->get_totp_table();
1504
1505	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i placeholder requires WP 6.2+.
1506	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1507	$results = $this->wpdb->get_results(
1508	$this->wpdb->prepare(
1509	'SELECT user_id, configured_at, last_used_at FROM %i WHERE is_configured = 1 ORDER BY configured_at DESC',
1510	$table
1511	),
1512	ARRAY_A
1513	);
1514	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
1515
1516	return $results ? $results : array();
1517	}
1518
1519	/**
1520	* Search users with TOTP configured by name or email
1521	*
1522	* @param string $query Search query.
1523	* @param int $limit Max results.
1524	* @return array
1525	*/
1526	public function search_totp_users( $query, $limit = 10 ) {
1527	$table = $this->get_totp_table();
1528
1529	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i placeholder requires WP 6.2+.
1530	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1531	$results = $this->wpdb->get_results(
1532	$this->wpdb->prepare(
1533	"SELECT t.user_id, t.configured_at, t.last_used_at, u.display_name, u.user_email
1534	FROM %i AS t
1535	INNER JOIN %i AS u ON t.user_id = u.ID
1536	WHERE t.is_configured = 1
1537	AND (u.display_name LIKE %s OR u.user_email LIKE %s OR u.user_login LIKE %s)
1538	ORDER BY u.display_name ASC
1539	LIMIT %d",
1540	$table,
1541	$this->wpdb->users,
1542	'%' . $this->wpdb->esc_like( $query ) . '%',
1543	'%' . $this->wpdb->esc_like( $query ) . '%',
1544	'%' . $this->wpdb->esc_like( $query ) . '%',
1545	$limit
1546	),
1547	ARRAY_A
1548	);
1549	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
1550
1551	return $results ? $results : array();
1552	}
1553	}