/** * Displays a table of results returned by a SQL query. * This function is called by the "sql.php" script. * * @param integer the link id associated to the query which results have * to be displayed * @param array the display mode * @param array the analyzed query * * @uses $_SESSION['tmp_user_values']['pos'] * @global string $db the database name * @global string $table the table name * @global string $goto the URL to go back in case of errors * @global string $sql_query the current SQL query * @global integer $num_rows the total number of rows returned by the * SQL query * @global integer $unlim_num_rows the total number of rows returned by the * SQL query without any programmatically * appended "LIMIT" clause * @global array $fields_meta the list of fields properties * @global integer $fields_cnt the total number of fields returned by * the SQL query * @global array $vertical_display informations used with vertical display * mode * @global array $highlight_columns column names to highlight * @global array $cfgRelation the relation settings * * @access private * * @see PMA_showMessage(), PMA_setDisplayMode(), * PMA_displayTableNavigation(), PMA_displayTableHeaders(), * PMA_displayTableBody(), PMA_displayResultsOperations() */ function PMA_displayTable(&$dt_result, &$the_disp_mode, $analyzed_sql) { global $db, $table, $goto; global $sql_query, $num_rows, $unlim_num_rows, $fields_meta, $fields_cnt; global $vertical_display, $highlight_columns; global $cfgRelation; global $showtable; // why was this called here? (already called from sql.php) //PMA_displayTable_checkConfigParams(); /** * @todo move this to a central place * @todo for other future table types */ $is_innodb = isset($showtable['Type']) && $showtable['Type'] == 'InnoDB'; if ($is_innodb && !isset($analyzed_sql[0]['queryflags']['union']) && !isset($analyzed_sql[0]['table_ref'][1]['table_name']) && (empty($analyzed_sql[0]['where_clause']) || $analyzed_sql[0]['where_clause'] == '1 ')) { // "j u s t b r o w s i n g" $pre_count = '~'; $after_count = PMA_showHint(PMA_sanitize($GLOBALS['strApproximateCount']), true); } else { $pre_count = ''; $after_count = ''; } // 1. ----- Prepares the work ----- // 1.1 Gets the informations about which functionalities should be // displayed $total = ''; $is_display = PMA_setDisplayMode($the_disp_mode, $total); // 1.2 Defines offsets for the next and previous pages if ($is_display['nav_bar'] == '1') { if ($_SESSION['tmp_user_values']['max_rows'] == 'all') { $pos_next = 0; $pos_prev = 0; } else { $pos_next = $_SESSION['tmp_user_values']['pos'] + $_SESSION['tmp_user_values']['max_rows']; $pos_prev = $_SESSION['tmp_user_values']['pos'] - $_SESSION['tmp_user_values']['max_rows']; if ($pos_prev < 0) { $pos_prev = 0; } } } // end if // 1.3 Find the sort expression // we need $sort_expression and $sort_expression_nodirection // even if there are many table references if (!empty($analyzed_sql[0]['order_by_clause'])) { $sort_expression = trim(str_replace(' ', ' ', $analyzed_sql[0]['order_by_clause'])); /** * Get rid of ASC|DESC */ preg_match('@(.*)([[:space:]]*(ASC|DESC))@si', $sort_expression, $matches); $sort_expression_nodirection = isset($matches[1]) ? trim($matches[1]) : $sort_expression; $sort_direction = isset($matches[2]) ? trim($matches[2]) : ''; unset($matches); } else { $sort_expression = $sort_expression_nodirection = $sort_direction = ''; } // 1.4 Prepares display of first and last value of the sorted column if (!empty($sort_expression_nodirection)) { list($sort_table, $sort_column) = explode('.', $sort_expression_nodirection); $sort_table = PMA_unQuote($sort_table); $sort_column = PMA_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) { // fetch first row of the result set $row = PMA_DBI_fetch_row($dt_result); $column_for_first_row = substr($row[$sorted_column_index], 0, $GLOBALS['cfg']['LimitChars']); // fetch last row of the result set PMA_DBI_data_seek($dt_result, $num_rows - 1); $row = PMA_DBI_fetch_row($dt_result); $column_for_last_row = substr($row[$sorted_column_index], 0, $GLOBALS['cfg']['LimitChars']); // reset to first row for the loop in PMA_displayTableBody() PMA_DBI_data_seek($dt_result, 0); // we could also use here $sort_expression_nodirection $sorted_column_message = ' [' . htmlspecialchars($sort_column) . ': <strong>' . htmlspecialchars($column_for_first_row) . ' - ' . htmlspecialchars($column_for_last_row) . '</strong>]'; unset($row, $column_for_first_row, $column_for_last_row); } unset($sorted_column_index, $sort_table, $sort_column); } // 2. ----- Displays the top of the page ----- // 2.1 Displays a messages with position informations if ($is_display['nav_bar'] == '1' && isset($pos_next)) { if (isset($unlim_num_rows) && $unlim_num_rows != $total) { $selectstring = ', ' . $unlim_num_rows . ' ' . $GLOBALS['strSelectNumRows']; } else { $selectstring = ''; } $last_shown_rec = $_SESSION['tmp_user_values']['max_rows'] == 'all' || $pos_next > $total ? $total - 1 : $pos_next - 1; if (PMA_Table::isView($db, $table) && $total == $GLOBALS['cfg']['MaxExactCountViews']) { $message = PMA_Message::notice('strViewHasAtLeast'); $message->addParam('[a@./Documentation.html#cfg_MaxExactCount@_blank]'); $message->addParam('[/a]'); $message_view_warning = PMA_showHint($message); } else { $message_view_warning = false; } $message = PMA_Message::success('strShowingRecords'); $message->addMessage($_SESSION['tmp_user_values']['pos']); if ($message_view_warning) { $message->addMessage('...', ' - '); $message->addMessage($message_view_warning); $message->addMessage('('); } else { $message->addMessage($last_shown_rec, ' - '); $message->addMessage($pre_count . PMA_formatNumber($total, 0) . $after_count, ' ('); $message->addString('strTotal'); $message->addMessage($selectstring, ''); $message->addMessage(', ', ''); } $messagge_qt = PMA_Message::notice('strQueryTime'); $messagge_qt->addParam($GLOBALS['querytime']); $message->addMessage($messagge_qt, ''); $message->addMessage(')', ''); $message->addMessage(isset($sorted_column_message) ? $sorted_column_message : '', ''); PMA_showMessage($message, $sql_query, 'success'); } elseif (!isset($GLOBALS['printview']) || $GLOBALS['printview'] != '1') { PMA_showMessage($GLOBALS['strSuccess'], $sql_query, 'success'); } // 2.3 Displays the navigation bars if (!strlen($table)) { if (isset($analyzed_sql[0]['query_type']) && $analyzed_sql[0]['query_type'] == 'SELECT') { // table does not always contain a real table name, // for example in MySQL 5.0.x, the query SHOW STATUS // returns STATUS as a table name $table = $fields_meta[0]->table; } else { $table = ''; } } if ($is_display['nav_bar'] == '1') { PMA_displayTableNavigation($pos_next, $pos_prev, $sql_query, 'top_direction_dropdown'); echo "\n"; } elseif (!isset($GLOBALS['printview']) || $GLOBALS['printview'] != '1') { echo "\n" . '<br /><br />' . "\n"; } // 2b ----- Get field references from Database ----- // (see the 'relation' configuration variable) // loic1, 2002-03-02: extended to php3 // initialize map $map = array(); // find tables $target = array(); if (isset($analyzed_sql[0]['table_ref']) && is_array($analyzed_sql[0]['table_ref'])) { foreach ($analyzed_sql[0]['table_ref'] as $table_ref_position => $table_ref) { $target[] = $analyzed_sql[0]['table_ref'][$table_ref_position]['table_true_name']; } } $tabs = '(\'' . join('\',\'', $target) . '\')'; if ($cfgRelation['displaywork']) { if (!strlen($table)) { $exist_rel = false; } else { $exist_rel = PMA_getForeigners($db, $table, '', 'both'); if ($exist_rel) { foreach ($exist_rel as $master_field => $rel) { $display_field = PMA_getDisplayField($rel['foreign_db'], $rel['foreign_table']); $map[$master_field] = array($rel['foreign_table'], $rel['foreign_field'], $display_field, $rel['foreign_db']); } // end while } // end if } // end if } // end if // end 2b // 3. ----- Displays the results table ----- PMA_displayTableHeaders($is_display, $fields_meta, $fields_cnt, $analyzed_sql, $sort_expression, $sort_expression_nodirection, $sort_direction); $url_query = ''; echo '<tbody>' . "\n"; $clause_is_unique = PMA_displayTableBody($dt_result, $is_display, $map, $analyzed_sql); // vertical output case if ($_SESSION['tmp_user_values']['disp_direction'] == 'vertical') { PMA_displayVerticalTable(); } // end if unset($vertical_display); echo '</tbody>' . "\n"; ?> </table> <?php // 4. ----- Displays the link for multi-fields edit and delete if ($is_display['del_lnk'] == 'dr' && $is_display['del_lnk'] != 'kp') { $delete_text = $is_display['del_lnk'] == 'dr' ? $GLOBALS['strDelete'] : $GLOBALS['strKill']; $_url_params = array('db' => $db, 'table' => $table, 'sql_query' => $sql_query, 'goto' => $goto); $uncheckall_url = 'sql.php' . PMA_generate_common_url($_url_params); $_url_params['checkall'] = '1'; $checkall_url = 'sql.php' . PMA_generate_common_url($_url_params); if ($_SESSION['tmp_user_values']['disp_direction'] == 'vertical') { $checkall_params['onclick'] = 'if (setCheckboxes(\'rowsDeleteForm\', true)) return false;'; $uncheckall_params['onclick'] = 'if (setCheckboxes(\'rowsDeleteForm\', false)) return false;'; } else { $checkall_params['onclick'] = 'if (markAllRows(\'rowsDeleteForm\')) return false;'; $uncheckall_params['onclick'] = 'if (unMarkAllRows(\'rowsDeleteForm\')) return false;'; } $checkall_link = PMA_linkOrButton($checkall_url, $GLOBALS['strCheckAll'], $checkall_params, false); $uncheckall_link = PMA_linkOrButton($uncheckall_url, $GLOBALS['strUncheckAll'], $uncheckall_params, false); if ($_SESSION['tmp_user_values']['disp_direction'] != 'vertical') { echo '<img class="selectallarrow" width="38" height="22"' . ' src="' . $GLOBALS['pmaThemeImage'] . 'arrow_' . $GLOBALS['text_dir'] . '.png' . '"' . ' alt="' . $GLOBALS['strWithChecked'] . '" />'; } echo $checkall_link . "\n" . ' / ' . "\n" . $uncheckall_link . "\n" . '<i>' . $GLOBALS['strWithChecked'] . '</i>' . "\n"; PMA_buttonOrImage('submit_mult', 'mult_submit', 'submit_mult_change', $GLOBALS['strChange'], 'b_edit.png'); PMA_buttonOrImage('submit_mult', 'mult_submit', 'submit_mult_delete', $delete_text, 'b_drop.png'); if ($analyzed_sql[0]['querytype'] == 'SELECT') { PMA_buttonOrImage('submit_mult', 'mult_submit', 'submit_mult_export', $GLOBALS['strExport'], 'b_tblexport.png'); } echo "\n"; echo '<input type="hidden" name="sql_query"' . ' value="' . htmlspecialchars($sql_query) . '" />' . "\n"; echo '<input type="hidden" name="url_query"' . ' value="' . $GLOBALS['url_query'] . '" />' . "\n"; echo '<input type="hidden" name="clause_is_unique"' . ' value="' . $clause_is_unique . '" />' . "\n"; echo '</form>' . "\n"; } // 5. ----- Displays the navigation bar at the bottom if required ----- if ($is_display['nav_bar'] == '1') { echo '<br />' . "\n"; PMA_displayTableNavigation($pos_next, $pos_prev, $sql_query, 'bottom_direction_dropdown'); } elseif (!isset($GLOBALS['printview']) || $GLOBALS['printview'] != '1') { echo "\n" . '<br /><br />' . "\n"; } // 6. ----- Displays "Query results operations" if (!isset($GLOBALS['printview']) || $GLOBALS['printview'] != '1') { PMA_displayResultsOperations($the_disp_mode, $analyzed_sql); } }
/** * 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_unQuote($parsed_query[$i]['data']); } else { if ($fetching == true && $parsed_query[$i]['type'] == 'punct_user') { $retval .= $parsed_query[$i]['data']; } } } } } return $retval; }
/** * 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. * * @uses $_SESSION['is_create_db_priv'] for caching * @uses $_SESSION['is_process_priv'] for caching * @uses $_SESSION['is_reload_priv'] for caching * @uses $_SESSION['db_to_create'] for caching * @uses $_SESSION['dbs_where_create_table_allowed'] for caching * @uses $GLOBALS['is_create_db_priv'] to set it * @uses $GLOBALS['is_process_priv'] to set it * @uses $GLOBALS['is_reload_priv'] to set it * @uses $GLOBALS['db_to_create'] to set it * @uses $GLOBALS['dbs_where_create_table_allowed'] to set it * @uses $GLOBALS['server'] * @uses PMA_DBI_try_query() * @uses PMA_DBI_fetch_row() * @uses PMA_DBI_free_result() * @uses PMA_DBI_getError() * @uses PMA_unQuote() * @uses PMA_backquote() * @uses preg_match() * @uses preg_replace() * @uses substr() * @uses strpos() */ function PMA_analyseShowGrant() { if (PMA_cacheExists('is_create_db_priv', true)) { $GLOBALS['is_create_db_priv'] = PMA_cacheGet('is_create_db_priv', true); $GLOBALS['is_process_priv'] = PMA_cacheGet('is_process_priv', true); $GLOBALS['is_reload_priv'] = PMA_cacheGet('is_reload_priv', true); $GLOBALS['db_to_create'] = PMA_cacheGet('db_to_create', true); $GLOBALS['dbs_where_create_table_allowed'] = PMA_cacheGet('dbs_where_create_table_allowed', true); return; } // defaults $GLOBALS['is_create_db_priv'] = false; $GLOBALS['is_process_priv'] = true; $GLOBALS['is_reload_priv'] = false; $GLOBALS['db_to_create'] = ''; $GLOBALS['dbs_where_create_table_allowed'] = array(); $rs_usr = PMA_DBI_try_query('SHOW GRANTS'); if (!$rs_usr) { return; } $re0 = '(^|(\\\\\\\\)+|[^\\\\])'; // non-escaped wildcards $re1 = '(^|[^\\\\])(\\\\)+'; // escaped wildcards while ($row = PMA_DBI_fetch_row($rs_usr)) { // extract db from GRANT ... ON *.* or GRANT ... ON db.* $db_name_offset = strpos($row[0], ' ON ') + 4; $show_grants_dbname = substr($row[0], $db_name_offset, strpos($row[0], '.', $db_name_offset) - $db_name_offset); $show_grants_dbname = PMA_unQuote($show_grants_dbname, '`'); $show_grants_str = substr($row[0], 6, strpos($row[0], ' ON ') - 6); 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_backquote($show_grants_dbname); if ($GLOBALS['is_create_db_priv']) { // no need for any more tests if we already know this continue; } if (preg_match('/' . $re0 . '%|_/', $show_grants_dbname) && !preg_match('/\\\\%|\\\\_/', $show_grants_dbname) || !PMA_DBI_try_query('USE ' . preg_replace('/' . $re1 . '(%|_)/', '\\1\\3', $dbname_to_test)) && substr(PMA_DBI_getError(), 1, 4) != 1044) { if ($GLOBALS['cfg']['SuggestDBName']) { $GLOBALS['db_to_create'] = preg_replace('/' . $re0 . '_/', '\\1?', $show_grants_dbname); $GLOBALS['db_to_create'] = preg_replace('/' . $re0 . '%/', '\\1...', $GLOBALS['db_to_create']); $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 new database" dialog */ // we don't break, we want all possible databases //break; } // end if } // end elseif } // end if } // end while PMA_DBI_free_result($rs_usr); // must also PMA_cacheUnset() them in libraries/auth/cookie.auth.lib.php PMA_cacheSet('is_create_db_priv', $GLOBALS['is_create_db_priv'], true); PMA_cacheSet('is_process_priv', $GLOBALS['is_process_priv'], true); PMA_cacheSet('is_reload_priv', $GLOBALS['is_reload_priv'], true); PMA_cacheSet('db_to_create', $GLOBALS['db_to_create'], true); PMA_cacheSet('dbs_where_create_table_allowed', $GLOBALS['dbs_where_create_table_allowed'], true); }
/** * Analyzes SQL queries * * @param array The SQL queries * * @return array The analyzed SQL queries * * @access public */ function PMA_SQP_analyze($arr) { if ($arr == array()) { return array(); } $result = array(); $size = $arr['len']; $subresult = array( 'querytype' => '', 'select_expr_clause'=> '', // the whole stuff between SELECT and FROM , except DISTINCT 'position_of_first_select' => '', // the array index '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 by lem9 * * 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 occurences 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']['need_confirm'] = 1; if the query needs confirmation * ['queryflags']['select_from'] = 1; if this is a real SELECT...FROM * ['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 * * query clauses * ------------- * * The select is splitted 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', 'GROUP', 'HAVING', 'LIMIT', 'LOCK', 'ORDER', 'PROCEDURE', 'UNION', 'WHERE' ); $words_ending_table_ref_cnt = 9; //count($words_ending_table_ref); $words_ending_clauses = array( 'FOR', 'LIMIT', 'LOCK', 'PROCEDURE', 'UNION' ); $words_ending_clauses_cnt = 5; //count($words_ending_clauses); // must be sorted $supported_query_types = array( 'SELECT' /* // Support for these additional query types will come later on. 'DELETE', 'INSERT', 'REPLACE', 'TRUNCATE', 'UPDATE' 'EXPLAIN', 'DESCRIBE', 'SHOW', 'CREATE', 'SET', 'ALTER' */ ); $supported_query_types_cnt = count($supported_query_types); // 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 = 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' // && $arr[$i]['forbidden'] == FALSE) { ) { // We don't know what type of query yet, so run this if ($subresult['querytype'] == '') { $subresult['querytype'] = strtoupper($arr[$i]['data']); } // end if (querytype was empty) // Check if we support this type of query if (!PMA_STR_binarySearchInArr($subresult['querytype'], $supported_query_types, $supported_query_types_cnt)) { // Skip ahead to the next one if we don't $seek_queryend = TRUE; continue; } // end if (query not supported) // upper once $upper_data = strtoupper($arr[$i]['data']); /** * @todo reset for each query? */ if ($upper_data == 'SELECT') { if ($number_of_brackets > 0) { $in_subquery = true; $seen_subquery = true; // 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 * * @todo avoid the pretty printing in color in this case */ $identifier = $arr[$i]['data']; break; case 'quote_backtick': case 'quote_double': case 'quote_single': $identifier = PMA_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 { $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 { $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) && 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) && 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) && 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 && PMA_STR_binarySearchInArr($upper_data, $words_ending_table_ref, $words_ending_table_ref_cnt))) { $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; for ($i = 0; $i < $size; $i++) { //DEBUG echo "Loop2 <strong>" . $arr[$i]['data'] . "</strong> (" . $arr[$i]['type'] . ")<br />"; // need_confirm // // check for reserved words that will have to generate // a confirmation request later in sql.php // the cases are: // DROP TABLE // DROP DATABASE // ALTER TABLE... DROP // DELETE FROM... // // this code is not used for confirmations coming from functions.js 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 = 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 the first reserved word is DROP or DELETE, // we know this is a query that needs to be confirmed if ($first_reserved_word=='DROP' || $first_reserved_word == 'DELETE' || $first_reserved_word == 'TRUNCATE') { $subresult['queryflags']['need_confirm'] = 1; } if ($first_reserved_word=='SELECT'){ $position_of_first_select = $i; } } else { if ($upper_data == 'DROP' && $first_reserved_word == 'ALTER') { $subresult['queryflags']['need_confirm'] = 1; } } if ($upper_data == 'LIMIT' && ! $in_subquery) { $section_before_limit = 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; } /** * @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 = ''; } 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; } // 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; } 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 PMA_substr() to be charset-safe; otherwise // if the table name contains accents, the unsorted // query would be missing some characters. $unsorted_query = PMA_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 (PMA_STR_binarySearchInArr($upper_data, $words_ending_clauses, $words_ending_clauses_cnt)) { $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 = 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 = ''; } 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++) { // DEBUG echo "Loop 3 <strong>" . $arr[$i]['data'] . "</strong> " . $arr[$i]['type'] . "<br />"; if ($arr[$i]['type'] == 'alpha_reservedWord') { $upper_data = strtoupper($arr[$i]['data']); if ($upper_data == 'NOT' && $in_timestamp_options) { $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 = strtoupper($arr[$i+1]['data']); if ($second_upper_data == 'DELETE') { $clause = 'on_delete'; } if ($second_upper_data == 'UPDATE') { $clause = 'on_update'; } if (isset($clause) && ($arr[$i+2]['type'] == 'alpha_reservedWord' // 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) || ($arr[$i+2]['type'] == 'alpha_identifier' && strtoupper($arr[$i+2]['data'])=='NO')) ) { $third_upper_data = 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 . '_' . 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)) { $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 = 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 = 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_unQuote($arr[$i]['data']); } else { $identifier = $arr[$i]['data']; } if ($seen_create_table && $in_create_table_fields) { $current_identifier = $identifier; // warning: 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; } // end of the "PMA_SQP_analyze()" function
/** * unQuote test with chosen quote * @dataProvider unQuoteSelectedProvider */ public function testUnQuoteSelectedChar($param, $expected) { $this->assertEquals($expected, PMA_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 PMA_backquote() // ( used in PMA_buildSQL() as: PMA_backquote($db_name), and then called // in PMA_importRunQuery() which in turn calls PMA_lookForUse() ) $db = PMA_unQuote($db); $reload = true; } return array($db, $reload); }