| @@ -1,5 +1,6 @@ | ||
| 1 | 1 | <?php |
| 2 | + | |
| 2 | 3 | /** |
| 3 | 4 | * Database Manager Class |
| 4 | 5 | * |
| 5 | 6 | * Handles database operations and provides repository pattern |
| @@ -25,9 +26,9 @@ | ||
| 25 | 26 | * |
| 26 | 27 | * @since 1.0.0 |
| 27 | 28 | */ |
| 28 | 29 | class Database { |
| 29 | - | |
| 30 | + | |
| 30 | 31 | /** |
| 31 | 32 | * WordPress database instance |
| 32 | 33 | * |
| 33 | 34 | * @var \wpdb |
| @@ -32,9 +33,9 @@ | ||
| 32 | 33 | * |
| 33 | 34 | * @var \wpdb |
| 34 | 35 | */ |
| 35 | 36 | private \wpdb $wpdb; |
| 36 | - | |
| 37 | + | |
| 37 | 38 | /** |
| 38 | 39 | * Table names |
| 39 | 40 | * |
| 40 | 41 | * @var array |
| @@ -39,9 +40,9 @@ | ||
| 39 | 40 | * |
| 40 | 41 | * @var array |
| 41 | 42 | */ |
| 42 | 43 | private array $tables; |
| 43 | - | |
| 44 | + | |
| 44 | 45 | /** |
| 45 | 46 | * Constructor |
| 46 | 47 | */ |
| 47 | 48 | public function __construct() { |
| @@ -46,9 +47,9 @@ | ||
| 46 | 47 | */ |
| 47 | 48 | public function __construct() { |
| 48 | 49 | global $wpdb; |
| 49 | 50 | $this->wpdb = $wpdb; |
| 50 | - | |
| 51 | + | |
| 51 | 52 | $this->tables = [ |
| 52 | 53 | 'ai_cache' => $wpdb->prefix . 'thinkrank_ai_cache', |
| 53 | 54 | 'ai_usage' => $wpdb->prefix . 'thinkrank_ai_usage', |
| 54 | 55 | 'content_briefs' => $wpdb->prefix . 'thinkrank_content_briefs', |
| @@ -53,11 +54,12 @@ | ||
| 53 | 54 | 'ai_usage' => $wpdb->prefix . 'thinkrank_ai_usage', |
| 54 | 55 | 'content_briefs' => $wpdb->prefix . 'thinkrank_content_briefs', |
| 55 | 56 | 'seo_scores' => $wpdb->prefix . 'thinkrank_seo_scores', |
| 56 | 57 | 'seo_performance' => $wpdb->prefix . 'thinkrank_seo_performance', |
| 58 | + 'instant_indexing_logs' => $wpdb->prefix . 'thinkrank_instant_indexing_logs', | |
| 57 | 59 | ]; |
| 58 | 60 | } |
| 59 | - | |
| 61 | + | |
| 60 | 62 | /** |
| 61 | 63 | * Initialize database operations |
| 62 | 64 | * |
| 63 | 65 | * @return void |
| @@ -68,9 +70,9 @@ | ||
| 68 | 70 | |
| 69 | 71 | // Hook the missing usage analytics cron handler |
| 70 | 72 | add_action('thinkrank_usage_analytics', [$this, 'process_weekly_analytics']); |
| 71 | 73 | } |
| 72 | - | |
| 74 | + | |
| 73 | 75 | /** |
| 74 | 76 | * Get table name |
| 75 | 77 | * |
| 76 | 78 | * @param string $table Table identifier |
| @@ -80,12 +82,12 @@ | ||
| 80 | 82 | public function get_table(string $table): string { |
| 81 | 83 | if (!isset($this->tables[$table])) { |
| 82 | 84 | throw new \InvalidArgumentException(sprintf("Table '%s' not found", esc_html($table))); |
| 83 | 85 | } |
| 84 | - | |
| 86 | + | |
| 85 | 87 | return $this->tables[$table]; |
| 86 | 88 | } |
| 87 | - | |
| 89 | + | |
| 88 | 90 | /** |
| 89 | 91 | * Execute prepared query safely |
| 90 | 92 | * |
| 91 | 93 | * @param string $query SQL query with placeholders |
| @@ -94,19 +96,19 @@ | ||
| 94 | 96 | */ |
| 95 | 97 | public function query(string $query, array $args = []) { |
| 96 | 98 | if (!empty($args)) { |
| 97 | 99 | // Prepare the query first, then execute |
| 98 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared in the line below | |
| 100 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is prepared in the line below | |
| 99 | 101 | $prepared_query = $this->wpdb->prepare($query, $args); |
| 100 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Using prepared query from above | |
| 102 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Using prepared query from above | |
| 101 | 103 | return $this->wpdb->query($prepared_query); |
| 102 | 104 | } |
| 103 | 105 | |
| 104 | 106 | // For queries without parameters, execute directly (safe for static queries) |
| 105 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- No user input in static queries | |
| 107 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- No user input in static queries | |
| 106 | 108 | return $this->wpdb->query($query); |
| 107 | 109 | } |
| 108 | - | |
| 110 | + | |
| 109 | 111 | /** |
| 110 | 112 | * Get single row |
| 111 | 113 | * |
| 112 | 114 | * @param string $query SQL query with placeholders |
| @@ -116,19 +118,19 @@ | ||
| 116 | 118 | */ |
| 117 | 119 | public function get_row(string $query, array $args = [], string $output = OBJECT) { |
| 118 | 120 | if (!empty($args)) { |
| 119 | 121 | // Prepare the query first, then execute |
| 120 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared in the line below | |
| 122 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is prepared in the line below | |
| 121 | 123 | $prepared_query = $this->wpdb->prepare($query, $args); |
| 122 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Using prepared query from above | |
| 124 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Using prepared query from above | |
| 123 | 125 | return $this->wpdb->get_row($prepared_query, $output); |
| 124 | 126 | } |
| 125 | 127 | |
| 126 | 128 | // For queries without parameters, execute directly (safe for static queries) |
| 127 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- No user input in static queries | |
| 129 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- No user input in static queries | |
| 128 | 130 | return $this->wpdb->get_row($query, $output); |
| 129 | 131 | } |
| 130 | - | |
| 132 | + | |
| 131 | 133 | /** |
| 132 | 134 | * Get multiple rows |
| 133 | 135 | * |
| 134 | 136 | * @param string $query SQL query with placeholders |
| @@ -138,21 +140,21 @@ | ||
| 138 | 140 | */ |
| 139 | 141 | public function get_results(string $query, array $args = [], string $output = OBJECT): array { |
| 140 | 142 | if (!empty($args)) { |
| 141 | 143 | // Prepare the query first, then execute |
| 142 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared in the line below | |
| 144 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is prepared in the line below | |
| 143 | 145 | $prepared_query = $this->wpdb->prepare($query, $args); |
| 144 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Using prepared query from above | |
| 146 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Using prepared query from above | |
| 145 | 147 | $results = $this->wpdb->get_results($prepared_query, $output); |
| 146 | 148 | } else { |
| 147 | 149 | // For queries without parameters, execute directly (safe for static queries) |
| 148 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- No user input in static queries | |
| 150 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- No user input in static queries | |
| 149 | 151 | $results = $this->wpdb->get_results($query, $output); |
| 150 | 152 | } |
| 151 | 153 | |
| 152 | 154 | return is_array($results) ? $results : []; |
| 153 | 155 | } |
| 154 | - | |
| 156 | + | |
| 155 | 157 | /** |
| 156 | 158 | * Get single variable |
| 157 | 159 | * |
| 158 | 160 | * @param string $query SQL query with placeholders |
| @@ -161,19 +163,19 @@ | ||
| 161 | 163 | */ |
| 162 | 164 | public function get_var(string $query, array $args = []) { |
| 163 | 165 | if (!empty($args)) { |
| 164 | 166 | // Prepare the query first, then execute |
| 165 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared in the line below | |
| 167 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is prepared in the line below | |
| 166 | 168 | $prepared_query = $this->wpdb->prepare($query, $args); |
| 167 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Using prepared query from above | |
| 169 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Using prepared query from above | |
| 168 | 170 | return $this->wpdb->get_var($prepared_query); |
| 169 | 171 | } |
| 170 | 172 | |
| 171 | 173 | // For queries without parameters, execute directly (safe for static queries) |
| 172 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- No user input in static queries | |
| 174 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- No user input in static queries | |
| 173 | 175 | return $this->wpdb->get_var($query); |
| 174 | 176 | } |
| 175 | - | |
| 177 | + | |
| 176 | 178 | /** |
| 177 | 179 | * Insert data into table |
| 178 | 180 | * |
| 179 | 181 | * @param string $table Table identifier |
| @@ -182,11 +184,11 @@ | ||
| 182 | 184 | * @return int|false Insert ID or false on failure |
| 183 | 185 | */ |
| 184 | 186 | public function insert(string $table, array $data, array $format = []) { |
| 185 | 187 | $table_name = $this->get_table($table); |
| 186 | - | |
| 188 | + | |
| 187 | 189 | $result = $this->wpdb->insert($table_name, $data, $format); |
| 188 | - | |
| 190 | + | |
| 189 | 191 | if (false === $result) { |
| 190 | 192 | $this->log_error('Insert failed', [ |
| 191 | 193 | 'table' => $table, |
| 192 | 194 | 'data' => $data, |
| @@ -193,12 +195,12 @@ | ||
| 193 | 195 | 'error' => $this->wpdb->last_error |
| 194 | 196 | ]); |
| 195 | 197 | return false; |
| 196 | 198 | } |
| 197 | - | |
| 199 | + | |
| 198 | 200 | return $this->wpdb->insert_id; |
| 199 | 201 | } |
| 200 | - | |
| 202 | + | |
| 201 | 203 | /** |
| 202 | 204 | * Update data in table |
| 203 | 205 | * |
| 204 | 206 | * @param string $table Table identifier |
| @@ -209,11 +211,11 @@ | ||
| 209 | 211 | * @return int|false Number of rows updated or false on failure |
| 210 | 212 | */ |
| 211 | 213 | public function update(string $table, array $data, array $where, array $format = [], array $where_format = []) { |
| 212 | 214 | $table_name = $this->get_table($table); |
| 213 | - | |
| 215 | + | |
| 214 | 216 | $result = $this->wpdb->update($table_name, $data, $where, $format, $where_format); |
| 215 | - | |
| 217 | + | |
| 216 | 218 | if (false === $result) { |
| 217 | 219 | $this->log_error('Update failed', [ |
| 218 | 220 | 'table' => $table, |
| 219 | 221 | 'data' => $data, |
| @@ -220,12 +222,12 @@ | ||
| 220 | 222 | 'where' => $where, |
| 221 | 223 | 'error' => $this->wpdb->last_error |
| 222 | 224 | ]); |
| 223 | 225 | } |
| 224 | - | |
| 226 | + | |
| 225 | 227 | return $result; |
| 226 | 228 | } |
| 227 | - | |
| 229 | + | |
| 228 | 230 | /** |
| 229 | 231 | * Delete data from table |
| 230 | 232 | * |
| 231 | 233 | * @param string $table Table identifier |
| @@ -234,11 +236,11 @@ | ||
| 234 | 236 | * @return int|false Number of rows deleted or false on failure |
| 235 | 237 | */ |
| 236 | 238 | public function delete(string $table, array $where, array $where_format = []) { |
| 237 | 239 | $table_name = $this->get_table($table); |
| 238 | - | |
| 240 | + | |
| 239 | 241 | $result = $this->wpdb->delete($table_name, $where, $where_format); |
| 240 | - | |
| 242 | + | |
| 241 | 243 | if (false === $result) { |
| 242 | 244 | $this->log_error('Delete failed', [ |
| 243 | 245 | 'table' => $table, |
| 244 | 246 | 'where' => $where, |
| @@ -244,12 +246,12 @@ | ||
| 244 | 246 | 'where' => $where, |
| 245 | 247 | 'error' => $this->wpdb->last_error |
| 246 | 248 | ]); |
| 247 | 249 | } |
| 248 | - | |
| 250 | + | |
| 249 | 251 | return $result; |
| 250 | 252 | } |
| 251 | - | |
| 253 | + | |
| 252 | 254 | /** |
| 253 | 255 | * Start database transaction |
| 254 | 256 | * |
| 255 | 257 | * @return void |
| @@ -277,9 +279,9 @@ | ||
| 277 | 279 | public function rollback(): void { |
| 278 | 280 | // Transaction commands don't need preparation as they contain no user input |
| 279 | 281 | $this->wpdb->query('ROLLBACK'); |
| 280 | 282 | } |
| 281 | - | |
| 283 | + | |
| 282 | 284 | /** |
| 283 | 285 | * Get last database error |
| 284 | 286 | * |
| 285 | 287 | * @return string Last error message |
| @@ -286,9 +288,9 @@ | ||
| 286 | 288 | */ |
| 287 | 289 | public function get_last_error(): string { |
| 288 | 290 | return $this->wpdb->last_error; |
| 289 | 291 | } |
| 290 | - | |
| 292 | + | |
| 291 | 293 | /** |
| 292 | 294 | * Clean up expired cache entries |
| 293 | 295 | * |
| 294 | 296 | * @return void |
| @@ -294,9 +296,9 @@ | ||
| 294 | 296 | * @return void |
| 295 | 297 | */ |
| 296 | 298 | public function cleanup_expired_cache(): void { |
| 297 | 299 | $cache_table = $this->get_table('ai_cache'); |
| 298 | - | |
| 300 | + | |
| 299 | 301 | // Check WordPress version for %i support (introduced in 6.2) |
| 300 | 302 | if (version_compare($GLOBALS['wp_version'], '6.2', '>=')) { |
| 301 | 303 | // Use %i placeholder for table identifier (WordPress 6.2+) |
| 302 | 304 | // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder |
| @@ -301,11 +303,13 @@ | ||
| 301 | 303 | // Use %i placeholder for table identifier (WordPress 6.2+) |
| 302 | 304 | // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder |
| 303 | 305 | $deleted = $this->wpdb->query( |
| 304 | 306 | $this->wpdb->prepare( |
| 305 | - 'DELETE FROM %i WHERE expires_at < %s', | |
| 307 | + // expires_at is a bigint unix timestamp (see Cache_Manager), | |
| 308 | + // so compare against time(), not a MySQL datetime string. | |
| 309 | + 'DELETE FROM %i WHERE expires_at < %d', | |
| 306 | 310 | $cache_table, |
| 307 | - current_time('mysql') | |
| 311 | + time() | |
| 308 | 312 | ) |
| 309 | 313 | ); |
| 310 | 314 | // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder |
| 311 | 315 | } else { |
| @@ -313,10 +317,10 @@ | ||
| 313 | 317 | $escaped_table = esc_sql($cache_table); |
| 314 | 318 | // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared |
| 315 | 319 | $deleted = $this->wpdb->query( |
| 316 | 320 | $this->wpdb->prepare( |
| 317 | - "DELETE FROM `{$escaped_table}` WHERE expires_at < %s", | |
| 318 | - current_time('mysql') | |
| 321 | + "DELETE FROM `{$escaped_table}` WHERE expires_at < %d", | |
| 322 | + time() | |
| 319 | 323 | ) |
| 320 | 324 | ); |
| 321 | 325 | // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared |
| 322 | 326 | } |
| @@ -327,9 +331,9 @@ | ||
| 327 | 331 | ['type' => 'info'] |
| 328 | 332 | ); |
| 329 | 333 | } |
| 330 | 334 | } |
| 331 | - | |
| 335 | + | |
| 332 | 336 | /** |
| 333 | 337 | * Get database statistics |
| 334 | 338 | * |
| 335 | 339 | * @return array Database statistics |
| @@ -335,9 +339,9 @@ | ||
| 335 | 339 | * @return array Database statistics |
| 336 | 340 | */ |
| 337 | 341 | public function get_stats(): array { |
| 338 | 342 | $stats = []; |
| 339 | - | |
| 343 | + | |
| 340 | 344 | foreach ($this->tables as $key => $table) { |
| 341 | 345 | // Check WordPress version for %i support (introduced in 6.2) |
| 342 | 346 | if (version_compare($GLOBALS['wp_version'], '6.2', '>=')) { |
| 343 | 347 | // Use %i placeholder for table identifier (WordPress 6.2+) |
| @@ -348,17 +352,17 @@ | ||
| 348 | 352 | // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder |
| 349 | 353 | } else { |
| 350 | 354 | // Fallback for older WordPress versions - table name is from our controlled list |
| 351 | 355 | $escaped_table = esc_sql($table); |
| 352 | - // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is properly escaped and from controlled source | |
| 356 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is properly escaped and from controlled source | |
| 353 | 357 | $count = $this->wpdb->get_var("SELECT COUNT(*) FROM `{$escaped_table}`"); |
| 354 | 358 | } |
| 355 | 359 | $stats[$key] = (int) $count; |
| 356 | 360 | } |
| 357 | - | |
| 361 | + | |
| 358 | 362 | return $stats; |
| 359 | 363 | } |
| 360 | - | |
| 364 | + | |
| 361 | 365 | /** |
| 362 | 366 | * Log database errors |
| 363 | 367 | * |
| 364 | 368 | * @param string $message Error message |
| @@ -420,9 +424,8 @@ | ||
| 420 | 424 | 'cleanup_results' => $cleanup_results, |
| 421 | 425 | 'cache_results' => $cache_results, |
| 422 | 426 | 'processed_at' => current_time('mysql') |
| 423 | 427 | ]); |
| 424 | - | |
| 425 | 428 | } catch (\Exception $e) { |
| 426 | 429 | // Log error but don't throw to prevent cron job failures |
| 427 | 430 | $this->log_debug('Weekly analytics processing failed', [ |
| 428 | 431 | 'error' => $e->getMessage(), |
| @@ -440,12 +443,13 @@ | ||
| 440 | 443 | $results = []; |
| 441 | 444 | |
| 442 | 445 | // Define retention periods (in days) |
| 443 | 446 | $retention_config = [ |
| 444 | - 'ai_usage' => 365, // 1 year | |
| 445 | - 'seo_scores' => 180, // 6 months | |
| 446 | - 'content_briefs' => 90, // 3 months | |
| 447 | - 'seo_performance' => 90 // 3 months (performance data grows fast) | |
| 447 | + 'ai_usage' => 365, // 1 year | |
| 448 | + 'seo_scores' => 180, // 6 months | |
| 449 | + 'content_briefs' => 90, // 3 months | |
| 450 | + 'seo_performance' => 90, // 3 months (performance data grows fast) | |
| 451 | + 'instant_indexing_logs' => 90 // 3 months (one row per URL per submit) | |
| 448 | 452 | ]; |
| 449 | 453 | |
| 450 | 454 | foreach ($retention_config as $table_key => $retention_days) { |
| 451 | 455 | try { |
| @@ -507,20 +511,20 @@ | ||
| 507 | 511 | |
| 508 | 512 | $date_column = $date_columns[$table_key] ?? 'created_at'; |
| 509 | 513 | |
| 510 | 514 | // Delete old records |
| 511 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Analytics cleanup requires direct database access, table and column names are validated internally | |
| 515 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Analytics cleanup requires direct database access, table and column names are validated internally | |
| 512 | 516 | $deleted = $this->wpdb->query( |
| 513 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is properly prepared with placeholders | |
| 517 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders | |
| 514 | 518 | $this->wpdb->prepare( |
| 515 | - // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table and column names are validated internally | |
| 516 | - "DELETE FROM `{$table_name}` WHERE {$date_column} < %s", | |
| 517 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $cutoff_date is validated and used as parameter | |
| 519 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table and column names are validated internally | |
| 520 | + "DELETE FROM `{$table_name}` WHERE `{$date_column}` < %s", | |
| 521 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $cutoff_date is validated and used as parameter | |
| 518 | 522 | $cutoff_date |
| 519 | 523 | ) |
| 520 | 524 | ); |
| 521 | 525 | |
| 522 | - return $deleted !== false ? (int)$deleted : 0; | |
| 526 | + return $deleted !== false ? (int) $deleted : 0; | |
| 523 | 527 | } |
| 524 | 528 | |
| 525 | 529 | /** |
| 526 | 530 | * Clear analytics-related cache |
| @@ -547,20 +551,24 @@ | ||
| 547 | 551 | } |
| 548 | 552 | } |
| 549 | 553 | |
| 550 | 554 | // Also clear any user-specific analytics cache |
| 551 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Cache cleanup requires direct database access | |
| 552 | - $user_cache_deleted = $this->wpdb->query( | |
| 553 | - // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- wpdb->options is WordPress core table, safe to use | |
| 554 | - "DELETE FROM {$this->wpdb->options} | |
| 555 | - WHERE option_name LIKE '_transient_thinkrank_analytics_%' | |
| 556 | - OR option_name LIKE '_transient_timeout_thinkrank_analytics_%'" | |
| 555 | + $analytics_like = $this->wpdb->esc_like('_transient_thinkrank_analytics_') . '%'; | |
| 556 | + $timeout_like = $this->wpdb->esc_like('_transient_timeout_thinkrank_analytics_') . '%'; | |
| 557 | + $options_table = $this->wpdb->options; | |
| 558 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $options_table is from $wpdb->options, a WordPress core table name. | |
| 559 | + $prepared_sql = $this->wpdb->prepare( | |
| 560 | + "DELETE FROM {$options_table} WHERE option_name LIKE %s OR option_name LIKE %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 561 | + $analytics_like, | |
| 562 | + $timeout_like | |
| 557 | 563 | ); |
| 564 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is prepared above. | |
| 565 | + $user_cache_deleted = $this->wpdb->query( $prepared_sql ); | |
| 558 | 566 | |
| 559 | 567 | return [ |
| 560 | 568 | 'transients_cleared' => $cleared_count, |
| 561 | - 'user_cache_cleared' => $user_cache_deleted !== false ? (int)$user_cache_deleted : 0, | |
| 562 | - 'total_cleared' => $cleared_count + ($user_cache_deleted !== false ? (int)$user_cache_deleted : 0) | |
| 569 | + 'user_cache_cleared' => $user_cache_deleted !== false ? (int) $user_cache_deleted : 0, | |
| 570 | + 'total_cleared' => $cleared_count + ($user_cache_deleted !== false ? (int) $user_cache_deleted : 0) | |
| 563 | 571 | ]; |
| 564 | 572 | } |
| 565 | 573 | |
| 566 | 574 | /** |
| @@ -581,21 +589,21 @@ | ||
| 581 | 589 | |
| 582 | 590 | foreach ($tables as $table_key) { |
| 583 | 591 | $table_name = $this->get_table($table_key); |
| 584 | 592 | |
| 585 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Table size monitoring requires direct database access | |
| 586 | - $size_result = $this->wpdb->get_row( | |
| 587 | - $this->wpdb->prepare( | |
| 588 | - "SELECT | |
| 589 | - table_name, | |
| 590 | - ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb, | |
| 591 | - table_rows | |
| 592 | - FROM information_schema.TABLES | |
| 593 | - WHERE table_schema = %s AND table_name = %s", | |
| 594 | - DB_NAME, | |
| 595 | - $table_name | |
| 596 | - ) | |
| 593 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is prepared with placeholders below. | |
| 594 | + $prepared_sql = $this->wpdb->prepare( | |
| 595 | + "SELECT | |
| 596 | + table_name, | |
| 597 | + ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb, | |
| 598 | + table_rows | |
| 599 | + FROM information_schema.TABLES | |
| 600 | + WHERE table_schema = %s AND table_name = %s", | |
| 601 | + DB_NAME, | |
| 602 | + $table_name | |
| 597 | 603 | ); |
| 604 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is prepared above. | |
| 605 | + $size_result = $this->wpdb->get_row( $prepared_sql ); | |
| 598 | 606 | |
| 599 | 607 | if ($size_result) { |
| 600 | 608 | $sizes[$table_key] = [ |
| 601 | 609 | 'size_mb' => (float) $size_result->size_mb, |
