/** * Prepares queries for adding users and * also create database and return query and message * * @param boolean $_error whether user create or not * @param string $real_sql_query SQL query for add a user * @param string $sql_query SQL query to be displayed * @param string $username username * @param string $hostname host name * @param string $dbname database name * * @return array $sql_query, $message */ function PMA_addUserAndCreateDatabase($_error, $real_sql_query, $sql_query, $username, $hostname, $dbname) { if ($_error || !empty($real_sql_query) && !$GLOBALS['dbi']->tryQuery($real_sql_query)) { $_REQUEST['createdb-1'] = $_REQUEST['createdb-2'] = $_REQUEST['createdb-3'] = null; $message = PMA_Message::rawError($GLOBALS['dbi']->getError()); } else { $message = PMA_Message::success(__('You have added a new user.')); } if (isset($_REQUEST['createdb-1'])) { // Create database with same name and grant all privileges $q = 'CREATE DATABASE IF NOT EXISTS ' . PMA_Util::backquote(PMA_Util::sqlAddSlashes($username)) . ';'; $sql_query .= $q; if (!$GLOBALS['dbi']->tryQuery($q)) { $message = PMA_Message::rawError($GLOBALS['dbi']->getError()); } /** * Reload the navigation */ $GLOBALS['reload'] = true; $GLOBALS['db'] = $username; $q = 'GRANT ALL PRIVILEGES ON ' . PMA_Util::backquote(PMA_Util::escapeMysqlWildcards(PMA_Util::sqlAddSlashes($username))) . '.* TO \'' . PMA_Util::sqlAddSlashes($username) . '\'@\'' . PMA_Util::sqlAddSlashes($hostname) . '\';'; $sql_query .= $q; if (!$GLOBALS['dbi']->tryQuery($q)) { $message = PMA_Message::rawError($GLOBALS['dbi']->getError()); } } if (isset($_REQUEST['createdb-2'])) { // Grant all privileges on wildcard name (username\_%) $q = 'GRANT ALL PRIVILEGES ON ' . PMA_Util::backquote(PMA_Util::sqlAddSlashes($username) . '\\_%') . '.* TO \'' . PMA_Util::sqlAddSlashes($username) . '\'@\'' . PMA_Util::sqlAddSlashes($hostname) . '\';'; $sql_query .= $q; if (!$GLOBALS['dbi']->tryQuery($q)) { $message = PMA_Message::rawError($GLOBALS['dbi']->getError()); } } if (isset($_REQUEST['createdb-3'])) { // Grant all privileges on the specified database to the new user $q = 'GRANT ALL PRIVILEGES ON ' . PMA_Util::backquote(PMA_Util::sqlAddSlashes($dbname)) . '.* TO \'' . PMA_Util::sqlAddSlashes($username) . '\'@\'' . PMA_Util::sqlAddSlashes($hostname) . '\';'; $sql_query .= $q; if (!$GLOBALS['dbi']->tryQuery($q)) { $message = PMA_Message::rawError($GLOBALS['dbi']->getError()); } } return array($sql_query, $message); }
/** * returns array of all tables in given db or dbs * this function expects unquoted names: * RIGHT: my_database * WRONG: `my_database` * WRONG: my\_database * if $tbl_is_group is true, $table is used as filter for table names * * <code> * $GLOBALS['dbi']->getTablesFull('my_database'); * $GLOBALS['dbi']->getTablesFull('my_database', 'my_table')); * $GLOBALS['dbi']->getTablesFull('my_database', 'my_tables_', true)); * </code> * * @param string $database database * @param string|bool $table table or false * @param boolean $tbl_is_group $table is a table group * @param mixed $link mysql link * @param integer $limit_offset zero-based offset for the count * @param boolean|integer $limit_count number of tables to return * @param string $sort_by table attribute to sort by * @param string $sort_order direction to sort (ASC or DESC) * @param string $tble_type whether table or view * * @todo move into PMA_Table * * @return array list of tables in given db(s) */ public function getTablesFull($database, $table = false, $tbl_is_group = false, $link = null, $limit_offset = 0, $limit_count = false, $sort_by = 'Name', $sort_order = 'ASC', $tble_type = null) { if (true === $limit_count) { $limit_count = $GLOBALS['cfg']['MaxTableList']; } // prepare and check parameters if (!is_array($database)) { $databases = array($database); } else { $databases = $database; } $tables = array(); // get table information from information_schema if ($table) { if (true === $tbl_is_group) { $sql_where_table = 'AND t.`TABLE_NAME` LIKE \'' . PMA_Util::escapeMysqlWildcards(PMA_Util::sqlAddSlashes($table)) . '%\''; } else { $sql_where_table = 'AND t.`TABLE_NAME` = \'' . PMA_Util::sqlAddSlashes($table) . '\''; } } else { $sql_where_table = ''; } if ($tble_type) { if ($tble_type == 'view') { if (PMA_DRIZZLE) { $sql_where_table .= " AND t.`TABLE_TYPE` != 'BASE'"; } else { $sql_where_table .= " AND t.`TABLE_TYPE` != 'BASE TABLE'"; } } else { if ($tble_type == 'table') { if (PMA_DRIZZLE) { $sql_where_table .= " AND t.`TABLE_TYPE` = 'BASE'"; } else { $sql_where_table .= " AND t.`TABLE_TYPE` = 'BASE TABLE'"; } } } } // for PMA bc: // `SCHEMA_FIELD_NAME` AS `SHOW_TABLE_STATUS_FIELD_NAME` // // on non-Windows servers, // added BINARY in the WHERE clause to force a case sensitive // comparison (if we are looking for the db Aa we don't want // to find the db aa) $this_databases = array_map('PMA_Util::sqlAddSlashes', $databases); if (PMA_DRIZZLE) { $engine_info = PMA_Util::cacheGet('drizzle_engines', true); $stats_join = "LEFT JOIN (SELECT 0 NUM_ROWS) AS stat ON false"; if (isset($engine_info['InnoDB']) && $engine_info['InnoDB']['module_library'] == 'innobase') { $stats_join = "LEFT JOIN data_dictionary.INNODB_SYS_TABLESTATS" . " stat ON (t.ENGINE = 'InnoDB' AND stat.NAME" . " = (t.TABLE_SCHEMA || '/') || t.TABLE_NAME)"; } // data_dictionary.table_cache may not contain any data // for some tables, it's just a table cache // auto_increment == 0 is cast to NULL because currently // (2011.03.13 GA) // Drizzle doesn't provide correct value $sql = "\n SELECT t.*,\n t.TABLE_SCHEMA AS `Db`,\n t.TABLE_NAME AS `Name`,\n t.TABLE_TYPE AS `TABLE_TYPE`,\n t.ENGINE AS `Engine`,\n t.ENGINE AS `Type`,\n t.TABLE_VERSION AS `Version`,-- VERSION\n t.ROW_FORMAT AS `Row_format`,\n coalesce(tc.ROWS, stat.NUM_ROWS)\n AS `Rows`,-- TABLE_ROWS,\n coalesce(tc.ROWS, stat.NUM_ROWS)\n AS `TABLE_ROWS`,\n tc.AVG_ROW_LENGTH AS `Avg_row_length`, -- AVG_ROW_LENGTH\n tc.TABLE_SIZE AS `Data_length`, -- DATA_LENGTH\n NULL AS `Max_data_length`, -- MAX_DATA_LENGTH\n NULL AS `Index_length`, -- INDEX_LENGTH\n NULL AS `Data_free`, -- DATA_FREE\n nullif(t.AUTO_INCREMENT, 0)\n AS `Auto_increment`,\n t.TABLE_CREATION_TIME AS `Create_time`, -- CREATE_TIME\n t.TABLE_UPDATE_TIME AS `Update_time`, -- UPDATE_TIME\n NULL AS `Check_time`, -- CHECK_TIME\n t.TABLE_COLLATION AS `Collation`,\n NULL AS `Checksum`, -- CHECKSUM\n NULL AS `Create_options`, -- CREATE_OPTIONS\n t.TABLE_COMMENT AS `Comment`\n FROM data_dictionary.TABLES t\n LEFT JOIN data_dictionary.TABLE_CACHE tc\n ON tc.TABLE_SCHEMA = t.TABLE_SCHEMA AND tc.TABLE_NAME\n = t.TABLE_NAME\n {$stats_join}\n WHERE t.TABLE_SCHEMA IN ('" . implode("', '", $this_databases) . "')\n " . $sql_where_table; } else { $sql = ' SELECT *, `TABLE_SCHEMA` AS `Db`, `TABLE_NAME` AS `Name`, `TABLE_TYPE` AS `TABLE_TYPE`, `ENGINE` AS `Engine`, `ENGINE` AS `Type`, `VERSION` AS `Version`, `ROW_FORMAT` AS `Row_format`, `TABLE_ROWS` AS `Rows`, `AVG_ROW_LENGTH` AS `Avg_row_length`, `DATA_LENGTH` AS `Data_length`, `MAX_DATA_LENGTH` AS `Max_data_length`, `INDEX_LENGTH` AS `Index_length`, `DATA_FREE` AS `Data_free`, `AUTO_INCREMENT` AS `Auto_increment`, `CREATE_TIME` AS `Create_time`, `UPDATE_TIME` AS `Update_time`, `CHECK_TIME` AS `Check_time`, `TABLE_COLLATION` AS `Collation`, `CHECKSUM` AS `Checksum`, `CREATE_OPTIONS` AS `Create_options`, `TABLE_COMMENT` AS `Comment` FROM `information_schema`.`TABLES` t WHERE ' . (PMA_IS_WINDOWS ? '' : 'BINARY') . ' `TABLE_SCHEMA` IN (\'' . implode("', '", $this_databases) . '\') ' . $sql_where_table; } // Sort the tables $sql .= " ORDER BY {$sort_by} {$sort_order}"; if ($limit_count) { $sql .= ' LIMIT ' . $limit_count . ' OFFSET ' . $limit_offset; } $tables = $this->fetchResult($sql, array('TABLE_SCHEMA', 'TABLE_NAME'), null, $link); unset($sql_where_table, $sql); if (PMA_DRIZZLE) { // correct I_S and D_D names returned by D_D.TABLES - // Drizzle generally uses lower case for them, // but TABLES returns uppercase foreach ((array) $database as $db) { $db_upper = strtoupper($db); if (!isset($tables[$db]) && isset($tables[$db_upper])) { $tables[$db] = $tables[$db_upper]; unset($tables[$db_upper]); } } } if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) { // here, the array's first key is by schema name foreach ($tables as $one_database_name => $one_database_tables) { uksort($one_database_tables, 'strnatcasecmp'); if ($sort_order == 'DESC') { $one_database_tables = array_reverse($one_database_tables); } $tables[$one_database_name] = $one_database_tables; } } else { if ($sort_by == 'Data_length') { // Size = Data_length + Index_length foreach ($tables as $one_database_name => $one_database_tables) { uasort($one_database_tables, function ($a, $b) { $aLength = $a['Data_length'] + $a['Index_length']; $bLength = $b['Data_length'] + $b['Index_length']; return $aLength == $bLength ? 0 : $aLength < $bLength ? -1 : 1; }); if ($sort_order == 'DESC') { $one_database_tables = array_reverse($one_database_tables); } $tables[$one_database_name] = $one_database_tables; } } } // end (get information from table schema) // If permissions are wrong on even one database directory, // information_schema does not return any table info for any database // this is why we fall back to SHOW TABLE STATUS even for MySQL >= 50002 if (empty($tables) && !PMA_DRIZZLE) { foreach ($databases as $each_database) { if ($table || true === $tbl_is_group || $tble_type) { $sql = 'SHOW TABLE STATUS FROM ' . PMA_Util::backquote($each_database) . ' WHERE'; $needAnd = false; if ($table || true === $tbl_is_group) { $sql .= " `Name` LIKE '" . PMA_Util::escapeMysqlWildcards(PMA_Util::sqlAddSlashes($table, true)) . "%'"; $needAnd = true; } if ($tble_type) { if ($needAnd) { $sql .= " AND"; } if ($tble_type == 'view') { $sql .= " `Comment` = 'VIEW'"; } else { if ($tble_type == 'table') { $sql .= " `Comment` != 'VIEW'"; } } } } else { $sql = 'SHOW TABLE STATUS FROM ' . PMA_Util::backquote($each_database); } $useStatusCache = false; if (extension_loaded('apc') && isset($GLOBALS['cfg']['Server']['StatusCacheDatabases']) && !empty($GLOBALS['cfg']['Server']['StatusCacheLifetime'])) { $statusCacheDatabases = (array) $GLOBALS['cfg']['Server']['StatusCacheDatabases']; if (in_array($each_database, $statusCacheDatabases)) { $useStatusCache = true; } } $each_tables = null; if ($useStatusCache) { $cacheKey = 'phpMyAdmin_tableStatus_' . sha1($GLOBALS['cfg']['Server']['host'] . '_' . $sql); $each_tables = apc_fetch($cacheKey); } if (!$each_tables) { $each_tables = $this->fetchResult($sql, 'Name', null, $link); } if ($useStatusCache) { apc_store($cacheKey, $each_tables, $GLOBALS['cfg']['Server']['StatusCacheLifetime']); } // Sort naturally if the config allows it and we're sorting // the Name column. if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) { uksort($each_tables, 'strnatcasecmp'); if ($sort_order == 'DESC') { $each_tables = array_reverse($each_tables); } } else { // Prepare to sort by creating array of the selected sort // value to pass to array_multisort // Size = Data_length + Index_length if ($sort_by == 'Data_length') { foreach ($each_tables as $table_name => $table_data) { ${$sort_by}[$table_name] = strtolower($table_data['Data_length'] + $table_data['Index_length']); } } else { foreach ($each_tables as $table_name => $table_data) { ${$sort_by}[$table_name] = strtolower($table_data[$sort_by]); } } if ($sort_order == 'DESC') { array_multisort(${$sort_by}, SORT_DESC, $each_tables); } else { array_multisort(${$sort_by}, SORT_ASC, $each_tables); } // cleanup the temporary sort array unset(${$sort_by}); } if ($limit_count) { $each_tables = array_slice($each_tables, $limit_offset, $limit_count); } $tables[$each_database] = $this->copyTableProperties($each_tables, $each_database); } } // cache table data // so PMA_Table does not require to issue SHOW TABLE STATUS again // Note: I don't see why we would need array_merge_recursive() here, // as it creates double entries for the same table (for example a double // entry for Comment when changing the storage engine in Operations) // Note 2: Instead of array_merge(), simply use the + operator because // array_merge() renumbers numeric keys starting with 0, therefore // we would lose a db name thats consists only of numbers foreach ($tables as $one_database => $its_tables) { if (isset(PMA_Table::$cache[$one_database])) { // the + operator does not do the intended effect // when the cache for one table already exists if ($table && isset(PMA_Table::$cache[$one_database][$table])) { unset(PMA_Table::$cache[$one_database][$table]); } PMA_Table::$cache[$one_database] = PMA_Table::$cache[$one_database] + $tables[$one_database]; } else { PMA_Table::$cache[$one_database] = $tables[$one_database]; } } unset($one_database, $its_tables); if (!is_array($database)) { if (isset($tables[$database])) { return $tables[$database]; } elseif (isset($tables[strtolower($database)])) { // on windows with lower_case_table_names = 1 // MySQL returns // with SHOW DATABASES or information_schema.SCHEMATA: `Test` // but information_schema.TABLES gives `test` // bug #2036 // https://sourceforge.net/p/phpmyadmin/bugs/2036/ return $tables[strtolower($database)]; } else { // one database but inexact letter case match // as Drizzle is always case insensitive, // we can safely return the only result if (PMA_DRIZZLE && count($tables) == 1) { $keys = array_keys($tables); if (strlen(array_pop($keys)) == strlen($database)) { return array_pop($tables); } } return $tables; } } else { return $tables; } }
/** * @global bool whether selected db is information_schema */ $db_is_information_schema = false; if (PMA_is_system_schema($db)) { $is_show_stats = false; $db_is_information_schema = true; } /** * @global array information about tables in db */ $tables = array(); // When used in Nested table group mode, // only show tables matching the given groupname if (PMA_isValid($_REQUEST['tbl_group'])) { $tbl_group_sql = ' LIKE "' . PMA_Util::escapeMysqlWildcards($_REQUEST['tbl_group']) . '%"'; } else { $tbl_group_sql = ''; } $tooltip_truename = array(); $tooltip_aliasname = array(); // Special speedup for newer MySQL Versions (in 4.0 format changed) if (true === $cfg['SkipLockedTables']) { $db_info_result = PMA_DBI_query('SHOW OPEN TABLES FROM ' . PMA_Util::backquote($db) . ';'); // Blending out tables in use if ($db_info_result && PMA_DBI_num_rows($db_info_result) > 0) { while ($tmp = PMA_DBI_fetch_row($db_info_result)) { // if in use memorize tablename if (preg_match('@in_use=[1-9]+@i', $tmp[1])) { $sot_cache[$tmp[0]] = true; }
/** * returns array of all tables in given db or dbs * this function expects unquoted names: * RIGHT: my_database * WRONG: `my_database` * WRONG: my\_database * if $tbl_is_group is true, $table is used as filter for table names * * <code> * $GLOBALS['dbi']->getTablesFull('my_database'); * $GLOBALS['dbi']->getTablesFull('my_database', 'my_table')); * $GLOBALS['dbi']->getTablesFull('my_database', 'my_tables_', true)); * </code> * * @param string $database database * @param string $table table name * @param boolean $tbl_is_group $table is a table group * @param mixed $link mysql link * @param integer $limit_offset zero-based offset for the count * @param boolean|integer $limit_count number of tables to return * @param string $sort_by table attribute to sort by * @param string $sort_order direction to sort (ASC or DESC) * @param string $table_type whether table or view * * @todo move into PMA_Table * * @return array list of tables in given db(s) */ public function getTablesFull($database, $table = '', $tbl_is_group = false, $link = null, $limit_offset = 0, $limit_count = false, $sort_by = 'Name', $sort_order = 'ASC', $table_type = null) { if (true === $limit_count) { $limit_count = $GLOBALS['cfg']['MaxTableList']; } // prepare and check parameters if (!is_array($database)) { $databases = array($database); } else { $databases = $database; } $tables = array(); if (!isset($GLOBALS['cfg']['Server']['DisableIS']) || !$GLOBALS['cfg']['Server']['DisableIS']) { $sql_where_table = $this->_getTableCondition($table, $tbl_is_group, $table_type); // for PMA bc: // `SCHEMA_FIELD_NAME` AS `SHOW_TABLE_STATUS_FIELD_NAME` // // on non-Windows servers, // added BINARY in the WHERE clause to force a case sensitive // comparison (if we are looking for the db Aa we don't want // to find the db aa) $this_databases = array_map('PMA_Util::sqlAddSlashes', $databases); $sql = $this->_getSqlForTablesFull($this_databases, $sql_where_table); // Sort the tables $sql .= " ORDER BY {$sort_by} {$sort_order}"; if ($limit_count) { $sql .= ' LIMIT ' . $limit_count . ' OFFSET ' . $limit_offset; } $tables = $this->fetchResult($sql, array('TABLE_SCHEMA', 'TABLE_NAME'), null, $link); if (PMA_DRIZZLE) { // correct I_S and D_D names returned by D_D.TABLES - // Drizzle generally uses lower case for them, // but TABLES returns uppercase foreach ((array) $database as $db) { $db_upper = mb_strtoupper($db); if (!isset($tables[$db]) && isset($tables[$db_upper])) { $tables[$db] = $tables[$db_upper]; unset($tables[$db_upper]); } } } if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) { // here, the array's first key is by schema name foreach ($tables as $one_database_name => $one_database_tables) { uksort($one_database_tables, 'strnatcasecmp'); if ($sort_order == 'DESC') { $one_database_tables = array_reverse($one_database_tables); } $tables[$one_database_name] = $one_database_tables; } } else { if ($sort_by == 'Data_length') { // Size = Data_length + Index_length foreach ($tables as $one_database_name => $one_database_tables) { uasort($one_database_tables, function ($a, $b) { $aLength = $a['Data_length'] + $a['Index_length']; $bLength = $b['Data_length'] + $b['Index_length']; return $aLength == $bLength ? 0 : $aLength < $bLength ? -1 : 1; }); if ($sort_order == 'DESC') { $one_database_tables = array_reverse($one_database_tables); } $tables[$one_database_name] = $one_database_tables; } } } } // end (get information from table schema) // If permissions are wrong on even one database directory, // information_schema does not return any table info for any database // this is why we fall back to SHOW TABLE STATUS even for MySQL >= 50002 if (empty($tables) && !PMA_DRIZZLE) { foreach ($databases as $each_database) { if ($table || true === $tbl_is_group || !empty($table_type)) { $sql = 'SHOW TABLE STATUS FROM ' . PMA_Util::backquote($each_database) . ' WHERE'; $needAnd = false; if ($table || true === $tbl_is_group) { $sql .= " `Name` LIKE '" . PMA_Util::escapeMysqlWildcards(PMA_Util::sqlAddSlashes($table, true)) . "%'"; $needAnd = true; } if (!empty($table_type)) { if ($needAnd) { $sql .= " AND"; } if ($table_type == 'view') { $sql .= " `Comment` = 'VIEW'"; } else { if ($table_type == 'table') { $sql .= " `Comment` != 'VIEW'"; } } } } else { $sql = 'SHOW TABLE STATUS FROM ' . PMA_Util::backquote($each_database); } $useStatusCache = false; if (extension_loaded('apc') && isset($GLOBALS['cfg']['Server']['StatusCacheDatabases']) && !empty($GLOBALS['cfg']['Server']['StatusCacheLifetime'])) { $statusCacheDatabases = (array) $GLOBALS['cfg']['Server']['StatusCacheDatabases']; if (in_array($each_database, $statusCacheDatabases)) { $useStatusCache = true; } } $each_tables = null; if ($useStatusCache) { $cacheKey = 'phpMyAdmin_tableStatus_' . sha1($GLOBALS['cfg']['Server']['host'] . '_' . $sql); $each_tables = apc_fetch($cacheKey); } if (!$each_tables) { $each_tables = $this->fetchResult($sql, 'Name', null, $link); } if ($useStatusCache) { apc_store($cacheKey, $each_tables, $GLOBALS['cfg']['Server']['StatusCacheLifetime']); } // Sort naturally if the config allows it and we're sorting // the Name column. if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) { uksort($each_tables, 'strnatcasecmp'); if ($sort_order == 'DESC') { $each_tables = array_reverse($each_tables); } } else { // Prepare to sort by creating array of the selected sort // value to pass to array_multisort // Size = Data_length + Index_length if ($sort_by == 'Data_length') { foreach ($each_tables as $table_name => $table_data) { ${$sort_by}[$table_name] = strtolower($table_data['Data_length'] + $table_data['Index_length']); } } else { foreach ($each_tables as $table_name => $table_data) { ${$sort_by}[$table_name] = strtolower($table_data[$sort_by]); } } if (!empty(${$sort_by})) { if ($sort_order == 'DESC') { array_multisort(${$sort_by}, SORT_DESC, $each_tables); } else { array_multisort(${$sort_by}, SORT_ASC, $each_tables); } } // cleanup the temporary sort array unset(${$sort_by}); } if ($limit_count) { $each_tables = array_slice($each_tables, $limit_offset, $limit_count); } foreach ($each_tables as $table_name => $each_table) { if (!isset($each_tables[$table_name]['Type']) && isset($each_tables[$table_name]['Engine'])) { // pma BC, same parts of PMA still uses 'Type' $each_tables[$table_name]['Type'] =& $each_tables[$table_name]['Engine']; } elseif (!isset($each_tables[$table_name]['Engine']) && isset($each_tables[$table_name]['Type'])) { // old MySQL reports Type, newer MySQL reports Engine $each_tables[$table_name]['Engine'] =& $each_tables[$table_name]['Type']; } // MySQL forward compatibility // so pma could use this array as if every server // is of version >5.0 // todo : remove and check usage in the rest of the code, // MySQL 5.0 is required by current PMA version $each_tables[$table_name]['TABLE_SCHEMA'] = $each_database; $each_tables[$table_name]['TABLE_NAME'] =& $each_tables[$table_name]['Name']; $each_tables[$table_name]['ENGINE'] =& $each_tables[$table_name]['Engine']; $each_tables[$table_name]['VERSION'] =& $each_tables[$table_name]['Version']; $each_tables[$table_name]['ROW_FORMAT'] =& $each_tables[$table_name]['Row_format']; $each_tables[$table_name]['TABLE_ROWS'] =& $each_tables[$table_name]['Rows']; $each_tables[$table_name]['AVG_ROW_LENGTH'] =& $each_tables[$table_name]['Avg_row_length']; $each_tables[$table_name]['DATA_LENGTH'] =& $each_tables[$table_name]['Data_length']; $each_tables[$table_name]['MAX_DATA_LENGTH'] =& $each_tables[$table_name]['Max_data_length']; $each_tables[$table_name]['INDEX_LENGTH'] =& $each_tables[$table_name]['Index_length']; $each_tables[$table_name]['DATA_FREE'] =& $each_tables[$table_name]['Data_free']; $each_tables[$table_name]['AUTO_INCREMENT'] =& $each_tables[$table_name]['Auto_increment']; $each_tables[$table_name]['CREATE_TIME'] =& $each_tables[$table_name]['Create_time']; $each_tables[$table_name]['UPDATE_TIME'] =& $each_tables[$table_name]['Update_time']; $each_tables[$table_name]['CHECK_TIME'] =& $each_tables[$table_name]['Check_time']; $each_tables[$table_name]['TABLE_COLLATION'] =& $each_tables[$table_name]['Collation']; $each_tables[$table_name]['CHECKSUM'] =& $each_tables[$table_name]['Checksum']; $each_tables[$table_name]['CREATE_OPTIONS'] =& $each_tables[$table_name]['Create_options']; $each_tables[$table_name]['TABLE_COMMENT'] =& $each_tables[$table_name]['Comment']; if (strtoupper($each_tables[$table_name]['Comment']) === 'VIEW' && $each_tables[$table_name]['Engine'] == null) { $each_tables[$table_name]['TABLE_TYPE'] = 'VIEW'; } elseif ($each_database == 'information_schema') { $each_tables[$table_name]['TABLE_TYPE'] = 'SYSTEM VIEW'; } else { /** * @todo difference between 'TEMPORARY' and 'BASE TABLE' * but how to detect? */ $each_tables[$table_name]['TABLE_TYPE'] = 'BASE TABLE'; } } $tables[$each_database] = $each_tables; } } // cache table data // so PMA_Table does not require to issue SHOW TABLE STATUS again $this->_cacheTableData($tables, $table); if (is_array($database)) { return $tables; } if (isset($tables[$database])) { return $tables[$database]; } if (isset($tables[mb_strtolower($database)])) { // on windows with lower_case_table_names = 1 // MySQL returns // with SHOW DATABASES or information_schema.SCHEMATA: `Test` // but information_schema.TABLES gives `test` // bug #2036 // https://sourceforge.net/p/phpmyadmin/bugs/2036/ return $tables[mb_strtolower($database)]; } // one database but inexact letter case match // as Drizzle is always case insensitive, // we can safely return the only result if (!PMA_DRIZZLE || !count($tables) == 1) { return $tables; } $keys = array_keys($tables); if (mb_strlen(array_pop($keys)) == mb_strlen($database)) { return array_pop($tables); } return $tables; }
/** * Gets the list of tables in the current db, taking into account * that they might be "in use" * * @param string $db database name * @param object $db_info_result result set * * @return array $tables list of tables * */ public function getTablesWhenOpen($db, $db_info_result) { $tables = array(); while ($tmp = $GLOBALS['dbi']->fetchAssoc($db_info_result)) { // if in use, memorize table name if ($tmp['In_use'] > 0) { $sot_cache[$tmp['Table']] = true; } } $GLOBALS['dbi']->freeResult($db_info_result); // is there at least one "in use" table? if (isset($sot_cache)) { $db_info_result = false; $tblGroupSql = ""; $whereAdded = false; if (PMA_isValid($_REQUEST['tbl_group'])) { $group = PMA_Util::escapeMysqlWildcards($_REQUEST['tbl_group']); $groupWithSeparator = PMA_Util::escapeMysqlWildcards($_REQUEST['tbl_group'] . $GLOBALS['cfg']['NavigationTreeTableSeparator']); $tblGroupSql .= " WHERE (" . PMA_Util::backquote('Tables_in_' . $db) . " LIKE '" . $groupWithSeparator . "%'" . " OR " . PMA_Util::backquote('Tables_in_' . $db) . " LIKE '" . $group . "')"; $whereAdded = true; } if (PMA_isValid($_REQUEST['tbl_type'], array('table', 'view'))) { $tblGroupSql .= $whereAdded ? " AND" : " WHERE"; if ($_REQUEST['tbl_type'] == 'view') { $tblGroupSql .= " `Table_type` != 'BASE TABLE'"; } else { $tblGroupSql .= " `Table_type` = 'BASE TABLE'"; } } $db_info_result = $GLOBALS['dbi']->query('SHOW FULL TABLES FROM ' . PMA_Util::backquote($db) . $tblGroupSql, null, PMA_DatabaseInterface::QUERY_STORE); unset($tblGroupSql, $whereAdded); if ($db_info_result && $GLOBALS['dbi']->numRows($db_info_result) > 0) { while ($tmp = $GLOBALS['dbi']->fetchRow($db_info_result)) { if (!isset($sot_cache[$tmp[0]])) { $sts_result = $GLOBALS['dbi']->query("SHOW TABLE STATUS FROM " . PMA_Util::backquote($db) . " LIKE '" . PMA_Util::sqlAddSlashes($tmp[0], true) . "';"); $sts_tmp = $GLOBALS['dbi']->fetchAssoc($sts_result); $GLOBALS['dbi']->freeResult($sts_result); unset($sts_result); $tableArray = $GLOBALS['dbi']->copyTableProperties(array($sts_tmp), $db); $tables[$sts_tmp['Name']] = $tableArray[0]; } else { // table in use $tables[$tmp[0]] = array('TABLE_NAME' => $tmp[0], 'ENGINE' => '', 'TABLE_TYPE' => '', 'TABLE_ROWS' => 0); } } // end while if ($GLOBALS['cfg']['NaturalOrder']) { uksort($tables, 'strnatcasecmp'); } } elseif ($db_info_result) { $GLOBALS['dbi']->freeResult($db_info_result); } unset($sot_cache); } return $tables; }
/** * returns array of all tables in given db or dbs * this function expects unquoted names: * RIGHT: my_database * WRONG: `my_database` * WRONG: my\_database * if $tbl_is_group is true, $table is used as filter for table names * * <code> * $GLOBALS['dbi']->getTablesFull('my_database'); * $GLOBALS['dbi']->getTablesFull('my_database', 'my_table')); * $GLOBALS['dbi']->getTablesFull('my_database', 'my_tables_', true)); * </code> * * @param string $database database * @param string|bool $table table name or false * @param boolean $tbl_is_group $table is a table group * @param mixed $link mysql link * @param integer $limit_offset zero-based offset for the count * @param boolean|integer $limit_count number of tables to return * @param string $sort_by table attribute to sort by * @param string $sort_order direction to sort (ASC or DESC) * @param string $tble_type whether table or view * * @todo move into PMA_Table * * @return array list of tables in given db(s) */ public function getTablesFull($database, $table = false, $tbl_is_group = false, $link = null, $limit_offset = 0, $limit_count = false, $sort_by = 'Name', $sort_order = 'ASC', $tble_type = null) { if (true === $limit_count) { $limit_count = $GLOBALS['cfg']['MaxTableList']; } // prepare and check parameters if (!is_array($database)) { $databases = array($database); } else { $databases = $database; } $tables = array(); $sql_where_table = $this->_getTableCondition($table, $tbl_is_group, $tble_type); // for PMA bc: // `SCHEMA_FIELD_NAME` AS `SHOW_TABLE_STATUS_FIELD_NAME` // // on non-Windows servers, // added BINARY in the WHERE clause to force a case sensitive // comparison (if we are looking for the db Aa we don't want // to find the db aa) $this_databases = array_map('PMA_Util::sqlAddSlashes', $databases); $sql = $this->_getSqlForTablesFull($this_databases, $sql_where_table); unset($sql_where_table); // Sort the tables $sql .= " ORDER BY {$sort_by} {$sort_order}"; if ($limit_count) { $sql .= ' LIMIT ' . $limit_count . ' OFFSET ' . $limit_offset; } $tables = $this->fetchResult($sql, array('TABLE_SCHEMA', 'TABLE_NAME'), null, $link); unset($sql); if (PMA_DRIZZLE) { // correct I_S and D_D names returned by D_D.TABLES - // Drizzle generally uses lower case for them, // but TABLES returns uppercase foreach ((array) $database as $db) { $db_upper = strtoupper($db); if (!isset($tables[$db]) && isset($tables[$db_upper])) { $tables[$db] = $tables[$db_upper]; unset($tables[$db_upper]); } } } if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) { // here, the array's first key is by schema name foreach ($tables as $one_database_name => $one_database_tables) { uksort($one_database_tables, 'strnatcasecmp'); if ($sort_order == 'DESC') { $one_database_tables = array_reverse($one_database_tables); } $tables[$one_database_name] = $one_database_tables; } } else { if ($sort_by == 'Data_length') { // Size = Data_length + Index_length foreach ($tables as $one_database_name => $one_database_tables) { uasort($one_database_tables, function ($a, $b) { $aLength = $a['Data_length'] + $a['Index_length']; $bLength = $b['Data_length'] + $b['Index_length']; return $aLength == $bLength ? 0 : $aLength < $bLength ? -1 : 1; }); if ($sort_order == 'DESC') { $one_database_tables = array_reverse($one_database_tables); } $tables[$one_database_name] = $one_database_tables; } } } // end (get information from table schema) // If permissions are wrong on even one database directory, // information_schema does not return any table info for any database // this is why we fall back to SHOW TABLE STATUS even for MySQL >= 50002 if (empty($tables) && !PMA_DRIZZLE) { foreach ($databases as $each_database) { if ($table || true === $tbl_is_group || $tble_type) { $sql = 'SHOW TABLE STATUS FROM ' . PMA_Util::backquote($each_database) . ' WHERE'; $needAnd = false; if ($table || true === $tbl_is_group) { $sql .= " `Name` LIKE '" . PMA_Util::escapeMysqlWildcards(PMA_Util::sqlAddSlashes($table, true)) . "%'"; $needAnd = true; } if ($tble_type) { if ($needAnd) { $sql .= " AND"; } if ($tble_type == 'view') { $sql .= " `Comment` = 'VIEW'"; } else { if ($tble_type == 'table') { $sql .= " `Comment` != 'VIEW'"; } } } } else { $sql = 'SHOW TABLE STATUS FROM ' . PMA_Util::backquote($each_database); } $useStatusCache = false; if (extension_loaded('apc') && isset($GLOBALS['cfg']['Server']['StatusCacheDatabases']) && !empty($GLOBALS['cfg']['Server']['StatusCacheLifetime'])) { $statusCacheDatabases = (array) $GLOBALS['cfg']['Server']['StatusCacheDatabases']; if (in_array($each_database, $statusCacheDatabases)) { $useStatusCache = true; } } $each_tables = null; if ($useStatusCache) { $cacheKey = 'phpMyAdmin_tableStatus_' . sha1($GLOBALS['cfg']['Server']['host'] . '_' . $sql); $each_tables = apc_fetch($cacheKey); } if (!$each_tables) { $each_tables = $this->fetchResult($sql, 'Name', null, $link); } if ($useStatusCache) { apc_store($cacheKey, $each_tables, $GLOBALS['cfg']['Server']['StatusCacheLifetime']); } // Sort naturally if the config allows it and we're sorting // the Name column. if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) { uksort($each_tables, 'strnatcasecmp'); if ($sort_order == 'DESC') { $each_tables = array_reverse($each_tables); } } else { // Prepare to sort by creating array of the selected sort // value to pass to array_multisort // Size = Data_length + Index_length if ($sort_by == 'Data_length') { foreach ($each_tables as $table_name => $table_data) { ${$sort_by}[$table_name] = strtolower($table_data['Data_length'] + $table_data['Index_length']); } } else { foreach ($each_tables as $table_name => $table_data) { ${$sort_by}[$table_name] = strtolower($table_data[$sort_by]); } } if ($sort_order == 'DESC') { array_multisort(${$sort_by}, SORT_DESC, $each_tables); } else { array_multisort(${$sort_by}, SORT_ASC, $each_tables); } // cleanup the temporary sort array unset(${$sort_by}); } if ($limit_count) { $each_tables = array_slice($each_tables, $limit_offset, $limit_count); } $tables[$each_database] = $this->copyTableProperties($each_tables, $each_database); } } $this->_cacheTableData($tables, $table); if (!is_array($database)) { if (isset($tables[$database])) { return $tables[$database]; } elseif (isset($tables[strtolower($database)])) { // on windows with lower_case_table_names = 1 // MySQL returns // with SHOW DATABASES or information_schema.SCHEMATA: `Test` // but information_schema.TABLES gives `test` // bug #2036 // https://sourceforge.net/p/phpmyadmin/bugs/2036/ return $tables[strtolower($database)]; } else { // one database but inexact letter case match // as Drizzle is always case insensitive, // we can safely return the only result if (PMA_DRIZZLE && count($tables) == 1) { $keys = array_keys($tables); if (strlen(array_pop($keys)) == strlen($database)) { return array_pop($tables); } } return $tables; } } else { return $tables; } }
// Blending out tables in use if ($db_info_result && $GLOBALS['dbi']->numRows($db_info_result) > 0) { while ($tmp = $GLOBALS['dbi']->fetchAssoc($db_info_result)) { // if in use, memorize table name if ($tmp['In_use'] > 0) { $sot_cache[$tmp['Table']] = true; } } $GLOBALS['dbi']->freeResult($db_info_result); if (isset($sot_cache)) { $db_info_result = false; $tblGroupSql = ""; $whereAdded = false; if (PMA_isValid($_REQUEST['tbl_group'])) { $group = PMA_Util::escapeMysqlWildcards($_REQUEST['tbl_group']); $groupWithSeparator = PMA_Util::escapeMysqlWildcards($_REQUEST['tbl_group'] . $GLOBALS['cfg']['NavigationTreeTableSeparator']); $tblGroupSql .= " WHERE (" . PMA_Util::backquote('Tables_in_' . $db) . " LIKE '" . $groupWithSeparator . "%'" . " OR " . PMA_Util::backquote('Tables_in_' . $db) . " LIKE '" . $group . "')"; $whereAdded = true; } if (PMA_isValid($_REQUEST['tbl_type'], array('table', 'view'))) { $tblGroupSql .= $whereAdded ? " AND" : " WHERE"; if ($_REQUEST['tbl_type'] == 'view') { $tblGroupSql .= " `Table_type` != 'BASE TABLE'"; } else { $tblGroupSql .= " `Table_type` = 'BASE TABLE'"; } } $db_info_result = $GLOBALS['dbi']->query('SHOW FULL TABLES FROM ' . PMA_Util::backquote($db) . $tblGroupSql, null, PMA_DatabaseInterface::QUERY_STORE); unset($tblGroupSql, $whereAdded); if ($db_info_result && $GLOBALS['dbi']->numRows($db_info_result) > 0) { while ($tmp = $GLOBALS['dbi']->fetchRow($db_info_result)) {
/** * PMA_Util::escapeMysqlWildcards tests * * @param string $a String to escape * @param string $b Expected value * * @return void * * @dataProvider escapeDataProvider */ public function testEscape($a, $b) { $this->assertEquals($a, PMA_Util::escapeMysqlWildcards($b)); }
/** * Prepares queries for adding users and * also create database and return query and message * * @param boolean $_error whether user create or not * @param string $real_sql_query SQL query for add a user * @param string $sql_query SQL query to be displayed * @param string $username username * @param string $hostname host name * * @return array $sql_query, $message */ function PMA_addUserAndCreateDatabase($_error, $real_sql_query, $sql_query, $username, $hostname) { if ($_error || !PMA_DBI_try_query($real_sql_query)) { $_REQUEST['createdb-1'] = $_REQUEST['createdb-2'] = $_REQUEST['createdb-3'] = false; $message = PMA_Message::rawError(PMA_DBI_getError()); } else { $message = PMA_Message::success(__('You have added a new user.')); } if (isset($_REQUEST['createdb-1'])) { // Create database with same name and grant all privileges $q = 'CREATE DATABASE IF NOT EXISTS ' . PMA_Util::backquote(PMA_Util::sqlAddSlashes($username)) . ';'; $sql_query .= $q; if (!PMA_DBI_try_query($q)) { $message = PMA_Message::rawError(PMA_DBI_getError()); } /** * If we are not in an Ajax request, we can't reload navigation now */ if ($GLOBALS['is_ajax_request'] != true) { // this is needed in case tracking is on: $GLOBALS['db'] = $username; $GLOBALS['reload'] = true; echo PMA_Util::getReloadNavigationScript(); } $q = 'GRANT ALL PRIVILEGES ON ' . PMA_Util::backquote(PMA_Util::escapeMysqlWildcards(PMA_Util::sqlAddSlashes($username))) . '.* TO \'' . PMA_Util::sqlAddSlashes($username) . '\'@\'' . PMA_Util::sqlAddSlashes($hostname) . '\';'; $sql_query .= $q; if (!PMA_DBI_try_query($q)) { $message = PMA_Message::rawError(PMA_DBI_getError()); } } if (isset($_REQUEST['createdb-2'])) { // Grant all privileges on wildcard name (username\_%) $q = 'GRANT ALL PRIVILEGES ON ' . PMA_Util::backquote(PMA_Util::sqlAddSlashes($username) . '\\_%') . '.* TO \'' . PMA_Util::sqlAddSlashes($username) . '\'@\'' . PMA_Util::sqlAddSlashes($hostname) . '\';'; $sql_query .= $q; if (!PMA_DBI_try_query($q)) { $message = PMA_Message::rawError(PMA_DBI_getError()); } } if (isset($_REQUEST['createdb-3'])) { // Grant all privileges on the specified database to the new user $q = 'GRANT ALL PRIVILEGES ON ' . PMA_Util::backquote(PMA_Util::sqlAddSlashes($dbname)) . '.* TO \'' . PMA_Util::sqlAddSlashes($username) . '\'@\'' . PMA_Util::sqlAddSlashes($hostname) . '\';'; $sql_query .= $q; if (!PMA_DBI_try_query($q)) { $message = PMA_Message::rawError(PMA_DBI_getError()); } } return array($sql_query, $message); }
/** * @global bool whether selected db is information_schema */ $db_is_information_schema = false; if (PMA_is_system_schema($db)) { $is_show_stats = false; $db_is_information_schema = true; } /** * @global array information about tables in db */ $tables = array(); // When used in Nested table group mode, // only show tables matching the given groupname if (PMA_isValid($tbl_group)) { $tbl_group_sql = ' LIKE "' . PMA_Util::escapeMysqlWildcards($tbl_group) . '%"'; } else { $tbl_group_sql = ''; } $tooltip_truename = array(); $tooltip_aliasname = array(); // Special speedup for newer MySQL Versions (in 4.0 format changed) if (true === $cfg['SkipLockedTables']) { $db_info_result = PMA_DBI_query('SHOW OPEN TABLES FROM ' . PMA_Util::backquote($db) . ';'); // Blending out tables in use if ($db_info_result && PMA_DBI_num_rows($db_info_result) > 0) { while ($tmp = PMA_DBI_fetch_row($db_info_result)) { // if in use memorize tablename if (preg_match('@in_use=[1-9]+@i', $tmp[1])) { $sot_cache[$tmp[0]] = true; }
$db_info_result = $GLOBALS['dbi']->query('SHOW OPEN TABLES FROM ' . PMA_Util::backquote($db) . ';'); // Blending out tables in use if ($db_info_result && $GLOBALS['dbi']->numRows($db_info_result) > 0) { while ($tmp = $GLOBALS['dbi']->fetchAssoc($db_info_result)) { // if in use, memorize table name if ($tmp['In_use'] > 0) { $sot_cache[$tmp['Table']] = true; } } $GLOBALS['dbi']->freeResult($db_info_result); if (isset($sot_cache)) { $db_info_result = false; $tblGroupSql = ""; $whereAdded = false; if (PMA_isValid($_REQUEST['tbl_group'])) { $tblGroupSql .= " WHERE " . PMA_Util::backquote('Tables_in_' . $db) . " LIKE '" . PMA_Util::escapeMysqlWildcards($_REQUEST['tbl_group']) . "%'"; $whereAdded = true; } if (PMA_isValid($_REQUEST['tbl_type'], array('table', 'view'))) { $tblGroupSql .= $whereAdded ? " AND" : " WHERE"; if ($_REQUEST['tbl_type'] == 'view') { $tblGroupSql .= " `Table_type` != 'BASE TABLE'"; } else { $tblGroupSql .= " `Table_type` = 'BASE TABLE'"; } } $db_info_result = $GLOBALS['dbi']->query('SHOW FULL TABLES FROM ' . PMA_Util::backquote($db) . $tblGroupSql, null, PMA_DatabaseInterface::QUERY_STORE); unset($tblGroupSql, $whereAdded); if ($db_info_result && $GLOBALS['dbi']->numRows($db_info_result) > 0) { while ($tmp = $GLOBALS['dbi']->fetchRow($db_info_result)) { if (!isset($sot_cache[$tmp[0]])) {
/** * returns a segment of the SQL WHERE clause regarding table name and type * * @param string|bool $table table or false * @param boolean $tbl_is_group $table is a table group * @param string $table_type whether table or view * * @return string a segment of the WHERE clause */ private function _getTableCondition($table, $tbl_is_group, $table_type) { // get table information from information_schema if ($table && is_string($table)) { if (true === $tbl_is_group) { $sql_where_table = 'AND t.`TABLE_NAME` LIKE \'' . PMA_Util::escapeMysqlWildcards(PMA_Util::sqlAddSlashes($table)) . '%\''; } else { $sql_where_table = 'AND t.`TABLE_NAME` = \'' . PMA_Util::sqlAddSlashes($table) . '\''; } } else { $sql_where_table = ''; } if ($table_type) { if ($table_type == 'view') { if (PMA_DRIZZLE) { $sql_where_table .= " AND t.`TABLE_TYPE` != 'BASE'"; } else { $sql_where_table .= " AND t.`TABLE_TYPE` != 'BASE TABLE'"; } } else { if ($table_type == 'table') { if (PMA_DRIZZLE) { $sql_where_table .= " AND t.`TABLE_TYPE` = 'BASE'"; } else { $sql_where_table .= " AND t.`TABLE_TYPE` = 'BASE TABLE'"; } } } } return $sql_where_table; }