← All changes
|
redirections/class-metasync-redirection-database.php
+118
-103
2.7.0
→
2.7.1
View file →
| @@ -7,8 +7,13 @@ | ||
| 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"; |
| @@ -32,9 +37,9 @@ | ||
| 32 | 37 | global $wpdb; |
| 33 | 38 | $table_name = $this->get_table_name(); |
| 34 | 39 | |
| 35 | 40 | // Check if table exists |
| 36 | - 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 | |
| 37 | 42 | // Table doesn't exist, run full migration |
| 38 | 43 | require_once dirname(__FILE__, 2) . '/database/class-db-migrations.php'; |
| 39 | 44 | MetaSync_DBMigration::activation(); |
| 40 | 45 | return; |
| @@ -40,53 +45,55 @@ | ||
| 40 | 45 | return; |
| 41 | 46 | } |
| 42 | 47 | |
| 43 | 48 | // Check if required columns exist |
| 44 | - $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 | |
| 45 | 50 | |
| 46 | - $required_columns = [ | |
| 47 | - 'pattern_type' => "ALTER TABLE {$table_name} ADD COLUMN pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex', 'wildcard') NOT NULL DEFAULT 'exact' AFTER status", | |
| 48 | - 'regex_pattern' => "ALTER TABLE {$table_name} ADD COLUMN regex_pattern TEXT NULL AFTER pattern_type", | |
| 49 | - 'description' => "ALTER TABLE {$table_name} ADD COLUMN description TEXT NULL AFTER regex_pattern", | |
| 50 | - 'created_at' => "ALTER TABLE {$table_name} ADD COLUMN created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER description", | |
| 51 | - 'updated_at' => "ALTER TABLE {$table_name} ADD COLUMN updated_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER created_at", | |
| 52 | - '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" | |
| 53 | - ]; | |
| 54 | - | |
| 55 | - foreach ($required_columns as $column => $sql) { | |
| 56 | - if (!in_array($column, $columns)) { | |
| 57 | - $wpdb->query($sql); | |
| 58 | - } | |
| 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 | |
| 59 | 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 | + } | |
| 60 | 69 | |
| 61 | 70 | // Widen pattern_type for installs whose column predates wildcard |
| 62 | 71 | // matching — column-exists checks never re-ALTER, so the enum would |
| 63 | 72 | // otherwise keep silently rejecting wildcard rows. |
| 64 | 73 | if (in_array('pattern_type', $columns)) { |
| 65 | - $column_info = $wpdb->get_row($wpdb->prepare("SHOW COLUMNS FROM {$table_name} LIKE %s", 'pattern_type')); | |
| 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 | |
| 66 | 75 | if ($column_info && strpos((string) $column_info->Type, 'wildcard') === false) { |
| 67 | - $wpdb->query("ALTER TABLE {$table_name} MODIFY COLUMN pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex', 'wildcard') NOT NULL DEFAULT 'exact'"); | |
| 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 | |
| 68 | 77 | } |
| 69 | 78 | } |
| 70 | 79 | |
| 71 | 80 | // Check and add indexes |
| 72 | - $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 | |
| 73 | 82 | $index_names = array_column($indexes, 'Key_name'); |
| 74 | 83 | |
| 75 | - $required_indexes = [ | |
| 76 | - 'pattern_type' => "ALTER TABLE {$table_name} ADD KEY pattern_type (pattern_type)", | |
| 77 | - 'created_at' => "ALTER TABLE {$table_name} ADD KEY created_at (created_at)", | |
| 78 | - 'status_created' => "ALTER TABLE {$table_name} ADD KEY status_created (status, created_at)" | |
| 79 | - ]; | |
| 80 | - | |
| 81 | - foreach ($required_indexes as $index => $sql) { | |
| 82 | - if (!in_array($index, $index_names)) { | |
| 83 | - $wpdb->query($sql); | |
| 84 | - } | |
| 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 | |
| 85 | 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 | + } | |
| 86 | 93 | |
| 87 | 94 | // Set default pattern_type for existing records |
| 88 | - $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 | |
| 89 | 96 | } |
| 90 | 97 | |
| 91 | 98 | /** |
| 92 | 99 | * Manually trigger table structure update |
| @@ -101,16 +108,14 @@ | ||
| 101 | 108 | |
| 102 | 109 | public function getAllRecords() |
| 103 | 110 | { |
| 104 | 111 | global $wpdb; |
| 105 | - $tableName = $this->get_table_name(); | |
| 106 | - 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 | |
| 107 | 113 | } |
| 108 | 114 | |
| 109 | 115 | public function getAllActiveRecords() |
| 110 | 116 | { |
| 111 | 117 | global $wpdb; |
| 112 | - $tableName = $this->get_table_name(); | |
| 113 | 118 | |
| 114 | 119 | // Check cache first |
| 115 | 120 | $cache_key = 'metasync_active_redirections'; |
| 116 | 121 | $cached_redirections = wp_cache_get($cache_key, 'metasync'); |
| @@ -122,9 +127,9 @@ | ||
| 122 | 127 | // id tiebreaker: created_at has one-second resolution, so rows created in |
| 123 | 128 | // the same second (bulk import, migrations) would come back in arbitrary |
| 124 | 129 | // order — and the engine's first-match-wins scan would then be |
| 125 | 130 | // nondeterministic between requests. |
| 126 | - $redirections = $wpdb->get_results($wpdb->prepare("SELECT * FROM `$tableName` WHERE status = %s ORDER BY created_at DESC, id DESC", 'active')); | |
| 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) | |
| 127 | 132 | |
| 128 | 133 | // Cache for 1 hour |
| 129 | 134 | wp_cache_set($cache_key, $redirections, 'metasync', HOUR_IN_SECONDS); |
| 130 | 135 | |
| @@ -138,10 +143,9 @@ | ||
| 138 | 143 | */ |
| 139 | 144 | public function find($id) |
| 140 | 145 | { |
| 141 | 146 | global $wpdb; |
| 142 | - $tableName = $this->get_table_name(); | |
| 143 | - 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 | |
| 144 | 148 | } |
| 145 | 149 | |
| 146 | 150 | /** |
| 147 | 151 | * Add a record. |
| @@ -175,9 +179,9 @@ | ||
| 175 | 179 | $sources = $args['sources_from']; |
| 176 | 180 | $args['sources_from'] = serialize( array_combine( array_values( $sources ), array_fill( 0, count( $sources ), 'exact' ) ) ?: [] ); |
| 177 | 181 | } |
| 178 | 182 | |
| 179 | - $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 | |
| 180 | 184 | |
| 181 | 185 | // Clear cache after adding |
| 182 | 186 | $this->clear_cache(); |
| 183 | 187 | |
| @@ -195,14 +199,13 @@ | ||
| 195 | 199 | |
| 196 | 200 | // Ensure table structure is up to date |
| 197 | 201 | $this->ensure_table_structure(); |
| 198 | 202 | |
| 199 | - $tableName = $this->get_table_name(); | |
| 200 | - $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM `$tableName` WHERE `id` = %s ", $id)); | |
| 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 | |
| 201 | 204 | if (!$row) return false; |
| 202 | 205 | |
| 203 | 206 | $args['updated_at'] = current_time('mysql'); |
| 204 | - $result = $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 | |
| 205 | 208 | |
| 206 | 209 | // Clear cache after updating |
| 207 | 210 | $this->clear_cache(); |
| 208 | 211 | |
| @@ -216,10 +219,9 @@ | ||
| 216 | 219 | */ |
| 217 | 220 | public function get_count() |
| 218 | 221 | { |
| 219 | 222 | global $wpdb; |
| 220 | - $tableName = $this->get_table_name(); | |
| 221 | - 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 | |
| 222 | 224 | } |
| 223 | 225 | |
| 224 | 226 | /** |
| 225 | 227 | * Delete a redirection record. |
| @@ -226,15 +228,11 @@ | ||
| 226 | 228 | */ |
| 227 | 229 | public function delete($items) |
| 228 | 230 | { |
| 229 | 231 | global $wpdb; |
| 230 | - $tableName = $this->get_table_name(); | |
| 231 | 232 | if (!is_array($items) || empty($items)) return; |
| 232 | - $ids = implode(',', array_fill(0, count($items), '%d')); | |
| 233 | - $wpdb->query($wpdb->prepare( | |
| 234 | - " | |
| 235 | - DELETE FROM `$tableName` | |
| 236 | - 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')) . ')', | |
| 237 | 235 | $items |
| 238 | 236 | )); |
| 239 | 237 | |
| 240 | 238 | // Clear cache after deleting |
| @@ -246,25 +244,17 @@ | ||
| 246 | 244 | */ |
| 247 | 245 | public function update_status($items, $status) |
| 248 | 246 | { |
| 249 | 247 | global $wpdb; |
| 250 | - $tableName = $this->get_table_name(); | |
| 251 | 248 | if (!is_array($items) || empty($items)) return; |
| 252 | - $ids = implode(', ', array_fill(0, count($items), '%d')); | |
| 253 | - $set_status = $wpdb->prepare( | |
| 254 | - " | |
| 255 | - UPDATE `$tableName` | |
| 256 | - SET `status` = %s, `updated_at` = %s", | |
| 257 | - $status, | |
| 258 | - 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 | + ) | |
| 259 | 256 | ); |
| 260 | - $where = $wpdb->prepare( | |
| 261 | - " | |
| 262 | - WHERE `id` IN ( $ids )", | |
| 263 | - $items | |
| 264 | - ); | |
| 265 | - $query = "{$set_status}{$where}"; | |
| 266 | - $wpdb->query($query); | |
| 267 | 257 | |
| 268 | 258 | // Clear cache after updating status |
| 269 | 259 | $this->clear_cache(); |
| 270 | 260 | } |
| @@ -279,11 +269,11 @@ | ||
| 279 | 269 | // Atomic increment. The $row object comes from getAllActiveRecords(), |
| 280 | 270 | // which can be an hour-stale cache hit, and concurrent requests that |
| 281 | 271 | // both read the same hits_count would lose every hit but one. Bump in |
| 282 | 272 | // SQL so the stored counter is correct regardless of what $row saw. |
| 283 | - $wpdb->query( | |
| 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 | |
| 284 | 274 | $wpdb->prepare( |
| 285 | - "UPDATE `" . $this->get_table_name() . "` SET `hits_count` = `hits_count` + 1, `last_accessed_at` = %s WHERE `id` = %d", | |
| 275 | + "UPDATE `{$wpdb->prefix}metasync_redirections` SET `hits_count` = `hits_count` + 1, `last_accessed_at` = %s WHERE `id` = %d", | |
| 286 | 276 | current_time('mysql'), |
| 287 | 277 | absint($row->id) |
| 288 | 278 | ) |
| 289 | 279 | ); |
| @@ -301,8 +291,34 @@ | ||
| 301 | 291 | do_action('metasync_redirections_changed'); |
| 302 | 292 | } |
| 303 | 293 | |
| 304 | 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 | + /** | |
| 305 | 321 | * Search redirections with filters |
| 306 | 322 | * @param array $filters Search filters |
| 307 | 323 | */ |
| 308 | 324 | public function search_redirections($filters = []) |
| @@ -307,13 +323,12 @@ | ||
| 307 | 323 | */ |
| 308 | 324 | public function search_redirections($filters = []) |
| 309 | 325 | { |
| 310 | 326 | global $wpdb; |
| 311 | - $tableName = $this->get_table_name(); | |
| 312 | - | |
| 327 | + | |
| 313 | 328 | $where_conditions = ['1=1']; |
| 314 | 329 | $where_values = []; |
| 315 | - | |
| 330 | + | |
| 316 | 331 | if (!empty($filters['search'])) { |
| 317 | 332 | $where_conditions[] = "(sources_from LIKE %s OR url_redirect_to LIKE %s OR description LIKE %s)"; |
| 318 | 333 | $search_term = '%' . $wpdb->esc_like($filters['search']) . '%'; |
| 319 | 334 | $where_values[] = $search_term; |
| @@ -319,43 +334,43 @@ | ||
| 319 | 334 | $where_values[] = $search_term; |
| 320 | 335 | $where_values[] = $search_term; |
| 321 | 336 | $where_values[] = $search_term; |
| 322 | 337 | } |
| 323 | - | |
| 338 | + | |
| 324 | 339 | if (!empty($filters['status'])) { |
| 325 | 340 | $where_conditions[] = "status = %s"; |
| 326 | 341 | $where_values[] = $filters['status']; |
| 327 | 342 | } |
| 328 | - | |
| 343 | + | |
| 329 | 344 | if (!empty($filters['pattern_type'])) { |
| 330 | 345 | $where_conditions[] = "pattern_type = %s"; |
| 331 | 346 | $where_values[] = $filters['pattern_type']; |
| 332 | 347 | } |
| 333 | - | |
| 348 | + | |
| 334 | 349 | if (!empty($filters['http_code'])) { |
| 335 | 350 | $where_conditions[] = "http_code = %d"; |
| 336 | 351 | $where_values[] = intval($filters['http_code']); |
| 337 | 352 | } |
| 338 | - | |
| 339 | - $where_clause = implode(' AND ', $where_conditions); | |
| 340 | - $order_by = !empty($filters['order_by']) ? sanitize_sql_orderby($filters['order_by']) : 'created_at'; | |
| 341 | - $order = !empty($filters['order']) && strtoupper($filters['order']) === 'ASC' ? 'ASC' : 'DESC'; | |
| 342 | - | |
| 343 | - // Add pagination support | |
| 344 | - $limit_clause = ''; | |
| 345 | - if (isset($filters['per_page']) && isset($filters['offset'])) { | |
| 346 | - $limit_clause = " LIMIT %d OFFSET %d"; | |
| 347 | - $where_values[] = intval($filters['per_page']); | |
| 348 | - $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 | |
| 349 | 371 | } |
| 350 | - | |
| 351 | - $query = "SELECT * FROM `$tableName` WHERE $where_clause ORDER BY $order_by $order" . $limit_clause; | |
| 352 | - | |
| 353 | - if (!empty($where_values)) { | |
| 354 | - return $wpdb->get_results($wpdb->prepare($query, $where_values)); | |
| 355 | - } else { | |
| 356 | - return $wpdb->get_results($query); | |
| 357 | - } | |
| 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() | |
| 358 | 373 | } |
| 359 | 374 | |
| 360 | 375 | /** |
| 361 | 376 | * Count total redirections with filters (for pagination) |
| @@ -363,13 +378,12 @@ | ||
| 363 | 378 | */ |
| 364 | 379 | public function count_redirections($filters = []) |
| 365 | 380 | { |
| 366 | 381 | global $wpdb; |
| 367 | - $tableName = $this->get_table_name(); | |
| 368 | - | |
| 382 | + | |
| 369 | 383 | $where_conditions = ['1=1']; |
| 370 | 384 | $where_values = []; |
| 371 | - | |
| 385 | + | |
| 372 | 386 | if (!empty($filters['search'])) { |
| 373 | 387 | $where_conditions[] = "(sources_from LIKE %s OR url_redirect_to LIKE %s OR description LIKE %s)"; |
| 374 | 388 | $search_term = '%' . $wpdb->esc_like($filters['search']) . '%'; |
| 375 | 389 | $where_values[] = $search_term; |
| @@ -375,31 +389,32 @@ | ||
| 375 | 389 | $where_values[] = $search_term; |
| 376 | 390 | $where_values[] = $search_term; |
| 377 | 391 | $where_values[] = $search_term; |
| 378 | 392 | } |
| 379 | - | |
| 393 | + | |
| 380 | 394 | if (!empty($filters['status'])) { |
| 381 | 395 | $where_conditions[] = "status = %s"; |
| 382 | 396 | $where_values[] = $filters['status']; |
| 383 | 397 | } |
| 384 | - | |
| 398 | + | |
| 385 | 399 | if (!empty($filters['pattern_type'])) { |
| 386 | 400 | $where_conditions[] = "pattern_type = %s"; |
| 387 | 401 | $where_values[] = $filters['pattern_type']; |
| 388 | 402 | } |
| 389 | - | |
| 403 | + | |
| 390 | 404 | if (!empty($filters['http_code'])) { |
| 391 | 405 | $where_conditions[] = "http_code = %d"; |
| 392 | 406 | $where_values[] = intval($filters['http_code']); |
| 393 | 407 | } |
| 394 | - | |
| 395 | - $where_clause = implode(' AND ', $where_conditions); | |
| 396 | - $query = "SELECT COUNT(*) FROM `$tableName` WHERE $where_clause"; | |
| 397 | - | |
| 398 | - if (!empty($where_values)) { | |
| 399 | - return $wpdb->get_var($wpdb->prepare($query, $where_values)); | |
| 400 | - } else { | |
| 401 | - 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 | |
| 402 | 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() | |
| 403 | 418 | } |
| 404 | 419 | } |
| 405 | 420 | } |