Esempio n. 1
0
/**
 * 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);
}