/**
* Prepare sorted column message
*
* @param integer &$dt_result the link id associated to the
* query which results have to
* be displayed
* @param string $sort_expression_nodirection sort expression without direction
*
* @return string html content
* null if not found sorted column
*
* @access private
*
* @see getTable()
*/
private function _getSortedColumnMessage(&$dt_result, $sort_expression_nodirection)
{
$fields_meta = $this->__get('fields_meta');
// To use array indexes
if (empty($sort_expression_nodirection)) {
return null;
}
if (mb_strpos($sort_expression_nodirection, '.') === false) {
$sort_table = $this->__get('table');
$sort_column = $sort_expression_nodirection;
} else {
list($sort_table, $sort_column) = explode('.', $sort_expression_nodirection);
}
$sort_table = PMA_Util::unQuote($sort_table);
$sort_column = PMA_Util::unQuote($sort_column);
// find the sorted column index in row result
// (this might be a multi-table query)
$sorted_column_index = false;
foreach ($fields_meta as $key => $meta) {
if ($meta->table == $sort_table && $meta->name == $sort_column) {
$sorted_column_index = $key;
break;
}
}
if ($sorted_column_index === false) {
return null;
}
// fetch first row of the result set
$row = $GLOBALS['dbi']->fetchRow($dt_result);
// initializing default arguments
$default_function = 'PMA_mimeDefaultFunction';
$transformation_plugin = $default_function;
$transform_options = array();
// check for non printable sorted row data
$meta = $fields_meta[$sorted_column_index];
if (stristr($meta->type, self::BLOB_FIELD) || $meta->type == self::GEOMETRY_FIELD) {
$column_for_first_row = $this->_handleNonPrintableContents($meta->type, $row[$sorted_column_index], $transformation_plugin, $transform_options, $default_function, $meta, null);
} else {
$column_for_first_row = $row[$sorted_column_index];
}
$column_for_first_row = mb_strtoupper(mb_substr($column_for_first_row, 0, $GLOBALS['cfg']['LimitChars']));
// fetch last row of the result set
$GLOBALS['dbi']->dataSeek($dt_result, $this->__get('num_rows') - 1);
$row = $GLOBALS['dbi']->fetchRow($dt_result);
// check for non printable sorted row data
$meta = $fields_meta[$sorted_column_index];
if (stristr($meta->type, self::BLOB_FIELD) || $meta->type == self::GEOMETRY_FIELD) {
$column_for_last_row = $this->_handleNonPrintableContents($meta->type, $row[$sorted_column_index], $transformation_plugin, $transform_options, $default_function, $meta, null);
} else {
$column_for_last_row = $row[$sorted_column_index];
}
$column_for_last_row = mb_strtoupper(mb_substr($column_for_last_row, 0, $GLOBALS['cfg']['LimitChars']));
// reset to first row for the loop in _getTableBody()
$GLOBALS['dbi']->dataSeek($dt_result, 0);
// we could also use here $sort_expression_nodirection
return ' [' . htmlspecialchars($sort_column) . ': <strong>' . htmlspecialchars($column_for_first_row) . ' - ' . htmlspecialchars($column_for_last_row) . '</strong>]';
}
/**
* This function looks through the contents of a parsed
* SHOW CREATE [PROCEDURE | FUNCTION] query and extracts
* information about the routine's definer.
*
* @param array $parsed_query Parsed query, returned by PMA_SQP_parse()
*
* @return string The definer of a routine.
*/
function PMA_RTN_parseRoutineDefiner($parsed_query)
{
$retval = '';
$fetching = false;
for ($i = 0; $i < $parsed_query['len']; $i++) {
if ($parsed_query[$i]['type'] == 'alpha_reservedWord' && $parsed_query[$i]['data'] == 'DEFINER') {
$fetching = true;
} else {
if ($fetching == true && $parsed_query[$i]['type'] != 'quote_backtick' && substr($parsed_query[$i]['type'], 0, 5) != 'punct') {
break;
} else {
if ($fetching == true && $parsed_query[$i]['type'] == 'quote_backtick') {
$retval .= PMA_Util::unQuote($parsed_query[$i]['data']);
} else {
if ($fetching == true && $parsed_query[$i]['type'] == 'punct_user') {
$retval .= $parsed_query[$i]['data'];
}
}
}
}
}
return $retval;
}
// run SQL query
$import_text = $sql_query;
$import_type = 'query';
$format = 'sql';
// refresh navigation and main panels
if (preg_match('/^(DROP)\\s+(VIEW|TABLE|DATABASE|SCHEMA)\\s+/i', $sql_query)) {
$GLOBALS['reload'] = true;
}
// refresh navigation panel only
if (preg_match('/^(CREATE|ALTER)\\s+(VIEW|TABLE|DATABASE|SCHEMA)\\s+/i', $sql_query)) {
$ajax_reload['reload'] = true;
}
// do a dynamic reload if table is RENAMED
// (by sending the instruction to the AJAX response handler)
if (preg_match('/^RENAME\\s+TABLE\\s+(.*?)\\s+TO\\s+(.*?)($|;|\\s)/i', $sql_query, $rename_table_names)) {
$ajax_reload['table_name'] = PMA_Util::unQuote($rename_table_names[2]);
$ajax_reload['reload'] = true;
}
$sql_query = '';
} elseif (!empty($sql_localfile)) {
// run SQL file on server
$local_import_file = $sql_localfile;
$import_type = 'queryfile';
$format = 'sql';
unset($sql_localfile);
} elseif (!empty($sql_file)) {
// run uploaded SQL file
$import_file = $sql_file;
$import_type = 'queryfile';
$format = 'sql';
unset($sql_file);
/**
* Analyzes SQL queries
*
* @param array $arr The SQL queries
*
* @return array The analyzed SQL queries
*
* @access public
*/
function PMA_SQP_analyze($arr)
{
if ($arr == array() || !isset($arr['len'])) {
return array();
}
$result = array();
$size = $arr['len'];
$subresult = array('querytype' => '', 'select_expr_clause' => '', 'position_of_first_select' => '', 'from_clause' => '', 'group_by_clause' => '', 'order_by_clause' => '', 'having_clause' => '', 'limit_clause' => '', 'where_clause' => '', 'where_clause_identifiers' => array(), 'unsorted_query' => '', 'queryflags' => array(), 'select_expr' => array(), 'table_ref' => array(), 'foreign_keys' => array(), 'create_table_fields' => array());
$subresult_empty = $subresult;
$seek_queryend = false;
$seen_end_of_table_ref = false;
$number_of_brackets_in_extract = 0;
$number_of_brackets_in_group_concat = 0;
$number_of_brackets = 0;
$in_subquery = false;
$seen_subquery = false;
$seen_from = false;
// for SELECT EXTRACT(YEAR_MONTH FROM CURDATE())
// we must not use CURDATE as a table_ref
// so we track whether we are in the EXTRACT()
$in_extract = false;
// for GROUP_CONCAT(...)
$in_group_concat = false;
/* Description of analyzer results
*
* db, table, column, alias
* ------------------------
*
* Inside the $subresult array, we create ['select_expr'] and ['table_ref']
* arrays.
*
* The SELECT syntax (simplified) is
*
* SELECT
* select_expression,...
* [FROM [table_references]
*
*
* ['select_expr'] is filled with each expression, the key represents the
* expression position in the list (0-based) (so we don't lose track of
* multiple occurrences of the same column).
*
* ['table_ref'] is filled with each table ref, same thing for the key.
*
* I create all sub-values empty, even if they are
* not present (for example no select_expression alias).
*
* There is a debug section at the end of loop #1, if you want to
* see the exact contents of select_expr and table_ref
*
* queryflags
* ----------
*
* In $subresult, array 'queryflags' is filled, according to what we
* find in the query.
*
* Currently, those are generated:
*
* ['queryflags']['select_from'] = 1; if this is a real SELECT...FROM
* ['queryflags']['drop_database'] = 1;if this is a DROP DATABASE
* ['queryflags']['reload'] = 1; for the purpose of reloading the
* navigation bar
* ['queryflags']['distinct'] = 1; for a DISTINCT
* ['queryflags']['union'] = 1; for a UNION
* ['queryflags']['join'] = 1; for a JOIN
* ['queryflags']['offset'] = 1; for the presence of OFFSET
* ['queryflags']['procedure'] = 1; for the presence of PROCEDURE
* ['queryflags']['is_explain'] = 1; for the presence of EXPLAIN
* ['queryflags']['is_delete'] = 1; for the presence of DELETE
* ['queryflags']['is_affected'] = 1; for the presence of UPDATE, DELETE
* or INSERT|LOAD DATA|REPLACE
* ['queryflags']['is_replace'] = 1; for the presence of REPLACE
* ['queryflags']['is_insert'] = 1; for the presence of INSERT
* ['queryflags']['is_maint'] = 1; for the presence of CHECK|ANALYZE
* |REPAIR|OPTIMIZE|CHECKSUM TABLE
* ['queryflags']['is_show'] = 1; for the presence of SHOW
* ['queryflags']['is_analyse'] = 1; for the presence of PROCEDURE ANALYSE
* ['queryflags']['is_export'] = 1; for the presence of INTO OUTFILE
* ['queryflags']['is_group'] = 1; for the presence of GROUP BY|HAVING|
* SELECT DISTINCT
* ['queryflags']['is_func'] = 1; for the presence of SUM|AVG|STD|STDDEV
* |MIN|MAX|BIT_OR|BIT_AND
* ['queryflags']['is_count'] = 1; for the presence of SELECT COUNT
* ['queryflags']['is_procedure'] = 1; for the presence of CALL
* ['queryflags']['is_subquery'] = 1; contains a subquery
*
* query clauses
* -------------
*
* The select is split in those clauses:
* ['select_expr_clause']
* ['from_clause']
* ['group_by_clause']
* ['order_by_clause']
* ['having_clause']
* ['limit_clause']
* ['where_clause']
*
* The identifiers of the WHERE clause are put into the array
* ['where_clause_identifier']
*
* For a SELECT, the whole query without the ORDER BY clause is put into
* ['unsorted_query']
*
* foreign keys
* ------------
* The CREATE TABLE may contain FOREIGN KEY clauses, so they get
* analyzed and ['foreign_keys'] is an array filled with
* the constraint name, the index list,
* the REFERENCES table name and REFERENCES index list,
* and ON UPDATE | ON DELETE clauses
*
* position_of_first_select
* ------------------------
*
* The array index of the first SELECT we find. Will be used to
* insert a SQL_CALC_FOUND_ROWS.
*
* create_table_fields
* -------------------
*
* Used to detect the DEFAULT CURRENT_TIMESTAMP and
* ON UPDATE CURRENT_TIMESTAMP clauses of the CREATE TABLE query.
* Also used to store the default value of the field.
* An array, each element is the identifier name.
* Note that for now, the timestamp_not_null element is created
* even for non-TIMESTAMP fields.
*
* Sub-elements: ['type'] which contains the column type
* optional (currently they are never false but can be absent):
* ['default_current_timestamp'] boolean
* ['on_update_current_timestamp'] boolean
* ['timestamp_not_null'] boolean
*
* section_before_limit, section_after_limit
* -----------------------------------------
*
* Marks the point of the query where we can insert a LIMIT clause;
* so the section_before_limit will contain the left part before
* a possible LIMIT clause
*
*
* End of description of analyzer results
*/
// must be sorted
// TODO: current logic checks for only one word, so I put only the
// first word of the reserved expressions that end a table ref;
// maybe this is not ok (the first word might mean something else)
// $words_ending_table_ref = array(
// 'FOR UPDATE',
// 'GROUP BY',
// 'HAVING',
// 'LIMIT',
// 'LOCK IN SHARE MODE',
// 'ORDER BY',
// 'PROCEDURE',
// 'UNION',
// 'WHERE'
// );
$words_ending_table_ref = array('FOR' => 1, 'GROUP' => 1, 'HAVING' => 1, 'LIMIT' => 1, 'LOCK' => 1, 'ORDER' => 1, 'PROCEDURE' => 1, 'UNION' => 1, 'WHERE' => 1);
$words_ending_clauses = array('FOR' => 1, 'LIMIT' => 1, 'LOCK' => 1, 'PROCEDURE' => 1, 'UNION' => 1);
$supported_query_types = array('SELECT' => 1);
// loop #1 for each token: select_expr, table_ref for SELECT
for ($i = 0; $i < $size; $i++) {
//DEBUG echo "Loop1 <strong>" . $arr[$i]['data']
//. "</strong> (" . $arr[$i]['type'] . ")<br />";
// High speed seek for locating the end of the current query
if ($seek_queryend == true) {
if ($arr[$i]['type'] == 'punct_queryend') {
$seek_queryend = false;
} else {
continue;
}
// end if (type == punct_queryend)
}
// end if ($seek_queryend)
/**
* Note: do not split if this is a punct_queryend for the first and only
* query
* @todo when we find a UNION, should we split in another subresult?
*/
if ($arr[$i]['type'] == 'punct_queryend' && $i + 1 != $size) {
$result[] = $subresult;
$subresult = $subresult_empty;
continue;
}
// end if (type == punct_queryend)
// ==============================================================
if ($arr[$i]['type'] == 'punct_bracket_open_round') {
$number_of_brackets++;
if ($in_extract) {
$number_of_brackets_in_extract++;
}
if ($in_group_concat) {
$number_of_brackets_in_group_concat++;
}
}
// ==============================================================
if ($arr[$i]['type'] == 'punct_bracket_close_round') {
$number_of_brackets--;
if ($number_of_brackets == 0) {
$in_subquery = false;
}
if ($in_extract) {
$number_of_brackets_in_extract--;
if ($number_of_brackets_in_extract == 0) {
$in_extract = false;
}
}
if ($in_group_concat) {
$number_of_brackets_in_group_concat--;
if ($number_of_brackets_in_group_concat == 0) {
$in_group_concat = false;
}
}
}
if ($in_subquery) {
/**
* skip the subquery to avoid setting
* select_expr or table_ref with the contents
* of this subquery; this is to avoid a bug when
* trying to edit the results of
* select * from child where not exists (select id from
* parent where child.parent_id = parent.id);
*/
continue;
}
// ==============================================================
if ($arr[$i]['type'] == 'alpha_functionName') {
$upper_data = mb_strtoupper($arr[$i]['data']);
if ($upper_data == 'EXTRACT') {
$in_extract = true;
$number_of_brackets_in_extract = 0;
}
if ($upper_data == 'GROUP_CONCAT') {
$in_group_concat = true;
$number_of_brackets_in_group_concat = 0;
}
}
// ==============================================================
if ($arr[$i]['type'] == 'alpha_reservedWord') {
// We don't know what type of query yet, so run this
if ($subresult['querytype'] == '') {
$subresult['querytype'] = mb_strtoupper($arr[$i]['data']);
}
// end if (querytype was empty)
// Check if we support this type of query
if (!isset($supported_query_types[$subresult['querytype']])) {
// Skip ahead to the next one if we don't
$seek_queryend = true;
continue;
}
// end if (query not supported)
// upper once
$upper_data = mb_strtoupper($arr[$i]['data']);
/**
* @todo reset for each query?
*/
if ($upper_data == 'SELECT') {
if ($number_of_brackets > 0) {
$in_subquery = true;
$seen_subquery = true;
$subresult['queryflags']['is_subquery'] = 1;
// this is a subquery so do not analyze inside it
continue;
}
$seen_from = false;
$previous_was_identifier = false;
$current_select_expr = -1;
$seen_end_of_table_ref = false;
}
// end if (data == SELECT)
if ($upper_data == 'FROM' && !$in_extract) {
$current_table_ref = -1;
$seen_from = true;
$previous_was_identifier = false;
$save_table_ref = true;
}
// end if (data == FROM)
// here, do not 'continue' the loop, as we have more work for
// reserved words below
}
// end if (type == alpha_reservedWord)
// ==============================
if ($arr[$i]['type'] == 'quote_backtick' || $arr[$i]['type'] == 'quote_double' || $arr[$i]['type'] == 'quote_single' || $arr[$i]['type'] == 'alpha_identifier' || $arr[$i]['type'] == 'alpha_reservedWord' && $arr[$i]['forbidden'] == false) {
switch ($arr[$i]['type']) {
case 'alpha_identifier':
case 'alpha_reservedWord':
/**
* this is not a real reservedWord, because it's not
* present in the list of forbidden words, for example
* "storage" which can be used as an identifier
*
*/
$identifier = $arr[$i]['data'];
break;
case 'quote_backtick':
case 'quote_double':
case 'quote_single':
$identifier = PMA_Util::unQuote($arr[$i]['data']);
break;
}
// end switch
if ($subresult['querytype'] == 'SELECT' && !$in_group_concat && !($seen_subquery && $arr[$i - 1]['type'] == 'punct_bracket_close_round')) {
if (!$seen_from) {
if ($previous_was_identifier && isset($chain)) {
// found alias for this select_expr, save it
// but only if we got something in $chain
// (for example, SELECT COUNT(*) AS cnt
// puts nothing in $chain, so we avoid
// setting the alias)
$alias_for_select_expr = $identifier;
} else {
if (!isset($chain)) {
$chain = array();
}
$chain[] = $identifier;
$previous_was_identifier = true;
}
// end if !$previous_was_identifier
} else {
// ($seen_from)
if ($save_table_ref && !$seen_end_of_table_ref) {
if ($previous_was_identifier) {
// found alias for table ref
// save it for later
$alias_for_table_ref = $identifier;
} else {
if (!isset($chain)) {
$chain = array();
}
$chain[] = $identifier;
$previous_was_identifier = true;
}
// end if ($previous_was_identifier)
}
// end if ($save_table_ref &&!$seen_end_of_table_ref)
}
// end if (!$seen_from)
}
// end if (querytype SELECT)
}
// end if (quote_backtick or double quote or alpha_identifier)
// ===================================
if ($arr[$i]['type'] == 'punct_qualifier') {
// to be able to detect an identifier following another
$previous_was_identifier = false;
continue;
}
// end if (punct_qualifier)
/**
* @todo check if 3 identifiers following one another -> error
*/
// s a v e a s e l e c t e x p r
// finding a list separator or FROM
// means that we must save the current chain of identifiers
// into a select expression
// for now, we only save a select expression if it contains
// at least one identifier, as we are interested in checking
// the columns and table names, so in "select * from persons",
// the "*" is not saved
if (isset($chain) && !$seen_end_of_table_ref && (!$seen_from && $arr[$i]['type'] == 'punct_listsep' || $arr[$i]['type'] == 'alpha_reservedWord' && $upper_data == 'FROM')) {
$size_chain = count($chain);
$current_select_expr++;
$subresult['select_expr'][$current_select_expr] = array('expr' => '', 'alias' => '', 'db' => '', 'table_name' => '', 'table_true_name' => '', 'column' => '');
if (isset($alias_for_select_expr) && mb_strlen($alias_for_select_expr)) {
// we had found an alias for this select expression
$subresult['select_expr'][$current_select_expr]['alias'] = $alias_for_select_expr;
unset($alias_for_select_expr);
}
// there is at least a column
$subresult['select_expr'][$current_select_expr]['column'] = $chain[$size_chain - 1];
$subresult['select_expr'][$current_select_expr]['expr'] = $chain[$size_chain - 1];
// maybe a table
if ($size_chain > 1) {
$subresult['select_expr'][$current_select_expr]['table_name'] = $chain[$size_chain - 2];
// we assume for now that this is also the true name
$subresult['select_expr'][$current_select_expr]['table_true_name'] = $chain[$size_chain - 2];
$subresult['select_expr'][$current_select_expr]['expr'] = $subresult['select_expr'][$current_select_expr]['table_name'] . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
}
// end if ($size_chain > 1)
// maybe a db
if ($size_chain > 2) {
$subresult['select_expr'][$current_select_expr]['db'] = $chain[$size_chain - 3];
$subresult['select_expr'][$current_select_expr]['expr'] = $subresult['select_expr'][$current_select_expr]['db'] . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
}
// end if ($size_chain > 2)
unset($chain);
/**
* @todo explain this:
*/
if ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data != 'FROM') {
$previous_was_identifier = true;
}
}
// end if (save a select expr)
//======================================
// s a v e a t a b l e r e f
//======================================
// maybe we just saw the end of table refs
// but the last table ref has to be saved
// or we are at the last token
// or we just got a reserved word
/**
* @todo there could be another query after this one
*/
if (isset($chain) && $seen_from && $save_table_ref && ($arr[$i]['type'] == 'punct_listsep' || $arr[$i]['type'] == 'alpha_reservedWord' && $upper_data != "AS" || $seen_end_of_table_ref || $i == $size - 1)) {
$size_chain = count($chain);
$current_table_ref++;
$subresult['table_ref'][$current_table_ref] = array('expr' => '', 'db' => '', 'table_name' => '', 'table_alias' => '', 'table_true_name' => '');
if (isset($alias_for_table_ref) && mb_strlen($alias_for_table_ref)) {
$subresult['table_ref'][$current_table_ref]['table_alias'] = $alias_for_table_ref;
unset($alias_for_table_ref);
}
$subresult['table_ref'][$current_table_ref]['table_name'] = $chain[$size_chain - 1];
// we assume for now that this is also the true name
$subresult['table_ref'][$current_table_ref]['table_true_name'] = $chain[$size_chain - 1];
$subresult['table_ref'][$current_table_ref]['expr'] = $subresult['table_ref'][$current_table_ref]['table_name'];
// maybe a db
if ($size_chain > 1) {
$subresult['table_ref'][$current_table_ref]['db'] = $chain[$size_chain - 2];
$subresult['table_ref'][$current_table_ref]['expr'] = $subresult['table_ref'][$current_table_ref]['db'] . '.' . $subresult['table_ref'][$current_table_ref]['expr'];
}
// end if ($size_chain > 1)
// add the table alias into the whole expression
$subresult['table_ref'][$current_table_ref]['expr'] .= ' ' . $subresult['table_ref'][$current_table_ref]['table_alias'];
unset($chain);
$previous_was_identifier = true;
//continue;
}
// end if (save a table ref)
// when we have found all table refs,
// for each table_ref alias, put the true name of the table
// in the corresponding select expressions
if (isset($current_table_ref) && ($seen_end_of_table_ref || $i == $size - 1) && $subresult != $subresult_empty) {
for ($tr = 0; $tr <= $current_table_ref; $tr++) {
$alias = $subresult['table_ref'][$tr]['table_alias'];
$truename = $subresult['table_ref'][$tr]['table_true_name'];
for ($se = 0; $se <= $current_select_expr; $se++) {
if (isset($alias) && mb_strlen($alias) && $subresult['select_expr'][$se]['table_true_name'] == $alias) {
$subresult['select_expr'][$se]['table_true_name'] = $truename;
}
// end if (found the alias)
}
// end for (select expressions)
}
// end for (table refs)
}
// end if (set the true names)
// e n d i n g l o o p #1
// set the $previous_was_identifier to false if the current
// token is not an identifier
if ($arr[$i]['type'] != 'alpha_identifier' && $arr[$i]['type'] != 'quote_double' && $arr[$i]['type'] != 'quote_single' && $arr[$i]['type'] != 'quote_backtick') {
$previous_was_identifier = false;
}
// end if
// however, if we are on AS, we must keep the $previous_was_identifier
if ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data == 'AS') {
$previous_was_identifier = true;
}
if ($arr[$i]['type'] == 'alpha_reservedWord' && ($upper_data == 'ON' || $upper_data == 'USING')) {
$save_table_ref = false;
}
// end if (data == ON)
if ($arr[$i]['type'] == 'alpha_reservedWord' && ($upper_data == 'JOIN' || $upper_data == 'FROM')) {
$save_table_ref = true;
}
// end if (data == JOIN)
/**
* no need to check the end of table ref if we already did
*
* @todo maybe add "&& $seen_from"
*/
if (!$seen_end_of_table_ref) {
// if this is the last token, it implies that we have
// seen the end of table references
// Check for the end of table references
//
// Note: if we are analyzing a GROUP_CONCAT clause,
// we might find a word that seems to indicate that
// we have found the end of table refs (like ORDER)
// but it's a modifier of the GROUP_CONCAT so
// it's not the real end of table refs
if ($i == $size - 1 || $arr[$i]['type'] == 'alpha_reservedWord' && !$in_group_concat && isset($words_ending_table_ref[$upper_data])) {
$seen_end_of_table_ref = true;
// to be able to save the last table ref, but do not
// set it true if we found a word like "ON" that has
// already set it to false
if (isset($save_table_ref) && $save_table_ref != false) {
$save_table_ref = true;
}
//end if
}
// end if (check for end of table ref)
}
//end if (!$seen_end_of_table_ref)
if ($seen_end_of_table_ref) {
$save_table_ref = false;
}
// end if
}
// end for $i (loop #1)
//DEBUG
/*
if (isset($current_select_expr)) {
for ($trace = 0; $trace <= $current_select_expr; $trace++) {
echo "<br />";
reset ($subresult['select_expr'][$trace]);
while (list ($key, $val) = each ($subresult['select_expr'][$trace]))
echo "sel expr $trace $key => $val<br />\n";
}
}
if (isset($current_table_ref)) {
echo "current_table_ref = " . $current_table_ref . "<br>";
for ($trace = 0; $trace <= $current_table_ref; $trace++) {
echo "<br />";
reset ($subresult['table_ref'][$trace]);
while (list ($key, $val) = each ($subresult['table_ref'][$trace]))
echo "table ref $trace $key => $val<br />\n";
}
}
*/
// -------------------------------------------------------
// loop #2: - queryflags
// - querytype (for queries != 'SELECT')
// - section_before_limit, section_after_limit
//
// we will also need this queryflag in loop 2
// so set it here
if (isset($current_table_ref) && $current_table_ref > -1) {
$subresult['queryflags']['select_from'] = 1;
}
$section_before_limit = '';
$section_after_limit = '';
// truly the section after the limit clause
$seen_reserved_word = false;
$seen_group = false;
$seen_order = false;
$seen_order_by = false;
$in_group_by = false;
// true when we are inside the GROUP BY clause
$in_order_by = false;
// true when we are inside the ORDER BY clause
$in_having = false;
// true when we are inside the HAVING clause
$in_select_expr = false;
// true when we are inside the select expr clause
$in_where = false;
// true when we are inside the WHERE clause
$seen_limit = false;
// true if we have seen a LIMIT clause
$in_limit = false;
// true when we are inside the LIMIT clause
$after_limit = false;
// true when we are after the LIMIT clause
$in_from = false;
// true when we are in the FROM clause
$in_group_concat = false;
$first_reserved_word = '';
$current_identifier = '';
$unsorted_query = $arr['raw'];
// in case there is no ORDER BY
$number_of_brackets = 0;
$in_subquery = false;
$arrayFunctions = array("SUM", "AVG", "STD", "STDDEV", "MIN", "MAX", "BIT_OR", "BIT_AND");
$arrayKeyWords = array("BY", "HAVING", "SELECT");
for ($i = 0; $i < $size; $i++) {
//DEBUG echo "Loop2 <strong>" . $arr[$i]['data']
//. "</strong> (" . $arr[$i]['type'] . ")<br />";
if ($arr[$i]['type'] == 'punct_bracket_open_round') {
$number_of_brackets++;
}
if ($arr[$i]['type'] == 'punct_bracket_close_round') {
$number_of_brackets--;
if ($number_of_brackets == 0) {
$in_subquery = false;
}
}
if ($arr[$i]['type'] == 'alpha_reservedWord') {
$upper_data = mb_strtoupper($arr[$i]['data']);
if ($upper_data == 'SELECT' && $number_of_brackets > 0) {
$in_subquery = true;
}
if (!$seen_reserved_word) {
$first_reserved_word = $upper_data;
$subresult['querytype'] = $upper_data;
$seen_reserved_word = true;
if ($first_reserved_word === 'SELECT') {
$position_of_first_select = $i;
} elseif ($first_reserved_word === 'EXPLAIN') {
$subresult['queryflags']['is_explain'] = 1;
} elseif ($first_reserved_word === 'DELETE') {
$subresult['queryflags']['is_delete'] = 1;
$subresult['queryflags']['is_affected'] = 1;
} elseif ($first_reserved_word === 'UPDATE') {
$subresult['queryflags']['is_affected'] = 1;
} elseif ($first_reserved_word === 'REPLACE') {
$subresult['queryflags']['is_replace'] = 1;
$subresult['queryflags']['is_affected'] = 1;
} elseif ($first_reserved_word === 'INSERT') {
$subresult['queryflags']['is_insert'] = 1;
$subresult['queryflags']['is_affected'] = 1;
} elseif ($first_reserved_word === 'SHOW') {
$subresult['queryflags']['is_show'] = 1;
}
} else {
// for the presence of DROP DATABASE
if ($first_reserved_word == 'DROP' && $upper_data == 'DATABASE') {
$subresult['queryflags']['drop_database'] = 1;
}
// A table has to be created, renamed, dropped -> navi panel
// should be reloaded
$keywords1 = array('CREATE', 'ALTER', 'DROP');
$keywords2 = array('VIEW', 'TABLE', 'DATABASE', 'SCHEMA');
if (in_array($first_reserved_word, $keywords1) && in_array($upper_data, $keywords2)) {
$subresult['queryflags']['reload'] = 1;
}
// for the presence of CHECK|ANALYZE|REPAIR|OPTIMIZE|CHECKSUM TABLE
$keywords = array('CHECK', 'ANALYZE', 'REPAIR', 'OPTIMIZE', 'CHECKSUM');
if (in_array($first_reserved_word, $keywords) && $upper_data == 'TABLE') {
$subresult['queryflags']['is_maint'] = 1;
}
}
if ($upper_data == 'LIMIT' && !$in_subquery) {
$section_before_limit = mb_substr($arr['raw'], 0, $arr[$i]['pos'] - 5);
$in_limit = true;
$seen_limit = true;
$limit_clause = '';
$in_order_by = false;
// @todo maybe others to set false
}
if ($upper_data == 'PROCEDURE') {
$subresult['queryflags']['procedure'] = 1;
$in_limit = false;
$after_limit = true;
// for the presence of PROCEDURE ANALYSE
if (isset($subresult['queryflags']['select_from']) && $subresult['queryflags']['select_from'] == 1 && $i + 1 < $size && $arr[$i + 1]['type'] == 'alpha_reservedWord' && mb_strtoupper($arr[$i + 1]['data']) == 'ANALYSE') {
$subresult['queryflags']['is_analyse'] = 1;
}
}
// for the presence of INTO OUTFILE
if ($upper_data == 'INTO' && isset($subresult['queryflags']['select_from']) && $subresult['queryflags']['select_from'] == 1 && $i + 1 < $size && $arr[$i + 1]['type'] == 'alpha_reservedWord' && mb_strtoupper($arr[$i + 1]['data']) == 'OUTFILE') {
$subresult['queryflags']['is_export'] = 1;
}
/**
* @todo set also to false if we find FOR UPDATE or LOCK IN SHARE MODE
*/
if ($upper_data == 'SELECT') {
$in_select_expr = true;
$select_expr_clause = '';
// for the presence of SELECT COUNT
if (isset($subresult['queryflags']['select_from']) && $subresult['queryflags']['select_from'] == 1 && !isset($subresult['queryflags']['is_group']) && $i + 1 < $size && $arr[$i + 1]['type'] == 'alpha_functionName' && mb_strtoupper($arr[$i + 1]['data']) == 'COUNT') {
$subresult['queryflags']['is_count'] = 1;
}
}
if ($upper_data == 'DISTINCT' && !$in_group_concat) {
$subresult['queryflags']['distinct'] = 1;
}
if ($upper_data == 'UNION') {
$subresult['queryflags']['union'] = 1;
}
if ($upper_data == 'JOIN') {
$subresult['queryflags']['join'] = 1;
}
if ($upper_data == 'OFFSET') {
$subresult['queryflags']['offset'] = 1;
}
// for the presence of CALL
if ($upper_data == 'CALL') {
$subresult['queryflags']['is_procedure'] = 1;
}
// if this is a real SELECT...FROM
if ($upper_data == 'FROM' && isset($subresult['queryflags']['select_from']) && $subresult['queryflags']['select_from'] == 1) {
$in_from = true;
$from_clause = '';
$in_select_expr = false;
}
// (we could have less resetting of variables to false
// if we trust that the query respects the standard
// MySQL order for clauses)
// we use $seen_group and $seen_order because we are looking
// for the BY
if ($upper_data == 'GROUP') {
$seen_group = true;
$seen_order = false;
$in_having = false;
$in_order_by = false;
$in_where = false;
$in_select_expr = false;
$in_from = false;
// for the presence of GROUP BY|HAVING|SELECT DISTINCT
if (isset($subresult['queryflags']['select_from']) && $subresult['queryflags']['select_from'] == 1 && $i + 1 < $size && $arr[$i + 1]['type'] == 'alpha_reservedWord' && in_array(mb_strtoupper($arr[$i + 1]['data']), $arrayKeyWords) && $i + 2 < $size && $arr[$i + 2]['type'] == 'alpha_reservedWord' && mb_strtoupper($arr[$i + 2]['data']) == 'DISTINCT') {
$subresult['queryflags']['is_group'] = 1;
}
}
if ($upper_data == 'ORDER' && !$in_group_concat) {
$seen_order = true;
$seen_group = false;
$in_having = false;
$in_group_by = false;
$in_where = false;
$in_select_expr = false;
$in_from = false;
}
if ($upper_data == 'HAVING') {
$in_having = true;
$having_clause = '';
$seen_group = false;
$seen_order = false;
$in_group_by = false;
$in_order_by = false;
$in_where = false;
$in_select_expr = false;
$in_from = false;
}
if ($upper_data == 'WHERE') {
$in_where = true;
$where_clause = '';
$where_clause_identifiers = array();
$seen_group = false;
$seen_order = false;
$in_group_by = false;
$in_order_by = false;
$in_having = false;
$in_select_expr = false;
$in_from = false;
}
if ($upper_data == 'BY') {
if ($seen_group) {
$in_group_by = true;
$group_by_clause = '';
}
if ($seen_order) {
$seen_order_by = true;
// Here we assume that the ORDER BY keywords took
// exactly 8 characters.
// We use $GLOBALS['PMA_String']->substr() to be charset-safe;
// otherwise if the table name contains accents, the unsorted
// query would be missing some characters.
$unsorted_query = mb_substr($arr['raw'], 0, $arr[$i]['pos'] - 8);
$in_order_by = true;
$order_by_clause = '';
}
}
// if we find one of the words that could end the clause
if (isset($words_ending_clauses[$upper_data])) {
$in_group_by = false;
$in_order_by = false;
$in_having = false;
$in_where = false;
$in_select_expr = false;
$in_from = false;
}
}
// endif (reservedWord)
// do not add a space after a function name
/**
* @todo can we combine loop 2 and loop 1? some code is repeated here...
*/
$sep = ' ';
if ($arr[$i]['type'] == 'alpha_functionName') {
$sep = '';
$upper_data = mb_strtoupper($arr[$i]['data']);
if ($upper_data == 'GROUP_CONCAT') {
$in_group_concat = true;
$number_of_brackets_in_group_concat = 0;
}
}
if ($arr[$i]['type'] == 'punct_bracket_open_round') {
if ($in_group_concat) {
$number_of_brackets_in_group_concat++;
}
}
if ($arr[$i]['type'] == 'punct_bracket_close_round') {
if ($in_group_concat) {
$number_of_brackets_in_group_concat--;
if ($number_of_brackets_in_group_concat == 0) {
$in_group_concat = false;
}
}
}
// do not add a space after an identifier if followed by a dot
if ($arr[$i]['type'] == 'alpha_identifier' && $i < $size - 1 && $arr[$i + 1]['data'] == '.') {
$sep = '';
}
// do not add a space after a dot if followed by an identifier
if ($arr[$i]['data'] == '.' && $i < $size - 1 && $arr[$i + 1]['type'] == 'alpha_identifier') {
$sep = '';
}
// for the presence of INSERT|LOAD DATA
if ($arr[$i]['type'] == 'alpha_identifier' && mb_strtoupper($arr[$i]['data']) == 'DATA' && $i - 1 >= 0 && $arr[$i - 1]['type'] == 'alpha_reservedWord' && in_array(mb_strtoupper($arr[$i - 1]['data']), array("INSERT", "LOAD"))) {
$subresult['queryflags']['is_insert'] = 1;
$subresult['queryflags']['is_affected'] = 1;
}
// for the presence of SUM|AVG|STD|STDDEV|MIN|MAX|BIT_OR|BIT_AND
if ($arr[$i]['type'] == 'alpha_functionName' && in_array(mb_strtoupper($arr[$i]['data']), $arrayFunctions) && isset($subresult['queryflags']['select_from']) && $subresult['queryflags']['select_from'] == 1 && !isset($subresult['queryflags']['is_group'])) {
$subresult['queryflags']['is_func'] = 1;
}
if ($in_select_expr && $upper_data != 'SELECT' && $upper_data != 'DISTINCT') {
$select_expr_clause .= $arr[$i]['data'] . $sep;
}
if ($in_from && $upper_data != 'FROM') {
$from_clause .= $arr[$i]['data'] . $sep;
}
if ($in_group_by && $upper_data != 'GROUP' && $upper_data != 'BY') {
$group_by_clause .= $arr[$i]['data'] . $sep;
}
if ($in_order_by && $upper_data != 'ORDER' && $upper_data != 'BY') {
// add a space only before ASC or DESC
// not around the dot between dbname and tablename
if ($arr[$i]['type'] == 'alpha_reservedWord') {
$order_by_clause .= $sep;
}
$order_by_clause .= $arr[$i]['data'];
}
if ($in_having && $upper_data != 'HAVING') {
$having_clause .= $arr[$i]['data'] . $sep;
}
if ($in_where && $upper_data != 'WHERE') {
$where_clause .= $arr[$i]['data'] . $sep;
if ($arr[$i]['type'] == 'quote_backtick' || $arr[$i]['type'] == 'alpha_identifier') {
$where_clause_identifiers[] = $arr[$i]['data'];
}
}
// to grab the rest of the query after the ORDER BY clause
if (isset($subresult['queryflags']['select_from']) && $subresult['queryflags']['select_from'] == 1 && !$in_order_by && $seen_order_by && $upper_data != 'BY') {
$unsorted_query .= $arr[$i]['data'];
if ($arr[$i]['type'] != 'punct_bracket_open_round' && $arr[$i]['type'] != 'punct_bracket_close_round' && $arr[$i]['type'] != 'punct') {
$unsorted_query .= $sep;
}
}
if ($in_limit) {
if ($upper_data == 'OFFSET') {
$limit_clause .= $sep;
}
$limit_clause .= $arr[$i]['data'];
if ($upper_data == 'LIMIT' || $upper_data == 'OFFSET') {
$limit_clause .= $sep;
}
}
if ($after_limit && $seen_limit) {
$section_after_limit .= $arr[$i]['data'] . $sep;
}
// clear $upper_data for next iteration
$upper_data = '';
}
// end for $i (loop #2)
if (empty($section_before_limit)) {
$section_before_limit = $arr['raw'];
}
// -----------------------------------------------------
// loop #3: foreign keys and MySQL 4.1.2+ TIMESTAMP options
// (for now, check only the first query)
// (for now, identifiers are assumed to be backquoted)
// If we find that we are dealing with a CREATE TABLE query,
// we look for the next punct_bracket_open_round, which
// introduces the fields list. Then, when we find a
// quote_backtick, it must be a field, so we put it into
// the create_table_fields array. Even if this field is
// not a timestamp, it will be useful when logic has been
// added for complete field attributes analysis.
$seen_foreign = false;
$seen_references = false;
$seen_constraint = false;
$foreign_key_number = -1;
$seen_create_table = false;
$seen_create = false;
$seen_alter = false;
$in_create_table_fields = false;
$brackets_level = 0;
$in_timestamp_options = false;
$seen_default = false;
for ($i = 0; $i < $size; $i++) {
if ($arr[$i]['type'] == 'alpha_reservedWord') {
$upper_data = mb_strtoupper($arr[$i]['data']);
if ($upper_data == 'NOT' && $in_timestamp_options) {
if (!isset($create_table_fields)) {
$create_table_fields = array();
}
$create_table_fields[$current_identifier]['timestamp_not_null'] = true;
}
if ($upper_data == 'CREATE') {
$seen_create = true;
}
if ($upper_data == 'ALTER') {
$seen_alter = true;
}
if ($upper_data == 'TABLE' && $seen_create) {
$seen_create_table = true;
$create_table_fields = array();
}
if ($upper_data == 'CURRENT_TIMESTAMP') {
if ($in_timestamp_options) {
if ($seen_default) {
$create_table_fields[$current_identifier]['default_current_timestamp'] = true;
}
}
}
if ($upper_data == 'CONSTRAINT') {
$foreign_key_number++;
$seen_foreign = false;
$seen_references = false;
$seen_constraint = true;
}
if ($upper_data == 'FOREIGN') {
$seen_foreign = true;
$seen_references = false;
$seen_constraint = false;
}
if ($upper_data == 'REFERENCES') {
$seen_foreign = false;
$seen_references = true;
$seen_constraint = false;
}
// Cases covered:
// [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
// [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
// but we set ['on_delete'] or ['on_cascade'] to
// CASCADE | SET_NULL | NO_ACTION | RESTRICT
// ON UPDATE CURRENT_TIMESTAMP
if ($upper_data == 'ON') {
if (isset($arr[$i + 1]) && $arr[$i + 1]['type'] == 'alpha_reservedWord') {
$second_upper_data = mb_strtoupper($arr[$i + 1]['data']);
if ($second_upper_data == 'DELETE') {
$clause = 'on_delete';
}
if ($second_upper_data == 'UPDATE') {
$clause = 'on_update';
}
// ugly workaround because currently, NO is not
// in the list of reserved words in sqlparser.data
// (we got a bug report about not being able to use
// 'no' as an identifier)
if (isset($clause) && ($arr[$i + 2]['type'] == 'alpha_reservedWord' || $arr[$i + 2]['type'] == 'alpha_identifier' && mb_strtoupper($arr[$i + 2]['data']) == 'NO')) {
$third_upper_data = mb_strtoupper($arr[$i + 2]['data']);
if ($third_upper_data == 'CASCADE' || $third_upper_data == 'RESTRICT') {
$value = $third_upper_data;
} elseif ($third_upper_data == 'SET' || $third_upper_data == 'NO') {
if ($arr[$i + 3]['type'] == 'alpha_reservedWord') {
$value = $third_upper_data . '_' . mb_strtoupper($arr[$i + 3]['data']);
}
} elseif ($third_upper_data == 'CURRENT_TIMESTAMP') {
if ($clause == 'on_update' && $in_timestamp_options) {
$create_table_fields[$current_identifier]['on_update_current_timestamp'] = true;
$seen_default = false;
}
} else {
$value = '';
}
if (!empty($value)) {
if (!isset($foreign)) {
$foreign = array();
}
$foreign[$foreign_key_number][$clause] = $value;
}
unset($clause);
}
// endif (isset($clause))
}
}
}
// end of reserved words analysis
if ($arr[$i]['type'] == 'punct_bracket_open_round') {
$brackets_level++;
if ($seen_create_table && $brackets_level == 1) {
$in_create_table_fields = true;
}
}
if ($arr[$i]['type'] == 'punct_bracket_close_round') {
$brackets_level--;
if ($seen_references) {
$seen_references = false;
}
if ($seen_create_table && $brackets_level == 0) {
$in_create_table_fields = false;
}
}
if ($arr[$i]['type'] == 'alpha_columnAttrib') {
$upper_data = mb_strtoupper($arr[$i]['data']);
if ($seen_create_table && $in_create_table_fields) {
if ($upper_data == 'DEFAULT') {
$seen_default = true;
$create_table_fields[$current_identifier]['default_value'] = $arr[$i + 1]['data'];
}
}
}
/**
* @see @todo 2005-10-16 note: the "or" part here is a workaround for a bug
*/
if ($arr[$i]['type'] == 'alpha_columnType' || $arr[$i]['type'] == 'alpha_functionName' && $seen_create_table) {
$upper_data = mb_strtoupper($arr[$i]['data']);
if ($seen_create_table && $in_create_table_fields && isset($current_identifier)) {
$create_table_fields[$current_identifier]['type'] = $upper_data;
if ($upper_data == 'TIMESTAMP') {
$arr[$i]['type'] = 'alpha_columnType';
$in_timestamp_options = true;
} else {
$in_timestamp_options = false;
if ($upper_data == 'CHAR') {
$arr[$i]['type'] = 'alpha_columnType';
}
}
}
}
if ($arr[$i]['type'] == 'quote_backtick' || $arr[$i]['type'] == 'alpha_identifier') {
if ($arr[$i]['type'] == 'quote_backtick') {
// remove backquotes
$identifier = PMA_Util::unQuote($arr[$i]['data']);
} else {
$identifier = $arr[$i]['data'];
}
if ($seen_create_table && $in_create_table_fields) {
$current_identifier = $identifier;
// we set this one even for non TIMESTAMP type
$create_table_fields[$current_identifier]['timestamp_not_null'] = false;
}
if ($seen_constraint) {
$foreign[$foreign_key_number]['constraint'] = $identifier;
}
if ($seen_foreign && $brackets_level > 0) {
$foreign[$foreign_key_number]['index_list'][] = $identifier;
}
if ($seen_references) {
if ($seen_alter && $brackets_level > 0) {
$foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
// here, the first bracket level corresponds to the
// bracket of CREATE TABLE
// so if we are on level 2, it must be the index list
// of the foreign key REFERENCES
} elseif ($brackets_level > 1) {
$foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
} elseif ($arr[$i + 1]['type'] == 'punct_qualifier') {
// identifier is `db`.`table`
// the first pass will pick the db name
// the next pass will pick the table name
$foreign[$foreign_key_number]['ref_db_name'] = $identifier;
} else {
// identifier is `table`
$foreign[$foreign_key_number]['ref_table_name'] = $identifier;
}
}
}
}
// end for $i (loop #3)
// Fill the $subresult array
if (isset($create_table_fields)) {
$subresult['create_table_fields'] = $create_table_fields;
}
if (isset($foreign)) {
$subresult['foreign_keys'] = $foreign;
}
if (isset($select_expr_clause)) {
$subresult['select_expr_clause'] = $select_expr_clause;
}
if (isset($from_clause)) {
$subresult['from_clause'] = $from_clause;
}
if (isset($group_by_clause)) {
$subresult['group_by_clause'] = $group_by_clause;
}
if (isset($order_by_clause)) {
$subresult['order_by_clause'] = $order_by_clause;
}
if (isset($having_clause)) {
$subresult['having_clause'] = $having_clause;
}
if (isset($limit_clause)) {
$subresult['limit_clause'] = $limit_clause;
}
if (isset($where_clause)) {
$subresult['where_clause'] = $where_clause;
}
if (isset($unsorted_query) && !empty($unsorted_query)) {
$subresult['unsorted_query'] = $unsorted_query;
}
if (isset($where_clause_identifiers)) {
$subresult['where_clause_identifiers'] = $where_clause_identifiers;
}
if (isset($position_of_first_select)) {
$subresult['position_of_first_select'] = $position_of_first_select;
$subresult['section_before_limit'] = $section_before_limit;
$subresult['section_after_limit'] = $section_after_limit;
}
// They are naughty and didn't have a trailing semi-colon,
// then still handle it properly
if ($subresult['querytype'] != '') {
$result[] = $subresult;
}
return $result;
}
/**
* sets privilege information extracted from SHOW GRANTS result
*
* Detection for some CREATE privilege.
*
* Since MySQL 4.1.2, we can easily detect current user's grants using $userlink
* (no control user needed) and we don't have to try any other method for
* detection
*
* @todo fix to get really all privileges, not only explicitly defined for this user
* from MySQL manual: (http://dev.mysql.com/doc/refman/5.0/en/show-grants.html)
* SHOW GRANTS displays only the privileges granted explicitly to the named
* account. Other privileges might be available to the account, but they are not
* displayed. For example, if an anonymous account exists, the named account
* might be able to use its privileges, but SHOW GRANTS will not display them.
*
* @return void
*/
function PMA_analyseShowGrant()
{
if (PMA_Util::cacheExists('is_create_db_priv')) {
$GLOBALS['is_create_db_priv'] = PMA_Util::cacheGet('is_create_db_priv');
$GLOBALS['is_reload_priv'] = PMA_Util::cacheGet('is_reload_priv');
$GLOBALS['db_to_create'] = PMA_Util::cacheGet('db_to_create');
$GLOBALS['dbs_where_create_table_allowed'] = PMA_Util::cacheGet('dbs_where_create_table_allowed');
$GLOBALS['dbs_to_test'] = PMA_Util::cacheGet('dbs_to_test');
return;
}
// defaults
$GLOBALS['is_create_db_priv'] = false;
$GLOBALS['is_reload_priv'] = false;
$GLOBALS['db_to_create'] = '';
$GLOBALS['dbs_where_create_table_allowed'] = array();
$GLOBALS['dbs_to_test'] = $GLOBALS['dbi']->getSystemSchemas();
$rs_usr = $GLOBALS['dbi']->tryQuery('SHOW GRANTS');
if (!$rs_usr) {
return;
}
$re0 = '(^|(\\\\\\\\)+|[^\\\\])';
// non-escaped wildcards
$re1 = '(^|[^\\\\])(\\\\)+';
// escaped wildcards
while ($row = $GLOBALS['dbi']->fetchRow($rs_usr)) {
// extract db from GRANT ... ON *.* or GRANT ... ON db.*
$db_name_offset = mb_strpos($row[0], ' ON ') + 4;
$show_grants_dbname = mb_substr($row[0], $db_name_offset, mb_strpos($row[0], '.', $db_name_offset) - $db_name_offset);
$show_grants_dbname = PMA_Util::unQuote($show_grants_dbname, '`');
$show_grants_str = mb_substr($row[0], 6, mb_strpos($row[0], ' ON ') - 6);
if ($show_grants_dbname == '*') {
if ($show_grants_str != 'USAGE') {
$GLOBALS['dbs_to_test'] = false;
}
} elseif ($GLOBALS['dbs_to_test'] !== false) {
$GLOBALS['dbs_to_test'][] = $show_grants_dbname;
}
if ($show_grants_str == 'RELOAD') {
$GLOBALS['is_reload_priv'] = true;
}
/**
* @todo if we find CREATE VIEW but not CREATE, do not offer
* the create database dialog box
*/
if ($show_grants_str == 'ALL' || $show_grants_str == 'ALL PRIVILEGES' || $show_grants_str == 'CREATE' || strpos($show_grants_str, 'CREATE,') !== false) {
if ($show_grants_dbname == '*') {
// a global CREATE privilege
$GLOBALS['is_create_db_priv'] = true;
$GLOBALS['is_reload_priv'] = true;
$GLOBALS['db_to_create'] = '';
$GLOBALS['dbs_where_create_table_allowed'][] = '*';
// @todo we should not break here, cause GRANT ALL *.*
// could be revoked by a later rule like GRANT SELECT ON db.*
break;
} else {
// this array may contain wildcards
$GLOBALS['dbs_where_create_table_allowed'][] = $show_grants_dbname;
$dbname_to_test = PMA_Util::backquote($show_grants_dbname);
if ($GLOBALS['is_create_db_priv']) {
// no need for any more tests if we already know this
continue;
}
// does this db exist?
if (preg_match('/' . $re0 . '%|_/', $show_grants_dbname) && !preg_match('/\\\\%|\\\\_/', $show_grants_dbname) || !$GLOBALS['dbi']->tryQuery('USE ' . preg_replace('/' . $re1 . '(%|_)/', '\\1\\3', $dbname_to_test)) && mb_substr($GLOBALS['dbi']->getError(), 1, 4) != 1044) {
/**
* Do not handle the underscore wildcard
* (this case must be rare anyway)
*/
$GLOBALS['db_to_create'] = preg_replace('/' . $re0 . '%/', '\\1', $show_grants_dbname);
$GLOBALS['db_to_create'] = preg_replace('/' . $re1 . '(%|_)/', '\\1\\3', $GLOBALS['db_to_create']);
$GLOBALS['is_create_db_priv'] = true;
/**
* @todo collect $GLOBALS['db_to_create'] into an array,
* to display a drop-down in the "Create database" dialog
*/
// we don't break, we want all possible databases
//break;
}
// end if
}
// end elseif
}
// end if
}
// end while
$GLOBALS['dbi']->freeResult($rs_usr);
// must also cacheUnset() them in
// libraries/plugins/auth/AuthenticationCookie.class.php
PMA_Util::cacheSet('is_create_db_priv', $GLOBALS['is_create_db_priv']);
PMA_Util::cacheSet('is_reload_priv', $GLOBALS['is_reload_priv']);
PMA_Util::cacheSet('db_to_create', $GLOBALS['db_to_create']);
PMA_Util::cacheSet('dbs_where_create_table_allowed', $GLOBALS['dbs_where_create_table_allowed']);
PMA_Util::cacheSet('dbs_to_test', $GLOBALS['dbs_to_test']);
}
/**
* Checks if a table is 'InnoDB' or not.
*
* @param string $table Table details
*
* @return bool
*/
function PMA_isTableTransactional($table)
{
$table = explode('.', $table);
if (count($table) == 2) {
$db = PMA_Util::unQuote($table[0]);
$table = PMA_Util::unQuote($table[1]);
} else {
$db = $GLOBALS['db'];
$table = PMA_Util::unQuote($table[0]);
}
// Query to check if table exists.
$check_table_query = 'SELECT * FROM ' . PMA_Util::backquote($db) . '.' . PMA_Util::backquote($table) . ' ' . 'LIMIT 1';
$result = $GLOBALS['dbi']->tryQuery($check_table_query);
if (!$result) {
return false;
}
// List of Transactional Engines.
$transactional_engines = array('INNODB', 'FALCON', 'NDB', 'INFINIDB', 'TOKUDB', 'XTRADB', 'SEQUENCE', 'BDB');
// Query to check if table is 'Transactional'.
$check_query = 'SELECT `ENGINE` FROM `information_schema`.`tables` ' . 'WHERE `table_name` = "' . $table . '" ' . 'AND `table_schema` = "' . $db . '" ' . 'AND UPPER(`engine`) IN ("' . implode('", "', $transactional_engines) . '")';
$result = $GLOBALS['dbi']->tryQuery($check_query);
if ($GLOBALS['dbi']->numRows($result) == 1) {
return true;
} else {
return false;
}
}
/**
* replaces db/table/column names with their aliases
*
* @param string $sql_query SQL query in which aliases are to be substituted
* @param array $aliases Alias information for db/table/column
* @param string $db the database name
* @param string $table the tablename
* @param string &$flag the flag denoting whether any replacement was done
*
* @return string query replaced with aliases
*/
public function replaceWithAliases($sql_query, $aliases, $db, $table = '', &$flag = null)
{
$flag = false;
// Return original sql query if no aliases are provided.
if (!is_array($aliases) || empty($aliases) || empty($sql_query)) {
return $sql_query;
}
$supported_query_types = array('CREATE' => true);
$supported_query_ons = array('TABLE' => true, 'VIEW' => true, 'TRIGGER' => true, 'FUNCTION' => true, 'PROCEDURE' => true);
$identifier_types = array('alpha_identifier', 'quote_backtick');
$query_type = '';
$query_on = '';
// Adjustment value for each pos value
// of token after replacement
$offset = 0;
$open_braces = 0;
$in_create_table_fields = false;
// flag to force end query parsing
$query_end = false;
// Convert all line feeds to Unix style
$sql_query = str_replace("\r\n", "\n", $sql_query);
$sql_query = str_replace("\r", "\n", $sql_query);
$tokens = PMA_SQP_parse($sql_query);
$ref_seen = false;
$ref_table_seen = false;
$old_table = $table;
$on_seen = false;
$size = $tokens['len'];
for ($i = 0; $i < $size && !$query_end; $i++) {
$type = $tokens[$i]['type'];
$data = $tokens[$i]['data'];
$data_next = isset($tokens[$i + 1]['data']) ? $tokens[$i + 1]['data'] : '';
$data_prev = $i > 0 ? $tokens[$i - 1]['data'] : '';
$d_unq = PMA_Util::unQuote($data);
$d_unq_next = PMA_Util::unQuote($data_next);
$d_unq_prev = PMA_Util::unQuote($data_prev);
$d_upper = mb_strtoupper($d_unq);
$d_upper_next = mb_strtoupper($d_unq_next);
$d_upper_prev = mb_strtoupper($d_unq_prev);
$pos = $tokens[$i]['pos'] + $offset;
if ($type === 'alpha_reservedWord') {
if ($query_type === '' && !empty($supported_query_types[$d_upper])) {
$query_type = $d_upper;
} elseif ($query_on === '' && !empty($supported_query_ons[$d_upper])) {
$query_on = $d_upper;
}
}
// CREATE TABLE - Alias replacement
if ($query_type === 'CREATE' && $query_on === 'TABLE') {
// replace create table name
if (!$in_create_table_fields && in_array($type, $identifier_types) && !empty($aliases[$db]['tables'][$table]['alias'])) {
$sql_query = $this->substituteAlias($sql_query, $data, $aliases[$db]['tables'][$table]['alias'], $pos, $offset);
$flag = true;
} elseif ($type === 'punct_bracket_open_round') {
// CREATE TABLE fields started
if (!$in_create_table_fields) {
$in_create_table_fields = true;
}
$open_braces++;
} elseif ($type === 'punct_bracket_close_round') {
// end our parsing after last )
// no columns appear after that
if ($in_create_table_fields && $open_braces === 0) {
$query_end = true;
}
// End of Foreign key reference
if ($ref_seen) {
$ref_seen = $ref_table_seen = false;
$table = $old_table;
}
$open_braces--;
// handles Foreign key references
} elseif ($type === 'alpha_reservedWord' && $d_upper === 'REFERENCES') {
$ref_seen = true;
} elseif (in_array($type, $identifier_types) && $ref_seen === true && !$ref_table_seen) {
$table = $d_unq;
$ref_table_seen = true;
if (!empty($aliases[$db]['tables'][$table]['alias'])) {
$sql_query = $this->substituteAlias($sql_query, $data, $aliases[$db]['tables'][$table]['alias'], $pos, $offset);
$flag = true;
}
// Replace column names
} elseif (in_array($type, $identifier_types) && !empty($aliases[$db]['tables'][$table]['columns'][$d_unq])) {
$sql_query = $this->substituteAlias($sql_query, $data, $aliases[$db]['tables'][$table]['columns'][$d_unq], $pos, $offset);
$flag = true;
}
// CREATE TRIGGER - Alias replacement
} elseif ($query_type === 'CREATE' && $query_on === 'TRIGGER') {
// Skip till 'ON' in encountered
if (!$on_seen && $type === 'alpha_reservedWord' && $d_upper === 'ON') {
$on_seen = true;
} elseif ($on_seen && in_array($type, $identifier_types)) {
if (!$ref_table_seen && !empty($aliases[$db]['tables'][$d_unq]['alias'])) {
$ref_table_seen = true;
$sql_query = $this->substituteAlias($sql_query, $data, $aliases[$db]['tables'][$d_unq]['alias'], $pos, $offset);
$flag = true;
} else {
// search for identifier alias
$alias = $this->getAlias($aliases, $d_unq);
if (!empty($alias)) {
$sql_query = $this->substituteAlias($sql_query, $data, $alias, $pos, $offset);
$flag = true;
}
}
}
// CREATE PROCEDURE|FUNCTION|VIEW - Alias replacement
} elseif ($query_type === 'CREATE' && ($query_on === 'FUNCTION' || $query_on === 'PROCEDURE' || $query_on === 'VIEW')) {
// LANGUAGE SQL | (READS|MODIFIES) SQL DATA
// characteristics are skipped
if ($type === 'alpha_identifier' && ($d_upper === 'LANGUAGE' && $d_upper_next === 'SQL' || $d_upper === 'DATA' && $d_upper_prev === 'SQL')) {
continue;
// No need to process further in case of VIEW
// when 'WITH' keyword has been detected
} elseif ($query_on === 'VIEW' && $type === 'alpha_reservedWord' && $d_upper === 'WITH') {
$query_end = true;
} elseif (in_array($type, $identifier_types)) {
// search for identifier alias
$alias = $this->getAlias($aliases, $d_unq);
if (!empty($alias)) {
$sql_query = $this->substituteAlias($sql_query, $data, $alias, $pos, $offset);
$flag = true;
}
}
}
}
return $sql_query;
}
/**
* PMA_Util::unQuote test with chosen quote
*
* @param string $param String
* @param string $expected Expected output
*
* @return void
*
* @dataProvider unQuoteSelectedProvider
*/
public function testUnQuoteSelectedChar($param, $expected)
{
$this->assertEquals($expected, PMA_Util::unQuote($param, '"'));
}
/**
* Looks for the presence of USE to possibly change current db
*
* @param string $buffer buffer to examine
* @param string $db current db
* @param bool $reload reload
*
* @return array (current or new db, whether to reload)
* @access public
*/
function PMA_lookForUse($buffer, $db, $reload)
{
if (preg_match('@^[\\s]*USE[[:space:]]+([\\S]+)@i', $buffer, $match)) {
$db = trim($match[1]);
$db = trim($db, ';');
// for example, USE abc;
// $db must not contain the escape characters generated by backquote()
// ( used in PMA_buildSQL() as: backquote($db_name), and then called
// in PMA_importRunQuery() which in turn calls PMA_lookForUse() )
$db = PMA_Util::unQuote($db);
$reload = true;
}
return array($db, $reload);
}
