| @@ -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 | } |
| @@ -1373,13 +1493,152 @@ | ||
| 1373 | 1493 | * @param int $user_id User ID. |
| 1374 | 1494 | * @return array|null |
| 1375 | 1495 | */ |
| 1376 | 1496 | public function get_totp_data( $user_id ) { |
| 1377 | - $table = $this->get_totp_table(); | |
| 1497 | + return $this->get_totp_row( $this->totp_table_for_user( $user_id ), $user_id ); | |
| 1498 | + } | |
| 1378 | 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 | + | |
| 1379 | 1638 | // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- %i placeholder requires WP 6.2+. |
| 1380 | 1639 | // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching |
| 1381 | - return $this->wpdb->get_row( | |
| 1640 | + $row = $this->wpdb->get_row( | |
| 1382 | 1641 | $this->wpdb->prepare( |
| 1383 | 1642 | 'SELECT * FROM %i WHERE user_id = %d LIMIT 1', |
| 1384 | 1643 | $table, |
| 1385 | 1644 | $user_id |
| @@ -1386,8 +1645,12 @@ | ||
| 1386 | 1645 | ), |
| 1387 | 1646 | ARRAY_A |
| 1388 | 1647 | ); |
| 1389 | 1648 | // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared |
| 1649 | + | |
| 1650 | + $this->wpdb->suppress_errors( $suppress ); | |
| 1651 | + | |
| 1652 | + return $row; | |
| 1390 | 1653 | } |
| 1391 | 1654 | |
| 1392 | 1655 | /** |
| 1393 | 1656 | * Create TOTP placeholder row (grace period tracking) |
| @@ -1396,9 +1659,9 @@ | ||
| 1396 | 1659 | * @param string $grace_expires Grace period expiry datetime. |
| 1397 | 1660 | * @return bool |
| 1398 | 1661 | */ |
| 1399 | 1662 | public function create_totp_placeholder( $user_id, $grace_expires ) { |
| 1400 | - $table = $this->get_totp_table(); | |
| 1663 | + $table = $this->totp_table_for_user( $user_id ); | |
| 1401 | 1664 | |
| 1402 | 1665 | // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery |
| 1403 | 1666 | return false !== $this->wpdb->replace( |
| 1404 | 1667 | $table, |
| @@ -1419,13 +1682,13 @@ | ||
| 1419 | 1682 | * @param string $encrypted Encrypted secret. |
| 1420 | 1683 | * @return bool |
| 1421 | 1684 | */ |
| 1422 | 1685 | public function save_totp_data( $user_id, $encrypted ) { |
| 1423 | - $table = $this->get_totp_table(); | |
| 1686 | + $table = $this->totp_table_for_user( $user_id ); | |
| 1424 | 1687 | $now = current_time( 'mysql', true ); |
| 1425 | 1688 | |
| 1426 | 1689 | // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery |
| 1427 | - return false !== $this->wpdb->replace( | |
| 1690 | + $saved = false !== $this->wpdb->replace( | |
| 1428 | 1691 | $table, |
| 1429 | 1692 | array( |
| 1430 | 1693 | 'user_id' => $user_id, |
| 1431 | 1694 | 'secret' => $encrypted, |
| @@ -1434,11 +1697,92 @@ | ||
| 1434 | 1697 | 'grace_period_expires' => null, |
| 1435 | 1698 | ), |
| 1436 | 1699 | array( '%d', '%s', '%d', '%s', '%s' ) |
| 1437 | 1700 | ); |
| 1701 | + | |
| 1702 | + if ( $saved ) { | |
| 1703 | + $this->remember_totp_site( $user_id, $this->blog_id_of_totp_table( $table ) ); | |
| 1704 | + } | |
| 1705 | + | |
| 1706 | + return $saved; | |
| 1438 | 1707 | } |
| 1439 | 1708 | |
| 1440 | 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 | + /** | |
| 1441 | 1785 | * Store backup codes for a user |
| 1442 | 1786 | * |
| 1443 | 1787 | * @param int $user_id User ID. |
| 1444 | 1788 | * @param string $hashed_codes JSON-encoded hashed codes. |
| @@ -1444,9 +1788,9 @@ | ||
| 1444 | 1788 | * @param string $hashed_codes JSON-encoded hashed codes. |
| 1445 | 1789 | * @return bool |
| 1446 | 1790 | */ |
| 1447 | 1791 | public function store_totp_backup_codes( $user_id, $hashed_codes ) { |
| 1448 | - $table = $this->get_totp_table(); | |
| 1792 | + $table = $this->totp_table_for_user( $user_id ); | |
| 1449 | 1793 | |
| 1450 | 1794 | // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching |
| 1451 | 1795 | return false !== $this->wpdb->update( |
| 1452 | 1796 | $table, |
| @@ -1463,9 +1807,9 @@ | ||
| 1463 | 1807 | * @param int $user_id User ID. |
| 1464 | 1808 | * @return bool |
| 1465 | 1809 | */ |
| 1466 | 1810 | public function update_totp_last_used( $user_id ) { |
| 1467 | - $table = $this->get_totp_table(); | |
| 1811 | + $table = $this->totp_table_for_user( $user_id ); | |
| 1468 | 1812 | $now = current_time( 'mysql', true ); |
| 1469 | 1813 | |
| 1470 | 1814 | // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching |
| 1471 | 1815 | return false !== $this->wpdb->update( |
| @@ -1483,9 +1827,13 @@ | ||
| 1483 | 1827 | * @param int $user_id User ID. |
| 1484 | 1828 | * @return bool |
| 1485 | 1829 | */ |
| 1486 | 1830 | public function reset_totp_data( $user_id ) { |
| 1487 | - $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' ); | |
| 1488 | 1836 | |
| 1489 | 1837 | // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching |
| 1490 | 1838 | return false !== $this->wpdb->delete( |
| 1491 | 1839 | $table, |
