/** * 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); }