token and is // recorded/auditable (it previously reached the code context but was discarded at mint). // The column defaults to '' so existing rows and the current "acts as the approver's // full account" behaviour are unchanged; a map that replaces the token's capabilities // (listed ones set outright, every other one denied) is opt-in via the // aafm_oauth_token_capabilities filter. Bumping the version makes // aafm_maybe_upgrade_oauth_tables() re-run dbDelta so existing installs pick the change // up (additive - one nullable-defaulted column per table, no data migration). // v7 pins the lifecycle tables (codes, access-tokens) to ENGINE=InnoDB so the code-redemption // and refresh-rotation transactions can actually roll back. New installs get InnoDB from the // CREATE; dbDelta never changes an existing table's engine, so the bump re-runs the installer // once, which converts a pre-existing MyISAM lifecycle table with a guarded one-time ALTER // (aafm_oauth_enforce_lifecycle_engine()). The stored charset/collation is unchanged. // v8 adds an `is_agent_identity` column to the clients table: an operator-settable flag, // distinct from the existing user-level aafm_agent_user_marker_meta_key() marker, that marks // an OAuth client itself as an agent connection. Defaults to 0 for every existing row, so a // bump changes no client's flagged state. Additive, no data migration. // v9 adds six nullable columns to the consents table (paused_at, expires_at, last_used_at, // last_used_ip, preset, rate_per_min) for per-connection pause, end date, last use, access preset // and rate limit. NULL in every column means today's behaviour, so no existing row changes meaning // and there is no data migration. define( 'AAFM_OAUTH_SCHEMA_VERSION', '9' ); } /** * The unprefixed suffixes of the four OAuth tables. * * @return string[] */ function aafm_oauth_table_suffixes(): array { return array( 'aafm_oauth_clients', 'aafm_oauth_codes', 'aafm_oauth_access_tokens', 'aafm_oauth_consents', ); } /** * Create (or upgrade) the four OAuth tables, then record the schema version. * * Idempotent: dbDelta() only applies the diff, so repeat calls are no-ops once the * tables match. Mirrors aafm_install_activity_log() in includes/audit/log.php. * * @return bool True only when the schema version was stamped. */ function aafm_install_oauth_tables(): bool { global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $prefix = $wpdb->prefix; $charset_collate = $wpdb->get_charset_collate(); $clients = "CREATE TABLE {$prefix}aafm_oauth_clients ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, client_id VARCHAR(191) NOT NULL DEFAULT '', client_name VARCHAR(191) NOT NULL DEFAULT '', redirect_uris LONGTEXT NULL, grant_types LONGTEXT NULL, response_types LONGTEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, created_by_ip VARCHAR(45) NOT NULL DEFAULT '', is_active TINYINT(1) NOT NULL DEFAULT 1, is_agent_identity TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY client_id (client_id), KEY created_at (created_at) ) ENGINE=InnoDB {$charset_collate};"; $codes = "CREATE TABLE {$prefix}aafm_oauth_codes ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, code_hash VARCHAR(191) NOT NULL DEFAULT '', client_id VARCHAR(191) NOT NULL DEFAULT '', wp_user_id BIGINT UNSIGNED NOT NULL DEFAULT 0, redirect_uri TEXT NULL, code_challenge VARCHAR(191) NOT NULL DEFAULT '', resource VARCHAR(2048) NOT NULL DEFAULT '', scope VARCHAR(191) NOT NULL DEFAULT '', expires_at DATETIME NULL, used_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY code_hash (code_hash), KEY expires_at (expires_at), KEY client_id (client_id), KEY user_client (wp_user_id, client_id) ) ENGINE=InnoDB {$charset_collate};"; $access_tokens = "CREATE TABLE {$prefix}aafm_oauth_access_tokens ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, token_hash VARCHAR(191) NOT NULL DEFAULT '', refresh_hash VARCHAR(191) NOT NULL DEFAULT '', refresh_parent_id BIGINT UNSIGNED NOT NULL DEFAULT 0, client_id VARCHAR(191) NOT NULL DEFAULT '', wp_user_id BIGINT UNSIGNED NOT NULL DEFAULT 0, resource VARCHAR(2048) NOT NULL DEFAULT '', scope VARCHAR(191) NOT NULL DEFAULT '', expires_at DATETIME NULL, refresh_expires_at DATETIME NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY token_hash (token_hash), UNIQUE KEY refresh_hash (refresh_hash), KEY refresh_parent_id (refresh_parent_id), KEY client_id (client_id), KEY gc (is_active, refresh_expires_at), KEY user_client (wp_user_id, client_id) ) ENGINE=InnoDB {$charset_collate};"; $consents = "CREATE TABLE {$prefix}aafm_oauth_consents ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, wp_user_id BIGINT UNSIGNED NOT NULL DEFAULT 0, client_id VARCHAR(191) NOT NULL DEFAULT '', granted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, paused_at DATETIME NULL, expires_at DATETIME NULL, last_used_at DATETIME NULL, last_used_ip VARCHAR(45) NULL, preset VARCHAR(20) NULL, rate_per_min INT UNSIGNED NULL, PRIMARY KEY (id), UNIQUE KEY user_client (wp_user_id, client_id) ) ENGINE=InnoDB {$charset_collate};"; dbDelta( $clients ); dbDelta( $codes ); dbDelta( $access_tokens ); dbDelta( $consents ); $engine_ok = aafm_oauth_enforce_lifecycle_engine(); return aafm_oauth_finalize_schema( $engine_ok ); } /** * The lifecycle tables whose rows a multi-statement transaction spans. * * The code-redemption path (includes/oauth/rest.php) and the refresh rotation (tokens.php) both * wrap consume + mint in START TRANSACTION/ROLLBACK, which only rolls back on a transactional * engine. These two tables therefore must be InnoDB; clients and consents are single-statement * writes and are not enrolled here. * * @return string[] */ function aafm_oauth_lifecycle_table_suffixes(): array { return array( 'aafm_oauth_codes', 'aafm_oauth_access_tokens' ); } /** * Ensure the lifecycle tables use a transactional (InnoDB) engine. * * New installs get ENGINE=InnoDB straight from the CREATE, but dbDelta never changes an existing * table's engine, so a table first created on a MyISAM-default server stays MyISAM through every * upgrade. There the consume/deactivate UPDATE commits immediately and a later failing mint cannot * roll back, permanently burning an auth code or refresh token with no successor. So convert any * non-InnoDB lifecycle table with a guarded one-time ALTER. If the ALTER cannot take (a server * with no InnoDB support at all), record a bounded operational warning rather than silently * trusting an atomicity the engine cannot provide - the token endpoints keep working, they just * lose clean rollback on a rare mid-request failure until the host enables InnoDB. * * The return value gates the schema-version stamp in aafm_oauth_finalize_schema(): a confirmed * non-InnoDB table that could not be converted must NOT let the install record v7 as complete, or * both self-heal hooks early-return on the version match and the conversion never retries - the * table stays non-transactional and the warning transient silently expires within a day. * * @return bool True when every lifecycle table is transactional OR its engine is unverifiable - it * is InnoDB, was just converted to InnoDB, or reads as '' (absent, or a TEMPORARY * harness table information_schema does not list). False only when a table's engine * reads as a confirmed non-InnoDB value AND the ALTER left it that way. Unverifiable is * deliberately treated as "allowed", not "failed": blocking on '' would stop every * PHPUnit install (whose TEMPORARY tables always read '') from stamping its version. */ function aafm_oauth_enforce_lifecycle_engine(): bool { global $wpdb; $non_transactional = array(); foreach ( aafm_oauth_lifecycle_table_suffixes() as $suffix ) { $table = $wpdb->prefix . $suffix; $view = aafm_oauth_table_engine_view( $table ); // A SUCCESSFUL read of '' means the engine could not be determined: the table is absent, or // it is a TEMPORARY table (the PHPUnit harness form), which information_schema does not // list. Either way there is nothing to convert, nothing to warn about, and nothing to block // the version stamp on. This must be gated on $view['ok'] as well as // the value - a query that FAILED (not merely found nothing) used to read identically to // '' here, so a stale non-null 'InnoDB' string left over from an earlier iteration's // successful read of a DIFFERENT, already-converted table could make this branch wrongly // believe a genuinely MyISAM table was already InnoDB and skip it entirely, with no warning // ever recorded. A failed read now falls through to the same conversion-attempt branch a // confirmed non-InnoDB table takes; if the follow-up read inside // aafm_oauth_convert_table_to_innodb() also fails, the table lands in $non_transactional // and the run is correctly reported incomplete instead of silently believed fine. if ( $view['ok'] && ( '' === $view['value'] || 0 === strcasecmp( $view['value'], 'InnoDB' ) ) ) { continue; } if ( ! aafm_oauth_convert_table_to_innodb( $table ) ) { $non_transactional[] = $suffix; } } if ( empty( $non_transactional ) ) { delete_transient( 'aafm_oauth_engine_warning' ); return true; } // Bounded like the schema-error flag: a day-long note the next successful enforce clears. set_transient( 'aafm_oauth_engine_warning', implode( ',', $non_transactional ), DAY_IN_SECONDS ); return false; } /** * The storage engine of a table for the current blog, and whether the read itself succeeded. * * The information_schema view may omit a TEMPORARY table (the PHPUnit harness rewrites plugin * CREATE TABLE to that form) or a table that does not exist, so both read as a SUCCESSFUL query * whose value is '' - callers treat that as "cannot verify, do not act". A query that fails * outright is a different thing entirely and must not be folded into the * same '' signal - see aafm_wpdb_scalar()'s docblock for why $wpdb->query()'s own return value, * not a bare get_var(), is what tells the two apart. The table name is bound as a value here * (information_schema stores it as data), never interpolated. * * @param string $table Fully-prefixed table name (an internal constant). * @return array{ok:bool,value:string} ok is false when the query itself failed - value is not * trustworthy in that case. value is the engine name (e.g. 'InnoDB', 'MyISAM'), or '' * when the query succeeded but the table is absent/unlisted. */ function aafm_oauth_table_engine_view( string $table ): array { global $wpdb; $view = aafm_wpdb_scalar( $wpdb->prepare( 'SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s', $table ) ); return array( 'ok' => $view['ok'], 'value' => is_string( $view['value'] ) ? $view['value'] : '', ); } /** * The storage engine of a table for the current blog, or '' when it cannot be determined - either * a genuinely absent/TEMPORARY table, or the read itself failing. Callers that must tell those two * apart (a query failure is not the same thing as a confirmed-absent table) use * aafm_oauth_table_engine_view() directly instead, the way aafm_oauth_enforce_lifecycle_engine() * does. * * @param string $table Fully-prefixed table name (an internal constant). * @return string Engine name (e.g. 'InnoDB', 'MyISAM'), or '' when unknown. */ function aafm_oauth_table_engine( string $table ): string { return aafm_oauth_table_engine_view( $table )['value']; } /** * Convert a table to InnoDB with a guarded one-time ALTER, and report whether it is now InnoDB. * * A dbDelta run cannot do this (it never touches an existing table's engine), hence the explicit * ALTER. The %i placeholder quotes the identifier (an internal constant). * * @param string $table Fully-prefixed table name. * @return bool True when the table is InnoDB after the attempt. */ function aafm_oauth_convert_table_to_innodb( string $table ): bool { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ENGINE=InnoDB', $table ) ); return 0 === strcasecmp( aafm_oauth_table_engine( $table ), 'InnoDB' ); } /** * Admin notice: an OAuth lifecycle table is not on a transactional engine. * * Shown only while aafm_oauth_enforce_lifecycle_engine()'s bounded transient is set (it clears the * moment every lifecycle table is InnoDB), and only to users who can act on it. * * @return void */ function aafm_oauth_engine_admin_notice(): void { if ( ! current_user_can( 'activate_plugins' ) ) { return; } if ( false === get_transient( 'aafm_oauth_engine_warning' ) ) { return; } echo '

'; echo esc_html__( 'Agent Abilities for MCP could not move its OAuth token tables to a transactional (InnoDB) storage engine. OAuth keeps working, but a rare failure mid-request may not roll back cleanly. Ask your host to enable InnoDB.', 'agent-abilities-for-mcp' ); echo '

'; } add_action( 'admin_notices', 'aafm_oauth_engine_admin_notice' ); /** * Record the schema version, but only once the tables are verified to be really there. * * A dbDelta() run reports nothing when an ALTER it wanted is refused - a transient loss of the * ALTER privilege mid-upgrade, a full disk, a single failed DDL - so the tables can be left partially * upgraded while the call returns as if it succeeded. Stamping the version unconditionally then * makes aafm_maybe_upgrade_oauth_tables() early-return forever, so a half-migrated install (say a * codes/tokens insert that a missing `scope` column rejects) never self-heals and only a manual * option delete recovers it. So verify first: advance the stored version only when the four tables * and the latest columns are actually present. On failure leave the prior version in place - the * next admin or REST request retries the install - and raise a bounded admin notice rather than * silently advancing past a broken schema. * * The stamp is also gated on $engine_ok, the result of aafm_oauth_enforce_lifecycle_engine(): the * v7 migration exists to make the lifecycle tables transactional, so a confirmed non-InnoDB table * that could not be converted is as much a failed migration as a missing column, and stamping past * it would freeze the table on a non-transactional engine forever (both self-heal hooks match on the * version and stop retrying, and the engine warning transient expires within a day). An unverifiable * engine ('' - the TEMPORARY harness tables) reports $engine_ok = true, so tests still stamp normally. * A pure engine failure does not raise the schema-error notice: aafm_oauth_enforce_lifecycle_engine() * already sets its own bounded engine warning, so the table-shape notice would be misleading here. * * @param bool $engine_ok Whether the lifecycle tables are confirmed transactional (or unverifiable). * @return bool True only when the version was stamped. */ function aafm_oauth_finalize_schema( bool $engine_ok ): bool { $schema_ok = aafm_oauth_schema_verify(); $stamped = false; if ( $schema_ok ) { delete_transient( 'aafm_oauth_schema_error' ); } else { // Bounded: a day-long flag the next successful verify clears, so the notice shows only while // the schema is genuinely behind and disappears the moment the self-heal lands. set_transient( 'aafm_oauth_schema_error', time(), DAY_IN_SECONDS ); } if ( $schema_ok && $engine_ok ) { // Verified, not a bare update_option(): aafm_maybe_upgrade_oauth_tables() // below trusts this stamp to decide whether the installer needs to run again, so a write // that a persistent cache silently no-ops must not be allowed to look like it landed. // A fresh install is seeded with Administrators as the only approvers before its first stamp, // and the stamp waits for the seed: an unstamped install is the one state the seed can still // recognise as fresh (aafm_seed_fresh_install_defaults()). $seeded = aafm_seed_fresh_install_defaults(); $stamped = $seeded && aafm_update_option_verified( 'aafm_oauth_schema_version', AAFM_OAUTH_SCHEMA_VERSION ); if ( ! $stamped ) { // This return value used to be discarded. The schema itself is // genuinely fine, but with the version left un-stamped, aafm_maybe_upgrade_oauth_tables() // reruns dbDelta() every request instead of settling - and the error transient above // was already cleared on the strength of $schema_ok alone, hiding the persist failure // from the admin notice. Re-set the transient and log the failure so it is visible. set_transient( 'aafm_oauth_schema_error', time(), DAY_IN_SECONDS ); if ( $seeded ) { aafm_log_ability_persist_failure( 'aafm_oauth_schema_version', __( 'The OAuth schema version', 'agent-abilities-for-mcp' ) ); } else { aafm_log_ability_persist_failure( 'aafm_oauth_connect_roles', __( 'The roles that can connect apps', 'agent-abilities-for-mcp' ) ); } } } return $stamped; } /** * The six per-connection columns schema v9 adds to the consents table. * * @return string[] */ function aafm_oauth_grant_columns(): array { return array( 'paused_at', 'expires_at', 'last_used_at', 'last_used_ip', 'preset', 'rate_per_min' ); } /** * Whether the OAuth schema is actually present: all four tables plus the latest columns. * * Verifies table presence, the v6 `scope` column on codes + access-tokens, the v8 * `is_agent_identity` column on clients, and every v9 column on consents. The most recent * migration is v9, and every column it adds is checked, because a partial dbDelta can land the * last column and miss an earlier one. Gates the version stamp in aafm_oauth_finalize_schema(). * * @return bool */ function aafm_oauth_schema_verify(): bool { global $wpdb; foreach ( aafm_oauth_table_suffixes() as $suffix ) { if ( ! aafm_oauth_table_present( $wpdb->prefix . $suffix ) ) { return false; } } foreach ( aafm_oauth_grant_columns() as $col ) { if ( ! aafm_oauth_table_has_column( $wpdb->prefix . 'aafm_oauth_consents', $col ) ) { return false; } } return aafm_oauth_table_has_column( $wpdb->prefix . 'aafm_oauth_codes', 'scope' ) && aafm_oauth_table_has_column( $wpdb->prefix . 'aafm_oauth_access_tokens', 'scope' ) && aafm_oauth_table_has_column( $wpdb->prefix . 'aafm_oauth_clients', 'is_agent_identity' ); } /** * Whether a table exists for the current blog. * * The PHPUnit harness rewrites plugin CREATE TABLE to its TEMPORARY form, which SHOW TABLES does * not list, so existence is probed with a trivial select that sees a temporary table the same way * the plugin's own queries do. The %i placeholder quotes the identifier (an internal constant). * * This used to read '' === $wpdb->last_error as its success signal, which * confuses a query that never ran (three of $wpdb->query()'s own false-returning paths never * touch last_error - see aafm_wpdb_scalar()'s docblock) with one that ran and found the * table. Under that failure shape an absent table (the consents table, say, after an * unsuccessful CREATE) reported present, and schema finalization could stamp the current version * without the normal missing-table retry ever firing. Delegating to aafm_wpdb_scalar(), which * checks $wpdb->query()'s own return value, closes the same gap the activity-log table's sibling * probe closes. * * @param string $table Fully-prefixed table name. * @return bool */ function aafm_oauth_table_present( string $table ): bool { global $wpdb; $suppressed = $wpdb->suppress_errors( true ); $result = aafm_wpdb_scalar( $wpdb->prepare( 'SELECT 1 FROM %i LIMIT 0', $table ) ); $wpdb->suppress_errors( $suppressed ); return $result['ok']; } /** * Whether a named column exists on a table. Works on the harness's TEMPORARY tables. * * A bare $wpdb->get_var() returns the PREVIOUS query's row when the current * query itself fails, so a failed SHOW COLUMNS could inherit an unrelated non-empty value left * over from an earlier, successful check and report a missing column as present - letting schema * finalization stamp the current version over an incomplete upgrade. Routed through * aafm_wpdb_scalar() so a failed read reports absent, the same fail-closed direction this * function already took for a genuinely missing column. * * @param string $table Fully-prefixed table name (an internal constant). * @param string $column Column name to look for (no wildcards; matched exactly by SHOW COLUMNS). * @return bool */ function aafm_oauth_table_has_column( string $table, string $column ): bool { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $view = aafm_wpdb_scalar( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, $column ) ); return $view['ok'] && null !== $view['value'] && '' !== $view['value']; } /** * Admin notice: the OAuth schema did not finish installing or upgrading. * * Shown only while aafm_oauth_finalize_schema()'s bounded transient is set (it clears the moment a * verify passes), and only to users who can act on it. * * @return void */ function aafm_oauth_schema_admin_notice(): void { if ( ! current_user_can( 'activate_plugins' ) ) { return; } if ( false === get_transient( 'aafm_oauth_schema_error' ) ) { return; } echo '

'; echo esc_html__( 'Agent Abilities for MCP could not finish setting up its OAuth database tables. OAuth may not work correctly until the update completes; the plugin retries automatically on the next admin or REST request.', 'agent-abilities-for-mcp' ); echo '

'; } add_action( 'admin_notices', 'aafm_oauth_schema_admin_notice' ); /** * Run the installer when the stored schema version is behind the current one. * * Cheap early return when the option already matches, so this can be hooked on * every admin request without churn. dbDelta() is safe to re-run and the * installer resets the option. Mirrors the audit log's activation wiring. * * Reads the database row directly rather than get_option()'s cache-trusting view: * a stale cached current version over an old/absent database row would skip a genuinely * needed install, and this file's own docblocks identify missing tables/columns as the failure * this guard exists to catch. * * Retry policy: activation takes no lock, so the first admin or REST request after a failed * activation retries at once; a failed retry holds aafm_oauth_upgrade_lock for an hour; deactivating * and reactivating the plugin retries immediately. * * @return void */ function aafm_maybe_upgrade_oauth_tables(): void { if ( AAFM_OAUTH_SCHEMA_VERSION === (string) aafm_read_option_views( 'aafm_oauth_schema_version' )['db_value'] ) { return; } // One request upgrades at a time, and a failed upgrade waits an hour before the next try, so a // refused ALTER does not rerun dbDelta() on every admin and REST request. Same lock and same // ceiling as aafm_maybe_upgrade_activity_log(). An unreadable lock counts as held. $held = aafm_transient_count( 'aafm_oauth_upgrade_lock' ); if ( null === $held || $held > 0 ) { return; } set_transient( 'aafm_oauth_upgrade_lock', 1, MINUTE_IN_SECONDS ); if ( aafm_install_oauth_tables() ) { delete_transient( 'aafm_oauth_upgrade_lock' ); return; } set_transient( 'aafm_oauth_upgrade_lock', 1, HOUR_IN_SECONDS ); } /** * Seconds before a never-consented, token-less DCR client row is reaped. * * @return int */ function aafm_oauth_client_reap_ttl(): int { /** * TTL (seconds) before a never-consented, token-less DCR client row is reaped. * * @param int $ttl TTL in seconds. Default 7 days. */ return max( 0, (int) apply_filters( 'aafm_oauth_client_reap_ttl', 7 * DAY_IN_SECONDS ) ); } /** * Empty all four OAuth tables, keeping their structure. Used by the plugin reset. * * Uses DELETE rather than TRUNCATE so it stays compatible with the PHPUnit harness, * which rewrites these tables to their TEMPORARY form (TRUNCATE cannot target a * temporary table in some MySQL configs). Mirrors aafm_drop_oauth_tables()' escaping. * * Certifies each table by reading its row count back rather than trusting the DELETE's own * affected-row count: a table this call never actually reached still * counts as failed, even though it never contributes a nonzero affected-row count either way. * * The confirmation read goes through aafm_wpdb_scalar() rather than a bare get_var(): * a get_var() read that itself failed used to cast straight to `(int) null === 0`, * the same "unreadable, so call it empty" mistake the DELETE-count fix above already closed, just * one query later. * * @return bool True when every table is confirmed empty after this call. */ function aafm_truncate_oauth_tables(): bool { global $wpdb; $ok = true; foreach ( aafm_oauth_table_suffixes() as $suffix ) { $table = $wpdb->prefix . $suffix; // Internal table name bound as a SQL identifier via %i (available since WP 6.2). // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $wpdb->query( $wpdb->prepare( 'DELETE FROM %i', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $remaining = aafm_wpdb_scalar( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) ); if ( ! $remaining['ok'] || 0 !== (int) $remaining['value'] ) { $ok = false; } } return $ok; } /** * Prune dead OAuth artifacts: expired authorization codes and inactive access * tokens whose refresh window has lapsed past a small grace period. * * Run daily by the `aafm_oauth_cleanup` cron event. Two passes: * * - Codes are one-time and short-lived (60-second TTL), so any row past its * `expires_at` is dead regardless of `used_at` and is deleted. * - Access-token rows are deleted only when inactive (`is_active = 0`) AND their * `refresh_expires_at` is older than now minus a grace window. An active row is * never pruned by access-token expiry alone: its refresh token is still valid. * The grace lets a rotated/replaced (now-inactive) refresh row linger briefly so * the token manager's reuse-detection still has the parent chain to walk during a * network race. Filterable via `aafm_oauth_cleanup_grace` (seconds). * * Times are compared in UTC `Y-m-d H:i:s`, the storage format used across the * plugin (matches the token manager and audit log). Mirrors the direct-query and * escaping precedent of aafm_truncate_oauth_tables() / aafm_drop_oauth_tables(). * * @return void */ function aafm_oauth_cleanup(): void { global $wpdb; $now = gmdate( 'Y-m-d H:i:s', time() ); /** * Grace period (in seconds) before an inactive token row is eligible for * deletion after its refresh window expires. Keeps a rotated refresh row around * long enough for reuse detection under a network race. * * @param int $grace Grace period in seconds. Default DAY_IN_SECONDS. */ $grace = (int) apply_filters( 'aafm_oauth_cleanup_grace', DAY_IN_SECONDS ); $token_cutoff = gmdate( 'Y-m-d H:i:s', time() - $grace ); // Internal constant table names; esc_sql() makes the safety explicit for analyzers. $codes_table = esc_sql( $wpdb->prefix . 'aafm_oauth_codes' ); $tokens_table = esc_sql( $wpdb->prefix . 'aafm_oauth_access_tokens' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $wpdb->query( $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is an internal constant. "DELETE FROM {$codes_table} WHERE expires_at IS NOT NULL AND expires_at < %s", $now ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $wpdb->query( $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is an internal constant. "DELETE FROM {$tokens_table} WHERE is_active = 0 AND refresh_expires_at IS NOT NULL AND refresh_expires_at < %s", $token_cutoff ) ); // Third pass: reap abandoned Dynamic Client Registration rows so the public DCR // endpoint cannot grow the clients table without bound. aafm_oauth_reap_abandoned_clients(); } /** * Delete Dynamic-Client-Registration rows that were never used and are past a TTL. * * The DCR endpoint is public (the OAuth grant is the auth), so a client row is created before * any human approves it. A client that registered but no one ever consented to - and that holds * no token rows of any state - is dead weight. This removes such rows once they are older than * the TTL (default 7 days), keeping a generous window for a legitimate register-then-approve * flow that spans a session. A client with at least one consent OR any token row is kept, so a * live or revoked-but-historical client is never reaped - the deletion re-checks that condition * itself rather than trusting the scan that found the candidates. Deletes the matching codes too. * * The TTL is filterable via `aafm_oauth_client_reap_ttl` (seconds). All three tables are * internal constants; the cutoff is bound. * * @return int Number of client rows reaped. */ function aafm_oauth_reap_abandoned_clients(): int { global $wpdb; $cutoff = gmdate( 'Y-m-d H:i:s', time() - aafm_oauth_client_reap_ttl() ); $clients_table = esc_sql( $wpdb->prefix . 'aafm_oauth_clients' ); $consents_table = esc_sql( $wpdb->prefix . 'aafm_oauth_consents' ); $tokens_table = esc_sql( $wpdb->prefix . 'aafm_oauth_access_tokens' ); $codes_table = esc_sql( $wpdb->prefix . 'aafm_oauth_codes' ); // Find abandoned client_ids: older than the cutoff, with no consent row and no token row. // All table names are internal constants; the cutoff is bound. Routed // through aafm_wpdb_col() rather than a bare get_col() - a failed query here must not be // allowed to return an earlier, unrelated query's column values, since this list feeds a // DELETE below and a stale, wrong client_id list would delete the wrong rows. // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $view = aafm_wpdb_col( $wpdb->prepare( "SELECT cl.client_id FROM {$clients_table} cl WHERE cl.created_at < %s AND NOT EXISTS ( SELECT 1 FROM {$consents_table} co WHERE co.client_id = cl.client_id ) AND NOT EXISTS ( SELECT 1 FROM {$tokens_table} t WHERE t.client_id = cl.client_id )", $cutoff ) ); // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared if ( ! $view['ok'] || empty( $view['value'] ) || ! is_array( $view['value'] ) ) { return 0; } $abandoned = $view['value']; $placeholders = implode( ', ', array_fill( 0, count( $abandoned ), '%s' ) ); // Delete the clients, but re-certify abandonment in the DELETE's own WHERE clause rather // than trusting the candidate list above: that list can go stale // between the scan and this delete if a candidate is approved in the meantime - consent // recorded, a code minted, a token issued - and a blind IN-list delete would then remove a // registration that just went live, orphaning the token issued seconds earlier. Carrying the // scan's exact predicate here means the database decides from current data at delete time, // not PHP from a stale snapshot, the same fix shape as the row-locked UPDATE. // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare $deleted = (int) $wpdb->query( $wpdb->prepare( "DELETE FROM {$clients_table} WHERE client_id IN ( {$placeholders} ) AND created_at < %s AND NOT EXISTS ( SELECT 1 FROM {$consents_table} co WHERE co.client_id = {$clients_table}.client_id ) AND NOT EXISTS ( SELECT 1 FROM {$tokens_table} t WHERE t.client_id = {$clients_table}.client_id )", array_merge( $abandoned, array( $cutoff ) ) ) ); // Only now delete the stray codes - scoped to client_ids that no longer have a client row // at all, derived from the delete that just happened rather than from the stale candidate // list. A candidate that survived the recheck above (freshly approved, consent and a code // both just minted) keeps its client row and, by the same token, its live code. $wpdb->query( $wpdb->prepare( "DELETE FROM {$codes_table} WHERE client_id IN ( {$placeholders} ) AND NOT EXISTS ( SELECT 1 FROM {$clients_table} cl WHERE cl.client_id = {$codes_table}.client_id )", $abandoned ) ); // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare return $deleted; } /** * Drop all four OAuth tables. Used by uninstall. * * @return void */ function aafm_drop_oauth_tables(): void { global $wpdb; foreach ( aafm_oauth_table_suffixes() as $suffix ) { // Internal table name bound as a SQL identifier via %i (available since WP 6.2). // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange $wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $wpdb->prefix . $suffix ) ); } }