/**
* LOAD
* Begins the processing for replace logic
* @param mysql $conn The db connection object
* @param array $list Key value pair of 'search' and 'replace' arrays
* @param array $tables The tables we want to look at
* @param array $fullsearch Search every column reguardless of its data type
* @return array Collection of information gathered during the run.
*/
public static function load($conn, $list = array(), $tables = array(), $fullsearch = false)
{
$report = array('scan_tables' => 0, 'scan_rows' => 0, 'scan_cells' => 0, 'updt_tables' => 0, 'updt_rows' => 0, 'updt_cells' => 0, 'errsql' => array(), 'errser' => array(), 'errkey' => array(), 'errsql_sum' => 0, 'errser_sum' => 0, 'errkey_sum' => 0, 'time' => '', 'err_all' => 0);
$walk_function = create_function('&$str', '$str = "`$str`";');
$profile_start = DUPX_Util::get_microtime();
if (is_array($tables) && !empty($tables)) {
foreach ($tables as $table) {
$report['scan_tables']++;
$columns = array();
// Get a list of columns in this table
$fields = mysqli_query($conn, 'DESCRIBE ' . $table);
while ($column = mysqli_fetch_array($fields)) {
$columns[$column['Field']] = $column['Key'] == 'PRI' ? true : false;
}
// Count the number of rows we have in the table if large we'll split into blocks
$row_count = mysqli_query($conn, "SELECT COUNT(*) FROM `{$table}`");
$rows_result = mysqli_fetch_array($row_count);
@mysqli_free_result($row_count);
$row_count = $rows_result[0];
if ($row_count == 0) {
DUPX_Log::Info("{$table}^ ({$row_count})");
continue;
}
$page_size = 25000;
$offset = $page_size + 1;
$pages = ceil($row_count / $page_size);
// Grab the columns of the table. Only grab text based columns because
// they are the only data types that should allow any type of search/replace logic
$colList = '*';
$colMsg = '*';
if (!$fullsearch) {
$colList = self::getTextColumns($conn, $table);
if ($colList != null && is_array($colList)) {
array_walk($colList, $walk_function);
$colList = implode(',', $colList);
}
$colMsg = empty($colList) ? '*' : '~';
}
if (empty($colList)) {
DUPX_Log::Info("{$table}^ ({$row_count})");
continue;
} else {
DUPX_Log::Info("{$table}{$colMsg} ({$row_count})");
}
//Paged Records
for ($page = 0; $page < $pages; $page++) {
$current_row = 0;
$start = $page * $page_size;
$end = $start + $page_size;
$sql = sprintf("SELECT {$colList} FROM `%s` LIMIT %d, %d", $table, $start, $offset);
$data = mysqli_query($conn, $sql);
if (!$data) {
$report['errsql'][] = mysqli_error($conn);
}
$scan_count = $row_count < $end ? $row_count : $end;
DUPX_Log::Info("\tScan => {$start} of {$scan_count}", 2);
//Loops every row
while ($row = mysqli_fetch_array($data)) {
$report['scan_rows']++;
$current_row++;
$upd_col = array();
$upd_sql = array();
$where_sql = array();
$upd = false;
$serial_err = 0;
//Loops every cell
foreach ($columns as $column => $primary_key) {
$report['scan_cells']++;
$edited_data = $data_to_fix = $row[$column];
$base64coverted = false;
$txt_found = false;
//Only replacing string values
if (!empty($row[$column]) && !is_numeric($row[$column])) {
//Base 64 detection
if (base64_decode($row[$column], true)) {
$decoded = base64_decode($row[$column], true);
if (self::is_serialized($decoded)) {
$edited_data = $decoded;
$base64coverted = true;
}
}
//Skip table cell if match not found
foreach ($list as $item) {
if (strpos($edited_data, $item['search']) !== false) {
$txt_found = true;
break;
}
}
if (!$txt_found) {
continue;
}
//Replace logic - level 1: simple check on any string or serlized strings
foreach ($list as $item) {
$edited_data = self::recursive_unserialize_replace($item['search'], $item['replace'], $edited_data);
}
//Replace logic - level 2: repair serilized strings that have become broken
$serial_check = self::fix_serial_string($edited_data);
if ($serial_check['fixed']) {
$edited_data = $serial_check['data'];
} elseif ($serial_check['tried'] && !$serial_check['fixed']) {
$serial_err++;
}
}
//Change was made
if ($edited_data != $data_to_fix || $serial_err > 0) {
$report['updt_cells']++;
//Base 64 encode
if ($base64coverted) {
$edited_data = base64_encode($edited_data);
}
$upd_col[] = $column;
$upd_sql[] = $column . ' = "' . mysqli_real_escape_string($conn, $edited_data) . '"';
$upd = true;
}
if ($primary_key) {
$where_sql[] = $column . ' = "' . mysqli_real_escape_string($conn, $data_to_fix) . '"';
}
}
//PERFORM ROW UPDATE
if ($upd && !empty($where_sql)) {
$sql = "UPDATE `{$table}` SET " . implode(', ', $upd_sql) . ' WHERE ' . implode(' AND ', array_filter($where_sql));
$result = mysqli_query($conn, $sql) or $report['errsql'][] = mysqli_error($conn);
//DEBUG ONLY:
DUPX_Log::Info("\t{$sql}\n", 3);
if ($result) {
if ($serial_err > 0) {
$report['errser'][] = "SELECT " . implode(', ', $upd_col) . " FROM `{$table}` WHERE " . implode(' AND ', array_filter($where_sql)) . ';';
}
$report['updt_rows']++;
}
} elseif ($upd) {
$report['errkey'][] = sprintf("Row [%s] on Table [%s] requires a manual update.", $current_row, $table);
}
}
DUPX_Util::fcgi_flush();
@mysqli_free_result($data);
}
if ($upd) {
$report['updt_tables']++;
}
}
}
$profile_end = DUPX_Util::get_microtime();
$report['time'] = DUPX_Util::elapsed_time($profile_end, $profile_start);
$report['errsql_sum'] = empty($report['errsql']) ? 0 : count($report['errsql']);
$report['errser_sum'] = empty($report['errser']) ? 0 : count($report['errser']);
$report['errkey_sum'] = empty($report['errkey']) ? 0 : count($report['errkey']);
$report['err_all'] = $report['errsql_sum'] + $report['errser_sum'] + $report['errkey_sum'];
return $report;
}
//Check to make sure the connection is alive
if (!empty($err)) {
if (!mysqli_ping($dbh)) {
mysqli_close($dbh);
$dbh = DUPX_Util::db_connect($_POST['dbhost'], $_POST['dbuser'], $_POST['dbpass'], $_POST['dbname'], $_POST['dbport']);
// Reset session setup
@mysqli_query($dbh, "SET wait_timeout = {$GLOBALS['DB_MAX_TIME']}");
DUPX_Util::mysql_set_charset($dbh, $_POST['dbcharset'], $_POST['dbcollate']);
}
DUPX_Log::Info("**ERROR** database error write '{$err}' - [sql=" . substr($sql_result_file_data[$counter], 0, 75) . "...]");
$dbquery_errs++;
//Buffer data to browser to keep connection open
} else {
if ($fcgi_buffer_count++ > $fcgi_buffer_pool) {
$fcgi_buffer_count = 0;
DUPX_Util::fcgi_flush();
}
$dbquery_rows++;
}
}
$counter++;
}
@mysqli_commit($dbh);
@mysqli_autocommit($dbh, true);
DUPX_Log::Info("ERRORS FOUND:\t{$dbquery_errs}");
DUPX_Log::Info("DROP TABLE:\t{$drop_log}");
DUPX_Log::Info("QUERIES RAN:\t{$dbquery_rows}\n");
$dbtable_count = 0;
if ($result = mysqli_query($dbh, "SHOW TABLES")) {
while ($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
$table_rows = DUPX_Util::table_row_count($dbh, $row[0]);