vigilante / includes / class-database.php

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

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