vigilante / includes / class-database.php

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

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