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.6.25, at otto/class-metasync-otto-excluded-urls-database.php

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