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

574 lines 15.9 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	$pattern = trim($excluded->url_pattern);
205	$pattern = rtrim($pattern, '/');
206	$pattern_type = $excluded->pattern_type;
207
208	switch ($pattern_type) {
209	case 'exact':
210	if ($url === $pattern) {
211	return true;
212	}
213	break;
214
215	case 'contain':
216	if (strpos($url, $pattern) !== false) {
217	return true;
218	}
219	break;
220
221	case 'start':
222	if (strpos($url, $pattern) === 0) {
223	return true;
224	}
225	break;
226
227	case 'end':
228	if (substr($url, -strlen($pattern)) === $pattern) {
229	return true;
230	}
231	break;
232
233	case 'regex':
234	// Normalize with delimiters
235	$test_pattern = Metasync_Redirection::normalize_regex_pattern($pattern);
236	$prev_limit = ini_get('pcre.backtrack_limit');
237	ini_set('pcre.backtrack_limit', 10000);
238	$match = @preg_match($test_pattern, $url);
239	ini_set('pcre.backtrack_limit', $prev_limit);
240	if ($match) {
241	return true;
242	}
243	break;
244	}
245	}
246
247	return false;
248	}
249
250	/**
251	* Add a new excluded URL
252	*
253	* @param array $args URL data to insert
254	* @return bool\|string True on success, false on failure, 'duplicate' if already exists, 'reactivated' if inactive entry was reactivated
255	*/
256	public function add($args)
257	{
258	global $wpdb;
259
260	// Ensure table structure is up to date
261	$this->ensure_table_structure();
262
263	$created_at = current_time('mysql');
264	$args = wp_parse_args(
265	$args,
266	[
267	'url_pattern' => '',
268	'pattern_type' => 'exact',
269	'description' => '',
270	'status' => 'active',
271	'is_permanent' => 0,
272	'auto_excluded' => 0,
273	'recheck_after' => null,
274	'created_at' => $created_at,
275	]
276	);
277
278	// Auto-excluded URLs: set recheck_after to 7 days from creation by default
279	if (!empty($args['auto_excluded']) && $args['recheck_after'] === null) {
280	$args['recheck_after'] = gmdate('Y-m-d H:i:s', strtotime('+7 days', strtotime($created_at)));
281	}
282
283	// Validate URL pattern
284	if (empty($args['url_pattern'])) {
285	return false;
286	}
287
288	// Normalize URL pattern for comparison (trim and remove trailing slash)
289	$normalized_pattern = rtrim(trim($args['url_pattern']), '/');
290
291	// Check for duplicate URL pattern with same type
292	$table_name = $this->get_table_name();
293	$existing = $wpdb->get_row($wpdb->prepare(
294	"SELECT id, status FROM `$table_name` WHERE TRIM(TRAILING '/' FROM url_pattern) = %s AND pattern_type = %s",
295	$normalized_pattern,
296	$args['pattern_type']
297	));
298
299	if ($existing) {
300
301	// If existing entry is inactive, reactivate it instead of creating duplicate
302	if ($existing->status === 'inactive') {
303	$update_data = ['status' => 'active', 'description' => $args['description']];
304	if (!empty($args['auto_excluded'])) {
305	$update_data['auto_excluded'] = 1;
306	$update_data['recheck_after'] = gmdate('Y-m-d H:i:s', strtotime('+7 days'));
307	}
308	$wpdb->update($table_name, $update_data, ['id' => $existing->id]);
309	$this->clear_cache();
310	return 'reactivated';
311	}
312
313	return 'duplicate';
314	}
315
316	// Validate pattern type
317	$valid_types = ['exact', 'contain', 'start', 'end', 'regex'];
318	if (!in_array($args['pattern_type'], $valid_types)) {
319	$args['pattern_type'] = 'exact';
320	}
321
322	// Validate regex pattern if type is regex (structural checks only; callers must validate syntax)
323	if ($args['pattern_type'] === 'regex') {
324	if (mb_strlen($args['url_pattern']) > 500) {
325	return false; // Pattern too long
326	}
327	// Reject nested quantifiers that could cause ReDoS
328	$raw = preg_replace('/^\S(.)\S[a-zA-Z]$/', '$1', $args['url_pattern']);
329	if (preg_match('/($[^)][+][^)]$)[+?{]\|(\[[^\]]\])[+][+*?{]/', $raw)) {
330	return false; // Unsafe pattern
331	}
332	}
333
334	$result = $wpdb->insert($this->get_table_name(), $args);
335
336	// Clear cache after adding
337	$this->clear_cache();
338
339	return $result !== false;
340	}
341
342	/**
343	* Update an excluded URL record
344	*
345	* @param array $args Values to update
346	* @param int $id Record ID
347	* @return bool True on success, false on failure
348	*/
349	public function update($args, $id)
350	{
351	global $wpdb;
352
353	// Ensure table structure is up to date
354	$this->ensure_table_structure();
355
356	$table_name = $this->get_table_name();
357	$row = $wpdb->get_row($wpdb->prepare("SELECT * FROM `$table_name` WHERE `id` = %d", $id));
358
359	if (!$row) {
360	return false;
361	}
362
363	// Validate pattern type if provided
364	if (isset($args['pattern_type'])) {
365	$valid_types = ['exact', 'contain', 'start', 'end', 'regex'];
366	if (!in_array($args['pattern_type'], $valid_types)) {
367	$args['pattern_type'] = 'exact';
368	}
369	}
370
371	// Validate regex pattern if type is regex (structural checks only; callers must validate syntax)
372	if (isset($args['pattern_type']) && $args['pattern_type'] === 'regex') {
373	if (isset($args['url_pattern'])) {
374	if (mb_strlen($args['url_pattern']) > 500) {
375	return false; // Pattern too long
376	}
377	// Reject nested quantifiers that could cause ReDoS
378	$raw = preg_replace('/^\S(.)\S[a-zA-Z]$/', '$1', $args['url_pattern']);
379	if (preg_match('/($[^)][+][^)]$)[+?{]\|(\[[^\]]\])[+][+*?{]/', $raw)) {
380	return false; // Unsafe pattern
381	}
382	}
383	}
384
385	$result = $wpdb->update($table_name, $args, ['id' => $id]);
386
387	// Clear cache after updating
388	$this->clear_cache();
389
390	return $result !== false;
391	}
392
393	/**
394	* Delete excluded URL records
395	*
396	* @param array $items Array of record IDs to delete
397	* @return bool True on success, false on failure
398	*/
399	public function delete($items)
400	{
401	global $wpdb;
402
403	// Ensure table exists before querying
404	$this->ensure_table_structure();
405
406	$table_name = $this->get_table_name();
407
408	if (!is_array($items) \|\| empty($items)) {
409	return false;
410	}
411
412	// Sanitize IDs
413	$items = array_map('intval', $items);
414	$ids = implode(',', array_fill(0, count($items), '%d'));
415
416	$result = $wpdb->query($wpdb->prepare(
417	"DELETE FROM `$table_name` WHERE `id` IN ($ids)",
418	$items
419	));
420
421	// Clear cache after deleting
422	$this->clear_cache();
423
424	return $result !== false;
425	}
426
427	/**
428	* Update status of excluded URL records
429	*
430	* @param array $items Array of record IDs
431	* @param string $status New status (active/inactive)
432	* @return bool True on success, false on failure
433	*/
434	public function update_status($items, $status)
435	{
436	global $wpdb;
437
438	// Ensure table exists before querying
439	$this->ensure_table_structure();
440
441	$table_name = $this->get_table_name();
442
443	if (!is_array($items) \|\| empty($items)) {
444	return false;
445	}
446
447	// Validate status
448	if (!in_array($status, ['active', 'inactive'])) {
449	return false;
450	}
451
452	// Sanitize IDs
453	$items = array_map('intval', $items);
454	$ids = implode(', ', array_fill(0, count($items), '%d'));
455
456	$set_status = $wpdb->prepare(
457	"UPDATE `$table_name` SET `status` = %s",
458	$status
459	);
460	$where = $wpdb->prepare(
461	" WHERE `id` IN ( $ids )",
462	$items
463	);
464
465	$query = "{$set_status}{$where}";
466	$result = $wpdb->query($query);
467
468	// Clear cache after updating status
469	$this->clear_cache();
470
471	return $result !== false;
472	}
473
474	/**
475	* Clear excluded URLs cache
476	*/
477	public function clear_cache()
478	{
479	wp_cache_delete('metasync_otto_excluded_urls', 'metasync');
480	if (defined('METASYNC_OTTO_EXCLUDED_TRANSIENT_KEY')) {
481	delete_transient(METASYNC_OTTO_EXCLUDED_TRANSIENT_KEY);
482	}
483	}
484
485	/**
486	* Get auto-excluded 404 URLs that are due for recheck
487	* Uses recheck_after timestamp: returns records where recheck_after <= now
488	* Excludes permanent exclusions (is_permanent = 1) - those are never rechecked
489	* Limited to max 50 URLs per run to avoid overloading
490	*
491	* @param int $limit Maximum number of URLs to return (default 50)
492	* @return array Array of records with id, url_pattern, created_at, is_permanent, recheck_after
493	*/
494	public function get_auto_excluded_404_urls_due_for_recheck($limit = 50)
495	{
496	global $wpdb;
497
498	$this->ensure_table_structure();
499
500	$table_name = $this->get_table_name();
501	$now = current_time('mysql');
502	$limit = max(1, min(100, intval($limit)));
503
504	return $wpdb->get_results($wpdb->prepare(
505	"SELECT id, url_pattern, pattern_type, created_at, is_permanent, recheck_after FROM `$table_name`
506	WHERE auto_excluded = 1 AND status = %s AND pattern_type = %s
507	AND (is_permanent = 0 OR is_permanent IS NULL)
508	AND recheck_after IS NOT NULL AND recheck_after <= %s
509	ORDER BY recheck_after ASC
510	LIMIT %d",
511	'active',
512	'exact',
513	$now,
514	$limit
515	));
516	}
517
518	/**
519	* Search excluded URLs with filters
520	*
521	* @param array $filters Search filters
522	* @return array Array of matching records
523	*/
524	public function search($filters = [])
525	{
526	global $wpdb;
527
528	// Ensure table exists before querying
529	$this->ensure_table_structure();
530
531	$table_name = $this->get_table_name();
532
533	$where_conditions = ['1=1'];
534	$where_values = [];
535
536	if (!empty($filters['search'])) {
537	$where_conditions[] = "(url_pattern LIKE %s OR description LIKE %s)";
538	$search_term = '%' . $wpdb->esc_like($filters['search']) . '%';
539	$where_values[] = $search_term;
540	$where_values[] = $search_term;
541	}
542
543	if (!empty($filters['status'])) {
544	$where_conditions[] = "status = %s";
545	$where_values[] = $filters['status'];
546	}
547
548	if (!empty($filters['pattern_type'])) {
549	$where_conditions[] = "pattern_type = %s";
550	$where_values[] = $filters['pattern_type'];
551	}
552
553	$where_clause = implode(' AND ', $where_conditions);
554	$order_by = !empty($filters['order_by']) ? sanitize_sql_orderby($filters['order_by']) : 'created_at';
555	$order = !empty($filters['order']) && strtoupper($filters['order']) === 'ASC' ? 'ASC' : 'DESC';
556
557	// Add pagination support
558	$limit_clause = '';
559	if (isset($filters['per_page']) && isset($filters['offset'])) {
560	$limit_clause = " LIMIT %d OFFSET %d";
561	$where_values[] = intval($filters['per_page']);
562	$where_values[] = intval($filters['offset']);
563	}
564
565	$query = "SELECT * FROM `$table_name` WHERE $where_clause ORDER BY $order_by $order" . $limit_clause;
566
567	if (!empty($where_values)) {
568	return $wpdb->get_results($wpdb->prepare($query, $where_values));
569	} else {
570	return $wpdb->get_results($query);
571	}
572	}
573	}
574