<?php
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Direct $wpdb query is required for this operation.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- some query operations need to always receive the most up-to-date or actual data directly from the database, reducing the risk of serving stale information.
if (!defined('ABSPATH')) die('No direct access allowed');

class UpdraftPlus_Search_Replace {

	private $columns = array();

	private $current_row = 0;

	private $use_wpdb = false;

	private $use_mysqli = false;

	private $wpdb_obj = null;

	private $mysql_dbh = null;

	protected $max_recursion = 0;
	
	/**
	 * Constructor
	 */
	public function __construct() {
		add_action('updraftplus_restore_db_pre', array($this, 'updraftplus_restore_db_pre'));
		$this->max_recursion = apply_filters('updraftplus_search_replace_max_recursion', 20);
	}

	/**
	 * This function is called via the filter updraftplus_restore_db_pre it sets up the search and replace database objects
	 *
	 * @return void
	 */
	public function updraftplus_restore_db_pre() {
		global $wpdb, $updraftplus_restorer;
		
		$this->use_wpdb = $updraftplus_restorer->use_wpdb();
		$this->wpdb_obj = $wpdb;

		$mysql_dbh = false;
		$use_mysqli = false;

		if (!$this->use_wpdb) {
			// We have our own extension which drops lots of the overhead on the query
			$wpdb_obj = $updraftplus_restorer->get_db_object();
			// Was that successful?
			if (!$wpdb_obj->is_mysql || !$wpdb_obj->ready) {
				$this->use_wpdb = true;
			} else {
				$this->wpdb_obj = $wpdb_obj;
				$mysql_dbh = $wpdb_obj->updraftplus_get_database_handle();
				$use_mysqli = $wpdb_obj->updraftplus_use_mysqli();
			}
		}

		$this->mysql_dbh = $mysql_dbh;
		$this->use_mysqli = $use_mysqli;
	}

	/**
	 * The engine
	 *
	 * @param string|array $search    - a string or array of things to search for
	 * @param string|array $replace   - a string or array of things to replace the search terms with
	 * @param array        $tables    - an array of tables
	 * @param integer      $page_size - the page size
	 */
	public function icit_srdb_replacer($search, $replace, $tables, $page_size) {

		if (!is_array($tables)) return false;

		global $wpdb, $updraftplus;

		$report = array(
			'tables' => 0,
			'rows' => 0,
			'change' => 0,
			'updates' => 0,
			'start' => microtime(true),
			'end' => microtime(true),
			'errors' => array(),
		);

		$page_size = (empty($page_size) || !is_numeric($page_size)) ? 5000 : $page_size;

		foreach ($tables as $table => $stripped_table) {

			$report['tables']++;

			if ($search === $replace) {
				$updraftplus->log("No search/replace required: would-be search and replacement are identical");
				continue;
			}

			$this->columns = array();

			$print_line = __('Search and replacing table:', 'updraftplus').' '.$table;

			$updraftplus->check_db_connection($this->wpdb_obj, true);

			// Get a list of columns in this table
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DESCRIBE is a DDL command that cannot be parameterized; $table is a SQL identifier sanitized using backquote().
			$fields = $wpdb->get_results('DESCRIBE '.UpdraftPlus_Manipulation_Functions::backquote($table), ARRAY_A);

			$prikey_field = false;
			foreach ($fields as $column) {
				$primary_key = ('PRI' == $column['Key']) ? true : false;
				if ($primary_key) $prikey_field = $column['Field'];
				if ('posts' == $stripped_table && 'guid' == $column['Field']) {
					$updraftplus->log('Skipping search/replace on GUID column in posts table');
					continue;
				}
				$this->columns[$column['Field']] = $primary_key;
			}

			// Count the number of rows we have in the table if large we'll split into blocks, This is a mod from Simon Wheatley

			// InnoDB does not do count(*) quickly. You can use an index for more speed - see: http://www.cloudspace.com/blog/2009/08/06/fast-mysql-innodb-count-really-fast/

			$where = '';
			// Opportunity to use internal knowledge on tables which may be huge
			if ('postmeta' == $stripped_table && ((is_array($search) && 0 === strpos($search[0], 'http')) || (is_string($search) && 0 === strpos($search, 'http')))) {
				$where = " WHERE meta_value LIKE '%http%'";
			}

			$count_rows_sql = 'SELECT COUNT(*) FROM '.$table;
			if ($prikey_field) $count_rows_sql .= " USE INDEX (PRIMARY)";
			$count_rows_sql .= $where;

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $count_rows_sql is constructed from safe table identifiers; SQL identifiers cannot be parameterized with $wpdb->prepare().
			$row_countr = $wpdb->get_results($count_rows_sql, ARRAY_N);

			// If that failed, try this
			if (false !== $prikey_field && $wpdb->last_error) {
				$escaped_table_name = UpdraftPlus_Database_Utility::escape_table_name($table);
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $escaped_table_name and $prikey_field are SQL identifiers; identifiers cannot be parameterized with $wpdb->prepare(), table name is safely escaped via escape_table_name().
				$row_countr = $wpdb->get_results("SELECT COUNT(*) FROM $escaped_table_name USE INDEX ($prikey_field)".$where, ARRAY_N);
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is safely escaped via escape_table_name().
				if ($wpdb->last_error) $row_countr = $wpdb->get_results("SELECT COUNT(*) FROM $escaped_table_name", ARRAY_N);
			}

			$row_count = $row_countr[0][0];
			/* translators: %d: Number of rows. */
			$print_line .= ': '.sprintf(__('rows: %d', 'updraftplus'), $row_count);
			$updraftplus->log($print_line, 'notice-restore', 'restoring-table-'.$table);
			$updraftplus->log('Search and replacing table: '.$table.": rows: ".$row_count);

			if (0 == $row_count) continue;

			for ($on_row = 0; $on_row <= $row_count; $on_row = $on_row+$page_size) {

				$this->current_row = 0;

				if ($on_row>0) $updraftplus->log_e("Searching and replacing reached row: %d", $on_row);

				// Grab the contents of the table
				list($data, $page_size) = $this->fetch_sql_result($table, $on_row, $page_size, $where);
				// $sql_line is calculated here only for the purpose of logging errors
				// $where might contain a %, so don't place it inside the main parameter

				$sql_line = sprintf('SELECT * FROM %s LIMIT %d, %d', $table.$where, $on_row, $on_row+$page_size);

				// Our strategy here is to minimise memory usage if possible; to process one row at a time if we can, rather than reading everything into memory
				if ($this->use_wpdb) {

					if ($wpdb->last_error) {
						$report['errors'][] = $this->print_error($sql_line);
					} else {
						foreach ($data as $row) {
							$rowrep = $this->process_row($table, $row, $search, $replace, $stripped_table);
							$report['rows']++;
							$report['updates'] += $rowrep['updates'];
							$report['change'] += $rowrep['change'];
							foreach ($rowrep['errors'] as $err) $report['errors'][] = $err;
						}
					}
				} else {
					if (false === $data) {
						$report['errors'][] = $this->print_error($sql_line);
					} elseif (true !== $data && null !== $data) {
						if ($this->use_mysqli) {
							while ($row = mysqli_fetch_array($data)) { // phpcs:ignore WordPress.DB.RestrictedFunctions.mysql_mysqli_fetch_array -- Using mysqli directly for streaming large result sets during restore to avoid high memory usage with $wpdb
								$rowrep = $this->process_row($table, $row, $search, $replace, $stripped_table);
								$report['rows']++;
								$report['updates'] += $rowrep['updates'];
								$report['change'] += $rowrep['change'];
								foreach ($rowrep['errors'] as $err) $report['errors'][] = $err;
							}
							mysqli_free_result($data); // phpcs:ignore WordPress.DB.RestrictedFunctions.mysql_mysqli_free_result -- Using mysqli directly for streaming large result sets during restore to avoid high memory usage with $wpdb
						} else {
							// phpcs:ignore PHPCompatibility.Extensions.RemovedExtensions.mysql_DeprecatedRemoved, WordPress.DB.RestrictedFunctions.mysql_mysql_fetch_array -- Ignore removed extension compatibility, direct mysql function used for low-level database operations outside of $wpdb.
							while ($row = mysql_fetch_array($data)) {
								$rowrep = $this->process_row($table, $row, $search, $replace, $stripped_table);
								$report['rows']++;
								$report['updates'] += $rowrep['updates'];
								$report['change'] += $rowrep['change'];
								foreach ($rowrep['errors'] as $err) $report['errors'][] = $err;
							}
							@mysql_free_result($data); // phpcs:ignore Generic.PHP.NoSilencedErrors.Discouraged, PHPCompatibility.Extensions.RemovedExtensions.mysql_DeprecatedRemoved, WordPress.DB.RestrictedFunctions.mysql_mysql_free_result -- If an error occurs during mysql free result and it fails to free result, it will not impact anything at all. mysql_* function used in the scenario in which the mysqli extension doesn't exist.
						}
					}
				}

			}

		}

		$report['end'] = microtime(true);

		return $report;
	}

	/**
	 * This function will get data from the passed in table ready to be search and replaced
	 *
	 * @param string  $table     - the table name
	 * @param integer $on_row    - the row to start from
	 * @param integer $page_size - the page size
	 * @param string  $where     - the where condition
	 *
	 * @return array - an array of data or an array with a false value
	 */
	private function fetch_sql_result($table, $on_row, $page_size, $where = '') {

		$sql_line = sprintf('SELECT * FROM %s%s LIMIT %d, %d', $table, $where, $on_row, $page_size);

		global $updraftplus;
		$updraftplus->check_db_connection($this->wpdb_obj, true);

		if ($this->use_wpdb) {
			global $wpdb;
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql_line is a SELECT query built from safe table identifiers and paginated offsets; SQL identifiers and LIMIT/OFFSET values cannot be parameterized with $wpdb->prepare().
			$data = $wpdb->get_results($sql_line, ARRAY_A);
			if (!$wpdb->last_error) return array($data, $page_size);
		} else {
			if ($this->use_mysqli) {
				// phpcs:ignore WordPress.DB.RestrictedFunctions.mysql_mysqli_query -- Direct mysqli call required; this uses a dedicated low-level database handle ($this->mysql_dbh) outside $wpdb for search-replace operations.
				$data = mysqli_query($this->mysql_dbh, $sql_line);
			} else {
				// phpcs:ignore PHPCompatibility.Extensions.RemovedExtensions.mysql_DeprecatedRemoved, WordPress.DB.RestrictedFunctions.mysql_mysql_query -- Ignore removed extension compatibility, direct mysql function used for low-level database operations outside of $wpdb.
				$data = mysql_query($sql_line, $this->mysql_dbh);
			}
			if (false !== $data) return array($data, $page_size);
		}
		
		if (5000 <= $page_size) return $this->fetch_sql_result($table, $on_row, 2000, $where);
		if (2000 <= $page_size) return $this->fetch_sql_result($table, $on_row, 500, $where);

		// At this point, $page_size should be 500; and that failed
		return array(false, $page_size);

	}

	/**
	 * This function will process a single row from the database calling recursive_unserialize_replace to search and replace the data found in the search and replace arrays
	 *
	 * @param string $table          - the current table we are working on
	 * @param array  $row            - the current row we are working on
	 * @param array  $search         - an array of things to search for
	 * @param array  $replace        - an array of things to replace the search terms with
	 * @param string $stripped_table - the stripped table
	 *
	 * @return array - returns an array report which includes changes made and any errors
	 */
	private function process_row($table, $row, $search, $replace, $stripped_table) {

		global $updraftplus, $wpdb, $updraftplus_restorer;

		$report = array('change' => 0, 'errors' => array(), 'updates' => 0);

		$this->current_row++;
		
		$update_sql = array();
		$where_sql = array();
		$upd = false;

		foreach ($this->columns as $column => $primary_key) {
		
			// Don't search/replace these
			if (('options' == $stripped_table && 'option_value' == $column && !empty($row['option_name']) && 'updraft_remotesites' == $row['option_name']) || ('sitemeta' == $stripped_table && 'meta_value' == $column && !empty($row['meta_key']) && 'updraftplus_options' == $row['meta_key'])) {
				continue;
			}
		
			$edited_data = $data_to_fix = $row[$column];
			$successful = false;

			// We catch errors/exceptions so that they're not fatal. Once saw a fatal ("Cannot access empty property") on "if (is_a($value, '__PHP_Incomplete_Class')) {" (not clear what $value has to be to cause that).
			try {
				// Run a search replace on the data that'll respect the serialisation.
				$edited_data = $this->recursive_unserialize_replace($search, $replace, $data_to_fix);
				$successful = true;
			} catch (Exception $e) {
				$log_message = 'An Exception ('.get_class($e).') occurred during the recursive search/replace. Exception message: '.$e->getMessage().' (Code: '.$e->getCode().', line '.$e->getLine().' in '.$e->getFile().')';
				$report['errors'][] = $log_message;
				UpdraftPlus_Manipulation_Functions::error_log($log_message);
				$updraftplus->log($log_message);
				/* translators: 1: Exception class, 2: Exception message. */
				$updraftplus->log(sprintf(__('A PHP exception (%1$s) has occurred: %2$s', 'updraftplus'), get_class($e), $e->getMessage()), 'warning-restore');
			} catch (Error $e) {// phpcs:ignore PHPCompatibility.Classes.NewClasses.errorFound -- The Error class does not exist in PHP below 5.6.
				$log_message = 'A PHP Fatal error (recoverable, '.get_class($e).') occurred during the recursive search/replace. Exception message: Error message: '.$e->getMessage().' (Code: '.$e->getCode().', line '.$e->getLine().' in '.$e->getFile().')';
				$report['errors'][] = $log_message;
				UpdraftPlus_Manipulation_Functions::error_log($log_message);
				$updraftplus->log($log_message);
				/* translators: 1: Fatal error class, 2: Error message. */
				$updraftplus->log(sprintf(__('A PHP fatal error (%1$s) has occurred: %2$s', 'updraftplus'), get_class($e), $e->getMessage()), 'warning-restore');
			}

			// Something was changed
			if ($successful && $edited_data != $data_to_fix) {
				$report['change']++;
				$ed = $edited_data;
				$wpdb->escape_by_ref($ed);
				// Undo breakage introduced in WP 4.8.3 core
				if (is_callable(array($wpdb, 'remove_placeholder_escape'))) $ed = $wpdb->remove_placeholder_escape($ed);
				$update_sql[] = UpdraftPlus_Manipulation_Functions::backquote($column) . ' = "' . $ed . '"';
				$upd = true;
			}

			if ($primary_key) {
				$df = $data_to_fix;
				$wpdb->escape_by_ref($df);
				// Undo breakage introduced in WP 4.8.3 core
				if (is_callable(array($wpdb, 'remove_placeholder_escape'))) $df = $wpdb->remove_placeholder_escape($df);
				$where_sql[] = UpdraftPlus_Manipulation_Functions::backquote($column) . ' = "' . $df . '"';
			}
		}

		if ($upd && !empty($where_sql)) {
			$sql = 'UPDATE '.UpdraftPlus_Manipulation_Functions::backquote($table).' SET '.implode(', ', $update_sql).' WHERE '.implode(' AND ', array_filter($where_sql));
			$result = $updraftplus_restorer->sql_exec($sql, 5, '', false);
			if (false === $result || is_wp_error($result)) {
				$last_error = $this->print_error($sql);
				$report['errors'][] = $last_error;
			} else {
				$report['updates']++;
			}

		} elseif ($upd) {
			$report['errors'][] = sprintf('"%s" has no primary key, manual change needed on row %s.', $table, $this->current_row);
			$updraftplus->log(__('Error:', 'updraftplus').' '.
				/* translators: 1: Table name, 2: Row number requiring manual change. */
				sprintf(__('"%1$s" has no primary key, manual change needed on row %2$s.', 'updraftplus'), $table, $this->current_row),
			'warning-restore');
		}

		return $report;

	}
	
	/**
	 * Take a serialised array and unserialise it replacing elements as needed and
	 * unserialising any subordinate arrays and performing the replace on those too.
	 * N.B. $from and $to can be arrays - they get passed only to str_replace(), which can take an array
	 *
	 * @param string $from            String we're looking to replace.
	 * @param string $to              What we want it to be replaced with
	 * @param array  $data            Used to pass any subordinate arrays back to in.
	 * @param bool   $serialised      Does the array passed via $data need serialising.
	 * @param int    $recursion_level Current recursion depth within the original data.
	 * @param array  $visited_data    Data that has been seen in previous recursion iterations.
	 *
	 * @return array	The original array with all elements replaced as needed.
	 */
	private function recursive_unserialize_replace($from = '', $to = '', $data = '', $serialised = false, $recursion_level = 0, $visited_data = array()) {

		global $updraftplus;

		static $error_count = 0;

		// some unserialised data cannot be re-serialised eg. SimpleXMLElements
		try {
			$case_insensitive = false;

			// If we've reached the maximum recursion level, short circuit
			if (0 !== $this->max_recursion && $recursion_level >= $this->max_recursion) {
				return $data;
			}

			if (is_array($data) || is_object($data)) {
				// If we've seen this exact object or array before, short circuit
				if (in_array($data, $visited_data, true)) {
					return $data; // Avoid infinite recursions when there's a circular reference
				}
				// Add this data to the list of
				$visited_data[] = $data;
			}

			if (is_array($from) && is_array($to)) {
				$case_insensitive = preg_match('#^https?:#i', implode($from)) && preg_match('#^https?:#i', implode($to)) ? true : false;
			} else {
				$case_insensitive = preg_match('#^https?:#i', $from) && preg_match('#^https?:#i', $to) ? true : false;
			}

			// O:8:"DateTime":0:{} : see https://bugs.php.net/bug.php?id=62852
			if (is_serialized($data) && false === strpos($data, 'O:8:"DateTime":0:{}') && false !== ($unserialized = UpdraftPlus::unserialize($data))) {
				$data = $this->recursive_unserialize_replace($from, $to, $unserialized, true, $recursion_level + 1);
			} elseif (is_array($data)) {
				$_tmp = array();
				foreach ($data as $key => $value) {
					$_tmp[$key] = $this->recursive_unserialize_replace($from, $to, $value, false, $recursion_level + 1, $visited_data);
				}

				$data = $_tmp;
				unset($_tmp);
			} elseif (is_object($data)) {
				$_tmp = clone $data;
				$props = get_object_vars($data);

				foreach ($props as $key => $value) {
					// Skip any representation of a protected property or integer property
					if (!preg_match('/^[a-zA-Z_\x80-\xff][a-zA-Z0-9_\x80-\xff]*$/', $key)) continue;

					$_tmp->$key = $this->recursive_unserialize_replace($from, $to, $value, false, $recursion_level + 1, $visited_data);
				}

				$data = $_tmp;
				unset($_tmp);
			} elseif (is_string($data) && (null !== ($_tmp = json_decode($data, true)))) {

				if (is_array($_tmp)) {
					foreach ($_tmp as $key => $value) {
						$_tmp[$key] = $this->recursive_unserialize_replace($from, $to, $value, false, $recursion_level + 1, $visited_data);
					}

					$data = json_encode($_tmp);
					unset($_tmp);
				}

			} else {
				if (is_string($data)) {
					if ($case_insensitive) {
						$data = str_ireplace($from, $to, $data);
					} else {
						$data = str_replace($from, $to, $data);
					}
// Below is the wrong approach. In fact, in the problematic case, the resolution is an extra search/replace to undo unnecessary ones
// if (is_string($from)) {
// $data = str_replace($from, $to, $data);
// } else {
// # Array. We only want a maximum of one replacement to take place. This is only an issue in non-default setups, but in those situations, carrying out all the search/replaces can be wrong. This is also why the most specific URL should be done first.
// foreach ($from as $i => $f) {
// $ndata = str_replace($f, $to[$i], $data);
// if ($ndata != $data) {
// $data = $ndata;
// break;
// }
// }
// }
				}
			}

			if ($serialised)
				return serialize($data);

		} catch (Exception $error) {
			if (3 > $error_count) {
				$log_message = 'PHP Fatal Exception error ('.get_class($error).') has occurred during recursive_unserialize_replace. Error Message: '.$error->getMessage().' (Code: '.$error->getCode().', line '.$error->getLine().' in '.$error->getFile().')';
				$updraftplus->log($log_message, 'warning-restore');
				$error_count++;
			}
		}

		return $data;
	}

	/**
	 * This function will get the last database error and log it
	 *
	 * @param string $sql_line - the sql line that caused the error
	 *
	 * @return void
	 */
	public function print_error($sql_line) {
		global $wpdb, $updraftplus;
		if ($this->use_wpdb) {
			$last_error = $wpdb->last_error;
		} else {
			// phpcs:ignore PHPCompatibility.Extensions.RemovedExtensions.mysql_DeprecatedRemoved, WordPress.DB.RestrictedFunctions.mysql_mysql_error, WordPress.DB.RestrictedFunctions.mysql_mysqli_error -- Ignore removed extension compatibility, direct mysql/mysqli function used for low-level database operations outside of $wpdb.
			$last_error = ($this->use_mysqli) ? mysqli_error($this->mysql_dbh) : mysql_error($this->mysql_dbh);
		}
		$updraftplus->log(__('Error:', 'updraftplus')." ".$last_error." - ".__('the database query being run was:', 'updraftplus').' '.$sql_line, 'warning-restore');
		return $last_error;
	}
}
