← All changes
|
redirections/class-metasync-redirection-database.php
+155
-106
2.6.6
→
2.7.1
View file →
| @@ -7,12 +7,18 @@ | ||
| 7 | 7 | * @package Metasync |
| 8 | 8 | * @subpackage Metasync/redirections |
| 9 | 9 | * @author Engineering Team <[email protected]> |
| 10 | 10 | */ |
| 11 | + | |
| 12 | +if (!defined('ABSPATH')) { | |
| 13 | + exit; | |
| 14 | +} | |
| 15 | + | |
| 11 | 16 | if (!class_exists('Metasync_Redirection_Database')) { |
| 12 | 17 | class Metasync_Redirection_Database |
| 13 | 18 | { |
| 14 | 19 | public static $table_name = "metasync_redirections"; |
| 20 | + private static $structure_verified = false; | |
| 15 | 21 | |
| 16 | 22 | private function get_table_name() |
| 17 | 23 | { |
| 18 | 24 | global $wpdb; |
| @@ -23,13 +29,17 @@ | ||
| 23 | 29 | * Ensure table structure is up to date |
| 24 | 30 | */ |
| 25 | 31 | private function ensure_table_structure() |
| 26 | 32 | { |
| 33 | + // Run schema inspection at most once per request — table schema only changes on activation/upgrade, handled by class-db-migrations.php. | |
| 34 | + if (self::$structure_verified) { return; } | |
| 35 | + self::$structure_verified = true; | |
| 36 | + | |
| 27 | 37 | global $wpdb; |
| 28 | 38 | $table_name = $this->get_table_name(); |
| 29 | - | |
| 39 | + | |
| 30 | 40 | // Check if table exists |
| 31 | - if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) != $table_name) { | |
| 41 | + if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) != $table_name) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate | |
| 32 | 42 | // Table doesn't exist, run full migration |
| 33 | 43 | require_once dirname(__FILE__, 2) . '/database/class-db-migrations.php'; |
| 34 | 44 | MetaSync_DBMigration::activation(); |
| 35 | 45 | return; |
| @@ -35,43 +45,55 @@ | ||
| 35 | 45 | return; |
| 36 | 46 | } |
| 37 | 47 | |
| 38 | 48 | // Check if required columns exist |
| 39 | - $columns = $wpdb->get_col("DESCRIBE {$table_name}"); | |
| 49 | + $columns = $wpdb->get_col("DESCRIBE {$wpdb->prefix}metasync_redirections"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop — column introspection, admin write paths only, once per request | |
| 40 | 50 | |
| 41 | - $required_columns = [ | |
| 42 | - 'pattern_type' => "ALTER TABLE {$table_name} ADD COLUMN pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex') NOT NULL DEFAULT 'exact' AFTER status", | |
| 43 | - 'regex_pattern' => "ALTER TABLE {$table_name} ADD COLUMN regex_pattern TEXT NULL AFTER pattern_type", | |
| 44 | - 'description' => "ALTER TABLE {$table_name} ADD COLUMN description TEXT NULL AFTER regex_pattern", | |
| 45 | - 'created_at' => "ALTER TABLE {$table_name} ADD COLUMN created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER description", | |
| 46 | - 'updated_at' => "ALTER TABLE {$table_name} ADD COLUMN updated_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER created_at", | |
| 47 | - 'last_accessed_at' => "ALTER TABLE {$table_name} ADD COLUMN last_accessed_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER updated_at" | |
| 48 | - ]; | |
| 49 | - | |
| 50 | - foreach ($required_columns as $column => $sql) { | |
| 51 | - if (!in_array($column, $columns)) { | |
| 52 | - $wpdb->query($sql); | |
| 51 | + if (!in_array('pattern_type', $columns)) { | |
| 52 | + $wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex', 'wildcard') NOT NULL DEFAULT 'exact' AFTER status"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate | |
| 53 | + } | |
| 54 | + if (!in_array('regex_pattern', $columns)) { | |
| 55 | + $wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN regex_pattern TEXT NULL AFTER pattern_type"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate | |
| 56 | + } | |
| 57 | + if (!in_array('description', $columns)) { | |
| 58 | + $wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN description TEXT NULL AFTER regex_pattern"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate | |
| 59 | + } | |
| 60 | + if (!in_array('created_at', $columns)) { | |
| 61 | + $wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER description"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate | |
| 62 | + } | |
| 63 | + if (!in_array('updated_at', $columns)) { | |
| 64 | + $wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN updated_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER created_at"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate | |
| 65 | + } | |
| 66 | + if (!in_array('last_accessed_at', $columns)) { | |
| 67 | + $wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN last_accessed_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER updated_at"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate | |
| 68 | + } | |
| 69 | + | |
| 70 | + // Widen pattern_type for installs whose column predates wildcard | |
| 71 | + // matching — column-exists checks never re-ALTER, so the enum would | |
| 72 | + // otherwise keep silently rejecting wildcard rows. | |
| 73 | + if (in_array('pattern_type', $columns)) { | |
| 74 | + $column_info = $wpdb->get_row($wpdb->prepare("SHOW COLUMNS FROM {$wpdb->prefix}metasync_redirections LIKE %s", 'pattern_type')); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop — enum widening for pre-wildcard installs, admin write paths only | |
| 75 | + if ($column_info && strpos((string) $column_info->Type, 'wildcard') === false) { | |
| 76 | + $wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections MODIFY COLUMN pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex', 'wildcard') NOT NULL DEFAULT 'exact'"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop — enum widening for pre-wildcard installs, admin write paths only | |
| 53 | 77 | } |
| 54 | 78 | } |
| 55 | - | |
| 79 | + | |
| 56 | 80 | // Check and add indexes |
| 57 | - $indexes = $wpdb->get_results("SHOW INDEX FROM {$table_name}"); | |
| 81 | + $indexes = $wpdb->get_results("SHOW INDEX FROM {$wpdb->prefix}metasync_redirections"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop — index introspection, admin write paths only, once per request | |
| 58 | 82 | $index_names = array_column($indexes, 'Key_name'); |
| 59 | 83 | |
| 60 | - $required_indexes = [ | |
| 61 | - 'pattern_type' => "ALTER TABLE {$table_name} ADD KEY pattern_type (pattern_type)", | |
| 62 | - 'created_at' => "ALTER TABLE {$table_name} ADD KEY created_at (created_at)", | |
| 63 | - 'status_created' => "ALTER TABLE {$table_name} ADD KEY status_created (status, created_at)" | |
| 64 | - ]; | |
| 65 | - | |
| 66 | - foreach ($required_indexes as $index => $sql) { | |
| 67 | - if (!in_array($index, $index_names)) { | |
| 68 | - $wpdb->query($sql); | |
| 69 | - } | |
| 84 | + if (!in_array('pattern_type', $index_names)) { | |
| 85 | + $wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD KEY pattern_type (pattern_type)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate | |
| 70 | 86 | } |
| 87 | + if (!in_array('created_at', $index_names)) { | |
| 88 | + $wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD KEY created_at (created_at)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate | |
| 89 | + } | |
| 90 | + if (!in_array('status_created', $index_names)) { | |
| 91 | + $wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD KEY status_created (status, created_at)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate | |
| 92 | + } | |
| 71 | 93 | |
| 72 | 94 | // Set default pattern_type for existing records |
| 73 | - $wpdb->query("UPDATE {$table_name} SET pattern_type = 'exact' WHERE pattern_type IS NULL OR pattern_type = ''"); | |
| 95 | + $wpdb->query("UPDATE {$wpdb->prefix}metasync_redirections SET pattern_type = 'exact' WHERE pattern_type IS NULL OR pattern_type = ''"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop — one-time backfill bound to the column additions above | |
| 74 | 96 | } |
| 75 | 97 | |
| 76 | 98 | /** |
| 77 | 99 | * Manually trigger table structure update |
| @@ -78,8 +100,9 @@ | ||
| 78 | 100 | * Can be called from admin or via AJAX if needed |
| 79 | 101 | */ |
| 80 | 102 | public function force_table_update() |
| 81 | 103 | { |
| 104 | + self::$structure_verified = false; | |
| 82 | 105 | $this->ensure_table_structure(); |
| 83 | 106 | return true; |
| 84 | 107 | } |
| 85 | 108 | |
| @@ -85,16 +108,14 @@ | ||
| 85 | 108 | |
| 86 | 109 | public function getAllRecords() |
| 87 | 110 | { |
| 88 | 111 | global $wpdb; |
| 89 | - $tableName = $this->get_table_name(); | |
| 90 | - return $wpdb->get_results(" SELECT * FROM `$tableName` "); | |
| 112 | + return $wpdb->get_results("SELECT * FROM `{$wpdb->prefix}metasync_redirections`"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write | |
| 91 | 113 | } |
| 92 | 114 | |
| 93 | 115 | public function getAllActiveRecords() |
| 94 | 116 | { |
| 95 | 117 | global $wpdb; |
| 96 | - $tableName = $this->get_table_name(); | |
| 97 | 118 | |
| 98 | 119 | // Check cache first |
| 99 | 120 | $cache_key = 'metasync_active_redirections'; |
| 100 | 121 | $cached_redirections = wp_cache_get($cache_key, 'metasync'); |
| @@ -102,9 +123,13 @@ | ||
| 102 | 123 | if ($cached_redirections !== false) { |
| 103 | 124 | return $cached_redirections; |
| 104 | 125 | } |
| 105 | 126 | |
| 106 | - $redirections = $wpdb->get_results($wpdb->prepare("SELECT * FROM `$tableName` WHERE status = %s ORDER BY created_at DESC", 'active')); | |
| 127 | + // id tiebreaker: created_at has one-second resolution, so rows created in | |
| 128 | + // the same second (bulk import, migrations) would come back in arbitrary | |
| 129 | + // order — and the engine's first-match-wins scan would then be | |
| 130 | + // nondeterministic between requests. | |
| 131 | + $redirections = $wpdb->get_results($wpdb->prepare("SELECT * FROM `{$wpdb->prefix}metasync_redirections` WHERE status = %s ORDER BY created_at DESC, id DESC", 'active')); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — this read is the cache SOURCE for the object-cached active set (invalidated on every write) | |
| 107 | 132 | |
| 108 | 133 | // Cache for 1 hour |
| 109 | 134 | wp_cache_set($cache_key, $redirections, 'metasync', HOUR_IN_SECONDS); |
| 110 | 135 | |
| @@ -118,10 +143,9 @@ | ||
| 118 | 143 | */ |
| 119 | 144 | public function find($id) |
| 120 | 145 | { |
| 121 | 146 | global $wpdb; |
| 122 | - $tableName = $this->get_table_name(); | |
| 123 | - return $wpdb->get_row($wpdb->prepare("SELECT * FROM `$tableName` WHERE id = %d", intval($id))); | |
| 147 | + return $wpdb->get_row($wpdb->prepare("SELECT * FROM `{$wpdb->prefix}metasync_redirections` WHERE id = %d", intval($id))); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write | |
| 124 | 148 | } |
| 125 | 149 | |
| 126 | 150 | /** |
| 127 | 151 | * Add a record. |
| @@ -155,9 +179,9 @@ | ||
| 155 | 179 | $sources = $args['sources_from']; |
| 156 | 180 | $args['sources_from'] = serialize( array_combine( array_values( $sources ), array_fill( 0, count( $sources ), 'exact' ) ) ?: [] ); |
| 157 | 181 | } |
| 158 | 182 | |
| 159 | - $result = $wpdb->insert( $this->get_table_name(), $args ); | |
| 183 | + $result = $wpdb->insert( $this->get_table_name(), $args ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write | |
| 160 | 184 | |
| 161 | 185 | // Clear cache after adding |
| 162 | 186 | $this->clear_cache(); |
| 163 | 187 | |
| @@ -175,17 +199,20 @@ | ||
| 175 | 199 | |
| 176 | 200 | // Ensure table structure is up to date |
| 177 | 201 | $this->ensure_table_structure(); |
| 178 | 202 | |
| 179 | - $tableName = $this->get_table_name(); | |
| 180 | - $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM `$tableName` WHERE `id` = %s ", $id)); | |
| 181 | - if (!$row) return; | |
| 203 | + $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM `{$wpdb->prefix}metasync_redirections` WHERE `id` = %s ", $id)); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write | |
| 204 | + if (!$row) return false; | |
| 182 | 205 | |
| 183 | 206 | $args['updated_at'] = current_time('mysql'); |
| 184 | - $wpdb->update($tableName, $args, ['id' => $id]); | |
| 207 | + $result = $wpdb->update($wpdb->prefix . 'metasync_redirections', $args, ['id' => $id]); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write | |
| 185 | 208 | |
| 186 | 209 | // Clear cache after updating |
| 187 | 210 | $this->clear_cache(); |
| 211 | + | |
| 212 | + // Row count on success (0 = no-op write), false on query failure — | |
| 213 | + // callers checking `=== false` could never fire against void. | |
| 214 | + return $result; | |
| 188 | 215 | } |
| 189 | 216 | |
| 190 | 217 | /** |
| 191 | 218 | * Get total number of rows in the DB table). |
| @@ -192,10 +219,9 @@ | ||
| 192 | 219 | */ |
| 193 | 220 | public function get_count() |
| 194 | 221 | { |
| 195 | 222 | global $wpdb; |
| 196 | - $tableName = $this->get_table_name(); | |
| 197 | - return (int) $wpdb->get_var("SELECT COUNT(*) FROM `$tableName`"); | |
| 223 | + return (int) $wpdb->get_var("SELECT COUNT(*) FROM `{$wpdb->prefix}metasync_redirections`"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write | |
| 198 | 224 | } |
| 199 | 225 | |
| 200 | 226 | /** |
| 201 | 227 | * Delete a redirection record. |
| @@ -202,15 +228,11 @@ | ||
| 202 | 228 | */ |
| 203 | 229 | public function delete($items) |
| 204 | 230 | { |
| 205 | 231 | global $wpdb; |
| 206 | - $tableName = $this->get_table_name(); | |
| 207 | 232 | if (!is_array($items) || empty($items)) return; |
| 208 | - $ids = implode(',', array_fill(0, count($items), '%d')); | |
| 209 | - $wpdb->query($wpdb->prepare( | |
| 210 | - " | |
| 211 | - DELETE FROM `$tableName` | |
| 212 | - WHERE `id` IN ($ids) ", | |
| 233 | + $wpdb->query($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write | |
| 234 | + 'DELETE FROM `' . $wpdb->prefix . 'metasync_redirections` WHERE `id` IN (' . implode(',', array_fill(0, count($items), '%d')) . ')', | |
| 213 | 235 | $items |
| 214 | 236 | )); |
| 215 | 237 | |
| 216 | 238 | // Clear cache after deleting |
| @@ -222,25 +244,17 @@ | ||
| 222 | 244 | */ |
| 223 | 245 | public function update_status($items, $status) |
| 224 | 246 | { |
| 225 | 247 | global $wpdb; |
| 226 | - $tableName = $this->get_table_name(); | |
| 227 | 248 | if (!is_array($items) || empty($items)) return; |
| 228 | - $ids = implode(', ', array_fill(0, count($items), '%d')); | |
| 229 | - $set_status = $wpdb->prepare( | |
| 230 | - " | |
| 231 | - UPDATE `$tableName` | |
| 232 | - SET `status` = %s, `updated_at` = %s", | |
| 233 | - $status, | |
| 234 | - current_time('mysql') | |
| 249 | + $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write | |
| 250 | + $wpdb->prepare( | |
| 251 | + 'UPDATE `' . $wpdb->prefix . 'metasync_redirections` | |
| 252 | + SET `status` = %s, `updated_at` = %s | |
| 253 | + WHERE `id` IN (' . implode(', ', array_fill(0, count($items), '%d')) . ')', | |
| 254 | + array_merge(array($status, current_time('mysql')), array_map('intval', $items)) | |
| 255 | + ) | |
| 235 | 256 | ); |
| 236 | - $where = $wpdb->prepare( | |
| 237 | - " | |
| 238 | - WHERE `id` IN ( $ids )", | |
| 239 | - $items | |
| 240 | - ); | |
| 241 | - $query = "{$set_status}{$where}"; | |
| 242 | - $wpdb->query($query); | |
| 243 | 257 | |
| 244 | 258 | // Clear cache after updating status |
| 245 | 259 | $this->clear_cache(); |
| 246 | 260 | } |
| @@ -251,13 +265,19 @@ | ||
| 251 | 265 | */ |
| 252 | 266 | public function update_counter($row) |
| 253 | 267 | { |
| 254 | 268 | global $wpdb; |
| 255 | - $update_data = [ | |
| 256 | - 'last_accessed_at' => current_time('mysql'), | |
| 257 | - 'hits_count' => absint($row->hits_count) + 1, | |
| 258 | - ]; | |
| 259 | - $wpdb->update($this->get_table_name(), $update_data, ['id' => $row->id]); | |
| 269 | + // Atomic increment. The $row object comes from getAllActiveRecords(), | |
| 270 | + // which can be an hour-stale cache hit, and concurrent requests that | |
| 271 | + // both read the same hits_count would lose every hit but one. Bump in | |
| 272 | + // SQL so the stored counter is correct regardless of what $row saw. | |
| 273 | + $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write | |
| 274 | + $wpdb->prepare( | |
| 275 | + "UPDATE `{$wpdb->prefix}metasync_redirections` SET `hits_count` = `hits_count` + 1, `last_accessed_at` = %s WHERE `id` = %d", | |
| 276 | + current_time('mysql'), | |
| 277 | + absint($row->id) | |
| 278 | + ) | |
| 279 | + ); | |
| 260 | 280 | } |
| 261 | 281 | |
| 262 | 282 | /** |
| 263 | 283 | * Clear redirection cache |
| @@ -264,11 +284,41 @@ | ||
| 264 | 284 | */ |
| 265 | 285 | public function clear_cache() |
| 266 | 286 | { |
| 267 | 287 | wp_cache_delete('metasync_active_redirections', 'metasync'); |
| 288 | + // Redirect sources must leave the sitemap promptly: the sitemap | |
| 289 | + // generator listens for this on every add/update/delete/status write | |
| 290 | + // and busts its (30-day) caches so the next crawl regenerates. | |
| 291 | + do_action('metasync_redirections_changed'); | |
| 268 | 292 | } |
| 269 | 293 | |
| 270 | 294 | /** |
| 295 | + * Columns that may be used for ORDER BY. Every entry maps to the literal | |
| 296 | + * column name, so only allowlisted identifiers can reach the query. | |
| 297 | + */ | |
| 298 | + private function get_sortable_columns() | |
| 299 | + { | |
| 300 | + return [ | |
| 301 | + 'id', 'sources_from', 'url_redirect_to', 'http_code', 'hits_count', | |
| 302 | + 'status', 'pattern_type', 'created_at', 'updated_at', 'last_accessed_at', 'description', | |
| 303 | + ]; | |
| 304 | + } | |
| 305 | + | |
| 306 | + /** | |
| 307 | + * Build the ORDER BY clause for search()/count() from the allowlist. | |
| 308 | + * Falls back to the default ordering for unknown/invalid input. | |
| 309 | + */ | |
| 310 | + private function get_order_by_sql($filters) | |
| 311 | + { | |
| 312 | + $direction = (!empty($filters['order']) && strtoupper($filters['order']) === 'ASC') ? 'ASC' : 'DESC'; | |
| 313 | + $requested = isset($filters['order_by']) ? (string) $filters['order_by'] : ''; | |
| 314 | + if (!in_array($requested, $this->get_sortable_columns(), true)) { | |
| 315 | + $requested = 'created_at'; | |
| 316 | + } | |
| 317 | + return sanitize_sql_orderby($requested . ' ' . $direction); | |
| 318 | + } | |
| 319 | + | |
| 320 | + /** | |
| 271 | 321 | * Search redirections with filters |
| 272 | 322 | * @param array $filters Search filters |
| 273 | 323 | */ |
| 274 | 324 | public function search_redirections($filters = []) |
| @@ -273,13 +323,12 @@ | ||
| 273 | 323 | */ |
| 274 | 324 | public function search_redirections($filters = []) |
| 275 | 325 | { |
| 276 | 326 | global $wpdb; |
| 277 | - $tableName = $this->get_table_name(); | |
| 278 | - | |
| 327 | + | |
| 279 | 328 | $where_conditions = ['1=1']; |
| 280 | 329 | $where_values = []; |
| 281 | - | |
| 330 | + | |
| 282 | 331 | if (!empty($filters['search'])) { |
| 283 | 332 | $where_conditions[] = "(sources_from LIKE %s OR url_redirect_to LIKE %s OR description LIKE %s)"; |
| 284 | 333 | $search_term = '%' . $wpdb->esc_like($filters['search']) . '%'; |
| 285 | 334 | $where_values[] = $search_term; |
| @@ -285,43 +334,43 @@ | ||
| 285 | 334 | $where_values[] = $search_term; |
| 286 | 335 | $where_values[] = $search_term; |
| 287 | 336 | $where_values[] = $search_term; |
| 288 | 337 | } |
| 289 | - | |
| 338 | + | |
| 290 | 339 | if (!empty($filters['status'])) { |
| 291 | 340 | $where_conditions[] = "status = %s"; |
| 292 | 341 | $where_values[] = $filters['status']; |
| 293 | 342 | } |
| 294 | - | |
| 343 | + | |
| 295 | 344 | if (!empty($filters['pattern_type'])) { |
| 296 | 345 | $where_conditions[] = "pattern_type = %s"; |
| 297 | 346 | $where_values[] = $filters['pattern_type']; |
| 298 | 347 | } |
| 299 | - | |
| 348 | + | |
| 300 | 349 | if (!empty($filters['http_code'])) { |
| 301 | 350 | $where_conditions[] = "http_code = %d"; |
| 302 | 351 | $where_values[] = intval($filters['http_code']); |
| 303 | 352 | } |
| 304 | - | |
| 305 | - $where_clause = implode(' AND ', $where_conditions); | |
| 306 | - $order_by = !empty($filters['order_by']) ? sanitize_sql_orderby($filters['order_by']) : 'created_at'; | |
| 307 | - $order = !empty($filters['order']) && strtoupper($filters['order']) === 'ASC' ? 'ASC' : 'DESC'; | |
| 308 | - | |
| 309 | - // Add pagination support | |
| 310 | - $limit_clause = ''; | |
| 311 | - if (isset($filters['per_page']) && isset($filters['offset'])) { | |
| 312 | - $limit_clause = " LIMIT %d OFFSET %d"; | |
| 313 | - $where_values[] = intval($filters['per_page']); | |
| 314 | - $where_values[] = intval($filters['offset']); | |
| 353 | + | |
| 354 | + $order_by_sql = $this->get_order_by_sql($filters) ?: 'created_at DESC'; | |
| 355 | + | |
| 356 | + // The query is assembled from literal placeholder fragments only; the | |
| 357 | + // ORDER BY fragment comes from the sortable-column allowlist wrapped in | |
| 358 | + // sanitize_sql_orderby(). All dynamic values bind via prepare() below. | |
| 359 | + $query = 'SELECT * FROM `' . $wpdb->prefix . 'metasync_redirections` WHERE ' . implode(' AND ', $where_conditions) | |
| 360 | + . ' ORDER BY ' . $order_by_sql | |
| 361 | + . (isset($filters['per_page'], $filters['offset']) ? ' LIMIT %d OFFSET %d' : ''); | |
| 362 | + $values = isset($filters['per_page'], $filters['offset']) | |
| 363 | + ? array_merge($where_values, array(intval($filters['per_page']), intval($filters['offset']))) | |
| 364 | + : $where_values; | |
| 365 | + | |
| 366 | + // With no filters the assembled query is literal-only (no | |
| 367 | + // placeholders); prepare() rejects placeholder-less queries, so only | |
| 368 | + // route it through prepare() when there are values to bind. | |
| 369 | + if (empty($values)) { | |
| 370 | + return $wpdb->get_results($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery -- literal fragments only, nothing to bind | |
| 315 | 371 | } |
| 316 | - | |
| 317 | - $query = "SELECT * FROM `$tableName` WHERE $where_clause ORDER BY $order_by $order" . $limit_clause; | |
| 318 | - | |
| 319 | - if (!empty($where_values)) { | |
| 320 | - return $wpdb->get_results($wpdb->prepare($query, $where_values)); | |
| 321 | - } else { | |
| 322 | - return $wpdb->get_results($query); | |
| 323 | - } | |
| 372 | + return $wpdb->get_results($wpdb->prepare($query, $values)); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery -- literal fragments + allowlisted ORDER BY, values bound via prepare() | |
| 324 | 373 | } |
| 325 | 374 | |
| 326 | 375 | /** |
| 327 | 376 | * Count total redirections with filters (for pagination) |
| @@ -329,13 +378,12 @@ | ||
| 329 | 378 | */ |
| 330 | 379 | public function count_redirections($filters = []) |
| 331 | 380 | { |
| 332 | 381 | global $wpdb; |
| 333 | - $tableName = $this->get_table_name(); | |
| 334 | - | |
| 382 | + | |
| 335 | 383 | $where_conditions = ['1=1']; |
| 336 | 384 | $where_values = []; |
| 337 | - | |
| 385 | + | |
| 338 | 386 | if (!empty($filters['search'])) { |
| 339 | 387 | $where_conditions[] = "(sources_from LIKE %s OR url_redirect_to LIKE %s OR description LIKE %s)"; |
| 340 | 388 | $search_term = '%' . $wpdb->esc_like($filters['search']) . '%'; |
| 341 | 389 | $where_values[] = $search_term; |
| @@ -341,31 +389,32 @@ | ||
| 341 | 389 | $where_values[] = $search_term; |
| 342 | 390 | $where_values[] = $search_term; |
| 343 | 391 | $where_values[] = $search_term; |
| 344 | 392 | } |
| 345 | - | |
| 393 | + | |
| 346 | 394 | if (!empty($filters['status'])) { |
| 347 | 395 | $where_conditions[] = "status = %s"; |
| 348 | 396 | $where_values[] = $filters['status']; |
| 349 | 397 | } |
| 350 | - | |
| 398 | + | |
| 351 | 399 | if (!empty($filters['pattern_type'])) { |
| 352 | 400 | $where_conditions[] = "pattern_type = %s"; |
| 353 | 401 | $where_values[] = $filters['pattern_type']; |
| 354 | 402 | } |
| 355 | - | |
| 403 | + | |
| 356 | 404 | if (!empty($filters['http_code'])) { |
| 357 | 405 | $where_conditions[] = "http_code = %d"; |
| 358 | 406 | $where_values[] = intval($filters['http_code']); |
| 359 | 407 | } |
| 360 | - | |
| 361 | - $where_clause = implode(' AND ', $where_conditions); | |
| 362 | - $query = "SELECT COUNT(*) FROM `$tableName` WHERE $where_clause"; | |
| 363 | - | |
| 364 | - if (!empty($where_values)) { | |
| 365 | - return $wpdb->get_var($wpdb->prepare($query, $where_values)); | |
| 366 | - } else { | |
| 367 | - return $wpdb->get_var($query); | |
| 408 | + | |
| 409 | + // The query is assembled from literal placeholder fragments only; all | |
| 410 | + // dynamic values bind via prepare() below. | |
| 411 | + $query = 'SELECT COUNT(*) FROM `' . $wpdb->prefix . 'metasync_redirections` WHERE ' . implode(' AND ', $where_conditions); | |
| 412 | + | |
| 413 | + // Placeholder-less queries must skip prepare() (see search above). | |
| 414 | + if (empty($where_values)) { | |
| 415 | + return $wpdb->get_var($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery -- literal fragments only, nothing to bind | |
| 368 | 416 | } |
| 417 | + return $wpdb->get_var($wpdb->prepare($query, $where_values)); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery -- literal fragments only, values bound via prepare() | |
| 369 | 418 | } |
| 370 | 419 | } |
| 371 | 420 | } |