← All changes | includes/class-database.php +242 -19 2.11.6 → 2.11.12 View file →

		@@ -113,18 +113,8 @@
113	113	return $this->wpdb->prefix . $table;
114	114	}
115	115
116	116	/**
117		- * Get escaped table name for use in SQL queries
118		- *
119		- * @param string $table Full table name.
120		- * @return string Escaped table name with backticks.
121		- */
122		- private function esc_table( $table ) {
123		- return '`' . esc_sql( $table ) . '`';
124		- }
125		-
126		- /**
127	117	* Get activity log table name
128	118	*
129	119	* @return string
130	120	*/
		@@ -1186,10 +1176,15 @@
1186	1176	'code' => $code,
1187	1177	'expires_at' => $expires_at,
1188	1178	'attempts' => 0,
1189	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 ),
1190	1185	),
1191		- array( '%d', '%s', '%s', '%d', '%d' )
	1186	+ array( '%d', '%s', '%s', '%d', '%d', '%s' )
1192	1187	);
1193	1188
1194	1189	return $result ? $this->wpdb->insert_id : false;
1195	1190	}
		@@ -1498,13 +1493,152 @@
1498	1493	* @param int $user_id User ID.
1499	1494	* @return array|null
1500	1495	*/
1501	1496	public function get_totp_data( $user_id ) {
1502		- $table = $this->get_totp_table();
	1497	+ return $this->get_totp_row( $this->totp_table_for_user( $user_id ), $user_id );
	1498	+ }
1503	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	+
1504	1638	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i placeholder requires WP 6.2+.
1505	1639	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1506		- return $this->wpdb->get_row(
	1640	+ $row = $this->wpdb->get_row(
1507	1641	$this->wpdb->prepare(
1508	1642	'SELECT * FROM %i WHERE user_id = %d LIMIT 1',
1509	1643	$table,
1510	1644	$user_id
		@@ -1511,8 +1645,12 @@
1511	1645	),
1512	1646	ARRAY_A
1513	1647	);
1514	1648	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
	1649	+
	1650	+ $this->wpdb->suppress_errors( $suppress );
	1651	+
	1652	+ return $row;
1515	1653	}
1516	1654
1517	1655	/**
1518	1656	* Create TOTP placeholder row (grace period tracking)
		@@ -1521,9 +1659,9 @@
1521	1659	* @param string $grace_expires Grace period expiry datetime.
1522	1660	* @return bool
1523	1661	*/
1524	1662	public function create_totp_placeholder( $user_id, $grace_expires ) {
1525		- $table = $this->get_totp_table();
	1663	+ $table = $this->totp_table_for_user( $user_id );
1526	1664
1527	1665	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
1528	1666	return false !== $this->wpdb->replace(
1529	1667	$table,
		@@ -1544,13 +1682,13 @@
1544	1682	* @param string $encrypted Encrypted secret.
1545	1683	* @return bool
1546	1684	*/
1547	1685	public function save_totp_data( $user_id, $encrypted ) {
1548		- $table = $this->get_totp_table();
	1686	+ $table = $this->totp_table_for_user( $user_id );
1549	1687	$now = current_time( 'mysql', true );
1550	1688
1551	1689	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
1552		- return false !== $this->wpdb->replace(
	1690	+ $saved = false !== $this->wpdb->replace(
1553	1691	$table,
1554	1692	array(
1555	1693	'user_id' => $user_id,
1556	1694	'secret' => $encrypted,
		@@ -1559,11 +1697,92 @@
1559	1697	'grace_period_expires' => null,
1560	1698	),
1561	1699	array( '%d', '%s', '%d', '%s', '%s' )
1562	1700	);
	1701	+
	1702	+ if ( $saved ) {
	1703	+ $this->remember_totp_site( $user_id, $this->blog_id_of_totp_table( $table ) );
	1704	+ }
	1705	+
	1706	+ return $saved;
1563	1707	}
1564	1708
1565	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	+ /**
1566	1785	* Store backup codes for a user
1567	1786	*
1568	1787	* @param int $user_id User ID.
1569	1788	* @param string $hashed_codes JSON-encoded hashed codes.
		@@ -1569,9 +1788,9 @@
1569	1788	* @param string $hashed_codes JSON-encoded hashed codes.
1570	1789	* @return bool
1571	1790	*/
1572	1791	public function store_totp_backup_codes( $user_id, $hashed_codes ) {
1573		- $table = $this->get_totp_table();
	1792	+ $table = $this->totp_table_for_user( $user_id );
1574	1793
1575	1794	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1576	1795	return false !== $this->wpdb->update(
1577	1796	$table,
		@@ -1588,9 +1807,9 @@
1588	1807	* @param int $user_id User ID.
1589	1808	* @return bool
1590	1809	*/
1591	1810	public function update_totp_last_used( $user_id ) {
1592		- $table = $this->get_totp_table();
	1811	+ $table = $this->totp_table_for_user( $user_id );
1593	1812	$now = current_time( 'mysql', true );
1594	1813
1595	1814	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1596	1815	return false !== $this->wpdb->update(
		@@ -1608,9 +1827,13 @@
1608	1827	* @param int $user_id User ID.
1609	1828	* @return bool
1610	1829	*/
1611	1830	public function reset_totp_data( $user_id ) {
1612		- $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' );
1613	1836
1614	1837	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
1615	1838	return false !== $this->wpdb->delete(
1616	1839	$table,