staging-url-finder.php

<?php
/**
 * Plugin Name: Staging URL Finder
 * Description: Finds and lists URLs in the database that differ from the site URL
 * Version: 0.2.0
 * Author: Nazar Hotsa
 */

// Prevent direct access
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// Add admin menu
function staging_url_finder_menu() {
	add_management_page(
		'Staging URL Finder',
		'Staging URL Finder',
		'manage_options',
		'staging-url-finder',
		'staging_url_finder_page'
	);
}
add_action( 'admin_menu', 'staging_url_finder_menu' );

// Admin page
function staging_url_finder_page() {
	global $wpdb;

	// Get the site URL
	$site_url = get_site_url();
	$site_domain = parse_url( $site_url, PHP_URL_HOST );

	echo '<div class="wrap">';
	echo '<h1>Staging URL Finder</h1>';
	echo '<p>This tool scans your WordPress database for URLs that differ from your current site URL.</p>';
	echo '<p>Current site URL: <code>' . esc_html( $site_url ) . '</code></p>';

	// Check if scan was requested
	if ( isset( $_POST['scan_database'] ) && check_admin_referer( 'staging_url_finder_scan' ) ) {
		// Perform the scan
		$results = staging_url_finder_scan_database( $site_url, $site_domain );

		// Display results
		if ( empty( $results ) ) {
			echo '<div class="notice notice-success"><p>No different URLs found in the database. Your site appears to be clean!</p></div>';
		} else {
			echo '<div class="notice notice-warning"><p>Found ' . count( $results ) . ' different URLs in your database:</p></div>';
			echo '<table class="widefat fixed" cellspacing="0">';
			echo '<thead><tr><th>URL</th><th>Occurrences</th><th>Tables</th></tr></thead>';
			echo '<tbody>';

			foreach ( $results as $url => $data ) {
				echo '<tr>';
				echo '<td>' . esc_html( $url ) . '</td>';
				echo '<td>' . esc_html( $data['count'] ) . '</td>';
				echo '<td>' . esc_html( implode( ', ', $data['tables'] ) ) . '</td>';
				echo '</tr>';
			}

			echo '</tbody></table>';
		}
	}

	// Scan form
	echo '<form method="post">';
	wp_nonce_field( 'staging_url_finder_scan' );
	echo '<p class="submit"><input type="submit" name="scan_database" class="button button-primary" value="Scan Database"></p>';
	echo '</form>';

	echo '</div>';
}

// Function to scan the database
function staging_url_finder_scan_database( $site_url, $site_domain ) {
	global $wpdb;

	$results = array();
	$excluded_tables = array( $wpdb->prefix . 'users' );

	// Get all tables
	$tables = $wpdb->get_results( 'SHOW TABLES', ARRAY_N );

	foreach ( $tables as $table ) {
		$table_name = $table[0];

		// Skip excluded tables
		if ( in_array( $table_name, $excluded_tables ) ) {
			continue;
		}

		// Get all text and varchar columns
		$columns = $wpdb->get_results( "DESCRIBE `$table_name`", ARRAY_A );

		foreach ( $columns as $column ) {
			if ( preg_match( '/(char|text|longtext|mediumtext|tinytext|varchar)/i', $column['Type'] ) ) {
				$column_name = $column['Field'];

				// Search for URLs in the column (excluding the site domain)
				$query = $wpdb->prepare(
					"SELECT DISTINCT `$column_name` FROM `$table_name` 
					 WHERE `$column_name` LIKE '%http://%' OR `$column_name` LIKE '%https://%'",
					''
				);

				$rows = $wpdb->get_results( $query, ARRAY_A );

				foreach ( $rows as $row ) {
					$content = $row[ $column_name ];

					// Find all URLs in the content
					preg_match_all( '/(https?:\/\/[^\s<>"\']+)/i', $content, $matches );

					foreach ( $matches[0] as $url ) {
						$url_domain = parse_url( $url, PHP_URL_HOST );

						// Only process URLs with a valid domain that differs from the site domain
						if ( $url_domain && $url_domain != $site_domain ) {
							if ( ! isset( $results[ $url_domain ] ) ) {
								$results[ $url_domain ] = array(
									'count'  => 0,
									'tables' => array()
								);
							}

							$results[ $url_domain ]['count']++;

							if ( ! in_array( $table_name, $results[ $url_domain ]['tables'] ) ) {
								$results[ $url_domain ]['tables'][] = $table_name;
							}
						}
					}
				}
			}
		}
	}

	return $results;
}

// Add a direct database query utility
function staging_url_finder_menu_sql() {
	add_management_page(
		'Staging URL SQL Query',
		'Staging URL SQL',
		'manage_options',
		'staging-url-sql',
		'staging_url_finder_sql_page'
	);
}
add_action( 'admin_menu', 'staging_url_finder_menu_sql' );

function staging_url_finder_sql_page() {
	global $wpdb;

	// Get the site URL
	$site_url = get_site_url();
	$site_domain = parse_url( $site_url, PHP_URL_HOST );

	echo '<div class="wrap">';
	echo '<h1>Staging URL SQL Query</h1>';
	echo '<p>Use this SQL query to find URLs in your WordPress database that differ from your current site URL.</p>';
	echo '<p>Current site URL: <code>' . esc_html( $site_url ) . '</code></p>';
	echo '<p>Current site domain: <code>' . esc_html( $site_domain ) . '</code></p>';

	echo '<h2>Options table query:</h2>';
	$options_query = "SELECT option_name, option_value
FROM {$wpdb->prefix}options
WHERE option_value LIKE '%http%'
  AND option_value NOT LIKE '%{$site_domain}%'
  AND option_name NOT IN ('_site_transient_update_core', '_site_transient_update_plugins', '_site_transient_update_themes')";

	echo '<pre>' . esc_html( $options_query ) . '</pre>';

	echo '<h2>Post content query:</h2>';
	$posts_query = "SELECT ID, post_title, post_content
FROM {$wpdb->prefix}posts
WHERE post_content LIKE '%http%'
  AND post_content NOT LIKE '%{$site_domain}%'";

	echo '<pre>' . esc_html( $posts_query ) . '</pre>';

	echo '<h2>Postmeta query:</h2>';
	$postmeta_query = "SELECT post_id, meta_key, meta_value
FROM {$wpdb->prefix}postmeta
WHERE meta_value LIKE '%http%'
  AND meta_value NOT LIKE '%{$site_domain}%'";

	echo '<pre>' . esc_html( $postmeta_query ) . '</pre>';

	echo '<p>Copy these queries and run them in your database administration tool (e.g., phpMyAdmin) to find all occurrences of URLs that differ from your site domain.</p>';

	echo '</div>';
}