/** * Sets globals from $_GET */ $get_params = array( 'db', 'table', 'token' ); foreach ($get_params as $one_get_param) { if (isset($_GET[$one_get_param])) { $GLOBALS[$one_get_param] = $_GET[$one_get_param]; } } $script_display_field = get_tables_info(); $tab_column = get_columns_info(); $script_tabs = get_script_tabs(); $script_contr = get_script_contr(); $tab_pos = get_tab_pos(); $tables_pk_or_unique_keys = get_pk_or_unique_keys(); $tables_all_keys = get_all_keys(); $params = array('lang' => $GLOBALS['lang']); if (isset($GLOBALS['db'])) { $params['db'] = $GLOBALS['db']; } $response = PMA_Response::getInstance(); $response->getFooter()->setMinimal(); $header = $response->getHeader();
} // All the assetids belonging to the site(s) to be deleted $DELETE_ASSETIDS = array(); foreach ($delete_site_assetids as $site_assetid) { $DELETE_ASSETIDS += $GLOBALS['SQ_SYSTEM']->am->getChildren($site_assetid); } $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2'); if (!$report_mode) { $GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN'); } // Global variables $REMAPPED_URLS = array(); $ERRORS = array(); $REPORT = array('match' => array(), 'no-match' => array(), 'dead' => array(), 'notice_links' => array()); // All the tables info $tables_info = get_tables_info(); // Table/field where the values are to be looked into $tables = array('ast_attr_val' => 'custom_val', 'ast_mdata_val' => 'value'); $count = 0; $records_updated_count = 0; foreach ($tables as $table => $value_field) { // Select the 'value' and 'key' fields from the table $key_fields = isset($tables_info[$table]['primary_key']) ? $tables_info[$table]['primary_key'] : ''; if (empty($key_fields)) { $ERRORS[] = 'Ignoring table "' . $table . '". Table info for this table not found'; continue; } $select_fields = array_merge(array($value_field), $key_fields); // We want 'assetid' to be at first item in the select if ($select_fields[0] != 'assetid') { $assetid_index = array_search('assetid', $select_fields);
/** * Fixes the char encoding in the given tables in the database * * @param int $root_node Assetid of rootnode, all childern of rootnode will be processed for char replacement * @param array $tables DB tables and colunms info * @param boolean $rollback If TRUE process rollback tables, else process regular tables * * @return void */ function fix_db($root_node, $tables, $rollback) { global $reportOnly; $tables_info = get_tables_info(); // All the Matrix attribute types with serialised value $serialsed_attrs = array('option_list', 'email_format', 'parameter_map', 'serialise', 'http_request', 'oauth'); // Get the list of attrids of the type 'serialise' $sql = "SELECT attrid FROM sq_ast_attr WHERE type IN ('" . implode("','", $serialsed_attrs) . "')"; $serialise_attrids = array_keys(MatrixDAL::executeSqlGrouped($sql)); if ($root_node != 1) { // Get the targetted asset list $target_assetids = array_keys($GLOBALS['SQ_SYSTEM']->am->getChildren($root_node)); // Since we include the root node, target assetids will always contain atleast one asset id array_unshift($target_assetids, $root_node); echo "\n\nNumber of assets to look into : " . count($target_assetids) . " \n"; // Go through 50 assets at a time. Applicable to asset specific tables only $chunks = array_chunk($target_assetids, 50); $chunks_count = count($chunks); } $errors_count = 0; $warnings_count = 0; $records_fixed_count = 0; $invalid_asset_records = array(); // Assets that will require filesystem content regeneration $affected_assetids = array(); $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2'); // Counter to count the number of records accessed/processed $count = 0; foreach ($tables as $table_data) { $table_records_count = 0; $table = isset($table_data['table']) ? $table_data['table'] : ''; if (empty($table)) { continue; } $key_fields = isset($tables_info[$table]['primary_key']) ? $tables_info[$table]['primary_key'] : ''; if (empty($key_fields)) { echo "\n" . 'Ignoring table "' . $table . '". Table info for this table not found' . " \n"; continue; } $value_fields = isset($table_data['values']) ? $table_data['values'] : ''; if (empty($value_fields)) { // Nothing to check continue; } if ($rollback) { // Make sure table has rollback trigggers enabled, otherwise it will have rollback table if (isset($tables_info[$table]['rollback']) && $tables_info[$table]['rollback']) { // Add rollback table primary key field to the table's keys $key_fields[] = 'sq_eff_from'; } else { // This table does not has corresponding rollback table continue; } } // Prepend table prefix $table = !$rollback ? 'sq_' . $table : 'sq_rb_' . $table; $asste_specific_table = $table_data['asset_assoc']; $select_fields = array_merge($value_fields, $key_fields); if ($asste_specific_table && !in_array('assetid', $select_fields)) { $select_fields[] = 'assetid'; } if ($root_node == 1) { if ($asste_specific_table) { // When running system wide, get the asset list from the respective db table $sql = "SELECT DISTINCT assetid FROM " . $table; $target_assetids = array_keys(MatrixDAL::executeSqlGrouped($sql)); // Go through 50 assets at a time. Applicable to asset specific tables only $chunks = array_chunk($target_assetids, 50); } else { // Dummy assetids chuck just so that we can get into next loop $chunks = array(array()); } } echo "\nChecking " . $table . " ."; // For non-asset specific table, this loop will break at end of the very first iteration foreach ($chunks as $chunk_index => $assetids) { $sql = 'SELECT ' . implode(',', $select_fields) . ' FROM ' . $table; // For non-asset specific table, "where" condition not is required. We get the whole table in a single go if ($asste_specific_table) { $sql .= ' WHERE assetid IN (\'' . implode('\',\'', $assetids) . '\')'; } else { if ($table == 'sq_internal_msg') { // Special case for non-asset specific records for 'interal_msg' table // Internal message has 'assetid' field but messages not associated with the asset will have empty assetid $sql .= " WHERE assetid = '' OR assetid IS NULL"; } } $results = MatrixDAL::executeSqlAssoc($sql); foreach ($results as $record) { $table_records_count++; $count++; if ($count % 10000 == 0) { echo '.'; } // Asset ID associated with this record $assetid = $asste_specific_table ? $record['assetid'] : 'n/a'; // Key field data $key_values = array(); foreach ($key_fields as $key_field) { $temp_key_v = array_get_index($record, $key_field, NULL); if (is_null($temp_key_v)) { // Primary key field must be there continue 2; } $key_values[$key_field] = $temp_key_v; } //end foreach // Original value field data. // This is the one we need to check/fix $org_values = array(); foreach ($value_fields as $value_field) { $org_values[$value_field] = array_get_index($record, $value_field, ''); } //end foreach // If it's the same in the new and old encodings, that's good. foreach ($org_values as $value_field => $value) { $checked = @iconv(SYS_OLD_ENCODING, SYS_NEW_ENCODING . '//IGNORE', $value); if ($value === $checked) { // This field does not requires conversion/checking unset($org_values[$value_field]); } } //end foreach if (empty($org_values)) { // No field values to convert/check continue; } // Being here means this record contains invalid chars $invalid_asset_records[] = array('asset' => $assetid, 'table' => $table, 'keys' => $key_values, 'values' => $org_values); $converted_values = array(); foreach ($org_values as $value_field => $value) { // If not valid, convert the values without igonoring or interprating any chars if (!isValidValue($value)) { // Serialised fields needs to be handled here $serialised_value = FALSE; if ($table == 'sq_ast_attr_val' && $value_field == 'custom_val' && in_array($record['attrid'], $serialise_attrids)) { $serialised_value = TRUE; } if ($table == 'sq_trig' && $value_field == 'data') { $serialised_value = TRUE; } if ($serialised_value) { $us_value = @unserialize($value); if ($us_value === FALSE && serialize(FALSE) !== $value) { // This has invalid serialsed value, but fix it anyway $converted_value = @iconv(SYS_OLD_ENCODING, SYS_NEW_ENCODING . '//IGNORE', $value); // Put this error notice in the script log file $warnings_count++; $msg = 'Serialsed data field "' . $value_field . '" in the table "' . $table . '" ('; foreach ($key_values as $field_name => $value) { $msg .= $field_name . '=' . $value . '; '; } $msg = rtrim($msg, '; ') . ') does not contain unserialisable data. ' . ($reportOnly ? 'Data can still be converted.' : 'Data will be converted anyway.'); log_error_msg($msg); } else { if (is_array($us_value)) { array_walk_recursive($us_value, 'fix_char'); $converted_value = serialize($us_value); } else { if (is_scalar($us_value)) { $us_value = @iconv(SYS_OLD_ENCODING, SYS_NEW_ENCODING . '//IGNORE', $us_value); $converted_value = serialize($us_value); } else { $converted_value = $value; } } } } else { $converted_value = @iconv(SYS_OLD_ENCODING, SYS_NEW_ENCODING . '//IGNORE', $value); } // If the converted value is valid in current encoding then its good to go // otherwise we'll just not use this value if ($converted_value != $value && isValidValue($converted_value)) { $value = $converted_value; $converted_values[$value_field] = $value; } } else { // if it's a valid encoded value, but was convertable before with iconv using old encoding // it might be only because value is already properly encoded with new encoding. so use md_detect to double check $encoding = mb_detect_encoding($value); if (strtolower($encoding) === strtolower(SYS_NEW_ENCODING)) { unset($org_values[$value_field]); } } } //end foreach if (empty($org_values)) { // All good array_pop($invalid_asset_records); continue; } // If the successfully converted fields count is same as the invalid fields count, we can proceed with the update $update_required = count($org_values) == count($converted_values); if ($update_required) { if (!$reportOnly) { $GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN'); // Generate update sql $bind_vars = array(); $set_sql = array(); foreach ($converted_values as $field_name => $value) { $set_sql[] = $field_name . '=:' . $field_name . '_v'; $bind_vars[$field_name . '_v'] = $value; } $where_sql = array(); foreach ($key_values as $field_name => $value) { $where_sql[] = $field_name . '=:' . $field_name . '_k'; $bind_vars[$field_name . '_k'] = $value; } try { $sql = 'UPDATE ' . $table . ' SET ' . implode(', ', $set_sql) . ' WHERE ' . implode(' AND ', $where_sql); $update_sql = MatrixDAL::preparePdoQuery($sql); foreach ($bind_vars as $var_name => $var_value) { MatrixDAL::bindValueToPdo($update_sql, $var_name, $var_value); } // Execute the update query $execute = MatrixDAL::executePdoAssoc($update_sql); if (count($execute) > 1) { foreach ($bind_vars as $var_name => $var_value) { $sql = str_replace(':' . $var_name, "'" . $var_value . "'", $sql); } $errors_count++; $msg = 'Executing query "' . $sql . '" will affect ' . count($execute) . ' records, instead of expected single record! Ignoring this sql.'; log_error_msg($msg); $GLOBALS['SQ_SYSTEM']->doTransaction('ROLLBACK'); } else { $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); $records_fixed_count++; $affected_assetids[$table][] = $assetid; } } catch (Exception $e) { $errors_count++; $msg = "Unexpected error occured while updating database: " . $e->getMessage(); log_error_msg($msg); $GLOBALS['SQ_SYSTEM']->doTransaction('ROLLBACK'); } } else { $records_fixed_count++; // For reporting purpose only $affected_assetids[$table][] = $assetid; } } else { // Trying to carryout charset conversion for this invalid value still resulted into invalid value // Hence record was not updated for this value conversion $errors_count++; $msg = 'Entry in the table "' . $table . '": ' . "\n"; foreach ($key_values as $field_name => $field_value) { $msg .= $field_name . '="' . $field_value . '"; '; } $msg .= "\n" . 'contains invalid char(s), which were not replaced because the charset conversion was not successful' . ($msg .= "\n" . 'Potentially invalid characters include:' . listProblematicCharacters($org_values)); log_error_msg($msg); } } //end foreach records if (!$asste_specific_table) { // We have processed all the entries for this non-asset specific table break; } } //end foreach assetids chunk echo " " . $table_records_count . " records"; } //end foreach tables $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); unset($target_assetids); unset($chunks); echo "\n"; $invalid_count = sizeof(array_keys($invalid_asset_records)); echo "Number of db records with invalid char(s): " . $invalid_count . "\n"; if ($invalid_count > 0) { foreach ($invalid_asset_records as $k => $details) { echo "\n\tAsset #" . $details['asset'] . " in table " . $details['table']; echo "\n\t" . 'Entry: '; foreach ($details['keys'] as $field_name => $field_value) { echo $field_name . '="' . $field_value . '"; '; } echo "\n\tPossibly problematic characters: " . listProblematicCharacters($details['values']) . "\n"; } echo "\n"; } return array('warning_count' => $warnings_count, 'error_count' => $errors_count, 'records_fixed_count' => $records_fixed_count, 'affected_assetids' => $affected_assetids); }