← All changes | includes/class-database.php +324 -23 2.11.3 → 2.11.12 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	*/
		@@ -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,10 +409,9 @@
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	/**
384	417	* Schema changes and purges of the 2.11.0 security release
		@@ -396,10 +429,40 @@
396	429	* hash from now on, so any pending code would fail; they expire in
397	430	* minutes and a new one is a click away.
398	431	*
399	432	* @since 2.11.0
	433	+ *
	434	+ * @return bool True when it ran, false when it had already run.
400	435	*/
401	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	+
402	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.
403	466	$this->wpdb->query(
404	467	$this->wpdb->prepare( 'DELETE FROM %i', $this->get_2fa_devices_table() )
405	468	);
		@@ -406,8 +469,12 @@
406	469	$this->wpdb->query(
407	470	$this->wpdb->prepare( 'DELETE FROM %i', $this->get_2fa_codes_table() )
408	471	);
409	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;
410	477	}
411	478
412	479	/**
413	480	* Drop all plugin tables
		@@ -431,8 +498,9 @@
431	498	}
432	499	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
433	500
434	501	delete_option( self::DB_VERSION_OPTION );
	502	+ delete_option( self::PURGE_2_11_0_OPTION );
435	503
436	504	return true;
437	505	}
438	506
		@@ -1108,10 +1176,15 @@
1108	1176	'code' => $code,
1109	1177	'expires_at' => $expires_at,
1110	1178	'attempts' => 0,
1111	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 ),
1112	1185	),
1113		- array( '%d', '%s', '%s', '%d', '%d' )
	1186	+ array( '%d', '%s', '%s', '%d', '%d', '%s' )
1114	1187	);
1115	1188
1116	1189	return $result ? $this->wpdb->insert_id : false;
1117	1190	}
		@@ -1420,13 +1493,152 @@
1420	1493	* @param int $user_id User ID.
1421	1494	* @return array|null
1422	1495	*/
1423	1496	public function get_totp_data( $user_id ) {
1424		- $table = $this->get_totp_table();
	1497	+ return $this->get_totp_row( $this->totp_table_for_user( $user_id ), $user_id );
	1498	+ }
1425	1499
	1500	+ /**
	1501	+ * The TOTP table that holds this account's enrolment
	1502	+ *
	1503	+ * On a network the enrolment has to be visible wherever the login arrives.
	1504	+ * This table carries the blog prefix, so an account enrolled on the main site
	1505	+ * had no row on a subsite: the "not set up yet" branch of
	1506	+ * check_2fa_requirement() let the login through and even wrote a grace
	1507	+ * placeholder there, and the cookie that came out was valid across the whole
	1508	+ * network.
	1509	+ *
	1510	+ * Every read AND every write goes through here, which is the part the first
	1511	+ * attempt got wrong: falling back only on the read meant verify_backup_code()
	1512	+ * found the code on the main site and then wrote the shortened list to the
	1513	+ * local table, where there is no row. wpdb::update() touches nothing, returns
	1514	+ * 0, and the caller reads that as success, so a single-use backup code stayed
	1515	+ * usable for ever. Found by the cross review of 2.11.10.
	1516	+ *
	1517	+ * A configured local row always wins, so an enrolment made on a subsite is
	1518	+ * never lost or overwritten.
	1519	+ *
	1520	+ * The search does not stop at the main site, and that is the second
	1521	+ * correction: looking only local then main left an account enrolled on one
	1522	+ * subsite reading as "not set up yet" from the main site and from every
	1523	+ * other subsite, which is the very branch that lets the login through. It
	1524	+ * did not show while the email class was registering on every network and
	1525	+ * catching those logins, and it would have become a live bypass the moment
	1526	+ * that was put right, which is exactly what this release also does. The two
	1527	+ * were found together by the second cross review of 2.11.10 and are fixed
	1528	+ * together on purpose: fixing one alone opens the other.
	1529	+ *
	1530	+ * @since 2.11.10
	1531	+ *
	1532	+ * @param int $user_id User ID.
	1533	+ * @return string Table name.
	1534	+ */
	1535	+ private function totp_table_for_user( $user_id ) {
	1536	+ $local = $this->get_totp_table();
	1537	+
	1538	+ if ( ! is_multisite() ) {
	1539	+ return $local;
	1540	+ }
	1541	+
	1542	+ $row = $this->get_totp_row( $local, $user_id );
	1543	+
	1544	+ if ( $row && ! empty( $row['is_configured'] ) ) {
	1545	+ return $local;
	1546	+ }
	1547	+
	1548	+ /*
	1549	+ * Where the enrolment is, asked of the account itself. User meta is
	1550	+ * network-global, so this one row answers from any site of the network,
	1551	+ * whatever its size and whoever the account is.
	1552	+ */
	1553	+ $marked = (int) get_user_meta( $user_id, 'vigilante_totp_site', true );
	1554	+
	1555	+ if ( $marked > 0 ) {
	1556	+ $table = $this->wpdb->get_blog_prefix( $marked ) . $this->two_factor_totp_table;
	1557	+ $candidate = ( $table === $local ) ? $row : $this->get_totp_row( $table, $user_id );
	1558	+
	1559	+ if ( $candidate && ! empty( $candidate['is_configured'] ) ) {
	1560	+ return $table;
	1561	+ }
	1562	+ }
	1563	+
	1564	+ /*
	1565	+ * No marker: an enrolment made before this version, or one whose site is
	1566	+ * gone. Searched once, the old way, and written down when found so the
	1567	+ * search never happens again for this account.
	1568	+ */
	1569	+ foreach ( $this->totp_legacy_blog_ids( $user_id ) as $blog_id ) {
	1570	+ $table = $this->wpdb->get_blog_prefix( $blog_id ) . $this->two_factor_totp_table;
	1571	+
	1572	+ if ( $table === $local ) {
	1573	+ continue;
	1574	+ }
	1575	+
	1576	+ $candidate = $this->get_totp_row( $table, $user_id );
	1577	+
	1578	+ if ( $candidate && ! empty( $candidate['is_configured'] ) ) {
	1579	+ $this->remember_totp_site( $user_id, (int) $blog_id );
	1580	+ return $table;
	1581	+ }
	1582	+ }
	1583	+
	1584	+ return $local;
	1585	+ }
	1586	+
	1587	+ /**
	1588	+ * Where an enrolment made before the marker existed could live
	1589	+ *
	1590	+ * Only for accounts with no vigilante_totp_site meta yet, and only until the
	1591	+ * first time one is found, because finding it writes the marker. The main
	1592	+ * site first, since that is where a network that configures two factor once
	1593	+ * sets it up, then the sites the account belongs to, then the rest of the
	1594	+ * network for a super administrator, who is asked for a second factor by
	1595	+ * every site and is a member of almost none.
	1596	+ *
	1597	+ * @since 2.11.10
	1598	+ *
	1599	+ * @param int $user_id User ID.
	1600	+ * @return int[] Blog IDs.
	1601	+ */
	1602	+ private function totp_legacy_blog_ids( $user_id ) {
	1603	+ $ids = array( (int) get_main_site_id() );
	1604	+
	1605	+ foreach ( get_blogs_of_user( $user_id ) as $blog ) {
	1606	+ $ids[] = (int) $blog->userblog_id;
	1607	+ }
	1608	+
	1609	+ if ( is_super_admin( $user_id ) ) {
	1610	+ foreach ( get_sites( array( 'fields' => 'ids', 'number' => 200 ) ) as $blog_id ) {
	1611	+ $ids[] = (int) $blog_id;
	1612	+ }
	1613	+ }
	1614	+
	1615	+ return array_values( array_unique( $ids ) );
	1616	+ }
	1617	+
	1618	+ /**
	1619	+ * One TOTP row from a given table
	1620	+ *
	1621	+ * @since 2.11.10
	1622	+ *
	1623	+ * @param string $table Table name.
	1624	+ * @param int $user_id User ID.
	1625	+ * @return array|null
	1626	+ */
	1627	+ private function get_totp_row( $table, $user_id ) {
	1628	+ /*
	1629	+ * The table asked for may not exist: on a network Vigilant can have been
	1630	+ * activated on some sites and not on others, and this is called with the
	1631	+ * candidate tables of every site the account belongs to. Asking for a
	1632	+ * table that is not there would print a database error on the login
	1633	+ * page, so errors are silenced for the duration and a missing table
	1634	+ * reads as what it means, no enrolment there.
	1635	+ */
	1636	+ $suppress = $this->wpdb->suppress_errors( true );
	1637	+
1426	1638	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i placeholder requires WP 6.2+.
1427	1639	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1428		- return $this->wpdb->get_row(
	1640	+ $row = $this->wpdb->get_row(
1429	1641	$this->wpdb->prepare(
1430	1642	'SELECT * FROM %i WHERE user_id = %d LIMIT 1',
1431	1643	$table,
1432	1644	$user_id
		@@ -1433,8 +1645,12 @@
1433	1645	),
1434	1646	ARRAY_A
1435	1647	);
1436	1648	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
	1649	+
	1650	+ $this->wpdb->suppress_errors( $suppress );
	1651	+
	1652	+ return $row;
1437	1653	}
1438	1654
1439	1655	/**
1440	1656	* Create TOTP placeholder row (grace period tracking)
		@@ -1443,9 +1659,9 @@
1443	1659	* @param string $grace_expires Grace period expiry datetime.
1444	1660	* @return bool
1445	1661	*/
1446	1662	public function create_totp_placeholder( $user_id, $grace_expires ) {
1447		- $table = $this->get_totp_table();
	1663	+ $table = $this->totp_table_for_user( $user_id );
1448	1664
1449	1665	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
1450	1666	return false !== $this->wpdb->replace(
1451	1667	$table,
		@@ -1466,13 +1682,13 @@
1466	1682	* @param string $encrypted Encrypted secret.
1467	1683	* @return bool
1468	1684	*/
1469	1685	public function save_totp_data( $user_id, $encrypted ) {
1470		- $table = $this->get_totp_table();
	1686	+ $table = $this->totp_table_for_user( $user_id );
1471	1687	$now = current_time( 'mysql', true );
1472	1688
1473	1689	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
1474		- return false !== $this->wpdb->replace(
	1690	+ $saved = false !== $this->wpdb->replace(
1475	1691	$table,
1476	1692	array(
1477	1693	'user_id' => $user_id,
1478	1694	'secret' => $encrypted,
		@@ -1481,11 +1697,92 @@
1481	1697	'grace_period_expires' => null,
1482	1698	),
1483	1699	array( '%d', '%s', '%d', '%s', '%s' )
1484	1700	);
	1701	+
	1702	+ if ( $saved ) {
	1703	+ $this->remember_totp_site( $user_id, $this->blog_id_of_totp_table( $table ) );
	1704	+ }
	1705	+
	1706	+ return $saved;
1485	1707	}
1486	1708
1487	1709	/**
	1710	+ * Whether this account has an enrolment anywhere in the network
	1711	+ *
	1712	+ * With the account's vigilante_totp_site marker in place this is one or two
	1713	+ * queries: the local table, and the one the marker points at. Without it,
	1714	+ * which is every account with no enrolment at all, totp_table_for_user() goes
	1715	+ * on to search the main site, the account's own sites and, for a super
	1716	+ * administrator, up to 200 more, and it does so every time, because the
	1717	+ * marker is only written once an enrolment is found. It runs at login and,
	1718	+ * since 2.11.10, from the dashboard hooks of the TOTP class too; that is why
	1719	+ * those ask for the election before reading the row (2.11.11).
	1720	+ *
	1721	+ * @since 2.11.10
	1722	+ *
	1723	+ * @param int $user_id User ID.
	1724	+ * @return bool
	1725	+ */
	1726	+ public function has_totp_enrolment( $user_id ) {
	1727	+ $row = $this->get_totp_data( $user_id );
	1728	+
	1729	+ return ( $row && ! empty( $row['is_configured'] ) );
	1730	+ }
	1731	+
	1732	+ /**
	1733	+ * The blog a TOTP table belongs to
	1734	+ *
	1735	+ * @since 2.11.10
	1736	+ *
	1737	+ * @param string $table Table name.
	1738	+ * @return int Blog ID, 0 when it cannot be told.
	1739	+ */
	1740	+ private function blog_id_of_totp_table( $table ) {
	1741	+ if ( ! is_multisite() ) {
	1742	+ return 0;
	1743	+ }
	1744	+
	1745	+ $base = $this->wpdb->base_prefix . $this->two_factor_totp_table;
	1746	+
	1747	+ if ( $table === $base ) {
	1748	+ return (int) get_main_site_id();
	1749	+ }
	1750	+
	1751	+ if ( 1 === preg_match( '/^' . preg_quote( $this->wpdb->base_prefix, '/' ) . '(\d+)_' . preg_quote( $this->two_factor_totp_table, '/' ) . '$/', $table, $m ) ) {
	1752	+ return (int) $m[1];
	1753	+ }
	1754	+
	1755	+ return 0;
	1756	+ }
	1757	+
	1758	+ /**
	1759	+ * Write down which site holds this account's enrolment
	1760	+ *
	1761	+ * User meta is network-global, so one row says where the enrolment is from
	1762	+ * anywhere. That is what makes the lookup exact instead of a search: the
	1763	+ * first version walked the main site plus the account's own sites, capped at
	1764	+ * 25, and any enrolment outside that set read as "not set up yet", which is
	1765	+ * the branch that lets the login through. The third cross review of 2.11.10
	1766	+ * measured all three ways out of it: a network with more sites than the cap,
	1767	+ * an enrolment on a site the account was later removed from, and a super
	1768	+ * administrator, who is asked for a second factor by every site of the
	1769	+ * network and is a member of almost none.
	1770	+ *
	1771	+ * @since 2.11.10
	1772	+ *
	1773	+ * @param int $user_id User ID.
	1774	+ * @param int $blog_id Blog the enrolment was written to.
	1775	+ */
	1776	+ private function remember_totp_site( $user_id, $blog_id ) {
	1777	+ if ( ! is_multisite() || $blog_id < 1 ) {
	1778	+ return;
	1779	+ }
	1780	+
	1781	+ update_user_meta( $user_id, 'vigilante_totp_site', (int) $blog_id );
	1782	+ }
	1783	+
	1784	+ /**
1488	1785	* Store backup codes for a user
1489	1786	*
1490	1787	* @param int $user_id User ID.
1491	1788	* @param string $hashed_codes JSON-encoded hashed codes.
		@@ -1491,9 +1788,9 @@
1491	1788	* @param string $hashed_codes JSON-encoded hashed codes.
1492	1789	* @return bool
1493	1790	*/
1494	1791	public function store_totp_backup_codes( $user_id, $hashed_codes ) {
1495		- $table = $this->get_totp_table();
	1792	+ $table = $this->totp_table_for_user( $user_id );
1496	1793
1497	1794	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1498	1795	return false !== $this->wpdb->update(
1499	1796	$table,
		@@ -1510,9 +1807,9 @@
1510	1807	* @param int $user_id User ID.
1511	1808	* @return bool
1512	1809	*/
1513	1810	public function update_totp_last_used( $user_id ) {
1514		- $table = $this->get_totp_table();
	1811	+ $table = $this->totp_table_for_user( $user_id );
1515	1812	$now = current_time( 'mysql', true );
1516	1813
1517	1814	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1518	1815	return false !== $this->wpdb->update(
		@@ -1530,9 +1827,13 @@
1530	1827	* @param int $user_id User ID.
1531	1828	* @return bool
1532	1829	*/
1533	1830	public function reset_totp_data( $user_id ) {
1534		- $table = $this->get_totp_table();
	1831	+ $table = $this->totp_table_for_user( $user_id );
	1832	+
	1833	+ // The marker goes with the row it points at, or the next login would look
	1834	+ // for an enrolment that is no longer there.
	1835	+ delete_user_meta( $user_id, 'vigilante_totp_site' );
1535	1836
1536	1837	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1537	1838	return false !== $this->wpdb->delete(
1538	1839	$table,