/** * Saves user preferences * * @param array $config_array configuration array * * @return true|PMA_Message */ function PMA_saveUserprefs(array $config_array) { $cfgRelation = PMA_getRelationsParam(); $server = isset($GLOBALS['server']) ? $GLOBALS['server'] : $GLOBALS['cfg']['ServerDefault']; $cache_key = 'server_' . $server; if (!$cfgRelation['userconfigwork']) { // no pmadb table, use session storage $_SESSION['userconfig'] = array('db' => $config_array, 'ts' => time()); if (isset($_SESSION['cache'][$cache_key]['userprefs'])) { unset($_SESSION['cache'][$cache_key]['userprefs']); } return true; } // save configuration to pmadb $query_table = PMA_Util::backquote($cfgRelation['db']) . '.' . PMA_Util::backquote($cfgRelation['userconfig']); $query = 'SELECT `username` FROM ' . $query_table . ' WHERE `username` = \'' . PMA_Util::sqlAddSlashes($cfgRelation['user']) . '\''; $has_config = $GLOBALS['dbi']->fetchValue($query, 0, 0, $GLOBALS['controllink']); $config_data = json_encode($config_array); if ($has_config) { $query = 'UPDATE ' . $query_table . ' SET `timevalue` = NOW(), `config_data` = \'' . PMA_Util::sqlAddSlashes($config_data) . '\'' . ' WHERE `username` = \'' . PMA_Util::sqlAddSlashes($cfgRelation['user']) . '\''; } else { $query = 'INSERT INTO ' . $query_table . ' (`username`, `timevalue`,`config_data`) ' . 'VALUES (\'' . PMA_Util::sqlAddSlashes($cfgRelation['user']) . '\', NOW(), ' . '\'' . PMA_Util::sqlAddSlashes($config_data) . '\')'; } if (isset($_SESSION['cache'][$cache_key]['userprefs'])) { unset($_SESSION['cache'][$cache_key]['userprefs']); } if (!$GLOBALS['dbi']->tryQuery($query, $GLOBALS['controllink'])) { $message = PMA_Message::error(__('Could not save configuration')); $message->addMessage('<br /><br />'); $message->addMessage(PMA_Message::rawError($GLOBALS['dbi']->getError($GLOBALS['controllink']))); return $message; } return true; }
/** * Get SQL query for store new transformation details of a VIEW * * @param mysqli_result $pma_transformation_data Result set of SQL execution * @param array $column_map Details of VIEW columns * @param string $view_name Name of the VIEW * @param string $db Database name of the VIEW * * @return string $new_transformations_sql SQL query for new transformations */ function PMA_getNewTransformationDataSql($pma_transformation_data, $column_map, $view_name, $db) { $cfgRelation = PMA_getRelationsParam(); // Need to store new transformation details for VIEW $new_transformations_sql = 'INSERT INTO ' . PMA_Util::backquote($cfgRelation['db']) . '.' . PMA_Util::backquote($cfgRelation['column_info']) . ' (`db_name`, `table_name`, `column_name`, `comment`, ' . '`mimetype`, `transformation`, `transformation_options`)' . ' VALUES '; $column_count = 0; $add_comma = false; while ($data_row = $GLOBALS['dbi']->fetchAssoc($pma_transformation_data)) { foreach ($column_map as $column) { if ($data_row['table_name'] == $column['table_name'] && $data_row['column_name'] == $column['refering_column']) { $new_transformations_sql .= $add_comma ? ', ' : ''; $new_transformations_sql .= '(' . '\'' . $db . '\', ' . '\'' . $view_name . '\', ' . '\''; $new_transformations_sql .= isset($column['real_column']) ? $column['real_column'] : $column['refering_column']; $new_transformations_sql .= '\', ' . '\'' . $data_row['comment'] . '\', ' . '\'' . $data_row['mimetype'] . '\', ' . '\'' . $data_row['transformation'] . '\', ' . '\'' . PMA_Util::sqlAddSlashes($data_row['transformation_options']) . '\')'; $add_comma = true; $column_count++; break; } } if ($column_count == count($column_map)) { break; } } return $column_count > 0 ? $new_transformations_sql : ''; }
/** * returns array of partition names for a specific db/table * * @param string $db database name * @param string $table table name * * @access public * @return array of partition names */ public static function getPartitionNames($db, $table) { if (PMA_Partition::havePartitioning()) { return $GLOBALS['dbi']->fetchResult("SELECT `PARTITION_NAME` FROM `information_schema`.`PARTITIONS`" . " WHERE `TABLE_SCHEMA` = '" . PMA_Util::sqlAddSlashes($db) . "' AND `TABLE_NAME` = '" . PMA_Util::sqlAddSlashes($table) . "'"); } else { return array(); } }
/** * Returns the comment associated with node * This method should be overridden by specific type of nodes * * @return string */ public function getComment() { $db = PMA_Util::sqlAddSlashes($this->realParent()->real_name); $event = PMA_Util::sqlAddSlashes($this->real_name); $query = "SELECT `EVENT_COMMENT` "; $query .= "FROM `INFORMATION_SCHEMA`.`EVENTS` "; $query .= "WHERE `EVENT_SCHEMA`='{$db}' "; $query .= "AND `EVENT_NAME`='{$event}' "; return PMA_DBI_fetch_value($query); }
/** * returns the partition method used by the table. * * @param string $db database name * @param string $table table name * * @return string partition method */ public static function getPartitionMethod($db, $table) { if (PMA_Partition::havePartitioning()) { $partition_method = $GLOBALS['dbi']->fetchResult("SELECT `PARTITION_METHOD` FROM `information_schema`.`PARTITIONS`" . " WHERE `TABLE_SCHEMA` = '" . PMA_Util::sqlAddSlashes($db) . "'" . " AND `TABLE_NAME` = '" . PMA_Util::sqlAddSlashes($table) . "'"); if (!empty($partition_method)) { return $partition_method[0]; } } return null; }
/** * returns collation of given db * * @param string $db name of db * * @return string collation of $db */ function PMA_getDbCollation($db) { if ($GLOBALS['dbi']->isSystemSchema($db)) { // We don't have to check the collation of the virtual // information_schema database: We know it! return 'utf8_general_ci'; } $sql = PMA_DRIZZLE ? 'SELECT DEFAULT_COLLATION_NAME FROM data_dictionary.SCHEMAS' . ' WHERE SCHEMA_NAME = \'' . PMA_Util::sqlAddSlashes($db) . '\' LIMIT 1' : 'SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA' . ' WHERE SCHEMA_NAME = \'' . PMA_Util::sqlAddSlashes($db) . '\' LIMIT 1'; return $GLOBALS['dbi']->fetchValue($sql); }
/** * Retrieve IDs and names of schema pages * * @param string $db database name * * @return array array of schema page id and names */ function PMA_getPageIdsAndNames($db) { $cfgRelation = PMA_getRelationsParam(); $page_query = "SELECT `page_nr`, `page_descr` FROM " . PMA_Util::backquote($cfgRelation['db']) . "." . PMA_Util::backquote($cfgRelation['pdf_pages']) . " WHERE db_name = '" . PMA_Util::sqlAddSlashes($db) . "'" . " ORDER BY `page_descr`"; $page_rs = PMA_queryAsControlUser($page_query, false, PMA_DatabaseInterface::QUERY_STORE); $result = array(); while ($curr_page = $GLOBALS['dbi']->fetchAssoc($page_rs)) { $result[$curr_page['page_nr']] = $curr_page['page_descr']; } return $result; }
/** * Returns the comment associated with node * This method should be overridden by specific type of nodes * * @return string */ public function getComment() { $db = PMA_Util::sqlAddSlashes($this->realParent()->real_name); $routine = PMA_Util::sqlAddSlashes($this->real_name); $query = "SELECT `ROUTINE_COMMENT` "; $query .= "FROM `INFORMATION_SCHEMA`.`ROUTINES` "; $query .= "WHERE `ROUTINE_SCHEMA`='{$db}' "; $query .= "AND `ROUTINE_NAME`='{$routine}' "; $query .= "AND `ROUTINE_TYPE`='FUNCTION' "; return PMA_DBI_fetch_value($query); }
/** * sqlAddslashes test * * @return void */ public function testAddSlashes() { $string = "\\'test''\\''\\'\r\t\n"; $this->assertEquals("\\\\\\\\\\'test\\'\\'\\\\\\\\\\'\\'\\\\\\\\\\'\\r\\t\\n", PMA_Util::sqlAddSlashes($string, true, true, true)); $this->assertEquals("\\\\\\\\''test''''\\\\\\\\''''\\\\\\\\''\\r\\t\\n", PMA_Util::sqlAddSlashes($string, true, true, false)); $this->assertEquals("\\\\\\\\\\'test\\'\\'\\\\\\\\\\'\\'\\\\\\\\\\'\r\t\n", PMA_Util::sqlAddSlashes($string, true, false, true)); $this->assertEquals("\\\\\\\\''test''''\\\\\\\\''''\\\\\\\\''\r\t\n", PMA_Util::sqlAddSlashes($string, true, false, false)); $this->assertEquals("\\\\\\'test\\'\\'\\\\\\'\\'\\\\\\'\\r\\t\\n", PMA_Util::sqlAddSlashes($string, false, true, true)); $this->assertEquals("\\\\''test''''\\\\''''\\\\''\\r\\t\\n", PMA_Util::sqlAddSlashes($string, false, true, false)); $this->assertEquals("\\\\\\'test\\'\\'\\\\\\'\\'\\\\\\'\r\t\n", PMA_Util::sqlAddSlashes($string, false, false, true)); $this->assertEquals("\\\\''test''''\\\\''''\\\\''\r\t\n", PMA_Util::sqlAddSlashes($string, false, false, false)); }
/** * Returns the comment associated with node * This method should be overridden by specific type of nodes * * @return string */ public function getComment() { $db = PMA_Util::sqlAddSlashes($this->realParent()->realParent()->real_name); $table = PMA_Util::sqlAddSlashes($this->realParent()->real_name); $column = PMA_Util::sqlAddSlashes($this->real_name); $query = "SELECT `COLUMN_COMMENT` "; $query .= "FROM `INFORMATION_SCHEMA`.`COLUMNS` "; $query .= "WHERE `TABLE_SCHEMA`='{$db}' "; $query .= "AND `TABLE_NAME`='{$table}' "; $query .= "AND `COLUMN_NAME`='{$column}' "; return PMA_DBI_fetch_value($query); }
/** * Get SQL query for store new transformation details of a VIEW * * @param object $pma_transformation_data Result set of SQL execution * @param array $column_map Details of VIEW columns * @param string $view_name Name of the VIEW * @param string $db Database name of the VIEW * * @return string $new_transformations_sql SQL query for new transformations */ function getNewTransformationDataSql($pma_transformation_data, $column_map, $view_name, $db) { $cfgRelation = \PMA_getRelationsParam(); // Need to store new transformation details for VIEW $new_transformations_sql = sprintf("INSERT INTO %s.%s (" . "`db_name`, `table_name`, `column_name`, " . "`comment`, `mimetype`, `transformation`, " . "`transformation_options`) VALUES", \PMA_Util::backquote($cfgRelation['db']), \PMA_Util::backquote($cfgRelation['column_info'])); $column_count = 0; $add_comma = false; while ($data_row = $this->dbi->fetchAssoc($pma_transformation_data)) { foreach ($column_map as $column) { if ($data_row['table_name'] != $column['table_name'] || $data_row['column_name'] != $column['refering_column']) { continue; } $new_transformations_sql .= sprintf("%s ('%s', '%s', '%s', '%s', '%s', '%s', '%s')", $add_comma ? ', ' : '', $db, $view_name, isset($column['real_column']) ? $column['real_column'] : $column['refering_column'], $data_row['comment'], $data_row['mimetype'], $data_row['transformation'], \PMA_Util::sqlAddSlashes($data_row['transformation_options'])); $add_comma = true; $column_count++; break; } if ($column_count == count($column_map)) { break; } } return $column_count > 0 ? $new_transformations_sql : ''; }
/** * Get Ajax return when $_REQUEST['type'] === 'setval' * * @param Array $variable_doc_links documentation links * * @return null */ function PMA_getAjaxReturnForSetVal($variable_doc_links) { $response = PMA_Response::getInstance(); $value = $_REQUEST['varValue']; $matches = array(); if (isset($variable_doc_links[$_REQUEST['varName']][3]) && $variable_doc_links[$_REQUEST['varName']][3] == 'byte' && preg_match('/^\\s*(\\d+(\\.\\d+)?)\\s*(mb|kb|mib|kib|gb|gib)\\s*$/i', $value, $matches)) { $exp = array('kb' => 1, 'kib' => 1, 'mb' => 2, 'mib' => 2, 'gb' => 3, 'gib' => 3); $value = floatval($matches[1]) * PMA_Util::pow(1024, $exp[mb_strtolower($matches[3])]); } else { $value = PMA_Util::sqlAddSlashes($value); } if (!is_numeric($value)) { $value = "'" . $value . "'"; } if (!preg_match("/[^a-zA-Z0-9_]+/", $_REQUEST['varName']) && $GLOBALS['dbi']->query('SET GLOBAL ' . $_REQUEST['varName'] . ' = ' . $value)) { // Some values are rounded down etc. $varValue = $GLOBALS['dbi']->fetchSingleRow('SHOW GLOBAL VARIABLES WHERE Variable_name="' . PMA_Util::sqlAddSlashes($_REQUEST['varName']) . '";', 'NUM'); $response->addJSON('variable', PMA_formatVariable($_REQUEST['varName'], $varValue[1], $variable_doc_links)); } else { $response->isSuccess(false); $response->addJSON('error', __('Setting variable failed')); } }
/** * Returns HTML for show hidden button displayed infront of database node * * @return String HTML for show hidden button */ public function getHtmlForControlButtons() { $ret = ''; $db = $this->real_name; $cfgRelation = PMA_getRelationsParam(); if ($cfgRelation['navwork']) { $navTable = PMA_Util::backquote($cfgRelation['db']) . "." . PMA_Util::backquote($cfgRelation['navigationhiding']); $sqlQuery = "SELECT COUNT(*) FROM " . $navTable . " WHERE `username`='" . PMA_Util::sqlAddSlashes($GLOBALS['cfg']['Server']['user']) . "'" . " AND `db_name`='" . PMA_Util::sqlAddSlashes($db) . "'"; $count = $GLOBALS['dbi']->fetchValue($sqlQuery, 0, 0, $GLOBALS['controllink']); if ($count > 0) { $ret = '<span class="dbItemControls">' . '<a href="navigation.php?' . PMA_URL_getCommon() . '&showUnhideDialog=true' . '&dbName=' . urldecode($db) . '"' . ' class="showUnhide ajax">' . PMA_Util::getImage('lightbulb.png', __('Show hidden items')) . '</a></span>'; } } return $ret; }
/** * Get data cell for non numeric type fields * * @param string $column the relevant column in data row * @param string $class the html class for column * @param object $meta the meta-information about * the field * @param array $map the list of relations * @param array $_url_params the parameters for generate * url * @param boolean $condition_field the column should highlighted * or not * @param object|string $transformation_plugin the name of transformation * function * @param string $default_function the default transformation * function * @param string $transform_options the transformation parameters * @param boolean $is_field_truncated is data truncated due to * LimitChars * @param array $analyzed_sql the analyzed query * @param integer &$dt_result the link id associated to * the query which results * have to be displayed * @param integer $col_index the column index * * @return string $cell the prepared data cell, html content * * @access private * * @see _getTableBody() */ private function _getDataCellForNonNumericColumns($column, $class, $meta, $map, $_url_params, $condition_field, $transformation_plugin, $default_function, $transform_options, $is_field_truncated, $analyzed_sql, &$dt_result, $col_index) { $is_analyse = $this->__get('is_analyse'); $field_flags = $GLOBALS['dbi']->fieldFlags($dt_result, $col_index); $bIsText = gettype($transformation_plugin) === 'object' && strpos($transformation_plugin->getMIMEtype(), 'Text') === false; // disable inline grid editing // if binary fields are protected // or transformation plugin is of non text type // such as image if (stristr($field_flags, self::BINARY_FIELD) && ($GLOBALS['cfg']['ProtectBinary'] === 'all' || $GLOBALS['cfg']['ProtectBinary'] === 'noblob' && !stristr($meta->type, self::BLOB_FIELD) || $GLOBALS['cfg']['ProtectBinary'] === 'blob' && stristr($meta->type, self::BLOB_FIELD)) || $bIsText) { $class = str_replace('grid_edit', '', $class); } if (!isset($column) || is_null($column)) { $cell = $this->_buildNullDisplay($class, $condition_field, $meta); return $cell; } if ($column == '') { $cell = $this->_buildEmptyDisplay($class, $condition_field, $meta); return $cell; } // Cut all fields to $GLOBALS['cfg']['LimitChars'] // (unless it's a link-type transformation or binary) if (!(gettype($transformation_plugin) === "object" && strpos($transformation_plugin->getName(), 'Link') !== false) && !stristr($field_flags, self::BINARY_FIELD)) { $is_field_truncated = $this->_getPartialText($column); } $formatted = false; if (isset($meta->_type) && $meta->_type === MYSQLI_TYPE_BIT) { $column = PMA_Util::printableBitValue($column, $meta->length); // some results of PROCEDURE ANALYSE() are reported as // being BINARY but they are quite readable, // so don't treat them as BINARY } elseif (stristr($field_flags, self::BINARY_FIELD) && !(isset($is_analyse) && $is_analyse)) { // we show the BINARY or BLOB message and field's size // (or maybe use a transformation) $binary_or_blob = self::BLOB_FIELD; if ($meta->type === self::STRING_FIELD) { $binary_or_blob = self::BINARY_FIELD; } $column = $this->_handleNonPrintableContents($binary_or_blob, $column, $transformation_plugin, $transform_options, $default_function, $meta, $_url_params, $is_field_truncated); $class = $this->_addClass($class, $condition_field, $meta, '', $is_field_truncated, $transformation_plugin, $default_function); $result = strip_tags($column); // disable inline grid editing // if binary or blob data is not shown if (stristr($result, $binary_or_blob)) { $class = str_replace('grid_edit', '', $class); } $formatted = true; } if ($formatted) { $cell = $this->_buildValueDisplay($class, $condition_field, $column); return $cell; } // transform functions may enable no-wrapping: $function_nowrap = 'applyTransformationNoWrap'; $bool_nowrap = $default_function != $transformation_plugin && function_exists($transformation_plugin->{$function_nowrap}()) ? $transformation_plugin->{$function_nowrap}($transform_options) : false; // do not wrap if date field type $nowrap = preg_match('@DATE|TIME@i', $meta->type) || $bool_nowrap ? ' nowrap' : ''; $where_comparison = ' = \'' . PMA_Util::sqlAddSlashes($column) . '\''; $cell = $this->_getRowData($class, $condition_field, $analyzed_sql, $meta, $map, $column, $transformation_plugin, $default_function, $nowrap, $where_comparison, $transform_options, $is_field_truncated); return $cell; }
if (isset($show_as_php)) { $url_params['show_as_php'] = $show_as_php; } PMA_sendHeaderLocation($cfg['PmaAbsoluteUri'] . 'index.php' . PMA_URL_getCommon($url_params, 'text')); } exit; } } } // end if (ensures db exists) if (empty($is_table) && !defined('PMA_SUBMIT_MULT') && !defined('TABLE_MAY_BE_ABSENT')) { // Not a valid table name -> back to the db_sql.php if (mb_strlen($table)) { $is_table = $GLOBALS['dbi']->getCachedTableContent("{$db}.{$table}", false); if (!$is_table) { $_result = $GLOBALS['dbi']->tryQuery('SHOW TABLES LIKE \'' . PMA_Util::sqlAddSlashes($table, true) . '\';', null, PMA_DatabaseInterface::QUERY_STORE); $is_table = @$GLOBALS['dbi']->numRows($_result); $GLOBALS['dbi']->freeResult($_result); } } else { $is_table = false; } if (!$is_table) { if (!defined('IS_TRANSFORMATION_WRAPPER')) { if (mb_strlen($table)) { // SHOW TABLES doesn't show temporary tables, so try select // (as it can happen just in case temporary table, it should be // fast): /** * @todo should this check really * only happen if IS_TRANSFORMATION_WRAPPER?
/** * Provides where clause for building SQL query * * @param string $table The table name * * @return string The generated where clause */ private function _getWhereClause($table) { // Columns to select $allColumns = $GLOBALS['dbi']->getColumns($GLOBALS['db'], $table); $likeClauses = array(); // Based on search type, decide like/regex & '%'/'' $like_or_regex = $this->_criteriaSearchType == 4 ? 'REGEXP' : 'LIKE'; $automatic_wildcard = $this->_criteriaSearchType < 3 ? '%' : ''; // For "as regular expression" (search option 4), LIKE won't be used // Usage example: If user is searching for a literal $ in a regexp search, // he should enter \$ as the value. $this->_criteriaSearchString = PMA_Util::sqlAddSlashes($this->_criteriaSearchString, $this->_criteriaSearchType == 4 ? false : true); // Extract search words or pattern $search_words = $this->_criteriaSearchType > 2 ? array($this->_criteriaSearchString) : explode(' ', $this->_criteriaSearchString); /** @var PMA_String $pmaString */ $pmaString = $GLOBALS['PMA_String']; foreach ($search_words as $search_word) { // Eliminates empty values if ($pmaString->strlen($search_word) === 0) { continue; } $likeClausesPerColumn = array(); // for each column in the table foreach ($allColumns as $column) { if (!isset($this->_criteriaColumnName) || $pmaString->strlen($this->_criteriaColumnName) == 0 || $column['Field'] == $this->_criteriaColumnName) { // Drizzle has no CONVERT and all text columns are UTF-8 $column = PMA_DRIZZLE ? PMA_Util::backquote($column['Field']) : 'CONVERT(' . PMA_Util::backquote($column['Field']) . ' USING utf8)'; $likeClausesPerColumn[] = $column . ' ' . $like_or_regex . ' ' . "'" . $automatic_wildcard . $search_word . $automatic_wildcard . "'"; } } // end for if (count($likeClausesPerColumn) > 0) { $likeClauses[] = implode(' OR ', $likeClausesPerColumn); } } // end for // Use 'OR' if 'at least one word' is to be searched, else use 'AND' $implode_str = $this->_criteriaSearchType == 1 ? ' OR ' : ' AND '; if (empty($likeClauses)) { // this could happen when the "inside column" does not exist // in any selected tables $where_clause = ' WHERE FALSE'; } else { $where_clause = ' WHERE (' . implode(') ' . $implode_str . ' (', $likeClauses) . ')'; } return $where_clause; }
/** * Returns the names of children of type $type present inside this container * This method is overridden by the Node_Database and Node_Table classes * * @param string $type The type of item we are looking for * ('tables', 'views', etc) * @param int $pos The offset of the list within the results * @param string $searchClause A string used to filter the results of the query * * @return array */ public function getData($type, $pos, $searchClause = '') { $maxItems = $GLOBALS['cfg']['MaxNavigationItems']; $retval = array(); $db = $this->realParent()->real_name; $table = $this->real_name; switch ($type) { case 'columns': if (!$GLOBALS['cfg']['Server']['DisableIS']) { $db = PMA_Util::sqlAddSlashes($db); $table = PMA_Util::sqlAddSlashes($table); $query = "SELECT `COLUMN_NAME` AS `name` "; $query .= "FROM `INFORMATION_SCHEMA`.`COLUMNS` "; $query .= "WHERE `TABLE_NAME`='{$table}' "; $query .= "AND `TABLE_SCHEMA`='{$db}' "; $query .= "ORDER BY `COLUMN_NAME` ASC "; $query .= "LIMIT " . intval($pos) . ", {$maxItems}"; $retval = $GLOBALS['dbi']->fetchResult($query); break; } $db = PMA_Util::backquote($db); $table = PMA_Util::backquote($table); $query = "SHOW COLUMNS FROM {$table} FROM {$db}"; $handle = $GLOBALS['dbi']->tryQuery($query); if ($handle === false) { break; } $count = 0; if ($GLOBALS['dbi']->dataSeek($handle, $pos)) { while ($arr = $GLOBALS['dbi']->fetchArray($handle)) { if ($count < $maxItems) { $retval[] = $arr['Field']; $count++; } else { break; } } } break; case 'indexes': $db = PMA_Util::backquote($db); $table = PMA_Util::backquote($table); $query = "SHOW INDEXES FROM {$table} FROM {$db}"; $handle = $GLOBALS['dbi']->tryQuery($query); if ($handle === false) { break; } $count = 0; while ($arr = $GLOBALS['dbi']->fetchArray($handle)) { if (in_array($arr['Key_name'], $retval)) { continue; } if ($pos <= 0 && $count < $maxItems) { $retval[] = $arr['Key_name']; $count++; } $pos--; } break; case 'triggers': if (!$GLOBALS['cfg']['Server']['DisableIS']) { $db = PMA_Util::sqlAddSlashes($db); $table = PMA_Util::sqlAddSlashes($table); $query = "SELECT `TRIGGER_NAME` AS `name` "; $query .= "FROM `INFORMATION_SCHEMA`.`TRIGGERS` "; $query .= "WHERE `EVENT_OBJECT_SCHEMA` " . PMA_Util::getCollateForIS() . "='{$db}' "; $query .= "AND `EVENT_OBJECT_TABLE` " . PMA_Util::getCollateForIS() . "='{$table}' "; $query .= "ORDER BY `TRIGGER_NAME` ASC "; $query .= "LIMIT " . intval($pos) . ", {$maxItems}"; $retval = $GLOBALS['dbi']->fetchResult($query); break; } $db = PMA_Util::backquote($db); $table = PMA_Util::sqlAddSlashes($table); $query = "SHOW TRIGGERS FROM {$db} WHERE `Table` = '{$table}'"; $handle = $GLOBALS['dbi']->tryQuery($query); if ($handle === false) { break; } $count = 0; if ($GLOBALS['dbi']->dataSeek($handle, $pos)) { while ($arr = $GLOBALS['dbi']->fetchArray($handle)) { if ($count < $maxItems) { $retval[] = $arr['Trigger']; $count++; } else { break; } } } break; default: break; } return $retval; }
/** * Get table alters array * * @param boolean $is_myisam_or_aria whether MYISAM | ARIA or not * @param boolean $is_isam whether ISAM or not * @param string $pack_keys pack keys * @param string $checksum value of checksum * @param boolean $is_aria whether ARIA or not * @param string $page_checksum value of page checksum * @param string $delay_key_write delay key write * @param boolean $is_innodb whether INNODB or not * @param boolean $is_pbxt whether PBXT or not * @param string $row_format row format * @param string $newTblStorageEngine table storage engine * @param string $transactional value of transactional * @param string $tbl_collation collation of the table * * @return array $table_alters */ function PMA_getTableAltersArray($is_myisam_or_aria, $is_isam, $pack_keys, $checksum, $is_aria, $page_checksum, $delay_key_write, $is_innodb, $is_pbxt, $row_format, $newTblStorageEngine, $transactional, $tbl_collation) { global $auto_increment; $table_alters = array(); if (isset($_REQUEST['comment']) && urldecode($_REQUEST['prev_comment']) !== $_REQUEST['comment']) { $table_alters[] = 'COMMENT = \'' . PMA_Util::sqlAddSlashes($_REQUEST['comment']) . '\''; } if (!empty($newTblStorageEngine) && mb_strtolower($newTblStorageEngine) !== mb_strtolower($GLOBALS['tbl_storage_engine'])) { $table_alters[] = 'ENGINE = ' . $newTblStorageEngine; } if (!empty($_REQUEST['tbl_collation']) && $_REQUEST['tbl_collation'] !== $tbl_collation) { $table_alters[] = 'DEFAULT ' . PMA_generateCharsetQueryPart($_REQUEST['tbl_collation']); } if (($is_myisam_or_aria || $is_isam) && isset($_REQUEST['new_pack_keys']) && $_REQUEST['new_pack_keys'] != (string) $pack_keys) { $table_alters[] = 'pack_keys = ' . $_REQUEST['new_pack_keys']; } $_REQUEST['new_checksum'] = empty($_REQUEST['new_checksum']) ? '0' : '1'; if ($is_myisam_or_aria && $_REQUEST['new_checksum'] !== $checksum) { $table_alters[] = 'checksum = ' . $_REQUEST['new_checksum']; } $_REQUEST['new_transactional'] = empty($_REQUEST['new_transactional']) ? '0' : '1'; if ($is_aria && $_REQUEST['new_transactional'] !== $transactional) { $table_alters[] = 'TRANSACTIONAL = ' . $_REQUEST['new_transactional']; } $_REQUEST['new_page_checksum'] = empty($_REQUEST['new_page_checksum']) ? '0' : '1'; if ($is_aria && $_REQUEST['new_page_checksum'] !== $page_checksum) { $table_alters[] = 'PAGE_CHECKSUM = ' . $_REQUEST['new_page_checksum']; } $_REQUEST['new_delay_key_write'] = empty($_REQUEST['new_delay_key_write']) ? '0' : '1'; if ($is_myisam_or_aria && $_REQUEST['new_delay_key_write'] !== $delay_key_write) { $table_alters[] = 'delay_key_write = ' . $_REQUEST['new_delay_key_write']; } if (($is_myisam_or_aria || $is_innodb || $is_pbxt) && !empty($_REQUEST['new_auto_increment']) && (!isset($auto_increment) || $_REQUEST['new_auto_increment'] !== $auto_increment)) { $table_alters[] = 'auto_increment = ' . PMA_Util::sqlAddSlashes($_REQUEST['new_auto_increment']); } if (!empty($_REQUEST['new_row_format'])) { $newRowFormat = $_REQUEST['new_row_format']; $newRowFormatLower = mb_strtolower($newRowFormat); if (($is_myisam_or_aria || $is_innodb || $is_pbxt) && (!mb_strlen($row_format) || $newRowFormatLower !== mb_strtolower($row_format))) { $table_alters[] = 'ROW_FORMAT = ' . PMA_Util::sqlAddSlashes($newRowFormat); } } return $table_alters; }
/** * Save recent/favorite tables into phpMyAdmin database. * * @return true|PMA_Message */ public function saveToDb() { $username = $GLOBALS['cfg']['Server']['user']; $sql_query = " REPLACE INTO " . $this->_pmaTable . " (`username`, `tables`)" . " VALUES ('" . $username . "', '" . PMA_Util::sqlAddSlashes(json_encode($this->_tables)) . "')"; $success = $GLOBALS['dbi']->tryQuery($sql_query, $GLOBALS['controllink']); if (!$success) { $error_msg = ''; switch ($this->_tableType) { case 'recent': $error_msg = __('Could not save recent table!'); break; case 'favorite': $error_msg = __('Could not save favorite table!'); break; } $message = PMA_Message::error($error_msg); $message->addMessage('<br /><br />'); $message->addMessage(PMA_Message::rawError($GLOBALS['dbi']->getError($GLOBALS['controllink']))); return $message; } return true; }
/** * Function to get update query for updating internal relations * * @param string $multi_edit_columns_name multi edit column names * @param string $master_field_md5 master field md5 * @param string $foreign_db foreign database * @param string $destination_table destination table * @param string $destination_column destination column * @param array $cfgRelation configuration relation * @param string $db current database * @param string $table current table * @param array $existrel db, table, column * * @return string */ function PMA_getQueryForInternalRelationUpdate($multi_edit_columns_name, $master_field_md5, $foreign_db, $destination_table, $destination_column, $cfgRelation, $db, $table, $existrel) { $upd_query = false; // Map the fieldname's md5 back to its real name $master_field = $multi_edit_columns_name[$master_field_md5]; $foreign_table = $destination_table[$master_field_md5]; $foreign_field = $destination_column[$master_field_md5]; if (!empty($foreign_db) && !empty($foreign_table) && !empty($foreign_field)) { if (!isset($existrel[$master_field])) { $upd_query = 'INSERT INTO ' . PMA_Util::backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_Util::backquote($cfgRelation['relation']) . '(master_db, master_table, master_field, foreign_db,' . ' foreign_table, foreign_field)' . ' values(' . '\'' . PMA_Util::sqlAddSlashes($db) . '\', ' . '\'' . PMA_Util::sqlAddSlashes($table) . '\', ' . '\'' . PMA_Util::sqlAddSlashes($master_field) . '\', ' . '\'' . PMA_Util::sqlAddSlashes($foreign_db) . '\', ' . '\'' . PMA_Util::sqlAddSlashes($foreign_table) . '\',' . '\'' . PMA_Util::sqlAddSlashes($foreign_field) . '\')'; } elseif ($existrel[$master_field]['foreign_db'] != $foreign_db || $existrel[$master_field]['foreign_table'] != $foreign_table || $existrel[$master_field]['foreign_field'] != $foreign_field) { $upd_query = 'UPDATE ' . PMA_Util::backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_Util::backquote($cfgRelation['relation']) . ' SET' . ' foreign_db = \'' . PMA_Util::sqlAddSlashes($foreign_db) . '\', ' . ' foreign_table = \'' . PMA_Util::sqlAddSlashes($foreign_table) . '\', ' . ' foreign_field = \'' . PMA_Util::sqlAddSlashes($foreign_field) . '\' ' . ' WHERE master_db = \'' . PMA_Util::sqlAddSlashes($db) . '\'' . ' AND master_table = \'' . PMA_Util::sqlAddSlashes($table) . '\'' . ' AND master_field = \'' . PMA_Util::sqlAddSlashes($master_field) . '\''; } // end if... else.... } elseif (isset($existrel[$master_field])) { $upd_query = 'DELETE FROM ' . PMA_Util::backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_Util::backquote($cfgRelation['relation']) . ' WHERE master_db = \'' . PMA_Util::sqlAddSlashes($db) . '\'' . ' AND master_table = \'' . PMA_Util::sqlAddSlashes($table) . '\'' . ' AND master_field = \'' . PMA_Util::sqlAddSlashes($master_field) . '\''; } // end if... else.... return $upd_query; }
/** * Add/update a user group with allowed menu tabs. * * @param string $userGroup user group name * @param boolean $new whether this is a new user group * * @return void */ function PMA_editUserGroup($userGroup, $new = false) { $tabs = PMA_Util::getMenuTabList(); $groupTable = PMA_Util::backquote($GLOBALS['cfg']['Server']['pmadb']) . "." . PMA_Util::backquote($GLOBALS['cfg']['Server']['usergroups']); if (!$new) { $sql_query = "DELETE FROM " . $groupTable . " WHERE `usergroup`='" . PMA_Util::sqlAddSlashes($userGroup) . "';"; PMA_queryAsControlUser($sql_query, true); } $sql_query = "INSERT INTO " . $groupTable . "(`usergroup`, `tab`, `allowed`)" . " VALUES "; $first = true; foreach ($tabs as $tabGroupName => $tabGroup) { foreach ($tabs[$tabGroupName] as $tab => $tabName) { if (!$first) { $sql_query .= ", "; } $tabName = $tabGroupName . '_' . $tab; $allowed = isset($_REQUEST[$tabName]) && $_REQUEST[$tabName] == 'Y'; $sql_query .= "('" . $userGroup . "', '" . $tabName . "', '" . ($allowed ? "Y" : "N") . "')"; $first = false; } } $sql_query .= ";"; PMA_queryAsControlUser($sql_query, true); }
/** * Handles requests for executing a routine * * @return Does not return */ function PMA_RTN_handleExecute() { global $_GET, $_POST, $_REQUEST, $GLOBALS, $db; /** * Handle all user requests other than the default of listing routines */ if (!empty($_REQUEST['execute_routine']) && !empty($_REQUEST['item_name'])) { // Build the queries $routine = PMA_RTN_getDataFromName($_REQUEST['item_name'], $_REQUEST['item_type'], false); if ($routine !== false) { $queries = array(); $end_query = array(); $args = array(); $all_functions = $GLOBALS['PMA_Types']->getAllFunctions(); for ($i = 0; $i < $routine['item_num_params']; $i++) { if (isset($_REQUEST['params'][$routine['item_param_name'][$i]])) { $value = $_REQUEST['params'][$routine['item_param_name'][$i]]; if (is_array($value)) { // is SET type $value = implode(',', $value); } $value = PMA_Util::sqlAddSlashes($value); if (!empty($_REQUEST['funcs'][$routine['item_param_name'][$i]]) && in_array($_REQUEST['funcs'][$routine['item_param_name'][$i]], $all_functions)) { $queries[] = "SET @p{$i}={$_REQUEST['funcs'][$routine['item_param_name'][$i]]}('{$value}');\n"; } else { $queries[] = "SET @p{$i}='{$value}';\n"; } $args[] = "@p{$i}"; } else { $args[] = "@p{$i}"; } if ($routine['item_type'] == 'PROCEDURE') { if ($routine['item_param_dir'][$i] == 'OUT' || $routine['item_param_dir'][$i] == 'INOUT') { $end_query[] = "@p{$i} AS " . PMA_Util::backquote($routine['item_param_name'][$i]); } } } if ($routine['item_type'] == 'PROCEDURE') { $queries[] = "CALL " . PMA_Util::backquote($routine['item_name']) . "(" . implode(', ', $args) . ");\n"; if (count($end_query)) { $queries[] = "SELECT " . implode(', ', $end_query) . ";\n"; } } else { $queries[] = "SELECT " . PMA_Util::backquote($routine['item_name']) . "(" . implode(', ', $args) . ") " . "AS " . PMA_Util::backquote($routine['item_name']) . ";\n"; } // Get all the queries as one SQL statement $multiple_query = implode("", $queries); $outcome = true; $affected = 0; // Execute query if (!PMA_DBI_try_multi_query($multiple_query)) { $outcome = false; } // Generate output if ($outcome) { // Pass the SQL queries through the "pretty printer" $output = '<code class="sql" style="margin-bottom: 1em;">'; $output .= PMA_SQP_formatHtml(PMA_SQP_parse(implode($queries))); $output .= '</code>'; // Display results $output .= "<fieldset><legend>"; $output .= sprintf(__('Execution results of routine %s'), PMA_Util::backquote(htmlspecialchars($routine['item_name']))); $output .= "</legend>"; $num_of_rusults_set_to_display = 0; do { $result = PMA_DBI_store_result(); $num_rows = PMA_DBI_num_rows($result); if ($result !== false && $num_rows > 0) { $output .= "<table><tr>"; foreach (PMA_DBI_get_fields_meta($result) as $key => $field) { $output .= "<th>"; $output .= htmlspecialchars($field->name); $output .= "</th>"; } $output .= "</tr>"; $color_class = 'odd'; while ($row = PMA_DBI_fetch_assoc($result)) { $output .= "<tr>"; foreach ($row as $key => $value) { if ($value === null) { $value = '<i>NULL</i>'; } else { $value = htmlspecialchars($value); } $output .= "<td class='" . $color_class . "'>" . $value . "</td>"; } $output .= "</tr>"; $color_class = $color_class == 'odd' ? 'even' : 'odd'; } $output .= "</table>"; $num_of_rusults_set_to_display++; $affected = $num_rows; } if (!PMA_DBI_more_results()) { break; } $output .= "<br/>"; PMA_DBI_free_result($result); } while (PMA_DBI_next_result()); $output .= "</fieldset>"; $message = __('Your SQL query has been executed successfully'); if ($routine['item_type'] == 'PROCEDURE') { $message .= '<br />'; // TODO : message need to be modified according to the // output from the routine $message .= sprintf(_ngettext('%d row affected by the last statement inside the procedure', '%d rows affected by the last statement inside the procedure', $affected), $affected); } $message = PMA_message::success($message); if ($num_of_rusults_set_to_display == 0) { $notice = __('MySQL returned an empty result set (i.e. zero rows).'); $output .= PMA_message::notice($notice)->getDisplay(); } } else { $output = ''; $message = PMA_message::error(sprintf(__('The following query has failed: "%s"'), htmlspecialchars($query)) . '<br /><br />' . __('MySQL said: ') . PMA_DBI_getError(null)); } // Print/send output if ($GLOBALS['is_ajax_request']) { $response = PMA_Response::getInstance(); $response->isSuccess($message->isSuccess()); $response->addJSON('message', $message->getDisplay() . $output); $response->addJSON('dialog', false); exit; } else { echo $message->getDisplay() . $output; if ($message->isError()) { // At least one query has failed, so shouldn't // execute any more queries, so we quit. exit; } unset($_POST); // Now deliberately fall through to displaying the routines list } } else { $message = __('Error in processing request') . ' : '; $message .= sprintf(PMA_RTE_getWord('not_found'), htmlspecialchars(PMA_Util::backquote($_REQUEST['item_name'])), htmlspecialchars(PMA_Util::backquote($db))); $message = PMA_message::error($message); if ($GLOBALS['is_ajax_request']) { $response = PMA_Response::getInstance(); $response->isSuccess(false); $response->addJSON('message', $message); exit; } else { echo $message->getDisplay(); unset($_POST); } } } else { if (!empty($_GET['execute_dialog']) && !empty($_GET['item_name'])) { /** * Display the execute form for a routine. */ $routine = PMA_RTN_getDataFromName($_GET['item_name'], $_GET['item_type'], true); if ($routine !== false) { $form = PMA_RTN_getExecuteForm($routine); if ($GLOBALS['is_ajax_request'] == true) { $title = __("Execute routine") . " " . PMA_Util::backquote(htmlentities($_GET['item_name'], ENT_QUOTES)); $response = PMA_Response::getInstance(); $response->addJSON('message', $form); $response->addJSON('title', $title); $response->addJSON('dialog', true); } else { echo "\n\n<h2>" . __("Execute routine") . "</h2>\n\n"; echo $form; } exit; } else { if ($GLOBALS['is_ajax_request'] == true) { $message = __('Error in processing request') . ' : '; $message .= sprintf(PMA_RTE_getWord('not_found'), htmlspecialchars(PMA_Util::backquote($_REQUEST['item_name'])), htmlspecialchars(PMA_Util::backquote($db))); $message = PMA_message::error($message); $response = PMA_Response::getInstance(); $response->isSuccess(false); $response->addJSON('message', $message); exit; } } } } }
/** * Get child table references for a table column. * This works only if 'DisableIS' is false. An empty array is returned otherwise. * * @param string $db name of master table db. * @param string $table name of master table. * @param string $column name of master table column. * * @return array $child_references */ function PMA_getChildReferences($db, $table, $column = '') { $child_references = array(); if (!$GLOBALS['cfg']['Server']['DisableIS']) { $rel_query = "SELECT `column_name`, `table_name`," . " `table_schema`, `referenced_column_name`" . " FROM `information_schema`.`key_column_usage`" . " WHERE `referenced_table_name` = '" . PMA_Util::sqlAddSlashes($table) . "'" . " AND `referenced_table_schema` = '" . PMA_Util::sqlAddSlashes($db) . "'"; if ($column) { $rel_query .= " AND `referenced_column_name` = '" . PMA_Util::sqlAddSlashes($column) . "'"; } $child_references = $GLOBALS['dbi']->fetchResult($rel_query, array('referenced_column_name', null)); } return $child_references; }
/** * Return the where clause for query generation based on the inputs provided. * * @param mixed $criteriaValues Search criteria input * @param string $names Name of the column on which search is submitted * @param string $types Type of the field * @param string $collations Field collation * @param string $func_type Search function/operator * @param bool $unaryFlag Whether operator unary or not * @param bool $geom_func Whether geometry functions should be applied * * @return string generated where clause. */ private function _getWhereClause($criteriaValues, $names, $types, $collations, $func_type, $unaryFlag, $geom_func = null) { // If geometry function is set if ($geom_func != null && trim($geom_func) != '') { return $this->_getGeomWhereClause($criteriaValues, $names, $func_type, $types, $geom_func); } $backquoted_name = PMA_Util::backquote($names); $where = ''; if ($unaryFlag) { $where = $backquoted_name . ' ' . $func_type; } elseif (strncasecmp($types, 'enum', 4) == 0 && !empty($criteriaValues)) { $where = $backquoted_name; $where .= $this->_getEnumWhereClause($criteriaValues, $func_type); } elseif ($criteriaValues != '') { // For these types we quote the value. Even if it's another type // (like INT), for a LIKE we always quote the value. MySQL converts // strings to numbers and numbers to strings as necessary // during the comparison if (preg_match('@char|binary|blob|text|set|date|time|year@i', $types) || strpos(' ' . $func_type, 'LIKE')) { $quot = '\''; } else { $quot = ''; } // LIKE %...% if ($func_type == 'LIKE %...%') { $func_type = 'LIKE'; $criteriaValues = '%' . $criteriaValues . '%'; } if ($func_type == 'REGEXP ^...$') { $func_type = 'REGEXP'; $criteriaValues = '^' . $criteriaValues . '$'; } if ('IN (...)' != $func_type && 'NOT IN (...)' != $func_type && 'BETWEEN' != $func_type && 'NOT BETWEEN' != $func_type) { if ($func_type == 'LIKE %...%' || $func_type == 'LIKE') { $where = $backquoted_name . ' ' . $func_type . ' ' . $quot . PMA_Util::sqlAddSlashes($criteriaValues, true) . $quot; } else { $where = $backquoted_name . ' ' . $func_type . ' ' . $quot . PMA_Util::sqlAddSlashes($criteriaValues) . $quot; } return $where; } $func_type = str_replace(' (...)', '', $func_type); //Don't explode if this is already an array //(Case for (NOT) IN/BETWEEN.) if (is_array($criteriaValues)) { $values = $criteriaValues; } else { $values = explode(',', $criteriaValues); } // quote values one by one $emptyKey = false; foreach ($values as $key => &$value) { if ('' === $value) { $emptyKey = $key; $value = 'NULL'; continue; } $value = $quot . PMA_Util::sqlAddSlashes(trim($value)) . $quot; } if ('BETWEEN' == $func_type || 'NOT BETWEEN' == $func_type) { $where = $backquoted_name . ' ' . $func_type . ' ' . (isset($values[0]) ? $values[0] : '') . ' AND ' . (isset($values[1]) ? $values[1] : ''); } else { //[NOT] IN if (false !== $emptyKey) { unset($values[$emptyKey]); } $wheres = array(); if (!empty($values)) { $wheres[] = $backquoted_name . ' ' . $func_type . ' (' . implode(',', $values) . ')'; } if (false !== $emptyKey) { $wheres[] = $backquoted_name . ' IS NULL'; } $where = implode(' OR ', $wheres); if (1 < count($wheres)) { $where = '(' . $where . ')'; } } } // end if return $where; }
/** * get all tables involved or included in page * * @param string $db name of the database * @param integer $pageNumber page no. whose tables will be fetched in an array * * @return Array an array of tables * * @access public */ public function getAllTables($db, $pageNumber) { global $cfgRelation; // Get All tables $tab_sql = 'SELECT table_name FROM ' . PMA_Util::backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_Util::backquote($cfgRelation['table_coords']) . ' WHERE db_name = \'' . PMA_Util::sqlAddSlashes($db) . '\'' . ' AND pdf_page_number = ' . $pageNumber; $tab_rs = PMA_queryAsControlUser($tab_sql, null, PMA_DBI_QUERY_STORE); if (!$tab_rs || !PMA_DBI_num_rows($tab_rs) > 0) { $this->dieSchema('', __('This page does not contain any tables!')); } while ($curr_table = @PMA_DBI_fetch_assoc($tab_rs)) { $alltables[] = PMA_Util::sqlAddSlashes($curr_table['table_name']); } return $alltables; }
/** * Outputs the content of a table in SQL format * * @param string $db database name * @param string $table table name * @param string $crlf the end of line sequence * @param string $error_url the url to go back in case of error * @param string $sql_query SQL query for obtaining data * * @return bool Whether it succeeded */ public function exportData($db, $table, $crlf, $error_url, $sql_query) { global $current_row, $sql_backquotes; if (isset($GLOBALS['sql_compatibility'])) { $compat = $GLOBALS['sql_compatibility']; } else { $compat = 'NONE'; } $formatted_table_name = isset($GLOBALS['sql_backquotes']) ? PMA_Util::backquoteCompat($table, $compat) : '\'' . $table . '\''; // Do not export data for a VIEW // (For a VIEW, this is called only when exporting a single VIEW) if (PMA_Table::isView($db, $table)) { $head = $this->_possibleCRLF() . $this->_exportComment() . $this->_exportComment('VIEW ' . ' ' . $formatted_table_name) . $this->_exportComment(__('Data') . ': ' . __('None')) . $this->_exportComment() . $this->_possibleCRLF(); if (!PMA_exportOutputHandler($head)) { return false; } return true; } // analyze the query to get the true column names, not the aliases // (this fixes an undefined index, also if Complete inserts // are used, we did not get the true column name in case of aliases) $analyzed_sql = PMA_SQP_analyze(PMA_SQP_parse($sql_query)); $result = PMA_DBI_try_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED); // a possible error: the table has crashed $tmp_error = PMA_DBI_getError(); if ($tmp_error) { return PMA_exportOutputHandler($this->_exportComment(__('Error reading data:') . ' (' . $tmp_error . ')')); } if ($result != false) { $fields_cnt = PMA_DBI_num_fields($result); // Get field information $fields_meta = PMA_DBI_get_fields_meta($result); $field_flags = array(); for ($j = 0; $j < $fields_cnt; $j++) { $field_flags[$j] = PMA_DBI_field_flags($result, $j); } for ($j = 0; $j < $fields_cnt; $j++) { if (isset($analyzed_sql[0]['select_expr'][$j]['column'])) { $field_set[$j] = PMA_Util::backquoteCompat($analyzed_sql[0]['select_expr'][$j]['column'], $compat, $sql_backquotes); } else { $field_set[$j] = PMA_Util::backquoteCompat($fields_meta[$j]->name, $compat, $sql_backquotes); } } if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'UPDATE') { // update $schema_insert = 'UPDATE '; if (isset($GLOBALS['sql_ignore'])) { $schema_insert .= 'IGNORE '; } // avoid EOL blank $schema_insert .= PMA_Util::backquoteCompat($table, $compat, $sql_backquotes) . ' SET'; } else { // insert or replace if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'REPLACE') { $sql_command = 'REPLACE'; } else { $sql_command = 'INSERT'; } // delayed inserts? if (isset($GLOBALS['sql_delayed'])) { $insert_delayed = ' DELAYED'; } else { $insert_delayed = ''; } // insert ignore? if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'INSERT' && isset($GLOBALS['sql_ignore'])) { $insert_delayed .= ' IGNORE'; } //truncate table before insert if (isset($GLOBALS['sql_truncate']) && $GLOBALS['sql_truncate'] && $sql_command == 'INSERT') { $truncate = 'TRUNCATE TABLE ' . PMA_Util::backquoteCompat($table, $compat, $sql_backquotes) . ";"; $truncatehead = $this->_possibleCRLF() . $this->_exportComment() . $this->_exportComment(__('Truncate table before insert') . ' ' . $formatted_table_name) . $this->_exportComment() . $crlf; PMA_exportOutputHandler($truncatehead); PMA_exportOutputHandler($truncate); } else { $truncate = ''; } // scheme for inserting fields if ($GLOBALS['sql_insert_syntax'] == 'complete' || $GLOBALS['sql_insert_syntax'] == 'both') { $fields = implode(', ', $field_set); $schema_insert = $sql_command . $insert_delayed . ' INTO ' . PMA_Util::backquoteCompat($table, $compat, $sql_backquotes) . ' (' . $fields . ') VALUES'; } else { $schema_insert = $sql_command . $insert_delayed . ' INTO ' . PMA_Util::backquoteCompat($table, $compat, $sql_backquotes) . ' VALUES'; } } //\x08\\x09, not required $search = array("", "\n", "\r", ""); $replace = array('\\0', '\\n', '\\r', '\\Z'); $current_row = 0; $query_size = 0; if (($GLOBALS['sql_insert_syntax'] == 'extended' || $GLOBALS['sql_insert_syntax'] == 'both') && (!isset($GLOBALS['sql_type']) || $GLOBALS['sql_type'] != 'UPDATE')) { $separator = ','; $schema_insert .= $crlf; } else { $separator = ';'; } while ($row = PMA_DBI_fetch_row($result)) { if ($current_row == 0) { $head = $this->_possibleCRLF() . $this->_exportComment() . $this->_exportComment(__('Dumping data for table') . ' ' . $formatted_table_name) . $this->_exportComment() . $crlf; if (!PMA_exportOutputHandler($head)) { return false; } } // We need to SET IDENTITY_INSERT ON for MSSQL if (isset($GLOBALS['sql_compatibility']) && $GLOBALS['sql_compatibility'] == 'MSSQL' && $current_row == 0) { if (!PMA_exportOutputHandler('SET IDENTITY_INSERT ' . PMA_Util::backquoteCompat($table, $compat) . ' ON ;' . $crlf)) { return false; } } $current_row++; for ($j = 0; $j < $fields_cnt; $j++) { // NULL if (!isset($row[$j]) || is_null($row[$j])) { $values[] = 'NULL'; } elseif ($fields_meta[$j]->numeric && $fields_meta[$j]->type != 'timestamp' && !$fields_meta[$j]->blob) { // a number // timestamp is numeric on some MySQL 4.1, BLOBs are // sometimes numeric $values[] = $row[$j]; } elseif (stristr($field_flags[$j], 'BINARY') && $fields_meta[$j]->blob && isset($GLOBALS['sql_hex_for_blob'])) { // a true BLOB // - mysqldump only generates hex data when the --hex-blob // option is used, for fields having the binary attribute // no hex is generated // - a TEXT field returns type blob but a real blob // returns also the 'binary' flag // empty blobs need to be different, but '0' is also empty // :-( if (empty($row[$j]) && $row[$j] != '0') { $values[] = '\'\''; } else { $values[] = '0x' . bin2hex($row[$j]); } } elseif ($fields_meta[$j]->type == 'bit') { // detection of 'bit' works only on mysqli extension $values[] = "b'" . PMA_Util::sqlAddSlashes(PMA_Util::printableBitValue($row[$j], $fields_meta[$j]->length)) . "'"; } else { // something else -> treat as a string $values[] = '\'' . str_replace($search, $replace, PMA_Util::sqlAddSlashes($row[$j])) . '\''; } // end if } // end for // should we make update? if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'UPDATE') { $insert_line = $schema_insert; for ($i = 0; $i < $fields_cnt; $i++) { if (0 == $i) { $insert_line .= ' '; } if ($i > 0) { // avoid EOL blank $insert_line .= ','; } $insert_line .= $field_set[$i] . ' = ' . $values[$i]; } list($tmp_unique_condition, $tmp_clause_is_unique) = PMA_Util::getUniqueCondition($result, $fields_cnt, $fields_meta, $row); $insert_line .= ' WHERE ' . $tmp_unique_condition; unset($tmp_unique_condition, $tmp_clause_is_unique); } else { // Extended inserts case if ($GLOBALS['sql_insert_syntax'] == 'extended' || $GLOBALS['sql_insert_syntax'] == 'both') { if ($current_row == 1) { $insert_line = $schema_insert . '(' . implode(', ', $values) . ')'; } else { $insert_line = '(' . implode(', ', $values) . ')'; $sql_max_size = $GLOBALS['sql_max_query_size']; if (isset($sql_max_size) && $sql_max_size > 0 && $query_size + strlen($insert_line) > $sql_max_size) { if (!PMA_exportOutputHandler(';' . $crlf)) { return false; } $query_size = 0; $current_row = 1; $insert_line = $schema_insert . $insert_line; } } $query_size += strlen($insert_line); // Other inserts case } else { $insert_line = $schema_insert . '(' . implode(', ', $values) . ')'; } } unset($values); if (!PMA_exportOutputHandler(($current_row == 1 ? '' : $separator . $crlf) . $insert_line)) { return false; } } // end while if ($current_row > 0) { if (!PMA_exportOutputHandler(';' . $crlf)) { return false; } } // We need to SET IDENTITY_INSERT OFF for MSSQL if (isset($GLOBALS['sql_compatibility']) && $GLOBALS['sql_compatibility'] == 'MSSQL' && $current_row > 0) { $outputSucceeded = PMA_exportOutputHandler($crlf . 'SET IDENTITY_INSERT ' . PMA_Util::backquoteCompat($table, $compat) . ' OFF;' . $crlf); if (!$outputSucceeded) { return false; } } } // end if ($result != false) PMA_DBI_free_result($result); return true; }
/** * Get SQL queries for Display and Add user * * @param string $username username * @param string $hostname host name * @param string $password password * * @return array ($create_user_real, $create_user_show,$real_sql_query, $sql_query) */ function PMA_getSqlQueriesForDisplayAndAddUser($username, $hostname, $password) { $create_user_real = 'CREATE USER \'' . PMA_Util::sqlAddSlashes($username) . '\'@\'' . PMA_Util::sqlAddSlashes($hostname) . '\''; $real_sql_query = 'GRANT ' . join(', ', PMA_extractPrivInfo()) . ' ON *.* TO \'' . PMA_Util::sqlAddSlashes($username) . '\'@\'' . PMA_Util::sqlAddSlashes($hostname) . '\''; if ($_POST['pred_password'] != 'none' && $_POST['pred_password'] != 'keep') { $sql_query = $real_sql_query; // Requires SELECT privilege on mysql database // for using this with GRANT queries. It can be skipped. if ($GLOBALS['is_superuser']) { $sql_query .= ' IDENTIFIED BY \'***\''; $real_sql_query .= ' IDENTIFIED BY \'' . PMA_Util::sqlAddSlashes($_POST['pma_pw']) . '\''; } if (isset($create_user_real)) { $create_user_show = $create_user_real . ' IDENTIFIED BY \'***\''; $create_user_real .= ' IDENTIFIED BY \'' . PMA_Util::sqlAddSlashes($_POST['pma_pw']) . '\''; } } else { if ($_POST['pred_password'] == 'keep' && !empty($password)) { $real_sql_query .= ' IDENTIFIED BY PASSWORD \'' . $password . '\''; if (isset($create_user_real)) { $create_user_real .= ' IDENTIFIED BY PASSWORD \'' . $password . '\''; } } $sql_query = $real_sql_query; if (isset($create_user_real)) { $create_user_show = $create_user_real; } } // add REQUIRE clause $require_clause = PMA_getRequireClause(); $real_sql_query .= $require_clause; $sql_query .= $require_clause; if (isset($_POST['Grant_priv']) && $_POST['Grant_priv'] == 'Y' || (isset($_POST['max_questions']) || isset($_POST['max_connections']) || isset($_POST['max_updates']) || isset($_POST['max_user_connections']))) { $with_clause = PMA_getWithClauseForAddUserAndUpdatePrivs(); $real_sql_query .= $with_clause; $sql_query .= $with_clause; } if (isset($create_user_real)) { $create_user_real .= ';'; $create_user_show .= ';'; } $real_sql_query .= ';'; $sql_query .= ';'; // No Global GRANT_OPTION privilege if (!$GLOBALS['is_grantuser']) { $real_sql_query = ''; $sql_query = ''; } return array($create_user_real, $create_user_show, $real_sql_query, $sql_query); }
/** * Test for PMA_getSqlQueryForDisplayPrivTable * * @return void */ public function testPMAGetSqlQueryForDisplayPrivTable() { $username = "******"; $db = '*'; $table = "pma_table"; $hostname = "pma_hostname"; //$db == '*' $ret = PMA_getSqlQueryForDisplayPrivTable($db, $table, $username, $hostname); $sql = "SELECT * FROM `mysql`.`user`" . " WHERE `User` = '" . PMA_Util::sqlAddSlashes($username) . "'" . " AND `Host` = '" . PMA_Util::sqlAddSlashes($hostname) . "';"; $this->assertEquals($sql, $ret); //$table == '*' $db = "pma_db"; $table = "*"; $ret = PMA_getSqlQueryForDisplayPrivTable($db, $table, $username, $hostname); $sql = "SELECT * FROM `mysql`.`db`" . " WHERE `User` = '" . PMA_Util::sqlAddSlashes($username) . "'" . " AND `Host` = '" . PMA_Util::sqlAddSlashes($hostname) . "'" . " AND '" . PMA_Util::unescapeMysqlWildcards($db) . "'" . " LIKE `Db`;"; $this->assertEquals($sql, $ret); //$table == 'pma_table' $db = "pma_db"; $table = "pma_table"; $ret = PMA_getSqlQueryForDisplayPrivTable($db, $table, $username, $hostname); $sql = "SELECT `Table_priv`" . " FROM `mysql`.`tables_priv`" . " WHERE `User` = '" . PMA_Util::sqlAddSlashes($username) . "'" . " AND `Host` = '" . PMA_Util::sqlAddSlashes($hostname) . "'" . " AND `Db` = '" . PMA_Util::unescapeMysqlWildcards($db) . "'" . " AND `Table_name` = '" . PMA_Util::sqlAddSlashes($table) . "';"; $this->assertEquals($sql, $ret); }
// In such case we can use the value of port. $server_details['port'] = $cfg['Server']['port']; } // otherwise we leave the $server_details['port'] unset, // allowing it to take default mysql port $controllink = $GLOBALS['dbi']->connect($cfg['Server']['controluser'], $cfg['Server']['controlpass'], true, $server_details); } else { $controllink = $GLOBALS['dbi']->connect($cfg['Server']['controluser'], $cfg['Server']['controlpass'], true); } } // Connects to the server (validates user's login) /** @var PMA_DatabaseInterface $userlink */ $userlink = $GLOBALS['dbi']->connect($cfg['Server']['user'], $cfg['Server']['password'], false); // Set timestamp for the session, if required. if ($cfg['Server']['SessionTimeZone'] != '') { $sql_query_tz = 'SET ' . PMA_Util::backquote('time_zone') . ' = ' . '\'' . PMA_Util::sqlAddSlashes($cfg['Server']['SessionTimeZone']) . '\''; if (!$userlink->query($sql_query_tz)) { $error_message_tz = sprintf(__('Unable to use timezone %1$s for server %2$d. ' . 'Please check your configuration setting for ' . '[em]$cfg[\'Servers\'][%3$d][\'SessionTimeZone\'][/em]. ' . 'phpMyAdmin is currently using the default time zone ' . 'of the database server.'), $cfg['Servers'][$GLOBALS['server']]['SessionTimeZone'], $GLOBALS['server'], $GLOBALS['server']); $GLOBALS['error_handler']->addError($error_message_tz, E_USER_WARNING, '', '', false); } } if (!$controllink) { $controllink = $userlink; } $auth_plugin->storeUserCredentials(); /* Log success */ PMA_logUser($cfg['Server']['user']); if (PMA_MYSQL_INT_VERSION < $cfg['MysqlMinVersion']['internal']) { PMA_fatalError(__('You should upgrade to %s %s or later.'), array('MySQL', $cfg['MysqlMinVersion']['human'])); } /**
/** * Function to get the default sql query for browsing page * * @param String $db the current database * @param String $table the current table * * @return String $sql_query the default $sql_query for browse page */ function PMA_getDefaultSqlQueryForBrowse($db, $table) { include_once 'libraries/bookmark.lib.php'; $book_sql_query = PMA_Bookmark_get($db, '\'' . PMA_Util::sqlAddSlashes($table) . '\'', 'label', false, true); if (!empty($book_sql_query)) { $GLOBALS['using_bookmark_message'] = PMA_message::notice(__('Using bookmark "%s" as default browse query.')); $GLOBALS['using_bookmark_message']->addParam($table); $GLOBALS['using_bookmark_message']->addMessage(PMA_Util::showDocu('faq', 'faq6-22')); $sql_query = $book_sql_query; } else { $defaultOrderByClause = ''; if (isset($GLOBALS['cfg']['TablePrimaryKeyOrder']) && $GLOBALS['cfg']['TablePrimaryKeyOrder'] !== 'NONE') { $primaryKey = null; $primary = PMA_Index::getPrimary($table, $db); if ($primary !== false) { $primarycols = $primary->getColumns(); foreach ($primarycols as $col) { $primaryKey = $col->getName(); break; } if ($primaryKey != null) { $defaultOrderByClause = ' ORDER BY ' . PMA_Util::backquote($table) . '.' . PMA_Util::backquote($primaryKey) . ' ' . $GLOBALS['cfg']['TablePrimaryKeyOrder']; } } } $sql_query = 'SELECT * FROM ' . PMA_Util::backquote($table) . $defaultOrderByClause; } unset($book_sql_query); return $sql_query; }