← All changes | includes/class-database.php +145 -22 2.10.5 → 2.11.8 View file →

		@@ -29,8 +29,18 @@
29	29	*/
30	30	const DB_VERSION_OPTION = 'vigilante_db_version';
31	31
32	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	+ /**
33	43	* Activity log table name (without prefix)
34	44	*
35	45	* @var string
36	46	*/
		@@ -103,18 +113,8 @@
103	113	return $this->wpdb->prefix . $table;
104	114	}
105	115
106	116	/**
107		- * Get escaped table name for use in SQL queries
108		- *
109		- * @param string $table Full table name.
110		- * @return string Escaped table name with backticks.
111		- */
112		- private function esc_table( $table ) {
113		- return '`' . esc_sql( $table ) . '`';
114		- }
115		-
116		- /**
117	117	* Get activity log table name
118	118	*
119	119	* @return string
120	120	*/
		@@ -262,9 +262,9 @@
262	262	// 2FA Codes table
263	263	$two_factor_codes_sql = "CREATE TABLE {$this->get_2fa_codes_table()} (
264	264	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
265	265	user_id bigint(20) unsigned NOT NULL,
266		- code varchar(6) NOT NULL,
	266	+ code varchar(64) NOT NULL,
267	267	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
268	268	expires_at datetime NOT NULL,
269	269	attempts int(11) unsigned DEFAULT 0,
270	270	used tinyint(1) DEFAULT 0,
		@@ -317,15 +317,49 @@
317	317	) $charset_collate;";
318	318
319	319	dbDelta( $two_factor_totp_sql );
320	320
321		- // Store database version
322		- update_option( self::DB_VERSION_OPTION, self::DB_VERSION );
	321	+ $this->store_schema_version();
323	322
324	323	return $result;
325	324	}
326	325
327	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	+ /**
328	362	* Check if tables need to be updated
329	363	*
330	364	* @return bool True if update needed.
331	365	*/
		@@ -375,13 +409,75 @@
375	409	}
376	410	}
377	411	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
378	412
379		- // Update stored version
380		- update_option( self::DB_VERSION_OPTION, self::DB_VERSION );
	413	+ $this->store_schema_version();
381	414	}
382	415
383	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	+ /**
384	480	* Drop all plugin tables
385	481	*
386	482	* @return bool
387	483	*/
		@@ -402,8 +498,9 @@
402	498	}
403	499	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
404	500
405	501	delete_option( self::DB_VERSION_OPTION );
	502	+ delete_option( self::PURGE_2_11_0_OPTION );
406	503
407	504	return true;
408	505	}
409	506
		@@ -633,18 +730,35 @@
633	730	$ip_address = sanitize_text_field( $ip_address );
634	731	$username = sanitize_user( $username );
635	732	$status = sanitize_key( $status );
636	733	$user_agent = isset( $_SERVER['HTTP_USER_AGENT'] ) ? sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : '';
637		- $now = current_time( 'mysql' );
638	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	+
639	746	// Check if record exists for this IP + username
640	747	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
641	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 );
642	749
643	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	+
644	758	// Update existing record
645	759	$data = array(
646		- 'status' => $status,
	760	+ 'status' => $locked ? 'lockout' : $status,
647	761	'attempt_count' => $existing['attempt_count'] + 1,
648	762	'last_attempt' => $now,
649	763	'user_agent' => $user_agent,
650	764	);
		@@ -739,9 +853,10 @@
739	853	* @return array|false Lockout data or false if not locked.
740	854	*/
741	855	public function is_locked_out( $ip_address ) {
742	856	$table = $this->get_login_attempts_table();
743		- $now = current_time( 'mysql' );
	857	+ // UTC: lockout_until is written with gmdate(). See record_login_attempt() (S18).
	858	+ $now = current_time( 'mysql', true );
744	859
745	860	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
746	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 );
747	862
		@@ -767,9 +882,10 @@
767	882	* @return array List of locked IPs with their data.
768	883	*/
769	884	public function get_active_lockouts() {
770	885	$table = $this->get_login_attempts_table();
771		- $now = current_time( 'mysql' );
	886	+ // UTC: lockout_until is written with gmdate(). See record_login_attempt() (S18).
	887	+ $now = current_time( 'mysql', true );
772	888
773	889	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
774	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 ) );
775	891
		@@ -814,10 +930,11 @@
814	930	public function cleanup_old_login_attempts( $hours = 24 ) {
815	931	$table = $this->get_login_attempts_table();
816	932	$date = gmdate( 'Y-m-d H:i:s', strtotime( "-{$hours} hours" ) );
817	933
	934	+ // UTC on both sides, like the rest of this table since 2.11.0 (S18).
818	935	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
819		- $deleted = $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM %i WHERE last_attempt < %s AND (lockout_until IS NULL OR lockout_until < %s)', $table, $date, current_time( 'mysql' ) ) );
	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 ) ) );
820	937
821	938	return $deleted ? $deleted : 0;
822	939	}
823	940
		@@ -827,9 +944,10 @@
827	944	* @return array
828	945	*/
829	946	public function get_locked_out_ips() {
830	947	$table = $this->get_login_attempts_table();
831		- $now = current_time( 'mysql' );
	948	+ // UTC: lockout_until is written with gmdate(). See record_login_attempt() (S18).
	949	+ $now = current_time( 'mysql', true );
832	950
833	951	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
834	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 );
835	953
		@@ -1058,10 +1176,15 @@
1058	1176	'code' => $code,
1059	1177	'expires_at' => $expires_at,
1060	1178	'attempts' => 0,
1061	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 ),
1062	1185	),
1063		- array( '%d', '%s', '%s', '%d', '%d' )
	1186	+ array( '%d', '%s', '%s', '%d', '%d', '%s' )
1064	1187	);
1065	1188
1066	1189	return $result ? $this->wpdb->insert_id : false;
1067	1190	}