3 // vim: expandtab sw=4 ts=4 sts=4:
5 /** SQL Parser Functions for phpMyAdmin
7 * Copyright 2002 Robin Johnson <robbat2@users.sourceforge.net>
8 * http://www.orbis-terrarum.net/?l=people.robbat2
10 * These functions define an SQL parser system, capable of understanding and
11 * extracting data from a MySQL type SQL query.
13 * The basic procedure for using the new SQL parser:
14 * On any page that needs to extract data from a query or to pretty-print a
15 * query, you need code like this up at the top:
17 * ($sql contains the query)
18 * $parsed_sql = PMA_SQP_parse($sql);
20 * If you want to extract data from it then, you just need to run
21 * $sql_info = PMA_SQP_analyze($parsed_sql);
23 * lem9: See comments in PMA_SQP_analyze for the returned info
26 * If you want a pretty-printed version of the query, do:
27 * $string = PMA_SQP_formatHtml($parsed_sql);
28 * (note that that you need to have syntax.css.php included somehow in your
29 * page for it to work, I recommend '<link rel="stylesheet" type="text/css"
30 * href="syntax.css.php" />' at the moment.)
35 * Minimum inclusion? (i.e. for the stylesheet builder)
37 if ( ! defined( 'PMA_MINIMUM_COMMON' ) ) {
39 * Include the string library as we use it heavily
41 require_once('./libraries/string.lib.php');
44 * Include data for the SQL Parser
46 require_once('./libraries/sqlparser.data.php');
47 require_once('./libraries/mysql_charsets.lib.php');
48 if (!isset($mysql_charsets)) {
49 $mysql_charsets = array();
50 $mysql_charsets_count = 0;
51 $mysql_collations_flat = array();
52 $mysql_collations_count = 0;
55 if (!defined('DEBUG_TIMING')) {
56 function PMA_SQP_arrayAdd(&$arr, $type, $data, &$arrsize)
58 $arr[] = array('type' => $type, 'data' => $data);
60 } // end of the "PMA_SQP_arrayAdd()" function
62 function PMA_SQP_arrayAdd(&$arr, $type, $data, &$arrsize)
67 $arr[] = array('type' => $type, 'data' => $data, 'time' => $t);
70 } // end of the "PMA_SQP_arrayAdd()" function
71 } // end if... else...
75 * Reset the error variable for the SQL parser
79 // Added, Robbat2 - 13 Janurary 2003, 2:59PM
80 function PMA_SQP_resetError()
82 global $SQP_errorString;
83 $SQP_errorString = '';
84 unset($SQP_errorString);
88 * Get the contents of the error variable for the SQL parser
90 * @return string Error string from SQL parser
94 // Added, Robbat2 - 13 Janurary 2003, 2:59PM
95 function PMA_SQP_getErrorString()
97 global $SQP_errorString;
98 return isset($SQP_errorString) ?
$SQP_errorString : '';
102 * Check if the SQL parser hit an error
104 * @return boolean error state
108 // Added, Robbat2 - 13 Janurary 2003, 2:59PM
109 function PMA_SQP_isError()
111 global $SQP_errorString;
112 return isset($SQP_errorString) && !empty($SQP_errorString);
116 * Set an error message for the system
118 * @param string The error message
119 * @param string The failing SQL query
122 * @scope SQL Parser internal
124 // Revised, Robbat2 - 13 Janurary 2003, 2:59PM
125 function PMA_SQP_throwError($message, $sql)
128 global $SQP_errorString;
129 $SQP_errorString = '<p>'.$GLOBALS['strSQLParserUserError'] . '</p>' . "\n"
131 . 'ERROR: ' . $message . "\n"
132 . 'SQL: ' . htmlspecialchars($sql) . "\n"
135 } // end of the "PMA_SQP_throwError()" function
139 * Do display the bug report
141 * @param string The error message
142 * @param string The failing SQL query
146 function PMA_SQP_bug($message, $sql)
148 global $SQP_errorString;
149 $debugstr = 'ERROR: ' . $message . "\n";
150 $debugstr .= 'SVN: $Id$' . "\n";
151 $debugstr .= 'MySQL: '.PMA_MYSQL_STR_VERSION
. "\n";
152 $debugstr .= 'USR OS, AGENT, VER: ' . PMA_USR_OS
. ' ' . PMA_USR_BROWSER_AGENT
. ' ' . PMA_USR_BROWSER_VER
. "\n";
153 $debugstr .= 'PMA: ' . PMA_VERSION
. "\n";
154 $debugstr .= 'PHP VER,OS: ' . PMA_PHP_STR_VERSION
. ' ' . PHP_OS
. "\n";
155 $debugstr .= 'LANG: ' . $GLOBALS['lang'] . "\n";
156 $debugstr .= 'SQL: ' . htmlspecialchars($sql);
158 $encodedstr = $debugstr;
159 if (@function_exists
('gzcompress')) {
160 $encodedstr = gzcompress($debugstr, 9);
162 $encodedstr = preg_replace("/(\015\012)|(\015)|(\012)/", '<br />' . "\n", chunk_split(base64_encode($encodedstr)));
164 $SQP_errorString .= $GLOBALS['strSQLParserBugMessage'] . '<br />' . "\n"
165 . '----' . $GLOBALS['strBeginCut'] . '----' . '<br />' . "\n"
167 . '----' . $GLOBALS['strEndCut'] . '----' . '<br />' . "\n";
169 $SQP_errorString .= '----' . $GLOBALS['strBeginRaw'] . '----<br />' . "\n"
173 . '----' . $GLOBALS['strEndRaw'] . '----<br />' . "\n";
175 } // end of the "PMA_SQP_bug()" function
179 * Parses the SQL queries
181 * @param string The SQL query list
183 * @return mixed Most of times, nothing...
185 * @global array The current PMA configuration
186 * @global array MySQL column attributes
187 * @global array MySQL reserved words
188 * @global array MySQL column types
189 * @global array MySQL function names
190 * @global integer MySQL column attributes count
191 * @global integer MySQL reserved words count
192 * @global integer MySQL column types count
193 * @global integer MySQL function names count
194 * @global array List of available character sets
195 * @global array List of available collations
196 * @global integer Character sets count
197 * @global integer Collations count
201 function PMA_SQP_parse($sql)
204 global $PMA_SQPdata_column_attrib, $PMA_SQPdata_reserved_word, $PMA_SQPdata_column_type, $PMA_SQPdata_function_name,
205 $PMA_SQPdata_column_attrib_cnt, $PMA_SQPdata_reserved_word_cnt, $PMA_SQPdata_column_type_cnt, $PMA_SQPdata_function_name_cnt;
206 global $mysql_charsets, $mysql_collations_flat, $mysql_charsets_count, $mysql_collations_count;
207 global $PMA_SQPdata_forbidden_word, $PMA_SQPdata_forbidden_word_cnt;
209 // rabus: Convert all line feeds to Unix style
210 $sql = str_replace("\r\n", "\n", $sql);
211 $sql = str_replace("\r", "\n", $sql);
213 $len = PMA_strlen($sql);
218 $sql_array = array();
219 $sql_array['raw'] = $sql;
222 $punct_queryend = ';';
223 $punct_qualifier = '.';
224 $punct_listsep = ',';
225 $punct_level_plus = '(';
226 $punct_level_minus = ')';
227 $digit_floatdecimal = '.';
229 $bracket_list = '()[]{}';
230 $allpunct_list = '-,;:!?/.^~\*&%+<=>|';
231 $allpunct_list_pair = array (
243 $allpunct_list_pair_size = 10; //count($allpunct_list_pair);
244 $quote_list = '\'"`';
247 while ($count2 < $len) {
248 $c = PMA_substr($sql, $count2, 1);
253 PMA_SQP_arrayAdd($sql_array, 'white_newline', '', $arraysize);
257 // Checks for white space
258 if (PMA_STR_isSpace($c)) {
263 // Checks for comment lines.
268 ||
(($count2 +
1 < $len) && ($c == '/') && (PMA_substr($sql, $count2 +
1, 1) == '*'))
269 ||
(($count2 +
2 == $len) && ($c == '-') && (PMA_substr($sql, $count2 +
1, 1) == '-'))
270 ||
(($count2 +
2 < $len) && ($c == '-') && (PMA_substr($sql, $count2 +
1, 1) == '-') && ((PMA_substr($sql, $count2 +
2, 1) <= ' ')))) {
279 $pos = $GLOBALS['PMA_strpos']($sql, "\n", $count2);
283 $pos = $GLOBALS['PMA_strpos']($sql, '*/', $count2);
289 $count2 = ($pos < $count2) ?
$len : $pos;
290 $str = PMA_substr($sql, $count1, $count2 - $count1);
291 PMA_SQP_arrayAdd($sql_array, 'comment_' . $type, $str, $arraysize);
295 // Checks for something inside quotation marks
296 if (PMA_STR_strInStr($c, $quote_list)) {
297 $startquotepos = $count2;
301 $escaped_escaped = FALSE;
306 $pos = $GLOBALS['PMA_strpos'](' ' . $sql, $quotetype, $oldpos +
1) - 1;
309 $debugstr = $GLOBALS['strSQPBugUnclosedQuote'] . ' @ ' . $startquotepos. "\n"
310 . 'STR: ' . htmlspecialchars($quotetype);
311 PMA_SQP_throwError($debugstr, $sql);
315 // If the quote is the first character, it can't be
316 // escaped, so don't do the rest of the code
321 // Checks for MySQL escaping using a \
322 // And checks for ANSI escaping using the $quotetype character
323 if (($pos < $len) && PMA_STR_charIsEscaped($sql, $pos)) {
326 } elseif (($pos +
1 < $len) && (PMA_substr($sql, $pos, 1) == $quotetype) && (PMA_substr($sql, $pos +
1, 1) == $quotetype)) {
332 } while ($len > $pos); // end do
337 switch ($quotetype) {
350 $data = PMA_substr($sql, $count1, $count2 - $count1);
351 PMA_SQP_arrayAdd($sql_array, $type, $data, $arraysize);
355 // Checks for brackets
356 if (PMA_STR_strInStr($c, $bracket_list)) {
357 // All bracket tokens are only one item long
360 if (PMA_STR_strInStr($c, '([{')) {
363 $type_type = 'close';
367 if (PMA_STR_strInStr($c, '()')) {
368 $type_style = 'round';
369 } elseif (PMA_STR_strInStr($c, '[]')) {
370 $type_style = 'square';
372 $type_style = 'curly';
375 $type = 'punct_bracket_' . $type_type . '_' . $type_style;
376 PMA_SQP_arrayAdd($sql_array, $type, $c, $arraysize);
380 // Checks for identifier (alpha or numeric)
381 if (PMA_STR_isSqlIdentifier($c, FALSE) ||
($c == '@') ||
($c == '.' && PMA_STR_isDigit(PMA_substr($sql, $count2 +
1, 1)))) {
385 * @todo a @ can also be present in expressions like
386 * FROM 'user'@'%' or TO 'user'@'%'
387 * in this case, the @ is wrongly marked as alpha_variable
390 $is_sql_variable = ($c == '@');
391 $is_digit = (!$is_sql_variable) && PMA_STR_isDigit($c);
392 $is_hex_digit = ($is_digit) && ($c == '.') && ($c == '0') && ($count2 < $len) && (PMA_substr($sql, $count2, 1) == 'x');
393 $is_float_digit = $c == '.';
394 $is_float_digit_exponent = FALSE;
396 // Nijel: Fast skip is especially needed for huge BLOB data, requires PHP at least 4.3.0:
397 if (PMA_PHP_INT_VERSION
>= 40300) {
400 $pos = strspn($sql, '0123456789abcdefABCDEF', $count2);
401 if ($pos > $count2) {
405 } elseif ($is_digit) {
406 $pos = strspn($sql, '0123456789', $count2);
407 if ($pos > $count2) {
414 while (($count2 < $len) && PMA_STR_isSqlIdentifier(PMA_substr($sql, $count2, 1), ($is_sql_variable ||
$is_digit))) {
415 $c2 = PMA_substr($sql, $count2, 1);
416 if ($is_sql_variable && ($c2 == '.')) {
420 if ($is_digit && (!$is_hex_digit) && ($c2 == '.')) {
422 if (!$is_float_digit) {
423 $is_float_digit = TRUE;
426 $debugstr = $GLOBALS['strSQPBugInvalidIdentifer'] . ' @ ' . ($count1+
1) . "\n"
427 . 'STR: ' . htmlspecialchars(PMA_substr($sql, $count1, $count2 - $count1));
428 PMA_SQP_throwError($debugstr, $sql);
432 if ($is_digit && (!$is_hex_digit) && (($c2 == 'e') ||
($c2 == 'E'))) {
433 if (!$is_float_digit_exponent) {
434 $is_float_digit_exponent = TRUE;
435 $is_float_digit = TRUE;
440 $is_float_digit = FALSE;
443 if (($is_hex_digit && PMA_STR_isHexDigit($c2)) ||
($is_digit && PMA_STR_isDigit($c2))) {
448 $is_hex_digit = FALSE;
454 $l = $count2 - $count1;
455 $str = PMA_substr($sql, $count1, $l);
460 if ($is_float_digit) {
462 } elseif ($is_hex_digit) {
468 if ($is_sql_variable != FALSE) {
469 $type = 'alpha_variable';
473 } // end if... else....
474 PMA_SQP_arrayAdd($sql_array, $type, $str, $arraysize);
480 if (PMA_STR_strInStr($c, $allpunct_list)) {
481 while (($count2 < $len) && PMA_STR_strInStr(PMA_substr($sql, $count2, 1), $allpunct_list)) {
484 $l = $count2 - $count1;
488 $punct_data = PMA_substr($sql, $count1, $l);
491 // Special case, sometimes, althought two characters are
492 // adjectent directly, they ACTUALLY need to be seperate
495 switch ($punct_data) {
496 case $punct_queryend:
497 $t_suffix = '_queryend';
499 case $punct_qualifier:
500 $t_suffix = '_qualifier';
503 $t_suffix = '_listsep';
508 PMA_SQP_arrayAdd($sql_array, 'punct' . $t_suffix, $punct_data, $arraysize);
509 } elseif (PMA_STR_binarySearchInArr($punct_data, $allpunct_list_pair, $allpunct_list_pair_size)) {
510 // Ok, we have one of the valid combined punct expressions
511 PMA_SQP_arrayAdd($sql_array, 'punct', $punct_data, $arraysize);
513 // Bad luck, lets split it up more
514 $first = $punct_data[0];
515 $first2 = $punct_data[0] . $punct_data[1];
516 $last2 = $punct_data[$l - 2] . $punct_data[$l - 1];
517 $last = $punct_data[$l - 1];
518 if (($first == ',') ||
($first == ';') ||
($first == '.') ||
($first == '*')) {
519 $count2 = $count1 +
1;
520 $punct_data = $first;
521 } elseif (($last2 == '/*') ||
(($last2 == '--') && ($count2 == $len ||
PMA_substr($sql, $count2, 1) <= ' ') )) {
523 $punct_data = PMA_substr($sql, $count1, $count2 - $count1);
524 } elseif (($last == '-') ||
($last == '+') ||
($last == '!')) {
526 $punct_data = PMA_substr($sql, $count1, $count2 - $count1);
528 * @todo for negation operator, split in 2 tokens ?
529 * "select x&~1 from t"
530 * becomes "select x & ~ 1 from t" ?
533 } elseif ($last != '~') {
534 $debugstr = $GLOBALS['strSQPBugUnknownPunctuation'] . ' @ ' . ($count1+
1) . "\n"
535 . 'STR: ' . htmlspecialchars($punct_data);
536 PMA_SQP_throwError($debugstr, $sql);
539 PMA_SQP_arrayAdd($sql_array, 'punct', $punct_data, $arraysize);
541 } // end if... elseif... else
548 $debugstr = 'C1 C2 LEN: ' . $count1 . ' ' . $count2 . ' ' . $len . "\n"
549 . 'STR: ' . PMA_substr($sql, $count1, $count2 - $count1) . "\n";
550 PMA_SQP_bug($debugstr, $sql);
553 } // end while ($count2 < $len)
556 if ($arraysize > 0) {
557 $t_next = $sql_array[0]['type'];
561 $d_next = $sql_array[0]['data'];
565 $d_next_upper = $t_next == 'alpha' ?
strtoupper($d_next) : $d_next;
567 $d_bef_prev_upper = '';
571 for ($i = 0; $i < $arraysize; $i++
) {
572 $t_bef_prev = $t_prev;
575 $d_bef_prev = $d_prev;
578 $d_bef_prev_upper = $d_prev_upper;
579 $d_prev_upper = $d_cur_upper;
580 $d_cur_upper = $d_next_upper;
581 if (($i +
1) < $arraysize) {
582 $t_next = $sql_array[$i +
1]['type'];
583 $d_next = $sql_array[$i +
1]['data'];
584 $d_next_upper = $t_next == 'alpha' ?
strtoupper($d_next) : $d_next;
591 //DEBUG echo "[prev: <b>".$d_prev."</b> ".$t_prev."][cur: <b>".$d_cur."</b> ".$t_cur."][next: <b>".$d_next."</b> ".$t_next."]<br />";
593 if ($t_cur == 'alpha') {
594 $t_suffix = '_identifier';
595 if (($t_next == 'punct_qualifier') ||
($t_prev == 'punct_qualifier')) {
596 $t_suffix = '_identifier';
597 } elseif (($t_next == 'punct_bracket_open_round')
598 && PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_function_name, $PMA_SQPdata_function_name_cnt)) {
600 * @todo 2005-10-16: in the case of a CREATE TABLE containing
601 * a TIMESTAMP, since TIMESTAMP() is also a function, it's
602 * found here and the token is wrongly marked as alpha_functionName.
603 * But we compensate for this when analysing for timestamp_not_null
604 * later in this script.
606 * Same applies to CHAR vs. CHAR() function.
608 $t_suffix = '_functionName';
609 /* There are functions which might be as well column types */
610 if (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_type, $PMA_SQPdata_column_type_cnt)) {
612 } elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_type, $PMA_SQPdata_column_type_cnt)) {
613 $t_suffix = '_columnType';
616 * Temporary fix for BUG #621357
618 * @todo FIX PROPERLY NEEDS OVERHAUL OF SQL TOKENIZER
620 if ($d_cur_upper == 'SET' && $t_next != 'punct_bracket_open_round') {
621 $t_suffix = '_reservedWord';
623 //END OF TEMPORARY FIX
625 // CHARACTER is a synonym for CHAR, but can also be meant as
626 // CHARACTER SET. In this case, we have a reserved word.
627 if ($d_cur_upper == 'CHARACTER' && $d_next_upper == 'SET') {
628 $t_suffix = '_reservedWord';
632 // current is a column type, so previous must not be
633 // a reserved word but an identifier
634 // CREATE TABLE SG_Persons (first varchar(64))
636 //if ($sql_array[$i-1]['type'] =='alpha_reservedWord') {
637 // $sql_array[$i-1]['type'] = 'alpha_identifier';
640 } elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_reserved_word, $PMA_SQPdata_reserved_word_cnt)) {
641 $t_suffix = '_reservedWord';
642 } elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_attrib, $PMA_SQPdata_column_attrib_cnt)) {
643 $t_suffix = '_columnAttrib';
644 // INNODB is a MySQL table type, but in "SHOW INNODB STATUS",
645 // it should be regarded as a reserved word.
646 if ($d_cur_upper == 'INNODB' && $d_prev_upper == 'SHOW' && $d_next_upper == 'STATUS') {
647 $t_suffix = '_reservedWord';
650 if ($d_cur_upper == 'DEFAULT' && $d_next_upper == 'CHARACTER') {
651 $t_suffix = '_reservedWord';
653 // Binary as character set
654 if ($d_cur_upper == 'BINARY' && (
655 ($d_bef_prev_upper == 'CHARACTER' && $d_prev_upper == 'SET')
656 ||
($d_bef_prev_upper == 'SET' && $d_prev_upper == '=')
657 ||
($d_bef_prev_upper == 'CHARSET' && $d_prev_upper == '=')
658 ||
$d_prev_upper == 'CHARSET'
659 ) && PMA_STR_binarySearchInArr($d_cur, $mysql_charsets, count($mysql_charsets))) {
660 $t_suffix = '_charset';
662 } elseif (PMA_STR_binarySearchInArr($d_cur, $mysql_charsets, $mysql_charsets_count)
663 ||
PMA_STR_binarySearchInArr($d_cur, $mysql_collations_flat, $mysql_collations_count)
664 ||
($d_cur{0} == '_' && PMA_STR_binarySearchInArr(substr($d_cur, 1), $mysql_charsets, $mysql_charsets_count))) {
665 $t_suffix = '_charset';
669 // check if present in the list of forbidden words
670 if ($t_suffix == '_reservedWord' && PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_forbidden_word, $PMA_SQPdata_forbidden_word_cnt)) {
671 $sql_array[$i]['forbidden'] = TRUE;
673 $sql_array[$i]['forbidden'] = FALSE;
675 $sql_array[$i]['type'] .= $t_suffix;
679 // Stores the size of the array inside the array, as count() is a slow
681 $sql_array['len'] = $arraysize;
683 // Sends the data back
685 } // end of the "PMA_SQP_parse()" function
688 * Checks for token types being what we want...
690 * @param string String of type that we have
691 * @param string String of type that we want
693 * @return boolean result of check
697 function PMA_SQP_typeCheck($toCheck, $whatWeWant)
699 $typeSeperator = '_';
700 if (strcmp($whatWeWant, $toCheck) == 0) {
703 if (strpos($whatWeWant, $typeSeperator) === FALSE) {
704 return strncmp($whatWeWant, $toCheck, strpos($toCheck, $typeSeperator)) == 0;
713 * Analyzes SQL queries
715 * @param array The SQL queries
717 * @return array The analyzed SQL queries
721 function PMA_SQP_analyze(&$arr)
723 if ($arr == array()) {
730 'select_expr_clause'=> '', // the whole stuff between SELECT and FROM , except DISTINCT
731 'position_of_first_select' => '', // the array index
733 'group_by_clause'=> '',
734 'order_by_clause'=> '',
735 'having_clause' => '',
736 'where_clause' => '',
737 'where_clause_identifiers' => array(),
738 'unsorted_query' => '',
739 'queryflags' => array(),
740 'select_expr' => array(),
741 'table_ref' => array(),
742 'foreign_keys' => array(),
743 'create_table_fields' => array()
745 $subresult_empty = $subresult;
746 $seek_queryend = FALSE;
747 $seen_end_of_table_ref = FALSE;
748 $number_of_brackets_in_extract = 0;
749 $number_of_brackets_in_group_concat = 0;
751 // for SELECT EXTRACT(YEAR_MONTH FROM CURDATE())
752 // we must not use CURDATE as a table_ref
753 // so we track wether we are in the EXTRACT()
756 // for GROUP_CONCAT( ... )
757 $in_group_concat = FALSE;
759 /* Description of analyzer results by lem9
761 * db, table, column, alias
762 * ------------------------
764 * Inside the $subresult array, we create ['select_expr'] and ['table_ref'] arrays.
766 * The SELECT syntax (simplified) is
769 * select_expression,...
770 * [FROM [table_references]
773 * ['select_expr'] is filled with each expression, the key represents the
774 * expression position in the list (0-based) (so we don't lose track of
775 * multiple occurences of the same column).
777 * ['table_ref'] is filled with each table ref, same thing for the key.
779 * I create all sub-values empty, even if they are
780 * not present (for example no select_expression alias).
782 * There is a debug section at the end of loop #1, if you want to
783 * see the exact contents of select_expr and table_ref
788 * In $subresult, array 'queryflags' is filled, according to what we
791 * Currently, those are generated:
793 * ['queryflags']['need_confirm'] = 1; if the query needs confirmation
794 * ['queryflags']['select_from'] = 1; if this is a real SELECT...FROM
795 * ['queryflags']['distinct'] = 1; for a DISTINCT
796 * ['queryflags']['union'] = 1; for a UNION
797 * ['queryflags']['join'] = 1; for a JOIN
798 * ['queryflags']['offset'] = 1; for the presence of OFFSET
803 * The select is splitted in those clauses:
804 * ['select_expr_clause']
806 * ['group_by_clause']
807 * ['order_by_clause']
811 * The identifiers of the WHERE clause are put into the array
812 * ['where_clause_identifier']
814 * For a SELECT, the whole query without the ORDER BY clause is put into
819 * The CREATE TABLE may contain FOREIGN KEY clauses, so they get
820 * analyzed and ['foreign_keys'] is an array filled with
821 * the constraint name, the index list,
822 * the REFERENCES table name and REFERENCES index list,
823 * and ON UPDATE | ON DELETE clauses
825 * position_of_first_select
826 * ------------------------
828 * The array index of the first SELECT we find. Will be used to
829 * insert a SQL_CALC_FOUND_ROWS.
831 * create_table_fields
832 * -------------------
834 * For now, mostly used to detect the DEFAULT CURRENT_TIMESTAMP and
835 * ON UPDATE CURRENT_TIMESTAMP clauses of the CREATE TABLE query.
836 * An array, each element is the identifier name.
837 * Note that for now, the timestamp_not_null element is created
838 * even for non-TIMESTAMP fields.
840 * Sub-elements: ['type'] which contains the column type
841 * optional (currently they are never false but can be absent):
842 * ['default_current_timestamp'] boolean
843 * ['on_update_current_timestamp'] boolean
844 * ['timestamp_not_null'] boolean
846 * section_before_limit, section_after_limit
847 * -----------------------------------------
849 * Marks the point of the query where we can insert a LIMIT clause;
850 * so the section_before_limit will contain the left part before
851 * a possible LIMIT clause
854 * End of description of analyzer results
858 // TODO: current logic checks for only one word, so I put only the
859 // first word of the reserved expressions that end a table ref;
860 // maybe this is not ok (the first word might mean something else)
861 // $words_ending_table_ref = array(
866 // 'LOCK IN SHARE MODE',
872 $words_ending_table_ref = array(
883 $words_ending_table_ref_cnt = 9; //count($words_ending_table_ref);
885 $words_ending_clauses = array(
892 $words_ending_clauses_cnt = 5; //count($words_ending_clauses);
898 $supported_query_types = array(
901 // Support for these additional query types will come later on.
915 $supported_query_types_cnt = count($supported_query_types);
917 // loop #1 for each token: select_expr, table_ref for SELECT
919 for ($i = 0; $i < $size; $i++
) {
920 //DEBUG echo "trace loop1 <b>" . $arr[$i]['data'] . "</b> (" . $arr[$i]['type'] . ")<br />";
922 // High speed seek for locating the end of the current query
923 if ($seek_queryend == TRUE) {
924 if ($arr[$i]['type'] == 'punct_queryend') {
925 $seek_queryend = FALSE;
928 } // end if (type == punct_queryend)
929 } // end if ($seek_queryend)
932 * Note: do not split if this is a punct_queryend for the first and only query
933 * @todo when we find a UNION, should we split in another subresult?
935 if ($arr[$i]['type'] == 'punct_queryend' && ($i +
1 != $size)) {
936 $result[] = $subresult;
937 $subresult = $subresult_empty;
939 } // end if (type == punct_queryend)
941 // ==============================================================
942 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
944 $number_of_brackets_in_extract++
;
946 if ($in_group_concat) {
947 $number_of_brackets_in_group_concat++
;
950 // ==============================================================
951 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
953 $number_of_brackets_in_extract--;
954 if ($number_of_brackets_in_extract == 0) {
958 if ($in_group_concat) {
959 $number_of_brackets_in_group_concat--;
960 if ($number_of_brackets_in_group_concat == 0) {
961 $in_group_concat = FALSE;
965 // ==============================================================
966 if ($arr[$i]['type'] == 'alpha_functionName') {
967 $upper_data = strtoupper($arr[$i]['data']);
968 if ($upper_data =='EXTRACT') {
970 $number_of_brackets_in_extract = 0;
972 if ($upper_data =='GROUP_CONCAT') {
973 $in_group_concat = TRUE;
974 $number_of_brackets_in_group_concat = 0;
978 // ==============================================================
979 if ($arr[$i]['type'] == 'alpha_reservedWord'
980 // && $arr[$i]['forbidden'] == FALSE) {
982 // We don't know what type of query yet, so run this
983 if ($subresult['querytype'] == '') {
984 $subresult['querytype'] = strtoupper($arr[$i]['data']);
985 } // end if (querytype was empty)
987 // Check if we support this type of query
988 if (!PMA_STR_binarySearchInArr($subresult['querytype'], $supported_query_types, $supported_query_types_cnt)) {
989 // Skip ahead to the next one if we don't
990 $seek_queryend = TRUE;
992 } // end if (query not supported)
995 $upper_data = strtoupper($arr[$i]['data']);
997 * @todo reset for each query?
1000 if ($upper_data == 'SELECT') {
1002 $previous_was_identifier = FALSE;
1003 $current_select_expr = -1;
1004 $seen_end_of_table_ref = FALSE;
1005 } // end if ( data == SELECT)
1007 if ($upper_data =='FROM' && !$in_extract) {
1008 $current_table_ref = -1;
1010 $previous_was_identifier = FALSE;
1011 $save_table_ref = TRUE;
1012 } // end if (data == FROM)
1014 // here, do not 'continue' the loop, as we have more work for
1015 // reserved words below
1016 } // end if (type == alpha_reservedWord)
1018 // ==============================
1019 if ($arr[$i]['type'] == 'quote_backtick'
1020 ||
$arr[$i]['type'] == 'quote_double'
1021 ||
$arr[$i]['type'] == 'quote_single'
1022 ||
$arr[$i]['type'] == 'alpha_identifier'
1023 ||
($arr[$i]['type'] == 'alpha_reservedWord'
1024 && $arr[$i]['forbidden'] == FALSE)) {
1026 switch ($arr[$i]['type']) {
1027 case 'alpha_identifier':
1028 case 'alpha_reservedWord':
1030 * this is not a real reservedWord, because it's not
1031 * present in the list of forbidden words, for example
1032 * "storage" which can be used as an identifier
1034 * @todo avoid the pretty printing in color in this case
1036 $identifier = $arr[$i]['data'];
1039 case 'quote_backtick':
1040 case 'quote_double':
1041 case 'quote_single':
1042 $identifier = PMA_unQuote($arr[$i]['data']);
1046 if ($subresult['querytype'] == 'SELECT' && !$in_group_concat) {
1048 if ($previous_was_identifier && isset($chain)) {
1049 // found alias for this select_expr, save it
1050 // but only if we got something in $chain
1051 // (for example, SELECT COUNT(*) AS cnt
1052 // puts nothing in $chain, so we avoid
1053 // setting the alias)
1054 $alias_for_select_expr = $identifier;
1056 $chain[] = $identifier;
1057 $previous_was_identifier = TRUE;
1059 } // end if !$previous_was_identifier
1062 if ($save_table_ref && !$seen_end_of_table_ref) {
1063 if ($previous_was_identifier) {
1064 // found alias for table ref
1065 // save it for later
1066 $alias_for_table_ref = $identifier;
1068 $chain[] = $identifier;
1069 $previous_was_identifier = TRUE;
1071 } // end if ($previous_was_identifier)
1072 } // end if ($save_table_ref &&!$seen_end_of_table_ref)
1073 } // end if (!$seen_from)
1074 } // end if (querytype SELECT)
1075 } // end if ( quote_backtick or double quote or alpha_identifier)
1077 // ===================================
1078 if ($arr[$i]['type'] == 'punct_qualifier') {
1079 // to be able to detect an identifier following another
1080 $previous_was_identifier = FALSE;
1082 } // end if (punct_qualifier)
1085 * @todo check if 3 identifiers following one another -> error
1088 // s a v e a s e l e c t e x p r
1089 // finding a list separator or FROM
1090 // means that we must save the current chain of identifiers
1091 // into a select expression
1093 // for now, we only save a select expression if it contains
1094 // at least one identifier, as we are interested in checking
1095 // the columns and table names, so in "select * from persons",
1096 // the "*" is not saved
1098 if (isset($chain) && !$seen_end_of_table_ref
1100 && $arr[$i]['type'] == 'punct_listsep')
1101 ||
($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data == 'FROM')) ) {
1102 $size_chain = count($chain);
1103 $current_select_expr++
;
1104 $subresult['select_expr'][$current_select_expr] = array(
1109 'table_true_name' => '',
1113 if (isset($alias_for_select_expr) && strlen($alias_for_select_expr)) {
1114 // we had found an alias for this select expression
1115 $subresult['select_expr'][$current_select_expr]['alias'] = $alias_for_select_expr;
1116 unset($alias_for_select_expr);
1118 // there is at least a column
1119 $subresult['select_expr'][$current_select_expr]['column'] = $chain[$size_chain - 1];
1120 $subresult['select_expr'][$current_select_expr]['expr'] = $chain[$size_chain - 1];
1123 if ($size_chain > 1) {
1124 $subresult['select_expr'][$current_select_expr]['table_name'] = $chain[$size_chain - 2];
1125 // we assume for now that this is also the true name
1126 $subresult['select_expr'][$current_select_expr]['table_true_name'] = $chain[$size_chain - 2];
1127 $subresult['select_expr'][$current_select_expr]['expr']
1128 = $subresult['select_expr'][$current_select_expr]['table_name']
1129 . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
1130 } // end if ($size_chain > 1)
1133 if ($size_chain > 2) {
1134 $subresult['select_expr'][$current_select_expr]['db'] = $chain[$size_chain - 3];
1135 $subresult['select_expr'][$current_select_expr]['expr']
1136 = $subresult['select_expr'][$current_select_expr]['db']
1137 . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
1138 } // end if ($size_chain > 2)
1142 * @todo explain this:
1144 if (($arr[$i]['type'] == 'alpha_reservedWord')
1145 && ($upper_data != 'FROM')) {
1146 $previous_was_identifier = TRUE;
1149 } // end if (save a select expr)
1152 //======================================
1153 // s a v e a t a b l e r e f
1154 //======================================
1156 // maybe we just saw the end of table refs
1157 // but the last table ref has to be saved
1158 // or we are at the last token
1159 // or we just got a reserved word
1161 * @todo there could be another query after this one
1164 if (isset($chain) && $seen_from && $save_table_ref
1165 && ($arr[$i]['type'] == 'punct_listsep'
1166 ||
($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data!="AS")
1167 ||
$seen_end_of_table_ref
1170 $size_chain = count($chain);
1171 $current_table_ref++
;
1172 $subresult['table_ref'][$current_table_ref] = array(
1176 'table_alias' => '',
1177 'table_true_name' => ''
1179 if (isset($alias_for_table_ref) && strlen($alias_for_table_ref)) {
1180 $subresult['table_ref'][$current_table_ref]['table_alias'] = $alias_for_table_ref;
1181 unset($alias_for_table_ref);
1183 $subresult['table_ref'][$current_table_ref]['table_name'] = $chain[$size_chain - 1];
1184 // we assume for now that this is also the true name
1185 $subresult['table_ref'][$current_table_ref]['table_true_name'] = $chain[$size_chain - 1];
1186 $subresult['table_ref'][$current_table_ref]['expr']
1187 = $subresult['table_ref'][$current_table_ref]['table_name'];
1189 if ($size_chain > 1) {
1190 $subresult['table_ref'][$current_table_ref]['db'] = $chain[$size_chain - 2];
1191 $subresult['table_ref'][$current_table_ref]['expr']
1192 = $subresult['table_ref'][$current_table_ref]['db']
1193 . '.' . $subresult['table_ref'][$current_table_ref]['expr'];
1194 } // end if ($size_chain > 1)
1196 // add the table alias into the whole expression
1197 $subresult['table_ref'][$current_table_ref]['expr']
1198 .= ' ' . $subresult['table_ref'][$current_table_ref]['table_alias'];
1201 $previous_was_identifier = TRUE;
1204 } // end if (save a table ref)
1207 // when we have found all table refs,
1208 // for each table_ref alias, put the true name of the table
1209 // in the corresponding select expressions
1211 if (isset($current_table_ref) && ($seen_end_of_table_ref ||
$i == $size-1) && $subresult != $subresult_empty) {
1212 for ($tr=0; $tr <= $current_table_ref; $tr++
) {
1213 $alias = $subresult['table_ref'][$tr]['table_alias'];
1214 $truename = $subresult['table_ref'][$tr]['table_true_name'];
1215 for ($se=0; $se <= $current_select_expr; $se++
) {
1216 if (isset($alias) && strlen($alias) && $subresult['select_expr'][$se]['table_true_name']
1218 $subresult['select_expr'][$se]['table_true_name']
1220 } // end if (found the alias)
1221 } // end for (select expressions)
1223 } // end for (table refs)
1224 } // end if (set the true names)
1227 // e n d i n g l o o p #1
1228 // set the $previous_was_identifier to FALSE if the current
1229 // token is not an identifier
1230 if (($arr[$i]['type'] != 'alpha_identifier')
1231 && ($arr[$i]['type'] != 'quote_double')
1232 && ($arr[$i]['type'] != 'quote_single')
1233 && ($arr[$i]['type'] != 'quote_backtick')) {
1234 $previous_was_identifier = FALSE;
1237 // however, if we are on AS, we must keep the $previous_was_identifier
1238 if (($arr[$i]['type'] == 'alpha_reservedWord')
1239 && ($upper_data == 'AS')) {
1240 $previous_was_identifier = TRUE;
1243 if (($arr[$i]['type'] == 'alpha_reservedWord')
1244 && ($upper_data =='ON' ||
$upper_data =='USING')) {
1245 $save_table_ref = FALSE;
1246 } // end if (data == ON)
1248 if (($arr[$i]['type'] == 'alpha_reservedWord')
1249 && ($upper_data =='JOIN' ||
$upper_data =='FROM')) {
1250 $save_table_ref = TRUE;
1251 } // end if (data == JOIN)
1254 * no need to check the end of table ref if we already did
1256 * @todo maybe add "&& $seen_from"
1258 if (!$seen_end_of_table_ref) {
1259 // if this is the last token, it implies that we have
1260 // seen the end of table references
1261 // Check for the end of table references
1263 // Note: if we are analyzing a GROUP_CONCAT clause,
1264 // we might find a word that seems to indicate that
1265 // we have found the end of table refs (like ORDER)
1266 // but it's a modifier of the GROUP_CONCAT so
1267 // it's not the real end of table refs
1269 ||
($arr[$i]['type'] == 'alpha_reservedWord'
1270 && !$in_group_concat
1271 && PMA_STR_binarySearchInArr($upper_data, $words_ending_table_ref, $words_ending_table_ref_cnt))) {
1272 $seen_end_of_table_ref = TRUE;
1273 // to be able to save the last table ref, but do not
1274 // set it true if we found a word like "ON" that has
1275 // already set it to false
1276 if (isset($save_table_ref) && $save_table_ref != FALSE) {
1277 $save_table_ref = TRUE;
1280 } // end if (check for end of table ref)
1281 } //end if (!$seen_end_of_table_ref)
1283 if ($seen_end_of_table_ref) {
1284 $save_table_ref = FALSE;
1287 } // end for $i (loop #1)
1289 // -------------------------------------------------------
1290 // This is a big hunk of debugging code by Marc for this.
1291 // -------------------------------------------------------
1293 if (isset($current_select_expr)) {
1294 for ($trace=0; $trace<=$current_select_expr; $trace++) {
1296 reset ($subresult['select_expr'][$trace]);
1297 while (list ($key, $val) = each ($subresult['select_expr'][$trace]))
1298 echo "sel expr $trace $key => $val<br />\n";
1302 if (isset($current_table_ref)) {
1303 echo "current_table_ref = " . $current_table_ref . "<br>";
1304 for ($trace=0; $trace<=$current_table_ref; $trace++) {
1307 reset ($subresult['table_ref'][$trace]);
1308 while (list ($key, $val) = each ($subresult['table_ref'][$trace]))
1309 echo "table ref $trace $key => $val<br />\n";
1313 // -------------------------------------------------------
1316 // loop #2: - queryflags
1317 // - querytype (for queries != 'SELECT')
1318 // - section_before_limit, section_after_limit
1320 // we will also need this queryflag in loop 2
1322 if (isset($current_table_ref) && $current_table_ref > -1) {
1323 $subresult['queryflags']['select_from'] = 1;
1326 $collect_section_before_limit = TRUE;
1327 $section_before_limit = '';
1328 $section_after_limit = '';
1329 $seen_reserved_word = FALSE;
1330 $seen_group = FALSE;
1331 $seen_order = FALSE;
1332 $in_group_by = FALSE; // true when we are inside the GROUP BY clause
1333 $in_order_by = FALSE; // true when we are inside the ORDER BY clause
1334 $in_having = FALSE; // true when we are inside the HAVING clause
1335 $in_select_expr = FALSE; // true when we are inside the select expr clause
1336 $in_where = FALSE; // true when we are inside the WHERE clause
1338 $in_group_concat = FALSE;
1339 $unsorted_query = '';
1340 $first_reserved_word = '';
1341 $current_identifier = '';
1343 for ($i = 0; $i < $size; $i++
) {
1344 //DEBUG echo "trace loop2 <b>" . $arr[$i]['data'] . "</b> (" . $arr[$i]['type'] . ")<br />";
1348 // check for reserved words that will have to generate
1349 // a confirmation request later in sql.php
1353 // ALTER TABLE... DROP
1356 // this code is not used for confirmations coming from functions.js
1359 * @todo check for punct_queryend
1360 * @todo verify C-style comments?
1362 if ($arr[$i]['type'] == 'comment_ansi') {
1363 $collect_section_before_limit = FALSE;
1366 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1367 $upper_data = strtoupper($arr[$i]['data']);
1368 if (!$seen_reserved_word) {
1369 $first_reserved_word = $upper_data;
1370 $subresult['querytype'] = $upper_data;
1371 $seen_reserved_word = TRUE;
1373 // if the first reserved word is DROP or DELETE,
1374 // we know this is a query that needs to be confirmed
1375 if ($first_reserved_word=='DROP'
1376 ||
$first_reserved_word == 'DELETE'
1377 ||
$first_reserved_word == 'TRUNCATE') {
1378 $subresult['queryflags']['need_confirm'] = 1;
1381 if ($first_reserved_word=='SELECT'){
1382 $position_of_first_select = $i;
1386 if ($upper_data=='DROP' && $first_reserved_word=='ALTER') {
1387 $subresult['queryflags']['need_confirm'] = 1;
1391 if ($upper_data == 'PROCEDURE') {
1392 $collect_section_before_limit = FALSE;
1395 * @todo set also to FALSE if we find FOR UPDATE or LOCK IN SHARE MODE
1397 if ($upper_data == 'SELECT') {
1398 $in_select_expr = TRUE;
1399 $select_expr_clause = '';
1401 if ($upper_data == 'DISTINCT' && !$in_group_concat) {
1402 $subresult['queryflags']['distinct'] = 1;
1405 if ($upper_data == 'UNION') {
1406 $subresult['queryflags']['union'] = 1;
1409 if ($upper_data == 'JOIN') {
1410 $subresult['queryflags']['join'] = 1;
1413 if ($upper_data == 'OFFSET') {
1414 $subresult['queryflags']['offset'] = 1;
1417 // if this is a real SELECT...FROM
1418 if ($upper_data == 'FROM' && isset($subresult['queryflags']['select_from']) && $subresult['queryflags']['select_from'] == 1) {
1421 $in_select_expr = FALSE;
1425 // (we could have less resetting of variables to FALSE
1426 // if we trust that the query respects the standard
1427 // MySQL order for clauses)
1429 // we use $seen_group and $seen_order because we are looking
1431 if ($upper_data == 'GROUP') {
1433 $seen_order = FALSE;
1435 $in_order_by = FALSE;
1437 $in_select_expr = FALSE;
1440 if ($upper_data == 'ORDER' && !$in_group_concat) {
1442 $seen_group = FALSE;
1444 $in_group_by = FALSE;
1446 $in_select_expr = FALSE;
1449 if ($upper_data == 'HAVING') {
1451 $having_clause = '';
1452 $seen_group = FALSE;
1453 $seen_order = FALSE;
1454 $in_group_by = FALSE;
1455 $in_order_by = FALSE;
1457 $in_select_expr = FALSE;
1461 if ($upper_data == 'WHERE') {
1464 $where_clause_identifiers = array();
1465 $seen_group = FALSE;
1466 $seen_order = FALSE;
1467 $in_group_by = FALSE;
1468 $in_order_by = FALSE;
1470 $in_select_expr = FALSE;
1474 if ($upper_data == 'BY') {
1476 $in_group_by = TRUE;
1477 $group_by_clause = '';
1480 $in_order_by = TRUE;
1481 $order_by_clause = '';
1485 // if we find one of the words that could end the clause
1486 if (PMA_STR_binarySearchInArr($upper_data, $words_ending_clauses, $words_ending_clauses_cnt)) {
1488 $in_group_by = FALSE;
1489 $in_order_by = FALSE;
1492 $in_select_expr = FALSE;
1496 } // endif (reservedWord)
1499 // do not add a blank after a function name
1501 * @todo can we combine loop 2 and loop 1? some code is repeated here...
1505 if ($arr[$i]['type'] == 'alpha_functionName') {
1507 $upper_data = strtoupper($arr[$i]['data']);
1508 if ($upper_data =='GROUP_CONCAT') {
1509 $in_group_concat = TRUE;
1510 $number_of_brackets_in_group_concat = 0;
1514 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1515 if ($in_group_concat) {
1516 $number_of_brackets_in_group_concat++
;
1519 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1520 if ($in_group_concat) {
1521 $number_of_brackets_in_group_concat--;
1522 if ($number_of_brackets_in_group_concat == 0) {
1523 $in_group_concat = FALSE;
1528 if ($in_select_expr && $upper_data != 'SELECT' && $upper_data != 'DISTINCT') {
1529 $select_expr_clause .= $arr[$i]['data'] . $sep;
1531 if ($in_from && $upper_data != 'FROM') {
1532 $from_clause .= $arr[$i]['data'] . $sep;
1534 if ($in_group_by && $upper_data != 'GROUP' && $upper_data != 'BY') {
1535 $group_by_clause .= $arr[$i]['data'] . $sep;
1537 if ($in_order_by && $upper_data != 'ORDER' && $upper_data != 'BY') {
1538 // add a space only before ASC or DESC
1539 // not around the dot between dbname and tablename
1540 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1541 $order_by_clause .= $sep;
1543 $order_by_clause .= $arr[$i]['data'];
1545 if ($in_having && $upper_data != 'HAVING') {
1546 $having_clause .= $arr[$i]['data'] . $sep;
1548 if ($in_where && $upper_data != 'WHERE') {
1549 $where_clause .= $arr[$i]['data'] . $sep;
1551 if (($arr[$i]['type'] == 'quote_backtick')
1552 ||
($arr[$i]['type'] == 'alpha_identifier')) {
1553 $where_clause_identifiers[] = $arr[$i]['data'];
1557 if (isset($subresult['queryflags']['select_from'])
1558 && $subresult['queryflags']['select_from'] == 1
1560 $unsorted_query .= $arr[$i]['data'];
1562 if ($arr[$i]['type'] != 'punct_bracket_open_round'
1563 && $arr[$i]['type'] != 'punct_bracket_close_round'
1564 && $arr[$i]['type'] != 'punct') {
1565 $unsorted_query .= $sep;
1569 // clear $upper_data for next iteration
1572 if ($collect_section_before_limit && $arr[$i]['type'] != 'punct_queryend') {
1573 $section_before_limit .= $arr[$i]['data'] . $sep;
1575 $section_after_limit .= $arr[$i]['data'] . $sep;
1579 } // end for $i (loop #2)
1582 // -----------------------------------------------------
1583 // loop #3: foreign keys and MySQL 4.1.2+ TIMESTAMP options
1584 // (for now, check only the first query)
1585 // (for now, identifiers are assumed to be backquoted)
1587 // If we find that we are dealing with a CREATE TABLE query,
1588 // we look for the next punct_bracket_open_round, which
1589 // introduces the fields list. Then, when we find a
1590 // quote_backtick, it must be a field, so we put it into
1591 // the create_table_fields array. Even if this field is
1592 // not a timestamp, it will be useful when logic has been
1593 // added for complete field attributes analysis.
1595 $seen_foreign = FALSE;
1596 $seen_references = FALSE;
1597 $seen_constraint = FALSE;
1598 $foreign_key_number = -1;
1599 $seen_create_table = FALSE;
1600 $seen_create = FALSE;
1601 $in_create_table_fields = FALSE;
1602 $brackets_level = 0;
1603 $in_timestamp_options = FALSE;
1604 $seen_default = FALSE;
1606 for ($i = 0; $i < $size; $i++
) {
1607 // DEBUG echo "<b>" . $arr[$i]['data'] . "</b> " . $arr[$i]['type'] . "<br />";
1609 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1610 $upper_data = strtoupper($arr[$i]['data']);
1612 if ($upper_data == 'NOT' && $in_timestamp_options) {
1613 $create_table_fields[$current_identifier]['timestamp_not_null'] = TRUE;
1617 if ($upper_data == 'CREATE') {
1618 $seen_create = TRUE;
1621 if ($upper_data == 'TABLE' && $seen_create) {
1622 $seen_create_table = TRUE;
1623 $create_table_fields = array();
1626 if ($upper_data == 'CURRENT_TIMESTAMP') {
1627 if ($in_timestamp_options) {
1628 if ($seen_default) {
1629 $create_table_fields[$current_identifier]['default_current_timestamp'] = TRUE;
1634 if ($upper_data == 'CONSTRAINT') {
1635 $foreign_key_number++
;
1636 $seen_foreign = FALSE;
1637 $seen_references = FALSE;
1638 $seen_constraint = TRUE;
1640 if ($upper_data == 'FOREIGN') {
1641 $seen_foreign = TRUE;
1642 $seen_references = FALSE;
1643 $seen_constraint = FALSE;
1645 if ($upper_data == 'REFERENCES') {
1646 $seen_foreign = FALSE;
1647 $seen_references = TRUE;
1648 $seen_constraint = FALSE;
1654 // [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
1655 // [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
1657 // but we set ['on_delete'] or ['on_cascade'] to
1658 // CASCADE | SET_NULL | NO_ACTION | RESTRICT
1660 // ON UPDATE CURRENT_TIMESTAMP
1662 if ($upper_data == 'ON') {
1663 if ($arr[$i+
1]['type'] == 'alpha_reservedWord') {
1664 $second_upper_data = strtoupper($arr[$i+
1]['data']);
1665 if ($second_upper_data == 'DELETE') {
1666 $clause = 'on_delete';
1668 if ($second_upper_data == 'UPDATE') {
1669 $clause = 'on_update';
1672 && ($arr[$i+
2]['type'] == 'alpha_reservedWord'
1674 // ugly workaround because currently, NO is not
1675 // in the list of reserved words in sqlparser.data
1676 // (we got a bug report about not being able to use
1677 // 'no' as an identifier)
1678 ||
($arr[$i+
2]['type'] == 'alpha_identifier'
1679 && strtoupper($arr[$i+
2]['data'])=='NO') )
1681 $third_upper_data = strtoupper($arr[$i+
2]['data']);
1682 if ($third_upper_data == 'CASCADE'
1683 ||
$third_upper_data == 'RESTRICT') {
1684 $value = $third_upper_data;
1685 } elseif ($third_upper_data == 'SET'
1686 ||
$third_upper_data == 'NO') {
1687 if ($arr[$i+
3]['type'] == 'alpha_reservedWord') {
1688 $value = $third_upper_data . '_' . strtoupper($arr[$i+
3]['data']);
1690 } elseif ($third_upper_data == 'CURRENT_TIMESTAMP') {
1691 if ($clause == 'on_update'
1692 && $in_timestamp_options) {
1693 $create_table_fields[$current_identifier]['on_update_current_timestamp'] = TRUE;
1694 $seen_default = FALSE;
1700 if (!empty($value)) {
1701 $foreign[$foreign_key_number][$clause] = $value;
1704 } // endif (isset($clause))
1708 } // end of reserved words analysis
1711 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1713 if ($seen_create_table && $brackets_level == 1) {
1714 $in_create_table_fields = TRUE;
1719 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1721 if ($seen_references) {
1722 $seen_references = FALSE;
1724 if ($seen_create_table && $brackets_level == 0) {
1725 $in_create_table_fields = FALSE;
1729 if (($arr[$i]['type'] == 'alpha_columnAttrib')) {
1730 $upper_data = strtoupper($arr[$i]['data']);
1731 if ($seen_create_table && $in_create_table_fields) {
1732 if ($upper_data == 'DEFAULT') {
1733 $seen_default = TRUE;
1739 * @see @todo 2005-10-16 note: the "or" part here is a workaround for a bug
1741 if (($arr[$i]['type'] == 'alpha_columnType') ||
($arr[$i]['type'] == 'alpha_functionName' && $seen_create_table)) {
1742 $upper_data = strtoupper($arr[$i]['data']);
1743 if ($seen_create_table && $in_create_table_fields && isset($current_identifier)) {
1744 $create_table_fields[$current_identifier]['type'] = $upper_data;
1745 if ($upper_data == 'TIMESTAMP') {
1746 $arr[$i]['type'] = 'alpha_columnType';
1747 $in_timestamp_options = TRUE;
1749 $in_timestamp_options = FALSE;
1750 if ($upper_data == 'CHAR') {
1751 $arr[$i]['type'] = 'alpha_columnType';
1758 if ($arr[$i]['type'] == 'quote_backtick' ||
$arr[$i]['type'] == 'alpha_identifier') {
1760 if ($arr[$i]['type'] == 'quote_backtick') {
1761 // remove backquotes
1762 $identifier = PMA_unQuote($arr[$i]['data']);
1764 $identifier = $arr[$i]['data'];
1767 if ($seen_create_table && $in_create_table_fields) {
1768 $current_identifier = $identifier;
1769 // warning: we set this one even for non TIMESTAMP type
1770 $create_table_fields[$current_identifier]['timestamp_not_null'] = FALSE;
1773 if ($seen_constraint) {
1774 $foreign[$foreign_key_number]['constraint'] = $identifier;
1777 if ($seen_foreign && $brackets_level > 0) {
1778 $foreign[$foreign_key_number]['index_list'][] = $identifier;
1781 if ($seen_references) {
1782 // here, the first bracket level corresponds to the
1783 // bracket of CREATE TABLE
1784 // so if we are on level 2, it must be the index list
1785 // of the foreign key REFERENCES
1786 if ($brackets_level > 1) {
1787 $foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
1789 // for MySQL 4.0.18, identifier is
1790 // `table` or `db`.`table`
1791 // the first pass will pick the db name
1792 // the next pass will execute the else and pick the
1793 // db name in $db_table[0]
1794 if ($arr[$i+
1]['type'] == 'punct_qualifier') {
1795 $foreign[$foreign_key_number]['ref_db_name'] = $identifier;
1797 // for MySQL 4.0.16, identifier is
1798 // `table` or `db.table`
1799 $db_table = explode('.', $identifier);
1800 if (isset($db_table[1])) {
1801 $foreign[$foreign_key_number]['ref_db_name'] = $db_table[0];
1802 $foreign[$foreign_key_number]['ref_table_name'] = $db_table[1];
1804 $foreign[$foreign_key_number]['ref_table_name'] = $db_table[0];
1810 } // end for $i (loop #3)
1813 // Fill the $subresult array
1815 if (isset($create_table_fields)) {
1816 $subresult['create_table_fields'] = $create_table_fields;
1819 if (isset($foreign)) {
1820 $subresult['foreign_keys'] = $foreign;
1823 if (isset($select_expr_clause)) {
1824 $subresult['select_expr_clause'] = $select_expr_clause;
1826 if (isset($from_clause)) {
1827 $subresult['from_clause'] = $from_clause;
1829 if (isset($group_by_clause)) {
1830 $subresult['group_by_clause'] = $group_by_clause;
1832 if (isset($order_by_clause)) {
1833 $subresult['order_by_clause'] = $order_by_clause;
1835 if (isset($having_clause)) {
1836 $subresult['having_clause'] = $having_clause;
1838 if (isset($where_clause)) {
1839 $subresult['where_clause'] = $where_clause;
1841 if (isset($unsorted_query) && !empty($unsorted_query)) {
1842 $subresult['unsorted_query'] = $unsorted_query;
1844 if (isset($where_clause_identifiers)) {
1845 $subresult['where_clause_identifiers'] = $where_clause_identifiers;
1848 if (isset($position_of_first_select)) {
1849 $subresult['position_of_first_select'] = $position_of_first_select;
1850 $subresult['section_before_limit'] = $section_before_limit;
1851 $subresult['section_after_limit'] = $section_after_limit;
1854 // They are naughty and didn't have a trailing semi-colon,
1855 // then still handle it properly
1856 if ($subresult['querytype'] != '') {
1857 $result[] = $subresult;
1860 } // end of the "PMA_SQP_analyze()" function
1864 * Colorizes SQL queries html formatted
1866 * @todo check why adding a "\n" after the </span> would cause extra blanks
1867 * to be displayed: SELECT p . person_name
1868 * @param array The SQL queries html formatted
1870 * @return array The colorized SQL queries
1874 function PMA_SQP_formatHtml_colorize($arr)
1876 $i = $GLOBALS['PMA_strpos']($arr['type'], '_');
1879 $class = 'syntax_' . PMA_substr($arr['type'], 0, $i) . ' ';
1882 $class .= 'syntax_' . $arr['type'];
1884 return '<span class="' . $class . '">' . htmlspecialchars($arr['data']) . '</span>';
1885 } // end of the "PMA_SQP_formatHtml_colorize()" function
1889 * Formats SQL queries to html
1891 * @param array The SQL queries
1892 * @param string mode
1893 * @param integer starting token
1894 * @param integer number of tokens to format, -1 = all
1896 * @return string The formatted SQL queries
1900 function PMA_SQP_formatHtml($arr, $mode='color', $start_token=0,
1901 $number_of_tokens=-1)
1903 // then check for an array
1904 if (!is_array($arr)) {
1905 return htmlspecialchars($arr);
1907 // first check for the SQL parser having hit an error
1908 if (PMA_SQP_isError()) {
1909 return htmlspecialchars($arr['raw']);
1911 // else do it properly
1914 $str = '<span class="syntax">';
1915 $html_line_break = '<br />';
1919 $html_line_break = "\n";
1923 $html_line_break = '<br />';
1929 $infunction = FALSE;
1930 $space_punct_listsep = ' ';
1931 $space_punct_listsep_function_name = ' ';
1932 // $space_alpha_reserved_word = '<br />'."\n";
1933 $space_alpha_reserved_word = ' ';
1935 $keywords_with_brackets_1before = array(
1941 $keywords_with_brackets_1before_cnt = 4;
1943 $keywords_with_brackets_2before = array(
1954 // $keywords_with_brackets_2before_cnt = count($keywords_with_brackets_2before);
1955 $keywords_with_brackets_2before_cnt = 9;
1957 // These reserved words do NOT get a newline placed near them.
1958 $keywords_no_newline = array(
1973 $keywords_no_newline_cnt = 12;
1975 // These reserved words introduce a privilege list
1976 $keywords_priv_list = array(
1980 $keywords_priv_list_cnt = 2;
1982 if ($number_of_tokens == -1) {
1983 $arraysize = $arr['len'];
1985 $arraysize = $number_of_tokens;
1988 if ($arraysize >= 0) {
1992 //$typearr[3] = $arr[0]['type'];
1993 $typearr[3] = $arr[$start_token]['type'];
1996 $in_priv_list = FALSE;
1997 for ($i = $start_token; $i < $arraysize; $i++
) {
1998 // DEBUG echo "<b>" . $arr[$i]['data'] . "</b> " . $arr[$i]['type'] . "<br />";
2002 // array_shift($typearr);
2009 if (($i +
1) < $arraysize) {
2010 // array_push($typearr, $arr[$i + 1]['type']);
2011 $typearr[4] = $arr[$i +
1]['type'];
2013 //array_push($typearr, null);
2017 for ($j=0; $j<4; $j++
) {
2018 $typearr[$j] = $typearr[$j +
1];
2021 switch ($typearr[2]) {
2022 case 'white_newline':
2025 case 'punct_bracket_open_round':
2027 $infunction = FALSE;
2028 // Make sure this array is sorted!
2029 if (($typearr[1] == 'alpha_functionName') ||
($typearr[1] == 'alpha_columnType') ||
($typearr[1] == 'punct')
2030 ||
($typearr[3] == 'digit_integer') ||
($typearr[3] == 'digit_hex') ||
($typearr[3] == 'digit_float')
2031 ||
(($typearr[0] == 'alpha_reservedWord')
2032 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 2]['data']), $keywords_with_brackets_2before, $keywords_with_brackets_2before_cnt))
2033 ||
(($typearr[1] == 'alpha_reservedWord')
2034 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_with_brackets_1before, $keywords_with_brackets_1before_cnt))
2041 $after .= ($mode != 'query_only' ?
'<div class="syntax_indent' . $indent . '">' : ' ');
2044 case 'alpha_identifier':
2045 if (($typearr[1] == 'punct_qualifier') ||
($typearr[3] == 'punct_qualifier')) {
2049 if (($typearr[3] == 'alpha_columnType') ||
($typearr[3] == 'alpha_identifier')) {
2053 case 'punct_qualifier':
2057 case 'punct_listsep':
2058 if ($infunction == TRUE) {
2059 $after .= $space_punct_listsep_function_name;
2061 $after .= $space_punct_listsep;
2064 case 'punct_queryend':
2065 if (($typearr[3] != 'comment_mysql') && ($typearr[3] != 'comment_ansi') && $typearr[3] != 'comment_c') {
2066 $after .= $html_line_break;
2067 $after .= $html_line_break;
2069 $space_punct_listsep = ' ';
2070 $space_punct_listsep_function_name = ' ';
2071 $space_alpha_reserved_word = ' ';
2072 $in_priv_list = FALSE;
2074 case 'comment_mysql':
2075 case 'comment_ansi':
2076 $after .= $html_line_break;
2081 // select * from mytable limit 0,-1
2082 // (a side effect of this workaround is that
2087 if ($typearr[3] != 'digit_integer') {
2091 case 'punct_bracket_close_round':
2093 if ($infunction == TRUE) {
2099 $before .= ($mode != 'query_only' ?
'</div>' : ' ');
2101 $infunction = ($functionlevel > 0) ?
TRUE : FALSE;
2103 case 'alpha_columnType':
2104 if ($typearr[3] == 'alpha_columnAttrib') {
2107 if ($typearr[1] == 'alpha_columnType') {
2111 case 'alpha_columnAttrib':
2113 // ALTER TABLE tbl_name AUTO_INCREMENT = 1
2114 // COLLATE LATIN1_GENERAL_CI DEFAULT
2115 if ($typearr[1] == 'alpha_identifier' ||
$typearr[1] == 'alpha_charset') {
2118 if (($typearr[3] == 'alpha_columnAttrib') ||
($typearr[3] == 'quote_single') ||
($typearr[3] == 'digit_integer')) {
2122 // AUTO_INCREMENT = 31DEFAULT_CHARSET = utf-8
2124 if ($typearr[2] == 'alpha_columnAttrib' && $typearr[3] == 'alpha_reservedWord') {
2128 // select * from mysql.user where binary user="root"
2129 // binary is marked as alpha_columnAttrib
2130 // but should be marked as a reserved word
2131 if (strtoupper($arr[$i]['data']) == 'BINARY'
2132 && $typearr[3] == 'alpha_identifier') {
2136 case 'alpha_reservedWord':
2137 // do not uppercase the reserved word if we are calling
2138 // this function in query_only mode, because we need
2139 // the original query (otherwise we get problems with
2140 // semi-reserved words like "storage" which is legal
2141 // as an identifier name)
2143 if ($mode != 'query_only') {
2144 $arr[$i]['data'] = strtoupper($arr[$i]['data']);
2147 if ((($typearr[1] != 'alpha_reservedWord')
2148 ||
(($typearr[1] == 'alpha_reservedWord')
2149 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_no_newline, $keywords_no_newline_cnt)))
2150 && ($typearr[1] != 'punct_level_plus')
2151 && (!PMA_STR_binarySearchInArr($arr[$i]['data'], $keywords_no_newline, $keywords_no_newline_cnt))) {
2152 // do not put a space before the first token, because
2153 // we use a lot of eregi() checking for the first
2154 // reserved word at beginning of query
2155 // so do not put a newline before
2157 // also we must not be inside a privilege list
2159 // the alpha_identifier exception is there to
2161 // GRANT SELECT ON mydb.mytable TO myuser@localhost
2162 // (else, we get mydb.mytableTO )
2164 // the quote_single exception is there to
2166 // GRANT ... TO 'marc'@'domain.com' IDENTIFIED...
2168 * @todo fix all cases and find why this happens
2171 if (!$in_priv_list ||
$typearr[1] == 'alpha_identifier' ||
$typearr[1] == 'quote_single' ||
$typearr[1] == 'white_newline') {
2172 $before .= $space_alpha_reserved_word;
2175 // on first keyword, check if it introduces a
2177 if (PMA_STR_binarySearchInArr($arr[$i]['data'], $keywords_priv_list, $keywords_priv_list_cnt)) {
2178 $in_priv_list = TRUE;
2185 switch ($arr[$i]['data']) {
2187 if (!$in_priv_list) {
2188 $space_punct_listsep = $html_line_break;
2189 $space_alpha_reserved_word = ' ';
2203 if (!$in_priv_list) {
2204 $space_punct_listsep = $html_line_break;
2205 $space_alpha_reserved_word = ' ';
2210 if (!$in_priv_list) {
2211 $space_punct_listsep = $html_line_break;
2212 $space_alpha_reserved_word = $html_line_break;
2216 $space_punct_listsep = ' ';
2217 $space_alpha_reserved_word = $html_line_break;
2220 $space_punct_listsep = ' ';
2221 $space_alpha_reserved_word = $html_line_break;
2225 } // end switch ($arr[$i]['data'])
2229 case 'digit_integer':
2233 * @todo could there be other types preceding a digit?
2235 if ($typearr[1] == 'alpha_reservedWord') {
2238 if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
2241 if ($typearr[1] == 'alpha_columnAttrib') {
2245 case 'alpha_variable':
2246 // other workaround for a problem similar to the one
2247 // explained below for quote_single
2248 if (!$in_priv_list && $typearr[3] != 'quote_backtick') {
2252 case 'quote_double':
2253 case 'quote_single':
2254 // workaround: for the query
2255 // REVOKE SELECT ON `base2\_db`.* FROM 'user'@'%'
2256 // the @ is incorrectly marked as alpha_variable
2257 // in the parser, and here, the '%' gets a blank before,
2258 // which is a syntax error
2259 if ($typearr[1] !='alpha_variable') {
2262 if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
2266 case 'quote_backtick':
2267 if ($typearr[3] != 'punct_qualifier' && $typearr[3] != 'alpha_variable') {
2270 if ($typearr[1] != 'punct_qualifier' && $typearr[1] != 'alpha_variable') {
2276 } // end switch ($typearr[2])
2279 if ($typearr[3] != 'punct_qualifier') {
2284 $str .= $before . ($mode=='color' ?
PMA_SQP_formatHTML_colorize($arr[$i]) : $arr[$i]['data']). $after;
2286 if ($mode=='color') {
2291 } // end of the "PMA_SQP_formatHtml()" function
2295 * Builds a CSS rule used for html formatted SQL queries
2297 * @param string The class name
2298 * @param string The property name
2299 * @param string The property value
2301 * @return string The CSS rule
2305 * @see PMA_SQP_buildCssData()
2307 function PMA_SQP_buildCssRule($classname, $property, $value)
2309 $str = '.' . $classname . ' {';
2311 $str .= $property . ': ' . $value . ';';
2316 } // end of the "PMA_SQP_buildCssRule()" function
2320 * Builds CSS rules used for html formatted SQL queries
2322 * @return string The CSS rules set
2326 * @global array The current PMA configuration
2328 * @see PMA_SQP_buildCssRule()
2330 function PMA_SQP_buildCssData()
2335 foreach ($cfg['SQP']['fmtColor'] AS $key => $col) {
2336 $css_string .= PMA_SQP_buildCssRule('syntax_' . $key, 'color', $col);
2339 for ($i = 0; $i < 8; $i++
) {
2340 $css_string .= PMA_SQP_buildCssRule('syntax_indent' . $i, 'margin-left', ($i * $cfg['SQP']['fmtInd']) . $cfg['SQP']['fmtIndUnit']);
2344 } // end of the "PMA_SQP_buildCssData()" function
2346 if ( ! defined( 'PMA_MINIMUM_COMMON' ) ) {
2348 * Gets SQL queries with no format
2350 * @param array The SQL queries list
2352 * @return string The SQL queries with no format
2356 function PMA_SQP_formatNone($arr)
2358 $formatted_sql = htmlspecialchars($arr['raw']);
2359 $formatted_sql = preg_replace("@((\015\012)|(\015)|(\012)){3,}@", "\n\n", $formatted_sql);
2361 return $formatted_sql;
2362 } // end of the "PMA_SQP_formatNone()" function
2366 * Gets SQL queries in text format
2369 * @param array The SQL queries list
2371 * @return string The SQL queries in text format
2375 function PMA_SQP_formatText($arr)
2377 return PMA_SQP_formatNone($arr);
2378 } // end of the "PMA_SQP_formatText()" function
2379 } // end if: minimal common.lib needed?