← All changes | includes/class-database.php +148 -28 2.9.5 → 2.11.8 View file →

		@@ -11,10 +11,8 @@
11	11	if ( ! defined( 'ABSPATH' ) ) {
12	12	exit;
13	13	}
14	14
15		-// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- All queries use $wpdb->prepare() with validated parameters
16		-
17	15	/**
18	16	* Class Vigilante_Database
19	17	*
20	18	* Manages custom database tables
		@@ -31,8 +29,18 @@
31	29	*/
32	30	const DB_VERSION_OPTION = 'vigilante_db_version';
33	31
34	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	+ /**
35	43	* Activity log table name (without prefix)
36	44	*
37	45	* @var string
38	46	*/
		@@ -105,18 +113,8 @@
105	113	return $this->wpdb->prefix . $table;
106	114	}
107	115
108	116	/**
109		- * Get escaped table name for use in SQL queries
110		- *
111		- * @param string $table Full table name.
112		- * @return string Escaped table name with backticks.
113		- */
114		- private function esc_table( $table ) {
115		- return '`' . esc_sql( $table ) . '`';
116		- }
117		-
118		- /**
119	117	* Get activity log table name
120	118	*
121	119	* @return string
122	120	*/
		@@ -264,9 +262,9 @@
264	262	// 2FA Codes table
265	263	$two_factor_codes_sql = "CREATE TABLE {$this->get_2fa_codes_table()} (
266	264	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
267	265	user_id bigint(20) unsigned NOT NULL,
268		- code varchar(6) NOT NULL,
	266	+ code varchar(64) NOT NULL,
269	267	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
270	268	expires_at datetime NOT NULL,
271	269	attempts int(11) unsigned DEFAULT 0,
272	270	used tinyint(1) DEFAULT 0,
		@@ -319,15 +317,49 @@
319	317	) $charset_collate;";
320	318
321	319	dbDelta( $two_factor_totp_sql );
322	320
323		- // Store database version
324		- update_option( self::DB_VERSION_OPTION, self::DB_VERSION );
	321	+ $this->store_schema_version();
325	322
326	323	return $result;
327	324	}
328	325
329	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	+ /**
330	362	* Check if tables need to be updated
331	363	*
332	364	* @return bool True if update needed.
333	365	*/
		@@ -344,13 +376,13 @@
344	376	public function run_migrations() {
345	377	$current_version = get_option( self::DB_VERSION_OPTION, '0' );
346	378
347	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().
348	381	if ( version_compare( $current_version, '1.3.0', '<' ) ) {
349	382	$table = $this->get_activity_log_table();
350	383
351	384	// Check if column already exists
352		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
353	385	$column_exists = $this->wpdb->get_results(
354	386	$this->wpdb->prepare(
355	387	'SHOW COLUMNS FROM %i LIKE %s',
356	388	$table,
		@@ -358,9 +390,8 @@
358	390	)
359	391	);
360	392
361	393	if ( empty( $column_exists ) ) {
362		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
363	394	$this->wpdb->query(
364	395	$this->wpdb->prepare(
365	396	'ALTER TABLE %i ADD COLUMN request_method varchar(10) DEFAULT %s AFTER user_agent',
366	397	$table,
		@@ -368,9 +399,8 @@
368	399	)
369	400	);
370	401
371	402	// Add index
372		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
373	403	$this->wpdb->query(
374	404	$this->wpdb->prepare(
375	405	'ALTER TABLE %i ADD KEY request_method (request_method)',
376	406	$table
		@@ -377,14 +407,77 @@
377	407	)
378	408	);
379	409	}
380	410	}
	411	+ // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
381	412
382		- // Update stored version
383		- update_option( self::DB_VERSION_OPTION, self::DB_VERSION );
	413	+ $this->store_schema_version();
384	414	}
385	415
386	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	+ /**
387	480	* Drop all plugin tables
388	481	*
389	482	* @return bool
390	483	*/
		@@ -402,13 +495,14 @@
402	495
403	496	foreach ( $tables as $table ) {
404	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
405	498	}
	499	+ // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
406	500
407	501	delete_option( self::DB_VERSION_OPTION );
	502	+ delete_option( self::PURGE_2_11_0_OPTION );
408	503
409	504	return true;
410		- // phpcs:enable
411	505	}
412	506
413	507	// =========================================================================
414	508	// ACTIVITY LOG METHODS
		@@ -636,18 +730,35 @@
636	730	$ip_address = sanitize_text_field( $ip_address );
637	731	$username = sanitize_user( $username );
638	732	$status = sanitize_key( $status );
639	733	$user_agent = isset( $_SERVER['HTTP_USER_AGENT'] ) ? sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : '';
640		- $now = current_time( 'mysql' );
641	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	+
642	746	// Check if record exists for this IP + username
643	747	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
644	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 );
645	749
646	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	+
647	758	// Update existing record
648	759	$data = array(
649		- 'status' => $status,
	760	+ 'status' => $locked ? 'lockout' : $status,
650	761	'attempt_count' => $existing['attempt_count'] + 1,
651	762	'last_attempt' => $now,
652	763	'user_agent' => $user_agent,
653	764	);
		@@ -742,9 +853,10 @@
742	853	* @return array|false Lockout data or false if not locked.
743	854	*/
744	855	public function is_locked_out( $ip_address ) {
745	856	$table = $this->get_login_attempts_table();
746		- $now = current_time( 'mysql' );
	857	+ // UTC: lockout_until is written with gmdate(). See record_login_attempt() (S18).
	858	+ $now = current_time( 'mysql', true );
747	859
748	860	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
749	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 );
750	862
		@@ -770,9 +882,10 @@
770	882	* @return array List of locked IPs with their data.
771	883	*/
772	884	public function get_active_lockouts() {
773	885	$table = $this->get_login_attempts_table();
774		- $now = current_time( 'mysql' );
	886	+ // UTC: lockout_until is written with gmdate(). See record_login_attempt() (S18).
	887	+ $now = current_time( 'mysql', true );
775	888
776	889	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
777	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 ) );
778	891
		@@ -817,10 +930,11 @@
817	930	public function cleanup_old_login_attempts( $hours = 24 ) {
818	931	$table = $this->get_login_attempts_table();
819	932	$date = gmdate( 'Y-m-d H:i:s', strtotime( "-{$hours} hours" ) );
820	933
	934	+ // UTC on both sides, like the rest of this table since 2.11.0 (S18).
821	935	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
822		- $deleted = $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM %i WHERE last_attempt < %s AND (lockout_until IS NULL OR lockout_until < %s)', $table, $date, current_time( 'mysql' ) ) );
	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 ) ) );
823	937
824	938	return $deleted ? $deleted : 0;
825	939	}
826	940
		@@ -830,9 +944,10 @@
830	944	* @return array
831	945	*/
832	946	public function get_locked_out_ips() {
833	947	$table = $this->get_login_attempts_table();
834		- $now = current_time( 'mysql' );
	948	+ // UTC: lockout_until is written with gmdate(). See record_login_attempt() (S18).
	949	+ $now = current_time( 'mysql', true );
835	950
836	951	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
837	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 );
838	953
		@@ -1061,10 +1176,15 @@
1061	1176	'code' => $code,
1062	1177	'expires_at' => $expires_at,
1063	1178	'attempts' => 0,
1064	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 ),
1065	1185	),
1066		- array( '%d', '%s', '%s', '%d', '%d' )
	1186	+ array( '%d', '%s', '%s', '%d', '%d', '%s' )
1067	1187	);
1068	1188
1069	1189	return $result ? $this->wpdb->insert_id : false;
1070	1190	}