prepare() with explicit phpcs ignore comments, matching * the coding style of the rest of the plugin (see class-ailm-link-db.php and * class-ailm-link-repository.php). * * @since 1.0 * @package AILinkMaster * @subpackage AILinkMaster\modules\url-migrator */ if ( ! defined( 'ABSPATH' ) ) { exit; // Exit if accessed directly. } class AILM_URL_Redirect_DB { /** * Cache group shared with the rest of the plugin (see * AILM_Link_Repository — group 'ailm_cache_group'). */ const CACHE_GROUP = 'ailm_cache_group'; /** * Get the redirects table name (with prefix). * * @return string */ public static function get_redirects_table() { global $wpdb; return $wpdb->prefix . 'ailm_url_redirects'; } /** * Get the backup/log table name (with prefix). * * @return string */ public static function get_backup_table() { global $wpdb; return $wpdb->prefix . 'ailm_url_backup'; } /** * Create both tables using dbDelta for safe upgrades. * * Called on plugin activation and from the admin UI on demand. Safe to * call multiple times — dbDelta only applies the diff. * * @return void */ public static function create_tables() { global $wpdb; $charset_collate = $wpdb->get_charset_collate(); // Load dbDelta (only if available — tests may stub it out). $upgrade_file = ABSPATH . 'wp-admin/includes/upgrade.php'; if ( is_string( ABSPATH ) && file_exists( $upgrade_file ) ) { require_once $upgrade_file; } $redirects_table = self::get_redirects_table(); $redirects_sql = "CREATE TABLE {$redirects_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, post_id bigint(20) unsigned NOT NULL DEFAULT 0, object_type varchar(20) NOT NULL DEFAULT 'post', from_url varchar(2048) NOT NULL, to_url varchar(2048) NOT NULL, redirect_type smallint(3) NOT NULL DEFAULT 301, status varchar(20) NOT NULL DEFAULT 'active', hit_count int(10) unsigned NOT NULL DEFAULT 0, last_hit datetime NOT NULL DEFAULT '0000-00-00 00:00:00', migration_batch varchar(40) DEFAULT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY idx_from (from_url(191)), KEY idx_to (to_url(191)), KEY idx_post (post_id), KEY idx_batch (migration_batch) ) {$charset_collate};"; dbDelta( $redirects_sql ); $backup_table = self::get_backup_table(); $backup_sql = "CREATE TABLE {$backup_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, migration_batch varchar(40) NOT NULL, object_type varchar(20) NOT NULL, object_id bigint(20) unsigned NOT NULL DEFAULT 0, field_name varchar(100) NOT NULL, old_value longtext, new_value longtext, new_value_hash varchar(32) DEFAULT NULL, level varchar(10) NOT NULL DEFAULT 'info', message text, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_batch (migration_batch), KEY idx_object (object_type, object_id) ) {$charset_collate};"; dbDelta( $backup_sql ); } /** * Check whether both tables exist. * * @return bool */ public static function tables_exist() { global $wpdb; $redirects_table = self::get_redirects_table(); $backup_table = self::get_backup_table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $r_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $redirects_table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $b_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $backup_table ) ); return ( $r_exists === $redirects_table && $b_exists === $backup_table ); } // ========================================================================= // REDIRECT CRUD // ========================================================================= /** * Normalize a URL path for storage and lookup. * * Stores the path relative to home_url, without query string, untrailingslashed * (except for the root "/"). This keeps lookups deterministic. * * @param string $url Raw URL or path. * @return string Normalized path, e.g. "/blog/my-post". */ public static function normalize_url( $url ) { // Strip protocol + host if a full URL was passed. $path = wp_parse_url( $url, PHP_URL_PATH ); if ( false === $path || null === $path ) { $path = $url; } $query = wp_parse_url( $url, PHP_URL_QUERY ); $path = '/' . ltrim( $path, '/' ); $path = rawurldecode( $path ); // Keep the root "/" as-is; otherwise strip trailing slash. if ( '/' !== $path ) { $path = untrailingslashit( $path ); } // Preserve query string (some sites rely on it for routing). if ( ! empty( $query ) ) { $path .= '?' . $query; } return $path; } /** * Per-request static cache of redirect lookups (L1). Kept as a class * property so it can be selectively invalidated when a redirect is * written/deleted in the same request. * * @var array */ private static $static_cache = array(); /** * Get a single active redirect by its from_url. * * Uses a 3-layer cache (static → wp_cache → DB) to keep the hot path * (every front-end request) fast. * * @param string $from_url Normalized from path. * @return object|null Redirect row object or null if none. */ public static function get_redirect( $from_url ) { $from_url = self::normalize_url( $from_url ); // L1: per-request static cache. if ( array_key_exists( $from_url, self::$static_cache ) ) { return self::$static_cache[ $from_url ]; } // L2: object cache (group shared with the plugin). $cache_key = 'ailm_redir:' . $from_url; $cached = wp_cache_get( $cache_key, self::CACHE_GROUP ); if ( false !== $cached ) { self::$static_cache[ $from_url ] = $cached; return $cached; } // L3: database. global $wpdb; $table = self::get_redirects_table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE from_url = %s AND status = 'active' LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $from_url ) ); // Cache null too (negative cache) but with a short TTL to avoid // permanently hiding a redirect created moments later. wp_cache_set( $cache_key, $row, self::CACHE_GROUP, $row ? HOUR_IN_SECONDS : 60 ); self::$static_cache[ $from_url ] = $row; return $row; } /** * Reset the per-request static cache (used by tests and after bulk writes). * * @return void */ public static function reset_static_cache() { self::$static_cache = array(); } /** * Get all redirects for a migration batch. * * @param string $batch_id Migration batch ID. * @return array Array of redirect row objects. */ public static function get_redirects_for_batch( $batch_id ) { global $wpdb; $table = self::get_redirects_table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE migration_batch = %s ORDER BY id ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $batch_id ) ); } /** * Get redirects with pagination (for the admin table). * * @param int $offset Offset. * @param int $limit Limit. * @param string $status Optional status filter ('active' | 'archived' | 'all'). * @return array Array of redirect row objects. */ public static function get_redirects_paginated( $offset = 0, $limit = 50, $status = 'all' ) { global $wpdb; $table = self::get_redirects_table(); if ( 'all' === $status ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} ORDER BY id DESC LIMIT %d OFFSET %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $limit, $offset ) ); } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE status = %s ORDER BY id DESC LIMIT %d OFFSET %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $status, $limit, $offset ) ); } /** * Count all redirects (optionally filtered by status). * * @param string $status 'active' | 'archived' | 'all'. * @return int */ public static function count_redirects( $status = 'all' ) { global $wpdb; $table = self::get_redirects_table(); if ( 'all' === $status ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE status = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $status ) ); } /** * Insert or update a redirect, enforcing the strict chain-compression and * cycle-blocking rules (architecture doc §6). * * Rules applied in order: * 1. Self-redirect (from === to) → blocked, returns WP_Error. * 2. Outgoing compression: if `to` already redirects to X, our `to` * becomes X (collapse the chain to a single hop). * 3. Incoming compression: any existing redirect whose `to_url` equals * our `from` is repointed to our `to` (so nothing points at `from` * anymore — `from` becomes a pure source). * 4. Cycle check: after compression, if `to` redirects back to `from`, * block the write. * 5. Upsert (INSERT ... ON DUPLICATE KEY UPDATE) on from_url. * * @param string $from_url Source URL/path. * @param string $to_url Target URL/path. * @param array $args { * Optional. Redirect metadata. * @type int $post_id Associated post ID. Default 0. * @type string $object_type 'post' | 'term'. Default 'post'. * @type int $redirect_type HTTP status (301/302). Default 301. * @type string $migration_batch Batch ID. Default null. * } * @return int|WP_Error Redirect ID on success, WP_Error on blocked write. */ public static function upsert_redirect( $from_url, $to_url, $args = array() ) { global $wpdb; $table = self::get_redirects_table(); $from_url = self::normalize_url( $from_url ); $to_url = self::normalize_url( $to_url ); // Rule 1: block self-redirects. if ( $from_url === $to_url ) { return new WP_Error( 'ailm_url_self_redirect', __( 'Cannot create a redirect from a URL to itself.', 'ailinkmaster-internal-auto-manager' ) ); } $post_id = isset( $args['post_id'] ) ? absint( $args['post_id'] ) : 0; $object_type = isset( $args['object_type'] ) ? sanitize_key( $args['object_type'] ) : 'post'; $redirect_type = isset( $args['redirect_type'] ) ? (int) $args['redirect_type'] : 301; $migration_batch = isset( $args['migration_batch'] ) ? sanitize_text_field( $args['migration_batch'] ) : null; // Rule 2: outgoing compression — if `to` already redirects somewhere, // collapse to that final destination. $existing_to = self::get_redirect( $to_url ); if ( $existing_to && $existing_to->to_url !== $from_url ) { $to_url = $existing_to->to_url; } // Rule 3: incoming compression — repoint anything that currently // redirects TO our `from` so it now points to our `to`. This keeps // `from` as a pure source with no inbound redirects. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->query( $wpdb->prepare( "UPDATE {$table} SET to_url = %s WHERE to_url = %s AND from_url <> %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $to_url, $from_url, $to_url ) ); // Rule 4: cycle check — after compression, does `to` redirect back to `from`? $cycle_check = self::get_redirect( $to_url ); if ( $cycle_check && $cycle_check->to_url === $from_url ) { return new WP_Error( 'ailm_url_cycle', __( 'Cannot create redirect: it would form a redirect cycle.', 'ailinkmaster-internal-auto-manager' ) ); } // Rule 5: upsert on from_url (UNIQUE key). // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->query( $wpdb->prepare( "INSERT INTO {$table} (post_id, object_type, from_url, to_url, redirect_type, status, migration_batch) VALUES (%d, %s, %s, %s, %d, 'active', %s) ON DUPLICATE KEY UPDATE to_url = VALUES(to_url), redirect_type = VALUES(redirect_type), status = 'active', post_id = VALUES(post_id), object_type = VALUES(object_type), migration_batch = VALUES(migration_batch)", $post_id, $object_type, $from_url, $to_url, $redirect_type, $migration_batch ) ); // phpcs:enable // Fetch the row id. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $row_id = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE from_url = %s LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $from_url ) ); // Invalidate caches for both endpoints. self::invalidate_redirect_cache( $from_url ); self::invalidate_redirect_cache( $to_url ); return $row_id; } /** * Archive all redirects belonging to a migration batch (used during rollback). * * Archived redirects are kept for audit but no longer match on the front end. * * @param string $batch_id Migration batch ID. * @return int Number of archived rows. */ public static function archive_batch( $batch_id ) { global $wpdb; $table = self::get_redirects_table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $rows = $wpdb->get_results( $wpdb->prepare( "SELECT from_url, to_url FROM {$table} WHERE migration_batch = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $batch_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $archived = $wpdb->query( $wpdb->prepare( "UPDATE {$table} SET status = 'archived' WHERE migration_batch = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $batch_id ) ); // Invalidate cache for every touched URL. foreach ( $rows as $r ) { self::invalidate_redirect_cache( $r->from_url ); self::invalidate_redirect_cache( $r->to_url ); } return (int) $archived; } /** * Delete a single redirect by ID. * * @param int $id Redirect ID. * @return bool True on success. */ public static function delete_redirect( $id ) { global $wpdb; $table = self::get_redirects_table(); // Fetch first so we can invalidate cache. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $row = $wpdb->get_row( $wpdb->prepare( "SELECT from_url, to_url FROM {$table} WHERE id = %d", $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $ok = (bool) $wpdb->delete( $table, array( 'id' => (int) $id ), array( '%d' ) ); if ( $ok && $row ) { self::invalidate_redirect_cache( $row->from_url ); self::invalidate_redirect_cache( $row->to_url ); } return $ok; } /** * Increment the hit counter for a redirect (deferred via shutdown hook). * * Uses a per-request static accumulator so multiple hits to the same * redirect in one request are coalesced into a single UPDATE. * * @param int $id Redirect ID. * @return void */ public static function register_hit( $id ) { static $pending = array(); $pending[ (int) $id ] = ( isset( $pending[ (int) $id ] ) ? $pending[ (int) $id ] : 0 ) + 1; if ( ! has_action( 'shutdown', array( __CLASS__, 'flush_hits' ) ) ) { add_action( 'shutdown', array( __CLASS__, 'flush_hits' ) ); } // Stash the accumulator on the static for the shutdown callback. self::$pending_hits = $pending; } /** * Pending hit increments, keyed by redirect ID. * * @var array */ private static $pending_hits = array(); /** * Shutdown callback: flush accumulated hit increments to the DB. * * Runs once at the end of the request so the redirect response is not * delayed by a write. * * @return void */ public static function flush_hits() { if ( empty( self::$pending_hits ) ) { return; } global $wpdb; $table = self::get_redirects_table(); foreach ( self::$pending_hits as $id => $count ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->query( $wpdb->prepare( "UPDATE {$table} SET hit_count = hit_count + %d, last_hit = %s WHERE id = %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $count, current_time( 'mysql' ), $id ) ); } self::$pending_hits = array(); } /** * Invalidate the redirect cache for a single from_url. * * Clears both the object cache entry and the per-request static cache * entry so a subsequent lookup in the same request sees the new value. * * @param string $from_url Normalized or raw from path. * @return void */ public static function invalidate_redirect_cache( $from_url ) { $from_url = self::normalize_url( $from_url ); $cache_key = 'ailm_redir:' . $from_url; wp_cache_delete( $cache_key, self::CACHE_GROUP ); unset( self::$static_cache[ $from_url ] ); } // ========================================================================= // BACKUP / LOG TABLE // ========================================================================= /** * Record a backup row (and/or a log entry — same table, dual purpose). * * @param string $batch_id Migration batch ID. * @param string $object_type 'post' | 'term' | 'option' | 'link_rules' | 'nav_menu'. * @param int $object_id Object ID (0 for global/log rows). * @param string $field_name Field name e.g. 'post_content', '_elementor_data'. * @param string $old_value Previous value (null for pure log rows). * @param string $new_value New value (null for pure log rows). * @param string $level 'info' | 'warning' | 'error'. * @param string $message Human-readable message. * @return int Inserted row ID. */ public static function add_backup_row( $batch_id, $object_type, $object_id, $field_name, $old_value = null, $new_value = null, $level = 'info', $message = '' ) { global $wpdb; $table = self::get_backup_table(); $new_value_hash = null; if ( null !== $new_value && '' !== $new_value ) { $new_value_hash = md5( $new_value ); } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery $wpdb->insert( $table, array( 'migration_batch' => sanitize_text_field( $batch_id ), 'object_type' => sanitize_key( $object_type ), 'object_id' => absint( $object_id ), 'field_name' => sanitize_text_field( $field_name ), 'old_value' => $old_value, // longtext; not sanitized — raw backup. 'new_value' => $new_value, 'new_value_hash' => $new_value_hash, 'level' => in_array( $level, array( 'info', 'warning', 'error' ), true ) ? $level : 'info', 'message' => sanitize_text_field( $message ), ), array( '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s' ) ); return (int) $wpdb->insert_id; } /** * Get all backup rows for a migration batch. * * @param string $batch_id Migration batch ID. * @return array Array of backup row objects. */ public static function get_backups_for_batch( $batch_id ) { global $wpdb; $table = self::get_backup_table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE migration_batch = %s ORDER BY id ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $batch_id ) ); } /** * Get a list of distinct migration batches (for the rollback UI). * * @return array Array of objects with batch_id, created_at, row_count. */ public static function get_batch_list() { global $wpdb; $table = self::get_backup_table(); // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter return $wpdb->get_results( "SELECT migration_batch, MIN(created_at) AS created_at, COUNT(*) AS row_count FROM {$table} GROUP BY migration_batch ORDER BY created_at DESC" ); // phpcs:enable } /** * Get the current value of a backed-up field (for hash comparison on rollback). * * @param string $object_type 'post' | 'term' | 'option' | 'nav_menu'. * @param int $object_id Object ID. * @param string $field_name Field name. * @return string|null Current value or null if not applicable. */ public static function get_current_value( $object_type, $object_id, $field_name ) { global $wpdb; switch ( $object_type ) { case 'post': if ( 'post_content' === $field_name ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter return (string) $wpdb->get_var( $wpdb->prepare( "SELECT post_content FROM {$wpdb->posts} WHERE ID = %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $object_id ) ); } // postmeta field. return get_post_meta( $object_id, $field_name, true ); case 'term': if ( 'description' === $field_name ) { $term = get_term( $object_id ); return ( $term && ! is_wp_error( $term ) ) ? $term->description : null; } return get_term_meta( $object_id, $field_name, true ); case 'option': return get_option( $field_name ); case 'nav_menu': // Custom link URL stored in postmeta _menu_item_url. return get_post_meta( $object_id, '_menu_item_url', true ); default: return null; } } }