metasync / otto / class-metasync-otto-excluded-urls-database.php

class-metasync-otto-excluded-urls-database.php in Search Atlas SEO – OTTO AI SEO Automation for WordPress 2.7.2, at otto/class-metasync-otto-excluded-urls-database.php

689 lines 26.1 KB

Raw Download Zip

1	<?php
2
3	/**
4	* The database operations for OTTO excluded URLs.
5	*
6	* @since 2.6.0
7	* @package Metasync
8	* @subpackage Metasync/otto
9	* @author Engineering Team <[email protected]>
10	*/
11	class Metasync_Otto_Excluded_URLs_Database
12	{
13	public static $table_name = "metasync_otto_excluded_urls";
14	private static $structure_verified = false;
15
16	private function get_table_name()
17	{
18	global $wpdb;
19	return $wpdb->prefix . self::$table_name;
20	}
21
22	/**
23	* Public schema-repair entry point for the background cron handler.
24	*
25	* ensure_table_structure() is instance-private and memoised per request;
26	* the scheduled repair must always inspect fresh, so the gate is reset
27	* before the check runs.
28	*/
29	public static function ensure_schema()
30	{
31	self::$structure_verified = false;
32	$instance = new self();
33	$instance->ensure_table_structure();
34	}
35
36	/**
37	* Ensure table structure is up to date
38	*/
39	private function ensure_table_structure()
40	{
41	// Run schema inspection at most once per request — table schema only changes on activation/upgrade, handled by class-db-migrations.php.
42	if (self::$structure_verified) { return; }
43	self::$structure_verified = true;
44
45	global $wpdb;
46	$table_name = $this->get_table_name();
47
48	// Check if table exists
49	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
50	// Table doesn't exist, run full migration
51	require_once dirname(__FILE__, 2) . '/database/class-db-migrations.php';
52	MetaSync_DBMigration::activation();
53	return;
54	}
55
56	// Fetch all column names once to avoid multiple SHOW COLUMNS calls
57	$existing_columns = $wpdb->get_col("SHOW COLUMNS FROM `{$wpdb->prefix}metasync_otto_excluded_urls`"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop — column introspection, admin write paths only, once per request
58
59	// is_permanent — added in v2.7.x; missing on sites that skipped the version migration
60	if (!in_array('is_permanent', $existing_columns, true)) {
61	$wpdb->query("ALTER TABLE `{$wpdb->prefix}metasync_otto_excluded_urls` ADD COLUMN `is_permanent` TINYINT(1) NOT NULL DEFAULT 0 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
62	$wpdb->query("ALTER TABLE `{$wpdb->prefix}metasync_otto_excluded_urls` ADD KEY `is_permanent` (`is_permanent`)"); // 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
63	}
64
65	// auto_excluded — added in v2.7.4; missing on older installs
66	if (!in_array('auto_excluded', $existing_columns, true)) {
67	$wpdb->query("ALTER TABLE `{$wpdb->prefix}metasync_otto_excluded_urls` ADD COLUMN `auto_excluded` TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_permanent`"); // 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	$wpdb->query("ALTER TABLE `{$wpdb->prefix}metasync_otto_excluded_urls` ADD KEY `auto_excluded` (`auto_excluded`)"); // 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
69	}
70
71	// recheck_after — added alongside auto_excluded; missing on the same older installs
72	if (!in_array('recheck_after', $existing_columns, true)) {
73	$wpdb->query("ALTER TABLE `{$wpdb->prefix}metasync_otto_excluded_urls` ADD COLUMN `recheck_after` DATETIME NULL DEFAULT NULL AFTER `auto_excluded`"); // 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
74	$wpdb->query("ALTER TABLE `{$wpdb->prefix}metasync_otto_excluded_urls` ADD KEY `recheck_after` (`recheck_after`)"); // 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
75	// Backfill: set recheck_after = created_at + 7 days for auto-excluded URLs
76	$wpdb->query("UPDATE `{$wpdb->prefix}metasync_otto_excluded_urls` SET recheck_after = DATE_ADD(created_at, INTERVAL 7 DAY) WHERE auto_excluded = 1 AND (recheck_after IS NULL OR recheck_after = '0000-00-00 00:00:00')"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop — one-time backfill bound to the column addition above
77	}
78
79	// status_auto_excluded composite index — speeds up the cache-miss query in metasync_is_otto_url_manually_excluded()
80	$has_composite_index = $wpdb->get_var("SHOW INDEX FROM `{$wpdb->prefix}metasync_otto_excluded_urls` WHERE Key_name = 'status_auto_excluded'"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop — index introspection, admin write paths only, once per request
81	if (empty($has_composite_index)) {
82	$wpdb->query("ALTER TABLE `{$wpdb->prefix}metasync_otto_excluded_urls` ADD KEY `status_auto_excluded` (`status`, `auto_excluded`)"); // 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
83	}
84	}
85
86	/**
87	* Get all excluded URLs with pagination
88	*
89	* @param int $per_page Number of items per page
90	* @param int $page_number Current page number
91	* @return array Array of excluded URL records
92	*/
93	public function get_paginated_records($per_page = 10, $page_number = 1)
94	{
95	global $wpdb;
96
97	// Ensure table exists before querying
98	$this->ensure_table_structure();
99
100	$table_name = $this->get_table_name();
101
102	$offset = ($page_number - 1) * $per_page;
103
104	$results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the render-path read is transient-cached with invalidation on every write
105	$wpdb->prepare(
106	"SELECT * FROM `{$wpdb->prefix}metasync_otto_excluded_urls` ORDER BY created_at DESC LIMIT %d OFFSET %d",
107	$per_page,
108	$offset
109	)
110	);
111
112	return $results;
113	}
114
115	/**
116	* Get a single record by ID
117	*
118	* @param int $id Record ID
119	* @return object\|null Record object or null if not found
120	*/
121	public function get_record_by_id($id)
122	{
123	global $wpdb;
124
125	$this->ensure_table_structure();
126
127	$table_name = $this->get_table_name();
128
129	return $wpdb->get_row($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the render-path read is transient-cached with invalidation on every write
130	"SELECT * FROM `{$wpdb->prefix}metasync_otto_excluded_urls` WHERE id = %d",
131	$id
132	));
133	}
134
135	/**
136	* Get total count of excluded URLs
137	*
138	* @return int Total count
139	*/
140	public function get_total_count()
141	{
142	global $wpdb;
143
144	// Ensure table exists before querying
145	$this->ensure_table_structure();
146
147	$table_name = $this->get_table_name();
148
149	$count = $wpdb->get_var("SELECT COUNT(*) FROM `{$wpdb->prefix}metasync_otto_excluded_urls`"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the render-path read is transient-cached with invalidation on every write
150
151	return intval($count);
152	}
153
154	/**
155	* Get all active excluded URLs (for checking during OTTO execution)
156	* Uses caching for performance
157	*
158	* @return array Array of excluded URL patterns
159	*/
160	public function get_all_active_urls()
161	{
162	global $wpdb;
163	$table_name = $this->get_table_name();
164
165	// Check cache first
166	$cache_key = 'metasync_otto_excluded_urls';
167	$cached_urls = wp_cache_get($cache_key, 'metasync');
168
169	if ($cached_urls !== false) {
170	return $cached_urls;
171	}
172
173	// Ensure table exists before querying
174	$this->ensure_table_structure();
175
176	$results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the render-path read is transient-cached with invalidation on every write
177	$wpdb->prepare(
178	"SELECT url_pattern, pattern_type FROM `{$wpdb->prefix}metasync_otto_excluded_urls` WHERE status = %s ORDER BY created_at DESC",
179	'active'
180	)
181	);
182
183	// Cache for 1 hour
184	wp_cache_set($cache_key, $results, 'metasync', HOUR_IN_SECONDS);
185
186	return $results;
187	}
188
189	/**
190	* Check if a URL matches any excluded URL patterns
191	*
192	* @param string $url URL to check
193	* @return bool True if URL is excluded, false otherwise
194	*/
195	public function is_url_excluded($url)
196	{
197	$excluded_urls = $this->get_all_active_urls();
198
199	if (empty($excluded_urls)) {
200	return false;
201	}
202
203	// Normalize URL for comparison
204	$url = trim($url);
205	$url = rtrim($url, '/');
206
207	foreach ($excluded_urls as $excluded) {
208	if (self::pattern_matches($url, $excluded->url_pattern, $excluded->pattern_type)) {
209	return true;
210	}
211	}
212
213	return false;
214	}
215
216	/**
217	* Match a URL against a single exclusion pattern.
218	*
219	* Single source of truth for exclusion matching. Both the queue/SEO-write path
220	* (self::is_url_excluded()) and the visitor render gate
221	* (metasync_is_otto_url_manually_excluded() in otto/otto_pixel.php) call this, so the
222	* set of supported pattern types cannot drift apart between the two paths again.
223	*
224	* Failure is deliberately fail-open (return false = not excluded = OTTO still renders):
225	* an unevaluable rule must not take the front end down. Regex rules that cannot be
226	* compiled are logged once per request so a broken rule is diagnosable rather than
227	* invisible.
228	*
229	* @param string $url URL to test. Callers pass it trimmed with no trailing slash.
230	* @param string $pattern Raw url_pattern column value. Normalized here, per type.
231	* @param string $pattern_type One of exact\|contain\|start\|end\|regex.
232	* @return bool True when the URL matches the pattern.
233	*/
234	public static function pattern_matches($url, $pattern, $pattern_type)
235	{
236	// Literal patterns are compared against a URL the caller already stripped of its
237	// trailing slash, so strip the pattern the same way. A regex must NOT be stripped:
238	// in a `/.../`-delimited pattern the trailing slash is the closing delimiter, and
239	// removing it yields an uncompilable pattern (`/\/blog\//` becomes `/\/blog\`) that
240	// silently matches nothing.
241	$pattern = ($pattern_type === 'regex') ? trim($pattern) : rtrim(trim($pattern), '/');
242
243	switch ($pattern_type) {
244	case 'exact':
245	return $url === $pattern;
246
247	case 'contain':
248	return strpos($url, $pattern) !== false;
249
250	case 'start':
251	return strpos($url, $pattern) === 0;
252
253	case 'end':
254	return substr($url, -strlen($pattern)) === $pattern;
255
256	case 'regex':
257	return self::regex_matches($url, $pattern);
258	}
259
260	return false;
261	}
262
263	/**
264	* Evaluate a single regex exclusion pattern against a URL.
265	*
266	* @param string $url URL to test.
267	* @param string $pattern Regex pattern, with or without delimiters.
268	* @return bool True on a match. False on no match, or on any evaluation failure.
269	*/
270	private static function regex_matches($url, $pattern)
271	{
272	if ($pattern === '') {
273	return false;
274	}
275
276	if (!self::regex_normalizer_available()) {
277	return false;
278	}
279
280	// Normalize with delimiters
281	$test_pattern = Metasync_Redirection::normalize_regex_pattern($pattern);
282
283	// Cap backtracking so a pathological pattern cannot burn CPU on a visitor request.
284	// ini_set() sits in disable_functions on some hardened hosts and calling a disabled
285	// function raises an Error, not an Exception - which the callers' catch (Exception)
286	// would not contain, white-screening the front end. Only restore the limit if it was
287	// actually read and changed.
288	$prev_limit = function_exists('ini_get') ? ini_get('pcre.backtrack_limit') : false;
289	$capped = ($prev_limit !== false && function_exists('ini_set')
290	&& ini_set('pcre.backtrack_limit', 10000) !== false); // phpcs:ignore Squiz.PHP.DiscouragedFunctions.Discouraged -- runtime PHP setting with no WordPress API
291
292	$match = @preg_match($test_pattern, $url);
293
294	if ($capped) {
295	ini_set('pcre.backtrack_limit', $prev_limit); // phpcs:ignore Squiz.PHP.DiscouragedFunctions.Discouraged -- runtime PHP setting with no WordPress API
296	}
297
298	if ($match === false) {
299	self::log_unusable_regex($pattern, $test_pattern);
300	}
301
302	return $match === 1;
303	}
304
305	/**
306	* Load the class that owns the regex normalizer, once per request.
307	*
308	* normalize_regex_pattern() is a static on Metasync_Redirection. It normally resolves via
309	* the Composer classmap registered in metasync.php, but release packages have shipped with
310	* an incomplete classmap before (see docs/wiki/release-package-classmaps.md), so the file
311	* is included explicitly rather than trusting the autoloader. If it is missing the caller
312	* must not attempt the static call at all: that raises an Error, not an Exception, so the
313	* callers' try/catch would not contain it and the front end would white-screen.
314	*
315	* Memoized because this runs on every front-end request - without it there would be one
316	* filesystem stat per regex exclusion row per page load.
317	*
318	* @return bool True when the normalizer can be called.
319	*/
320	private static function regex_normalizer_available()
321	{
322	static $available = null;
323
324	if ($available === null) {
325	$class_path = dirname(__FILE__, 2) . '/redirections/class-metasync-redirection.php';
326	$available = file_exists($class_path);
327
328	if ($available) {
329	require_once $class_path;
330	}
331	}
332
333	return $available;
334	}
335
336	/**
337	* Log an exclusion regex that could not be evaluated, once per pattern per request.
338	*
339	* Without this the rule is skipped in total silence: the admin sees an active exclusion
340	* and a page that is still being rewritten, with nothing to go on.
341	*
342	* @param string $pattern The stored pattern.
343	* @param string $test_pattern The pattern as handed to preg_match().
344	* @return void
345	*/
346	private static function log_unusable_regex($pattern, $test_pattern)
347	{
348	static $logged = [];
349
350	$key = md5($pattern);
351	if (isset($logged[$key])) {
352	return;
353	}
354	$logged[$key] = true;
355
356	error_log(sprintf( // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- genuine failure path, bounded, no secrets
357	'MetaSync OTTO: skipped an exclusion rule whose regex could not be evaluated - stored "%s", compiled "%s" (%s)',
358	$pattern,
359	$test_pattern,
360	preg_last_error_msg()
361	));
362	}
363
364	/**
365	* Add a new excluded URL
366	*
367	* @param array $args URL data to insert
368	* @return bool\|string True on success, false on failure, 'duplicate' if already exists, 'reactivated' if inactive entry was reactivated
369	*/
370	public function add($args)
371	{
372	global $wpdb;
373
374	// Ensure table structure is up to date
375	$this->ensure_table_structure();
376
377	$created_at = current_time('mysql');
378	$args = wp_parse_args(
379	$args,
380	[
381	'url_pattern' => '',
382	'pattern_type' => 'exact',
383	'description' => '',
384	'status' => 'active',
385	'is_permanent' => 0,
386	'auto_excluded' => 0,
387	'recheck_after' => null,
388	'created_at' => $created_at,
389	]
390	);
391
392	// Auto-excluded URLs: set recheck_after to 7 days from creation by default
393	if (!empty($args['auto_excluded']) && $args['recheck_after'] === null) {
394	$args['recheck_after'] = gmdate('Y-m-d H:i:s', strtotime('+7 days', strtotime($created_at)));
395	}
396
397	// Validate URL pattern
398	if (empty($args['url_pattern'])) {
399	return false;
400	}
401
402	// Normalize URL pattern for comparison (trim and remove trailing slash)
403	$normalized_pattern = rtrim(trim($args['url_pattern']), '/');
404
405	// Check for duplicate URL pattern with same type
406	$table_name = $this->get_table_name();
407	$existing = $wpdb->get_row($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the render-path read is transient-cached with invalidation on every write
408	"SELECT id, status FROM `{$wpdb->prefix}metasync_otto_excluded_urls` WHERE TRIM(TRAILING '/' FROM url_pattern) = %s AND pattern_type = %s",
409	$normalized_pattern,
410	$args['pattern_type']
411	));
412
413	if ($existing) {
414
415	// If existing entry is inactive, reactivate it instead of creating duplicate
416	if ($existing->status === 'inactive') {
417	$update_data = ['status' => 'active', 'description' => $args['description']];
418	if (!empty($args['auto_excluded'])) {
419	$update_data['auto_excluded'] = 1;
420	$update_data['recheck_after'] = gmdate('Y-m-d H:i:s', strtotime('+7 days'));
421	}
422	$wpdb->update($table_name, $update_data, ['id' => $existing->id]); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the render-path read is transient-cached with invalidation on every write
423	$this->clear_cache();
424	return 'reactivated';
425	}
426
427	return 'duplicate';
428	}
429
430	// Validate pattern type
431	$valid_types = ['exact', 'contain', 'start', 'end', 'regex'];
432	if (!in_array($args['pattern_type'], $valid_types)) {
433	$args['pattern_type'] = 'exact';
434	}
435
436	// Validate regex pattern if type is regex (structural checks only; callers must validate syntax)
437	if ($args['pattern_type'] === 'regex') {
438	if (mb_strlen($args['url_pattern']) > 500) {
439	return false; // Pattern too long
440	}
441	// Reject nested quantifiers that could cause ReDoS
442	$raw = preg_replace('/^\S(.)\S[a-zA-Z]$/', '$1', $args['url_pattern']);
443	if (preg_match('/($[^)][+][^)]$)[+?{]\|(\[[^\]]\])[+][+*?{]/', $raw)) {
444	return false; // Unsafe pattern
445	}
446	}
447
448	$result = $wpdb->insert($this->get_table_name(), $args); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the render-path read is transient-cached with invalidation on every write
449
450	// Clear cache after adding
451	$this->clear_cache();
452
453	return $result !== false;
454	}
455
456	/**
457	* Update an excluded URL record
458	*
459	* @param array $args Values to update
460	* @param int $id Record ID
461	* @return bool True on success, false on failure
462	*/
463	public function update($args, $id)
464	{
465	global $wpdb;
466
467	// Ensure table structure is up to date
468	$this->ensure_table_structure();
469
470	$table_name = $this->get_table_name();
471	$row = $wpdb->get_row($wpdb->prepare("SELECT * FROM `{$wpdb->prefix}metasync_otto_excluded_urls` WHERE `id` = %d", $id)); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the render-path read is transient-cached with invalidation on every write
472
473	if (!$row) {
474	return false;
475	}
476
477	// Validate pattern type if provided
478	if (isset($args['pattern_type'])) {
479	$valid_types = ['exact', 'contain', 'start', 'end', 'regex'];
480	if (!in_array($args['pattern_type'], $valid_types)) {
481	$args['pattern_type'] = 'exact';
482	}
483	}
484
485	// Validate regex pattern if type is regex (structural checks only; callers must validate syntax)
486	if (isset($args['pattern_type']) && $args['pattern_type'] === 'regex') {
487	if (isset($args['url_pattern'])) {
488	if (mb_strlen($args['url_pattern']) > 500) {
489	return false; // Pattern too long
490	}
491	// Reject nested quantifiers that could cause ReDoS
492	$raw = preg_replace('/^\S(.)\S[a-zA-Z]$/', '$1', $args['url_pattern']);
493	if (preg_match('/($[^)][+][^)]$)[+?{]\|(\[[^\]]\])[+][+*?{]/', $raw)) {
494	return false; // Unsafe pattern
495	}
496	}
497	}
498
499	$result = $wpdb->update($table_name, $args, ['id' => $id]); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the render-path read is transient-cached with invalidation on every write
500
501	// Clear cache after updating
502	$this->clear_cache();
503
504	return $result !== false;
505	}
506
507	/**
508	* Delete excluded URL records
509	*
510	* @param array $items Array of record IDs to delete
511	* @return bool True on success, false on failure
512	*/
513	public function delete($items)
514	{
515	global $wpdb;
516
517	// Ensure table exists before querying
518	$this->ensure_table_structure();
519
520	$table_name = $this->get_table_name();
521
522	if (!is_array($items) \|\| empty($items)) {
523	return false;
524	}
525
526	// Sanitize IDs
527	$items = array_map('intval', $items);
528
529	$result = $wpdb->query($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the render-path read is transient-cached with invalidation on every write
530	'DELETE FROM `' . $wpdb->prefix . 'metasync_otto_excluded_urls` WHERE `id` IN (' . implode(',', array_fill(0, count($items), '%d')) . ')',
531	$items
532	));
533
534	// Clear cache after deleting
535	$this->clear_cache();
536
537	return $result !== false;
538	}
539
540	/**
541	* Update status of excluded URL records
542	*
543	* @param array $items Array of record IDs
544	* @param string $status New status (active/inactive)
545	* @return bool True on success, false on failure
546	*/
547	public function update_status($items, $status)
548	{
549	global $wpdb;
550
551	// Ensure table exists before querying
552	$this->ensure_table_structure();
553
554	$table_name = $this->get_table_name();
555
556	if (!is_array($items) \|\| empty($items)) {
557	return false;
558	}
559
560	// Validate status
561	if (!in_array($status, ['active', 'inactive'])) {
562	return false;
563	}
564
565	// Sanitize IDs
566	$items = array_map('intval', $items);
567
568	$result = $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the render-path read is transient-cached with invalidation on every write
569	$wpdb->prepare(
570	'UPDATE `' . $wpdb->prefix . 'metasync_otto_excluded_urls` SET `status` = %s WHERE `id` IN (' . implode(', ', array_fill(0, count($items), '%d')) . ')',
571	array_merge(array($status), $items)
572	)
573	);
574
575	// Clear cache after updating status
576	$this->clear_cache();
577
578	return $result !== false;
579	}
580
581	/**
582	* Clear excluded URLs cache
583	*/
584	public function clear_cache()
585	{
586	wp_cache_delete('metasync_otto_excluded_urls', 'metasync');
587	if (defined('METASYNC_OTTO_EXCLUDED_TRANSIENT_KEY')) {
588	delete_transient(METASYNC_OTTO_EXCLUDED_TRANSIENT_KEY);
589	}
590	}
591
592	/**
593	* Get auto-excluded 404 URLs that are due for recheck
594	* Uses recheck_after timestamp: returns records where recheck_after <= now
595	* Excludes permanent exclusions (is_permanent = 1) - those are never rechecked
596	* Limited to max 50 URLs per run to avoid overloading
597	*
598	* @param int $limit Maximum number of URLs to return (default 50)
599	* @return array Array of records with id, url_pattern, created_at, is_permanent, recheck_after
600	*/
601	public function get_auto_excluded_404_urls_due_for_recheck($limit = 50)
602	{
603	global $wpdb;
604
605	$this->ensure_table_structure();
606
607	$table_name = $this->get_table_name();
608	$now = current_time('mysql');
609	$limit = max(1, min(100, intval($limit)));
610
611	return $wpdb->get_results($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the render-path read is transient-cached with invalidation on every write
612	"SELECT id, url_pattern, pattern_type, created_at, is_permanent, recheck_after FROM `{$wpdb->prefix}metasync_otto_excluded_urls`
613	WHERE auto_excluded = 1 AND status = %s AND pattern_type = %s
614	AND (is_permanent = 0 OR is_permanent IS NULL)
615	AND recheck_after IS NOT NULL AND recheck_after <= %s
616	ORDER BY recheck_after ASC
617	LIMIT %d",
618	'active',
619	'exact',
620	$now,
621	$limit
622	));
623	}
624
625	/**
626	* Search excluded URLs with filters
627	*
628	* @param array $filters Search filters
629	* @return array Array of matching records
630	*/
631	public function search($filters = [])
632	{
633	global $wpdb;
634
635	// Ensure table exists before querying
636	$this->ensure_table_structure();
637
638	$table_name = $this->get_table_name();
639
640	$where_conditions = ['1=1'];
641	$where_values = [];
642
643	if (!empty($filters['search'])) {
644	$where_conditions[] = "(url_pattern LIKE %s OR description LIKE %s)";
645	$search_term = '%' . $wpdb->esc_like($filters['search']) . '%';
646	$where_values[] = $search_term;
647	$where_values[] = $search_term;
648	}
649
650	if (!empty($filters['status'])) {
651	$where_conditions[] = "status = %s";
652	$where_values[] = $filters['status'];
653	}
654
655	if (!empty($filters['pattern_type'])) {
656	$where_conditions[] = "pattern_type = %s";
657	$where_values[] = $filters['pattern_type'];
658	}
659
660	$where_clause = implode(' AND ', $where_conditions);
661	$order_by = !empty($filters['order_by']) ? sanitize_sql_orderby($filters['order_by']) : 'created_at';
662	$order = (!empty($filters['order']) && strtoupper($filters['order']) === 'ASC') ? 'ASC' : 'DESC';
663
664	// ORDER BY is allowlisted: only literal column fragments from the map
665	// below can reach the query; unknown input falls back to created_at.
666	$sortable = array('id', 'url_pattern', 'pattern_type', 'status', 'is_permanent', 'auto_excluded', 'created_at', 'recheck_after');
667	$order_by_column = (isset($filters['order_by']) && in_array($filters['order_by'], $sortable, true)) ? $filters['order_by'] : 'created_at';
668	$order_by_sql = sanitize_sql_orderby($order_by_column . ' ' . $order) ?: 'created_at DESC';
669
670	// The query is assembled from literal placeholder fragments only; all
671	// dynamic values bind via prepare() below.
672	$query = 'SELECT * FROM `' . $wpdb->prefix . 'metasync_otto_excluded_urls` WHERE ' . implode(' AND ', $where_conditions)
673	. ' ORDER BY ' . $order_by_sql
674	. (isset($filters['per_page'], $filters['offset']) ? ' LIMIT %d OFFSET %d' : '');
675	if (isset($filters['per_page'], $filters['offset'])) {
676	$where_values[] = intval($filters['per_page']);
677	$where_values[] = intval($filters['offset']);
678	}
679
680	// With no filters the assembled query is literal-only (no
681	// placeholders); prepare() rejects placeholder-less queries, so only
682	// route it through prepare() when there are values to bind.
683	if (empty($where_values)) {
684	return $wpdb->get_results($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery -- literal fragments only, nothing to bind
685	}
686	return $wpdb->get_results($wpdb->prepare($query, $where_values)); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery -- literal fragments + allowlisted ORDER BY, values bound via prepare()
687	}
688	}
689