vigilante / includes / class-database.php

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

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