OwlCyberSecurity - MANAGER

Edit File: DatabaseUpgradesEtc.php

<?php

/* Functions in this class should all reference one of the following variables or support functions that do.
 *      $wpdb, $_GET, $_POST, $_SERVER, $_.*
 * everything $wpdb related.
 * everything $_GET, $_POST, (etc) related.
 * Read the database, Store to the database,
 */

class ABJ_404_Solution_DatabaseUpgradesEtc {

private static $instance = null;
	
	private static $uniqID = null;
	
	public static function getInstance() {
		if (self::$instance == null) {
			self::$instance = new ABJ_404_Solution_DatabaseUpgradesEtc();
			self::$uniqID = uniqid("", true);
		}
		
		return self::$instance;
	}
	
	/** Create the tables when the plugin is first activated. 
     * @global type $wpdb
     */
    function createDatabaseTables($updatingToNewVersion = false) {
    	$abj404logging = ABJ_404_Solution_Logging::getInstance();
    	$syncUtils = ABJ_404_Solution_SynchronizationUtils::getInstance();
    	
    	$synchronizedKeyFromUser = "create_db_tables";
    	$uniqueID = $syncUtils->synchronizerAcquireLockTry($synchronizedKeyFromUser);
    	
    	if ($uniqueID == '' || $uniqueID == null) {
    		$abj404logging->debugMessage("Avoiding multiple calls for creating database tables.");
    		return;
    	}
    	
    	try {
    		$this->reallyCreateDatabaseTables($updatingToNewVersion);
    		
    	} catch (Exception $e) {
    		$abj404logging->errorMessage("Error creating database tables. ", $e);
    	}
    	$syncUtils->synchronizerReleaseLock($uniqueID, $synchronizedKeyFromUser);
    }
    
    private function reallyCreateDatabaseTables($updatingToNewVersion = false) {
		$this->renameAbj404TablesToLowerCase();
		
    	if ($updatingToNewVersion) {
    		$this->correctIssuesBefore();
    	}
    	
    	$this->runInitialCreateTables();
    	
    	$this->correctCollations();
    	
    	$this->updateTableEngineToInnoDB();
    	
    	$this->createIndexes();
    	
    	// we could do this only when a table is created or when the "meta" column is created
    	// but it doesn't take long anyway so we do it every night.
    	$plCache = ABJ_404_Solution_PermalinkCache::getInstance();
    	$plCache->updatePermalinkCache(1);
    	
    	if ($updatingToNewVersion) {
    		$this->correctIssuesAfter();
    	}
    }
    
    /** Correct any possible outstanding issues. */
    function correctIssuesBefore() {
    	$abj404dao = ABJ_404_Solution_DataAccess::getInstance();
    	$abj404dao->correctDuplicateLookupValues();
    	
    	$this->correctMatchData();
    }
    
    /** Correct any possible outstanding issues. */
    function correctIssuesAfter() {
    	$this->correctMatchData();
    }

/** Makes all plugin table names lowercase, in case someone thought it was funny to use
	 * the lower_case_table_names=0 setting. */
	function renameAbj404TablesToLowerCase() {
		global $wpdb;
    	$abj404logging = ABJ_404_Solution_Logging::getInstance();
    	$abj404dao = ABJ_404_Solution_DataAccess::getInstance();
		// Fetch all tables starting with "abj404", case-insensitive
		$dbName = esc_sql($wpdb->dbname);
		$query = "SELECT table_name 
			FROM information_schema.tables 
			WHERE table_schema = '{$dbName}' 
			AND LOWER(table_name) LIKE '%abj404%'";
		$results = $abj404dao->queryAndGetResults($query);
		
		foreach ($results['rows'] as $row) {
			$tableName = $row['table_name'] ?? $row['TABLE_NAME'];

if (!empty($tableName)) {
				$lowercaseName = strtolower($tableName);
		
				// Check if the table name is already lowercase, skip if it is
				if ($tableName !== $lowercaseName) {
					// Rename the table to lowercase
					$renameQuery = "RENAME TABLE `{$tableName}` TO `{$lowercaseName}`";
					$abj404dao->queryAndGetResults($renameQuery, 
						['ignore_errors' => ["already exists"]]);
					$abj404logging->infoMessage("Renamed table {$tableName} to {$lowercaseName}\n");
				}
			} else {
				$abj404logging->warn("I didn't find a table name in the results of this row: " . 
					print_r($row, true));
			}
		}
	}
    
    function correctMatchData() {
    	$abj404dao = ABJ_404_Solution_DataAccess::getInstance();
    	$abj404dao->queryAndGetResults("delete from {wp_abj404_spelling_cache} " .
    		"where matchdata is null or matchdata = ''");
    }
    
	/** When certain columns are created we have to populate data.
     * @param string $tableName
     * @param string $colName
     */
    function handleSpecificCases($tableName, $colName) {
    	if (strpos($tableName, 'abj404_logsv2') !== false && $colName == 'min_log_id') {
    		global $wpdb;
    		$abj404dao = ABJ_404_Solution_DataAccess::getInstance();
    		$f = ABJ_404_Solution_Functions::getInstance();
    		$query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/logsSetMinLogID.sql");
    		$abj404dao->queryAndGetResults($query);
    	}
    	if (strpos($tableName, 'abj404_permalink_cache') !== false && $colName == 'url_length') {
    		// clear the permalink cache so that the url length column will be populated.
    		// this could be more efficient but I'll assume that's not necessary.
    		$abj404dao = ABJ_404_Solution_DataAccess::getInstance();
    		$abj404dao->truncatePermalinkCacheTable();
    	}
    }
    
    function runInitialCreateTables() {
    	$f = ABJ_404_Solution_Functions::getInstance();
		$abj404dao = ABJ_404_Solution_DataAccess::getInstance();
    	global $wpdb;
    	$redirectsTable = $abj404dao->doTableNameReplacements("{wp_abj404_redirects}");
    	$logsTable = $abj404dao->doTableNameReplacements("{wp_abj404_logsv2}");
    	$lookupTable = $abj404dao->doTableNameReplacements("{wp_abj404_lookup}");
    	$permalinkCacheTable = $abj404dao->doTableNameReplacements("{wp_abj404_permalink_cache}");
    	$spellingCacheTable = $abj404dao->doTableNameReplacements("{wp_abj404_spelling_cache}");

$query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/createPermalinkCacheTable.sql");
        $abj404dao->queryAndGetResults($query);
        $this->verifyColumns($permalinkCacheTable, $query);
        
        $query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/createSpellingCacheTable.sql");
        $abj404dao->queryAndGetResults($query);
        $this->verifyColumns($spellingCacheTable, $query);
        
        $query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/createRedirectsTable.sql");
        $abj404dao->queryAndGetResults($query);
        $this->verifyColumns($redirectsTable, $query);
        
        $query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/createLogTable.sql");
        $abj404dao->queryAndGetResults($query);
        $this->verifyColumns($logsTable, $query);
        
        $query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/createLookupTable.sql");
        $abj404dao->queryAndGetResults($query);
        $this->verifyColumns($lookupTable, $query);
    }
    
    function createIndexes() {
    	global $wpdb;
    	$f = ABJ_404_Solution_Functions::getInstance();
		$abj404dao = ABJ_404_Solution_DataAccess::getInstance();
    	$redirectsTable = $abj404dao->doTableNameReplacements("{wp_abj404_redirects}");
    	$logsTable = $abj404dao->doTableNameReplacements("{wp_abj404_logsv2}");
    	$lookupTable = $abj404dao->doTableNameReplacements("{wp_abj404_lookup}");
    	$permalinkCacheTable = $abj404dao->doTableNameReplacements("{wp_abj404_permalink_cache}");
    	$spellingCacheTable = $abj404dao->doTableNameReplacements("{wp_abj404_spelling_cache}");
    	
    	$query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/createPermalinkCacheTable.sql");
    	$query = $f->str_replace('{wp_abj404_permalink_cache}', $permalinkCacheTable, $query);
    	$this->verifyIndexes($permalinkCacheTable, $query);
    	
    	$query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/createSpellingCacheTable.sql");
    	$query = $f->str_replace('{wp_abj404_spelling_cache}', $spellingCacheTable, $query);
    	$this->verifyIndexes($spellingCacheTable, $query);
    	
    	$query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/createRedirectsTable.sql");
    	$query = $f->str_replace('{redirectsTable}', $redirectsTable, $query);
    	$this->verifyIndexes($redirectsTable, $query);
    	
    	$query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/createLogTable.sql");
    	$query = $f->str_replace('{wp_abj404_logsv2}', $logsTable, $query);
    	$this->verifyIndexes($logsTable, $query);
    	
    	$query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/createLookupTable.sql");
    	$query = $f->str_replace('{wp_abj404_lookup}', $lookupTable, $query);
    	$this->verifyIndexes($lookupTable, $query);
    }

/** Retrieve the collation for a given table name.
     * @param string $tableName
     * @param ABJ_404_Solution_DataAccess $abj404dao
     * @param ABJ_404_Solution_Logging $abj404logging
     * @return string|null The collation for the table, or null if the query failed.
     */
	function getTableCollation($tableName) {
		$abj404logging = ABJ_404_Solution_Logging::getInstance();
		$abj404dao = ABJ_404_Solution_DataAccess::getInstance();

$query = "SHOW CREATE TABLE `$tableName`";
		$results = $abj404dao->queryAndGetResults($query);
	
		if (!empty($results['rows'][0]['Create Table'])) {
			$createTableSQL = $results['rows'][0]['Create Table'];
	
			preg_match('/COLLATE=([\w\d_]+)/', $createTableSQL, $collationMatch);
			preg_match('/CHARSET=([\w\d]+)/', $createTableSQL, $charsetMatch);
	
			$collation = $collationMatch[1] ?? null;
			$charset = $charsetMatch[1] ?? null;

return ($collation && $charset) ? [$collation, $charset] : null;
		} else {
			$abj404logging->warn("SHOW CREATE TABLE returned no data for $tableName.");
			return null;
		}
	}
	
	/** Make the collations of our tables match the WP_POSTS table collation. */
	function correctCollations() {
		global $wpdb;
		$abj404logging = ABJ_404_Solution_Logging::getInstance();
		$abj404dao = ABJ_404_Solution_DataAccess::getInstance();
		
		$collationNeedsUpdating = false;
		
		$redirectsTable = $abj404dao->doTableNameReplacements("{wp_abj404_redirects}");
		$logsTable = $abj404dao->doTableNameReplacements("{wp_abj404_logsv2}");
		$lookupTable = $abj404dao->doTableNameReplacements("{wp_abj404_lookup}");
		$permalinkCacheTable = $abj404dao->doTableNameReplacements("{wp_abj404_permalink_cache}");
		$spellingCacheTable = $abj404dao->doTableNameReplacements("{wp_abj404_spelling_cache}");
		$postsTable = $wpdb->prefix . 'posts';
		
		$abjTableNames = array($redirectsTable, $logsTable, $lookupTable, $permalinkCacheTable, $spellingCacheTable);
	
		// Get collation and charset for wp_posts
		$postsTableData = $this->getTableCollation($postsTable);
	
		if ($postsTableData === null) {
			$abj404logging->warn("Failed to retrieve collation/charset for $postsTable. Aborting collation checks.");
			return;
		}
	
		[$postsTableCollation, $postsTableCharset] = $postsTableData;
		
		// Check our own tables to see if they match.
		foreach ($abjTableNames as $tableName) {
			$abjTableData = $this->getTableCollation($tableName);
	
			if ($abjTableData === null) {
				$abj404logging->warn("Failed to retrieve collation for $tableName.");
				continue;  // Skip this table if collation can't be determined
			}
	
			[$abjTableCollation, $abjTableCharset] = $abjTableData;
	
			// Compare collations
			if ($abjTableCollation != $postsTableCollation) {
				$collationNeedsUpdating = true;
				break;  // Exit early if update is needed
			}
		}
		
        // if they match then we're done.
		if (!$collationNeedsUpdating) {
			return;
		}
		
		// if they don't match then update our tables to match the target tables.
		$abj404logging->infoMessage("Updating collation from $abjTableCollation to $postsTableCollation");
	
		foreach ($abjTableNames as $tableName) {
			// Update the collation
			$query = "ALTER TABLE {table_name} CONVERT TO CHARSET " . $postsTableCharset . 
					 " COLLATE " . $postsTableCollation;
			$query = str_replace('{table_name}', $tableName, $query);
			$results = $abj404dao->queryAndGetResults($query, 
				array('ignore_errors' => array("Index column size too large")));
	
			if ($results['last_error'] != null && $results['last_error'] != '' && 
				strpos($results['last_error'], "Index column size too large") !== false) {
				
				$abj404logging->infoMessage("Collation change for $tableName failed due to 'Index column size too large'. Deleting indexes and retrying...");
	
				// delete indexes and try again.
				$this->deleteIndexes($tableName);
				
				$abj404dao->queryAndGetResults($query);
            	$abj404logging->infoMessage("I tried to change a collation again: " . $query);
	
			} else if ($results['last_error'] == null || $results['last_error'] == '') {
				$abj404logging->infoMessage("Successfully changed collation of $tableName to $postsTableCollation");
			}
		}
	}
    
    /** Delete all non-primary indexes from a table.
     * @param string $tableName */
    function deleteIndexes($tableName) {
    	$abj404dao = ABJ_404_Solution_DataAccess::getInstance();
    	$f = ABJ_404_Solution_Functions::getInstance();
    	
    	// get the indexes list.
    	$results = $abj404dao->queryAndGetResults("show index from " . $tableName . 
    		" where key_name != 'PRIMARY'");
    	$rows = $results['rows'];
    	
    	if (empty($rows)) {
    		return;
    	}
    	
    	// find the key_name column because the case can be different on different systems.
    	$keyNameColumn = 'key_name';
    	$aRow = $rows[0];
    	foreach (array_keys($aRow) as $someKey) {
    		if ($f->strtolower($someKey) == 'key_name') {
    			$keyNameColumn = $someKey;
    			break;
    		}
    	}
    	
    	foreach ($rows as $row) {
    		// delete them
    		$query = "alter table " . $tableName . " drop index " . $row[$keyNameColumn];
    		$abj404dao->queryAndGetResults($query);
    	}
    }
    
    function updatePluginCheck() {
        $abj404dao = ABJ_404_Solution_DataAccess::getInstance();
        
        $pluginInfo = $abj404dao->getLatestPluginVersion();
        
        $shouldUpdate = $this->shouldUpdate($pluginInfo);
        
        if ($shouldUpdate) {
            $this->doUpdatePlugin($pluginInfo);
        }
    }
    
    function doUpdatePlugin($pluginInfo) {
        $abj404logging = ABJ_404_Solution_Logging::getInstance();
        $f = ABJ_404_Solution_Functions::getInstance();

$abj404logging->infoMessage("Attempting update to " . $pluginInfo['version']);
        
        // do the update.
        if (!class_exists('WP_Upgrader')) {
        	$abj404logging->infoMessage("Including WP_Upgrader for update.");
        	require_once ABSPATH . 'wp-admin/includes/class-wp-upgrader.php';
        }        
        if (!class_exists('Plugin_Upgrader')) {
        	$abj404logging->infoMessage("Including Plugin_Upgrader for update.");
        	require_once ABSPATH . 'wp-admin/includes/class-plugin-upgrader.php';
        }
        if (!function_exists('show_message')) {
        	$abj404logging->infoMessage("Including misc.php for update.");
        	require_once ABSPATH . 'wp-admin/includes/misc.php';
        }
        if (!class_exists('Plugin_Upgrader')) {
        	$abj404logging->warn("There was an issue including the Plugin_Upgrader class.");
        	return;
        }
        if (!function_exists('show_message')) {
        	$abj404logging->warn("There was an issue including the misc.php class.");
        	return;
        }
        
        $abj404logging->infoMessage("Includes for update complete. Updating... ");
        
        ob_start();
        $upgrader = new Plugin_Upgrader();
        $upret = $upgrader->upgrade(ABJ404_SOLUTION_BASENAME);
        if ($upret) {
            $abj404logging->infoMessage("Plugin successfully upgraded to " . $pluginInfo['version']);
            
        } else if ($upret instanceof WP_Error) {
            $abj404logging->infoMessage("Plugin upgrade error " . 
                json_encode($upret->get_error_codes()) . ": " . json_encode($upret->get_error_messages()));
        }
        $output = "";
        if (@ob_get_contents()) {
        	$output = @ob_get_contents();
        	@ob_end_clean();
        }
        if ($f->strlen(trim($output)) > 0) {
            $abj404logging->infoMessage("Upgrade output: " . $output);
        }
        
        $activateResult = activate_plugin(ABJ404_NAME);
        if ($activateResult instanceof WP_Error) {
            $abj404logging->errorMessage("Plugin activation error " . 
                json_encode($upret->get_error_codes()) . ": " . json_encode($upret->get_error_messages()));
            
        } else if ($activateResult == null) {
            $abj404logging->infoMessage("Successfully reactivated plugin after upgrade to version " . 
                $pluginInfo['version']);
        }        
    }
    
    function shouldUpdate($pluginInfo) {
        $abj404logic = ABJ_404_Solution_PluginLogic::getInstance();
        $abj404logging = ABJ_404_Solution_Logging::getInstance();
        
        $options = $abj404logic->getOptions(true);
        $latestVersion = $pluginInfo['version'];
        
        if (ABJ404_VERSION == $latestVersion) {
            $abj404logging->debugMessage("The latest plugin version is already installed (" . 
                    ABJ404_VERSION . ").");
            return false;
        }
        
        // don't overwrite development versions.
        if (version_compare(ABJ404_VERSION, $latestVersion) == 1) {
            $abj404logging->infoMessage("Development version: A more recent version is installed than " . 
                    "what is available on the WordPress site (" . ABJ404_VERSION . " / " . 
                     $latestVersion . ").");
            return false;
        }
        
        $serverName = array_key_exists('SERVER_NAME', $_SERVER) ? $_SERVER['SERVER_NAME'] : (array_key_exists('HTTP_HOST', $_SERVER) ? $_SERVER['HTTP_HOST'] : '(not found)');
        if (in_array($serverName, array('127.0.0.1', '::1', 'localhost'))) {
            $abj404logging->infoMessage("Update narrowly avoided on localhost.");
            return false;
        }        
        
        // 1.12.0 becomes array("1", "12", "0")
        $myVersionArray = explode(".", ABJ404_VERSION);
        $latestVersionArray = explode(".", $latestVersion);

// check the latest date to see if it's been long enough to update.
        $lastUpdated = $pluginInfo['last_updated'];
        $lastReleaseDate = new DateTime($lastUpdated);
        $todayDate = new DateTime();
        $dateInterval = $lastReleaseDate->diff($todayDate);
        $daysDifference = $dateInterval->days;
        
        // if there's a new minor version then update.
        // only update if it was released at least 3 days ago.
        if ($myVersionArray[0] == $latestVersionArray[0] && 
        	$myVersionArray[1] == $latestVersionArray[1] && 
        	intval($myVersionArray[2]) < intval($latestVersionArray[2]) &&
        	$daysDifference >= 3) {
        		
            $abj404logging->infoMessage("A new minor version is available (" . 
                    $latestVersion . "), currently version " . ABJ404_VERSION . " is installed.");
            return true;
        }

$minDaysDifference = $options['days_wait_before_major_update'];
        if ($daysDifference >= $minDaysDifference) {
            $abj404logging->infoMessage("The latest major version is old enough for updating automatically (" . 
                    $minDaysDifference . "days minimum, version " . $latestVersion . " is " . $daysDifference . 
                    " days old), currently version " . ABJ404_VERSION . " is installed.");
            return true;
        }
        
        return false;
    }
}