<?php/* $Id: sqlparser.lib.php,v 2.47 2006/01/17 17:02:30 cybot_tm Exp $ */// vim: expandtab sw=4 ts=4 sts=4:/** SQL Parser Functions for phpMyAdmin** Copyright 2002 Robin Johnson <robbat2@users.sourceforge.net>* http://www.orbis-terrarum.net/?l=people.robbat2** These functions define an SQL parser system, capable of understanding and* extracting data from a MySQL type SQL query.** The basic procedure for using the new SQL parser:* On any page that needs to extract data from a query or to pretty-print a* query, you need code like this up at the top:** ($sql contains the query)* $parsed_sql = PMA_SQP_parse($sql);** If you want to extract data from it then, you just need to run* $sql_info = PMA_SQP_analyze($parsed_sql);** lem9: See comments in PMA_SQP_analyze for the returned info* from the analyzer.** If you want a pretty-printed version of the query, do:* $string = PMA_SQP_formatHtml($parsed_sql);* (note that that you need to have syntax.css.php included somehow in your* page for it to work, I recommend '<link rel="stylesheet" type="text/css"* href="syntax.css.php" />' at the moment.)*//*** Minimum inclusion? (i.e. for the stylesheet builder)*/if ( ! defined( 'PMA_MINIMUM_COMMON' ) ) {/*** Include the string library as we use it heavily*/require_once('./libraries/string.lib.php');/*** Include data for the SQL Parser*/require_once('./libraries/sqlparser.data.php');require_once('./libraries/mysql_charsets.lib.php');if (!isset($mysql_charsets)) {$mysql_charsets = array();$mysql_charsets_count = 0;$mysql_collations_flat = array();$mysql_collations_count = 0;}if (!defined('DEBUG_TIMING')) {function PMA_SQP_arrayAdd(&$arr, $type, $data, &$arrsize){$arr[] = array('type' => $type, 'data' => $data);$arrsize++;} // end of the "PMA_SQP_arrayAdd()" function} else {function PMA_SQP_arrayAdd(&$arr, $type, $data, &$arrsize){global $timer;$t = $timer;$arr[] = array('type' => $type, 'data' => $data, 'time' => $t);$timer = microtime();$arrsize++;} // end of the "PMA_SQP_arrayAdd()" function} // end if... else.../*** Reset the error variable for the SQL parser** @access public*/// Added, Robbat2 - 13 Janurary 2003, 2:59PMfunction PMA_SQP_resetError(){global $SQP_errorString;$SQP_errorString = '';unset($SQP_errorString);}/*** Get the contents of the error variable for the SQL parser** @return string Error string from SQL parser** @access public*/// Added, Robbat2 - 13 Janurary 2003, 2:59PMfunction PMA_SQP_getErrorString(){global $SQP_errorString;return isset($SQP_errorString) ? $SQP_errorString : '';}/*** Check if the SQL parser hit an error** @return boolean error state** @access public*/// Added, Robbat2 - 13 Janurary 2003, 2:59PMfunction PMA_SQP_isError(){global $SQP_errorString;return isset($SQP_errorString) && !empty($SQP_errorString);}/*** Set an error message for the system** @param string The error message* @param string The failing SQL query** @access private* @scope SQL Parser internal*/// Revised, Robbat2 - 13 Janurary 2003, 2:59PMfunction PMA_SQP_throwError($message, $sql){global $SQP_errorString;$SQP_errorString = '<p>'.$GLOBALS['strSQLParserUserError'] . '</p>' . "\n". '<pre>' . "\n". 'ERROR: ' . $message . "\n". 'SQL: ' . htmlspecialchars($sql) . "\n". '</pre>' . "\n";} // end of the "PMA_SQP_throwError()" function/*** Do display the bug report** @param string The error message* @param string The failing SQL query** @access public*/function PMA_SQP_bug($message, $sql){global $SQP_errorString;$debugstr = 'ERROR: ' . $message . "\n";$debugstr .= 'CVS: $Id: sqlparser.lib.php,v 2.47 2006/01/17 17:02:30 cybot_tm Exp $' . "\n";$debugstr .= 'MySQL: '.PMA_MYSQL_STR_VERSION . "\n";$debugstr .= 'USR OS, AGENT, VER: ' . PMA_USR_OS . ' ' . PMA_USR_BROWSER_AGENT . ' ' . PMA_USR_BROWSER_VER . "\n";$debugstr .= 'PMA: ' . PMA_VERSION . "\n";$debugstr .= 'PHP VER,OS: ' . PMA_PHP_STR_VERSION . ' ' . PHP_OS . "\n";$debugstr .= 'LANG: ' . $GLOBALS['lang'] . "\n";$debugstr .= 'SQL: ' . htmlspecialchars($sql);$encodedstr = $debugstr;if (@function_exists('gzcompress')) {$encodedstr = gzcompress($debugstr, 9);}$encodedstr = preg_replace("/(\015\012)|(\015)|(\012)/", '<br />' . "\n", chunk_split(base64_encode($encodedstr)));$SQP_errorString .= $GLOBALS['strSQLParserBugMessage'] . '<br />' . "\n". '----' . $GLOBALS['strBeginCut'] . '----' . '<br />' . "\n". $encodedstr . "\n". '----' . $GLOBALS['strEndCut'] . '----' . '<br />' . "\n";$SQP_errorString .= '----' . $GLOBALS['strBeginRaw'] . '----<br />' . "\n". '<pre>' . "\n". $debugstr. '</pre>' . "\n". '----' . $GLOBALS['strEndRaw'] . '----<br />' . "\n";} // end of the "PMA_SQP_bug()" function/*** Parses the SQL queries** @param string The SQL query list** @return mixed Most of times, nothing...** @global array The current PMA configuration* @global array MySQL column attributes* @global array MySQL reserved words* @global array MySQL column types* @global array MySQL function names* @global integer MySQL column attributes count* @global integer MySQL reserved words count* @global integer MySQL column types count* @global integer MySQL function names count* @global array List of available character sets* @global array List of available collations* @global integer Character sets count* @global integer Collations count** @access public*/function PMA_SQP_parse($sql){global $cfg;global $PMA_SQPdata_column_attrib, $PMA_SQPdata_reserved_word, $PMA_SQPdata_column_type, $PMA_SQPdata_function_name,$PMA_SQPdata_column_attrib_cnt, $PMA_SQPdata_reserved_word_cnt, $PMA_SQPdata_column_type_cnt, $PMA_SQPdata_function_name_cnt;global $mysql_charsets, $mysql_collations_flat, $mysql_charsets_count, $mysql_collations_count;global $PMA_SQPdata_forbidden_word, $PMA_SQPdata_forbidden_word_cnt;// rabus: Convert all line feeds to Unix style$sql = str_replace("\r\n", "\n", $sql);$sql = str_replace("\r", "\n", $sql);$len = PMA_strlen($sql);if ($len == 0) {return array();}$sql_array = array();$sql_array['raw'] = $sql;$count1 = 0;$count2 = 0;$punct_queryend = ';';$punct_qualifier = '.';$punct_listsep = ',';$punct_level_plus = '(';$punct_level_minus = ')';$digit_floatdecimal = '.';$digit_hexset = 'x';$bracket_list = '()[]{}';$allpunct_list = '-,;:!?/.^~\*&%+<=>|';$allpunct_list_pair = array (0 => '!=',1 => '&&',2 => ':=',3 => '<<',4 => '<=',5 => '<=>',6 => '<>',7 => '>=',8 => '>>',9 => '||');$allpunct_list_pair_size = 10; //count($allpunct_list_pair);$quote_list = '\'"`';$arraysize = 0;while ($count2 < $len) {$c = PMA_substr($sql, $count2, 1);$count1 = $count2;if (($c == "\n")) {$count2++;PMA_SQP_arrayAdd($sql_array, 'white_newline', '', $arraysize);continue;}// Checks for white spaceif (PMA_STR_isSpace($c)) {$count2++;continue;}// Checks for comment lines.// MySQL style #// C style /* */// ANSI style --if (($c == '#')|| (($count2 + 1 < $len) && ($c == '/') && (PMA_substr($sql, $count2 + 1, 1) == '*'))|| (($count2 + 2 == $len) && ($c == '-') && (PMA_substr($sql, $count2 + 1, 1) == '-'))|| (($count2 + 2 < $len) && ($c == '-') && (PMA_substr($sql, $count2 + 1, 1) == '-') && ((PMA_substr($sql, $count2 + 2, 1) <= ' ')))) {$count2++;$pos = 0;$type = 'bad';switch ($c) {case '#':$type = 'mysql';case '-':$type = 'ansi';$pos = $GLOBALS['PMA_strpos']($sql, "\n", $count2);break;case '/':$type = 'c';$pos = $GLOBALS['PMA_strpos']($sql, '*/', $count2);$pos += 2;break;default:break;} // end switch$count2 = ($pos < $count2) ? $len : $pos;$str = PMA_substr($sql, $count1, $count2 - $count1);PMA_SQP_arrayAdd($sql_array, 'comment_' . $type, $str, $arraysize);continue;} // end if// Checks for something inside quotation marksif (PMA_STR_strInStr($c, $quote_list)) {$startquotepos = $count2;$quotetype = $c;$count2++;$escaped = FALSE;$escaped_escaped = FALSE;$pos = $count2;$oldpos = 0;do {$oldpos = $pos;$pos = $GLOBALS['PMA_strpos'](' ' . $sql, $quotetype, $oldpos + 1) - 1;// ($pos === FALSE)if ($pos < 0) {$debugstr = $GLOBALS['strSQPBugUnclosedQuote'] . ' @ ' . $startquotepos. "\n". 'STR: ' . htmlspecialchars($quotetype);PMA_SQP_throwError($debugstr, $sql);return $sql;}// If the quote is the first character, it can't be// escaped, so don't do the rest of the codeif ($pos == 0) {break;}// Checks for MySQL escaping using a \// And checks for ANSI escaping using the $quotetype characterif (($pos < $len) && PMA_STR_charIsEscaped($sql, $pos)) {$pos ++;continue;} elseif (($pos + 1 < $len) && (PMA_substr($sql, $pos, 1) == $quotetype) && (PMA_substr($sql, $pos + 1, 1) == $quotetype)) {$pos = $pos + 2;continue;} else {break;}} while ($len > $pos); // end do$count2 = $pos;$count2++;$type = 'quote_';switch ($quotetype) {case '\'':$type .= 'single';break;case '"':$type .= 'double';break;case '`':$type .= 'backtick';break;default:break;} // end switch$data = PMA_substr($sql, $count1, $count2 - $count1);PMA_SQP_arrayAdd($sql_array, $type, $data, $arraysize);continue;}// Checks for bracketsif (PMA_STR_strInStr($c, $bracket_list)) {// All bracket tokens are only one item long$count2++;$type_type = '';if (PMA_STR_strInStr($c, '([{')) {$type_type = 'open';} else {$type_type = 'close';}$type_style = '';if (PMA_STR_strInStr($c, '()')) {$type_style = 'round';} elseif (PMA_STR_strInStr($c, '[]')) {$type_style = 'square';} else {$type_style = 'curly';}$type = 'punct_bracket_' . $type_type . '_' . $type_style;PMA_SQP_arrayAdd($sql_array, $type, $c, $arraysize);continue;}// Checks for identifier (alpha or numeric)if (PMA_STR_isSqlIdentifier($c, FALSE) || ($c == '@') || ($c == '.' && PMA_STR_isDigit(PMA_substr($sql, $count2 + 1, 1)))) {$count2 ++;//TODO: a @ can also be present in expressions like// FROM 'user'@'%'// or TO 'user'@'%'// in this case, the @ is wrongly marked as alpha_variable$is_sql_variable = ($c == '@');$is_digit = (!$is_sql_variable) && PMA_STR_isDigit($c);$is_hex_digit = ($is_digit) && ($c == '.') && ($c == '0') && ($count2 < $len) && (PMA_substr($sql, $count2, 1) == 'x');$is_float_digit = $c == '.';$is_float_digit_exponent = FALSE;// Nijel: Fast skip is especially needed for huge BLOB data, requires PHP at least 4.3.0:if (PMA_PHP_INT_VERSION >= 40300) {if ($is_hex_digit) {$count2++;$pos = strspn($sql, '0123456789abcdefABCDEF', $count2);if ($pos > $count2) {$count2 = $pos;}unset($pos);} elseif ($is_digit) {$pos = strspn($sql, '0123456789', $count2);if ($pos > $count2) {$count2 = $pos;}unset($pos);}}while (($count2 < $len) && PMA_STR_isSqlIdentifier(PMA_substr($sql, $count2, 1), ($is_sql_variable || $is_digit))) {$c2 = PMA_substr($sql, $count2, 1);if ($is_sql_variable && ($c2 == '.')) {$count2++;continue;}if ($is_digit && (!$is_hex_digit) && ($c2 == '.')) {$count2++;if (!$is_float_digit) {$is_float_digit = TRUE;continue;} else {$debugstr = $GLOBALS['strSQPBugInvalidIdentifer'] . ' @ ' . ($count1+1) . "\n". 'STR: ' . htmlspecialchars(PMA_substr($sql, $count1, $count2 - $count1));PMA_SQP_throwError($debugstr, $sql);return $sql;}}if ($is_digit && (!$is_hex_digit) && (($c2 == 'e') || ($c2 == 'E'))) {if (!$is_float_digit_exponent) {$is_float_digit_exponent = TRUE;$is_float_digit = TRUE;$count2++;continue;} else {$is_digit = FALSE;$is_float_digit = FALSE;}}if (($is_hex_digit && PMA_STR_isHexDigit($c2)) || ($is_digit && PMA_STR_isDigit($c2))) {$count2++;continue;} else {$is_digit = FALSE;$is_hex_digit = FALSE;}$count2++;} // end while$l = $count2 - $count1;$str = PMA_substr($sql, $count1, $l);$type = '';if ($is_digit) {$type = 'digit';if ($is_float_digit) {$type .= '_float';} elseif ($is_hex_digit) {$type .= '_hex';} else {$type .= '_integer';}} else {if ($is_sql_variable != FALSE) {$type = 'alpha_variable';} else {$type = 'alpha';}} // end if... else....PMA_SQP_arrayAdd($sql_array, $type, $str, $arraysize);continue;}// Checks for punctif (PMA_STR_strInStr($c, $allpunct_list)) {while (($count2 < $len) && PMA_STR_strInStr(PMA_substr($sql, $count2, 1), $allpunct_list)) {$count2++;}$l = $count2 - $count1;if ($l == 1) {$punct_data = $c;} else {$punct_data = PMA_substr($sql, $count1, $l);}// Special case, sometimes, althought two characters are// adjectent directly, they ACTUALLY need to be seperateif ($l == 1) {$t_suffix = '';switch ($punct_data) {case $punct_queryend:$t_suffix = '_queryend';break;case $punct_qualifier:$t_suffix = '_qualifier';break;case $punct_listsep:$t_suffix = '_listsep';break;default:break;}PMA_SQP_arrayAdd($sql_array, 'punct' . $t_suffix, $punct_data, $arraysize);} elseif (PMA_STR_binarySearchInArr($punct_data, $allpunct_list_pair, $allpunct_list_pair_size)) {// Ok, we have one of the valid combined punct expressionsPMA_SQP_arrayAdd($sql_array, 'punct', $punct_data, $arraysize);} else {// Bad luck, lets split it up more$first = $punct_data[0];$first2 = $punct_data[0] . $punct_data[1];$last2 = $punct_data[$l - 2] . $punct_data[$l - 1];$last = $punct_data[$l - 1];if (($first == ',') || ($first == ';') || ($first == '.') || ($first == '*')) {$count2 = $count1 + 1;$punct_data = $first;} elseif (($last2 == '/*') || (($last2 == '--') && ($count2 == $len || PMA_substr($sql, $count2, 1) <= ' ') )) {$count2 -= 2;$punct_data = PMA_substr($sql, $count1, $count2 - $count1);} elseif (($last == '-') || ($last == '+') || ($last == '!')) {$count2--;$punct_data = PMA_substr($sql, $count1, $count2 - $count1);// TODO: for negation operator, split in 2 tokens ?// "select x&~1 from t"// becomes "select x & ~ 1 from t" ?} elseif ($last != '~') {$debugstr = $GLOBALS['strSQPBugUnknownPunctuation'] . ' @ ' . ($count1+1) . "\n". 'STR: ' . htmlspecialchars($punct_data);PMA_SQP_throwError($debugstr, $sql);return $sql;}PMA_SQP_arrayAdd($sql_array, 'punct', $punct_data, $arraysize);continue;} // end if... elseif... elsecontinue;}// DEBUG$count2++;$debugstr = 'C1 C2 LEN: ' . $count1 . ' ' . $count2 . ' ' . $len . "\n". 'STR: ' . PMA_substr($sql, $count1, $count2 - $count1) . "\n";PMA_SQP_bug($debugstr, $sql);return $sql;} // end while ($count2 < $len)if ($arraysize > 0) {$t_next = $sql_array[0]['type'];$t_prev = '';$t_bef_prev = '';$t_cur = '';$d_next = $sql_array[0]['data'];$d_prev = '';$d_bef_prev = '';$d_cur = '';$d_next_upper = $t_next == 'alpha' ? strtoupper($d_next) : $d_next;$d_prev_upper = '';$d_bef_prev_upper = '';$d_cur_upper = '';}for ($i = 0; $i < $arraysize; $i++) {$t_bef_prev = $t_prev;$t_prev = $t_cur;$t_cur = $t_next;$d_bef_prev = $d_prev;$d_prev = $d_cur;$d_cur = $d_next;$d_bef_prev_upper = $d_prev_upper;$d_prev_upper = $d_cur_upper;$d_cur_upper = $d_next_upper;if (($i + 1) < $arraysize) {$t_next = $sql_array[$i + 1]['type'];$d_next = $sql_array[$i + 1]['data'];$d_next_upper = $t_next == 'alpha' ? strtoupper($d_next) : $d_next;} else {$t_next = '';$d_next = '';$d_next_upper = '';}//DEBUG echo "[prev: <b>".$d_prev."</b> ".$t_prev."][cur: <b>".$d_cur."</b> ".$t_cur."][next: <b>".$d_next."</b> ".$t_next."]<br />";if ($t_cur == 'alpha') {$t_suffix = '_identifier';if (($t_next == 'punct_qualifier') || ($t_prev == 'punct_qualifier')) {$t_suffix = '_identifier';} elseif (($t_next == 'punct_bracket_open_round')&& PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_function_name, $PMA_SQPdata_function_name_cnt)) {// FIXME-2005-10-16: in the case of a CREATE TABLE containing a TIMESTAMP,// since TIMESTAMP() is also a function, it's found here and// the token is wrongly marked as alpha_functionName. But we// compensate for this when analysing for timestamp_not_null// later in this script.$t_suffix = '_functionName';} elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_type, $PMA_SQPdata_column_type_cnt)) {$t_suffix = '_columnType';// Temporary fix for BUG #621357//TODO FIX PROPERLY NEEDS OVERHAUL OF SQL TOKENIZERif ($d_cur_upper == 'SET' && $t_next != 'punct_bracket_open_round') {$t_suffix = '_reservedWord';}//END OF TEMPORARY FIX// CHARACTER is a synonym for CHAR, but can also be meant as// CHARACTER SET. In this case, we have a reserved word.if ($d_cur_upper == 'CHARACTER' && $d_next_upper == 'SET') {$t_suffix = '_reservedWord';}// experimental// current is a column type, so previous must not be// a reserved word but an identifier// CREATE TABLE SG_Persons (first varchar(64))//if ($sql_array[$i-1]['type'] =='alpha_reservedWord') {// $sql_array[$i-1]['type'] = 'alpha_identifier';//}} elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_reserved_word, $PMA_SQPdata_reserved_word_cnt)) {$t_suffix = '_reservedWord';} elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_attrib, $PMA_SQPdata_column_attrib_cnt)) {$t_suffix = '_columnAttrib';// INNODB is a MySQL table type, but in "SHOW INNODB STATUS",// it should be regarded as a reserved word.if ($d_cur_upper == 'INNODB' && $d_prev_upper == 'SHOW' && $d_next_upper == 'STATUS') {$t_suffix = '_reservedWord';}if ($d_cur_upper == 'DEFAULT' && $d_next_upper == 'CHARACTER') {$t_suffix = '_reservedWord';}// Binary as character setif ($d_cur_upper == 'BINARY' && (($d_bef_prev_upper == 'CHARACTER' && $d_prev_upper == 'SET')|| ($d_bef_prev_upper == 'SET' && $d_prev_upper == '=')|| ($d_bef_prev_upper == 'CHARSET' && $d_prev_upper == '=')|| $d_prev_upper == 'CHARSET') && PMA_STR_binarySearchInArr($d_cur, $mysql_charsets, count($mysql_charsets))) {$t_suffix = '_charset';}} elseif (PMA_STR_binarySearchInArr($d_cur, $mysql_charsets, $mysql_charsets_count)|| PMA_STR_binarySearchInArr($d_cur, $mysql_collations_flat, $mysql_collations_count)|| ($d_cur{0} == '_' && PMA_STR_binarySearchInArr(substr($d_cur, 1), $mysql_charsets, $mysql_charsets_count))) {$t_suffix = '_charset';} else {// Do nothing}// check if present in the list of forbidden wordsif ($t_suffix == '_reservedWord' && PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_forbidden_word, $PMA_SQPdata_forbidden_word_cnt)) {$sql_array[$i]['forbidden'] = TRUE;} else {$sql_array[$i]['forbidden'] = FALSE;}$sql_array[$i]['type'] .= $t_suffix;}} // end for// Stores the size of the array inside the array, as count() is a slow// operation.$sql_array['len'] = $arraysize;// Sends the data backreturn $sql_array;} // end of the "PMA_SQP_parse()" function/*** Checks for token types being what we want...** @param string String of type that we have* @param string String of type that we want** @return boolean result of check** @access private*/function PMA_SQP_typeCheck($toCheck, $whatWeWant){$typeSeperator = '_';if (strcmp($whatWeWant, $toCheck) == 0) {return TRUE;} else {if (strpos($whatWeWant, $typeSeperator) === FALSE) {return strncmp($whatWeWant, $toCheck, strpos($toCheck, $typeSeperator)) == 0;} else {return FALSE;}}}/*** 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' => '','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;// for SELECT EXTRACT(YEAR_MONTH FROM CURDATE())// we must not use CURDATE as a table_ref// so we track wether 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** query clauses* -------------** The select is splitted in those clauses:* ['select_expr_clause']* ['from_clause']* ['group_by_clause']* ['order_by_clause']* ['having_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* -------------------** For now, mostly used to detect the DEFAULT CURRENT_TIMESTAMP and* ON UPDATE CURRENT_TIMESTAMP clauses of the CREATE TABLE query.* 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 SELECTfor ($i = 0; $i < $size; $i++) {//DEBUG echo "trace loop1 <b>" . $arr[$i]['data'] . "</b> (" . $arr[$i]['type'] . ")<br />";// High speed seek for locating the end of the current queryif ($seek_queryend == TRUE) {if ($arr[$i]['type'] == 'punct_queryend') {$seek_queryend = FALSE;} else {continue;} // end if (type == punct_queryend)} // end if ($seek_queryend)// TODO: when we find a UNION, should we split// in another subresult?// Note: do not split if this is a punct_queryend for the// first and only queryif ($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') {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') {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 ($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 thisif ($subresult['querytype'] == '') {$subresult['querytype'] = strtoupper($arr[$i]['data']);} // end if (querytype was empty)// Check if we support this type of queryif (!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') {$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;//TODO: check embedded double quotes or backticks?// and/or remove just the first and last character?case 'quote_backtick':$identifier = str_replace('`', '', $arr[$i]['data']);break;case 'quote_double':$identifier = str_replace('"', '', $arr[$i]['data']);break;case 'quote_single':$identifier = str_replace("'", "", $arr[$i]['data']);break;} // end switchif ($subresult['querytype'] == 'SELECT' && !$in_group_concat) {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 savedif (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 tableif ($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 dbif ($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 (TODO: there could be another// query after this one)// or we just got a reserved wordif (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 dbif ($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 expressionsif (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 identifierif (($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_identifierif (($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 refsif (($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 falseif (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)// -------------------------------------------------------// This is a big hunk of debugging code by Marc for this.// -------------------------------------------------------/*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 hereif (isset($current_table_ref) && $current_table_ref > -1) {$subresult['queryflags']['select_from'] = 1;}$collect_section_before_limit = TRUE;$section_before_limit = '';$section_after_limit = '';$seen_reserved_word = FALSE;$seen_group = FALSE;$seen_order = 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$in_from = FALSE;$in_group_concat = FALSE;$unsorted_query = '';$first_reserved_word = '';$current_identifier = '';for ($i = 0; $i < $size; $i++) {//DEBUG echo "trace loop2 <b>" . $arr[$i]['data'] . "</b> (" . $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// TODO: check for punct_queryend// TODO: verify C-style comments?if ($arr[$i]['type'] == 'comment_ansi') {$collect_section_before_limit = FALSE;}if ($arr[$i]['type'] == 'alpha_reservedWord') {$upper_data = strtoupper($arr[$i]['data']);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 confirmedif ($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 == 'PROCEDURE') {$collect_section_before_limit = FALSE;}// TODO: set also to FALSE if we find// FOR UPDATE// LOCK IN SHARE MODEif ($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...FROMif ($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 BYif ($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) {$in_order_by = TRUE;$order_by_clause = '';}}// if we find one of the words that could end the clauseif (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 blank 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;}}}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') {$order_by_clause .= $arr[$i]['data'] . $sep;}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'];}}if (isset($subresult['queryflags']['select_from'])&& $subresult['queryflags']['select_from'] == 1&& !$seen_order) {$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;}}// clear $upper_data for next iteration$upper_data='';if ($collect_section_before_limit && $arr[$i]['type'] != 'punct_queryend') {$section_before_limit .= $arr[$i]['data'] . $sep;} else {$section_after_limit .= $arr[$i]['data'] . $sep;}} // end for $i (loop #2)// -----------------------------------------------------// 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;$in_create_table_fields = FALSE;$brackets_level = 0;$in_timestamp_options = FALSE;$seen_default = FALSE;for ($i = 0; $i < $size; $i++) {// DEBUG echo "<b>" . $arr[$i]['data'] . "</b> " . $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 == '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_TIMESTAMPif ($upper_data == 'ON') {if ($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 analysisif ($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;}}}// note: the "or" part here is a workaround for a bug// (see FIXME-2005-10-16)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') {$in_timestamp_options = TRUE;} else {$in_timestamp_options = FALSE;}}}if ($arr[$i]['type'] == 'quote_backtick' || $arr[$i]['type'] == 'alpha_identifier') {if ($arr[$i]['type'] == 'quote_backtick') {// remove backquotes$identifier = str_replace('`', '', $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) {// 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 REFERENCESif ($brackets_level > 1) {$foreign[$foreign_key_number]['ref_index_list'][] = $identifier;} else {// for MySQL 4.0.18, identifier is// `table` or `db`.`table`// the first pass will pick the db name// the next pass will execute the else and pick the// db name in $db_table[0]if ($arr[$i+1]['type'] == 'punct_qualifier') {$foreign[$foreign_key_number]['ref_db_name'] = $identifier;} else {// for MySQL 4.0.16, identifier is// `table` or `db.table`$db_table = explode('.', $identifier);if (isset($db_table[1])) {$foreign[$foreign_key_number]['ref_db_name'] = $db_table[0];$foreign[$foreign_key_number]['ref_table_name'] = $db_table[1];} else {$foreign[$foreign_key_number]['ref_table_name'] = $db_table[0];}}}}}} // end for $i (loop #3)// Fill the $subresult arrayif (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($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 properlyif ($subresult['querytype'] != '') {$result[] = $subresult;}return $result;} // end of the "PMA_SQP_analyze()" function/*** Colorizes SQL queries html formatted** @param array The SQL queries html formatted** @return array The colorized SQL queries** @access public*/function PMA_SQP_formatHtml_colorize($arr){$i = $GLOBALS['PMA_strpos']($arr['type'], '_');$class = '';if ($i > 0) {$class = 'syntax_' . PMA_substr($arr['type'], 0, $i) . ' ';}$class .= 'syntax_' . $arr['type'];//TODO: check why adding a "\n" after the </span> would cause extra// blanks to be displayed:// SELECT p . person_namereturn '<span class="' . $class . '">' . htmlspecialchars($arr['data']) . '</span>';} // end of the "PMA_SQP_formatHtml_colorize()" function/*** Formats SQL queries to html** @param array The SQL queries* @param string mode* @param integer starting token* @param integer number of tokens to format, -1 = all** @return string The formatted SQL queries** @access public*/function PMA_SQP_formatHtml($arr, $mode='color', $start_token=0,$number_of_tokens=-1){// then check for an arrayif (!is_array($arr)) {return htmlspecialchars($arr);}// first check for the SQL parser having hit an errorif (PMA_SQP_isError()) {return htmlspecialchars($arr['raw']);}// else do it properlyswitch ($mode) {case 'color':$str = '<span class="syntax">';$html_line_break = '<br />';break;case 'query_only':$str = '';$html_line_break = "\n";break;case 'text':$str = '';$html_line_break = '<br />';break;} // end switch$indent = 0;$bracketlevel = 0;$functionlevel = 0;$infunction = FALSE;$space_punct_listsep = ' ';$space_punct_listsep_function_name = ' ';// $space_alpha_reserved_word = '<br />'."\n";$space_alpha_reserved_word = ' ';$keywords_with_brackets_1before = array('INDEX','KEY','ON','USING');$keywords_with_brackets_1before_cnt = 4;$keywords_with_brackets_2before = array('IGNORE','INDEX','INTO','KEY','PRIMARY','PROCEDURE','REFERENCES','UNIQUE','USE');// $keywords_with_brackets_2before_cnt = count($keywords_with_brackets_2before);$keywords_with_brackets_2before_cnt = 9;// These reserved words do NOT get a newline placed near them.$keywords_no_newline = array('AS','ASC','DESC','DISTINCT','HOUR','INTERVAL','IS','LIKE','NOT','NULL','ON','REGEXP');$keywords_no_newline_cnt = 12;// These reserved words introduce a privilege list$keywords_priv_list = array('GRANT','REVOKE');$keywords_priv_list_cnt = 2;if ($number_of_tokens == -1) {$arraysize = $arr['len'];} else {$arraysize = $number_of_tokens;}$typearr = array();if ($arraysize >= 0) {$typearr[0] = '';$typearr[1] = '';$typearr[2] = '';//$typearr[3] = $arr[0]['type'];$typearr[3] = $arr[$start_token]['type'];}$in_priv_list = FALSE;for ($i = $start_token; $i < $arraysize; $i++) {// DEBUG echo "<b>" . $arr[$i]['data'] . "</b> " . $arr[$i]['type'] . "<br />";$before = '';$after = '';$indent = 0;// array_shift($typearr);/*0 prev21 prev2 current3 next*/if (($i + 1) < $arraysize) {// array_push($typearr, $arr[$i + 1]['type']);$typearr[4] = $arr[$i + 1]['type'];} else {//array_push($typearr, null);$typearr[4] = '';}for ($j=0; $j<4; $j++) {$typearr[$j] = $typearr[$j + 1];}switch ($typearr[2]) {case 'white_newline':$before = '';break;case 'punct_bracket_open_round':$bracketlevel++;$infunction = FALSE;// Make sure this array is sorted!if (($typearr[1] == 'alpha_functionName') || ($typearr[1] == 'alpha_columnType') || ($typearr[1] == 'punct')|| ($typearr[3] == 'digit_integer') || ($typearr[3] == 'digit_hex') || ($typearr[3] == 'digit_float')|| (($typearr[0] == 'alpha_reservedWord')&& PMA_STR_binarySearchInArr(strtoupper($arr[$i - 2]['data']), $keywords_with_brackets_2before, $keywords_with_brackets_2before_cnt))|| (($typearr[1] == 'alpha_reservedWord')&& PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_with_brackets_1before, $keywords_with_brackets_1before_cnt))) {$functionlevel++;$infunction = TRUE;$after .= ' ';} else {$indent++;$after .= ($mode != 'query_only' ? '<div class="syntax_indent' . $indent . '">' : ' ');}break;case 'alpha_identifier':if (($typearr[1] == 'punct_qualifier') || ($typearr[3] == 'punct_qualifier')) {$after = '';$before = '';}if (($typearr[3] == 'alpha_columnType') || ($typearr[3] == 'alpha_identifier')) {$after .= ' ';}break;case 'punct_qualifier':$before = '';$after = '';break;case 'punct_listsep':if ($infunction == TRUE) {$after .= $space_punct_listsep_function_name;} else {$after .= $space_punct_listsep;}break;case 'punct_queryend':if (($typearr[3] != 'comment_mysql') && ($typearr[3] != 'comment_ansi') && $typearr[3] != 'comment_c') {$after .= $html_line_break;$after .= $html_line_break;}$space_punct_listsep = ' ';$space_punct_listsep_function_name = ' ';$space_alpha_reserved_word = ' ';$in_priv_list = FALSE;break;case 'comment_mysql':case 'comment_ansi':$after .= $html_line_break;break;case 'punct':$before .= ' ';// workaround for// select * from mytable limit 0,-1// (a side effect of this workaround is that// select 20 - 9// becomes// select 20 -9// )if ($typearr[3] != 'digit_integer') {$after .= ' ';}break;case 'punct_bracket_close_round':$bracketlevel--;if ($infunction == TRUE) {$functionlevel--;$after .= ' ';$before .= ' ';} else {$indent--;$before .= ($mode != 'query_only' ? '</div>' : ' ');}$infunction = ($functionlevel > 0) ? TRUE : FALSE;break;case 'alpha_columnType':if ($typearr[3] == 'alpha_columnAttrib') {$after .= ' ';}if ($typearr[1] == 'alpha_columnType') {$before .= ' ';}break;case 'alpha_columnAttrib':// ALTER TABLE tbl_name AUTO_INCREMENT = 1// COLLATE LATIN1_GENERAL_CI DEFAULTif ($typearr[1] == 'alpha_identifier' || $typearr[1] == 'alpha_charset') {$before .= ' ';}if (($typearr[3] == 'alpha_columnAttrib') || ($typearr[3] == 'quote_single') || ($typearr[3] == 'digit_integer')) {$after .= ' ';}// workaround for// select * from mysql.user where binary user="root"// binary is marked as alpha_columnAttrib// but should be marked as a reserved wordif (strtoupper($arr[$i]['data']) == 'BINARY'&& $typearr[3] == 'alpha_identifier') {$after .= ' ';}break;case 'alpha_reservedWord':// do not uppercase the reserved word if we are calling// this function in query_only mode, because we need// the original query (otherwise we get problems with// semi-reserved words like "storage" which is legal// as an identifier name)if ($mode != 'query_only') {$arr[$i]['data'] = strtoupper($arr[$i]['data']);}if ((($typearr[1] != 'alpha_reservedWord')|| (($typearr[1] == 'alpha_reservedWord')&& PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_no_newline, $keywords_no_newline_cnt)))&& ($typearr[1] != 'punct_level_plus')&& (!PMA_STR_binarySearchInArr($arr[$i]['data'], $keywords_no_newline, $keywords_no_newline_cnt))) {// do not put a space before the first token, because// we use a lot of eregi() checking for the first// reserved word at beginning of query// so do not put a newline before//// also we must not be inside a privilege listif ($i > 0) {// the alpha_identifier exception is there to// catch cases like// GRANT SELECT ON mydb.mytable TO myuser@localhost// (else, we get mydb.mytableTO )//// the quote_single exception is there to// catch cases like// GRANT ... TO 'marc'@'domain.com' IDENTIFIED...//// TODO: fix all cases and find why this happensif (!$in_priv_list || $typearr[1] == 'alpha_identifier' || $typearr[1] == 'quote_single' || $typearr[1] == 'white_newline') {$before .= $space_alpha_reserved_word;}} else {// on first keyword, check if it introduces a// privilege listif (PMA_STR_binarySearchInArr($arr[$i]['data'], $keywords_priv_list, $keywords_priv_list_cnt)) {$in_priv_list = TRUE;}}} else {$before .= ' ';}switch ($arr[$i]['data']) {case 'CREATE':if (!$in_priv_list) {$space_punct_listsep = $html_line_break;$space_alpha_reserved_word = ' ';}break;case 'EXPLAIN':case 'DESCRIBE':case 'SET':case 'ALTER':case 'DELETE':case 'SHOW':case 'DROP':case 'UPDATE':case 'TRUNCATE':case 'ANALYZE':case 'ANALYSE':if (!$in_priv_list) {$space_punct_listsep = $html_line_break;$space_alpha_reserved_word = ' ';}break;case 'INSERT':case 'REPLACE':if (!$in_priv_list) {$space_punct_listsep = $html_line_break;$space_alpha_reserved_word = $html_line_break;}break;case 'VALUES':$space_punct_listsep = ' ';$space_alpha_reserved_word = $html_line_break;break;case 'SELECT':$space_punct_listsep = ' ';$space_alpha_reserved_word = $html_line_break;break;default:break;} // end switch ($arr[$i]['data'])$after .= ' ';break;case 'digit_integer':case 'digit_float':case 'digit_hex'://TODO: could there be other types preceding a digit?if ($typearr[1] == 'alpha_reservedWord') {$after .= ' ';}if ($infunction && $typearr[3] == 'punct_bracket_close_round') {$after .= ' ';}if ($typearr[1] == 'alpha_columnAttrib') {$before .= ' ';}break;case 'alpha_variable':// other workaround for a problem similar to the one// explained below for quote_singleif (!$in_priv_list) {$after = ' ';}break;case 'quote_double':case 'quote_single':// workaround: for the query// REVOKE SELECT ON `base2\_db`.* FROM 'user'@'%'// the @ is incorrectly marked as alpha_variable// in the parser, and here, the '%' gets a blank before,// which is a syntax errorif ($typearr[1] !='alpha_variable') {$before .= ' ';}if ($infunction && $typearr[3] == 'punct_bracket_close_round') {$after .= ' ';}break;case 'quote_backtick':if ($typearr[3] != 'punct_qualifier') {$after .= ' ';}if ($typearr[1] != 'punct_qualifier') {$before .= ' ';}break;default:break;} // end switch ($typearr[2])/*if ($typearr[3] != 'punct_qualifier') {$after .= ' ';}$after .= "\n";*/$str .= $before . ($mode=='color' ? PMA_SQP_formatHTML_colorize($arr[$i]) : $arr[$i]['data']). $after;} // end forif ($mode=='color') {$str .= '</span>';}return $str;} // end of the "PMA_SQP_formatHtml()" function}/*** Builds a CSS rule used for html formatted SQL queries** @param string The class name* @param string The property name* @param string The property value** @return string The CSS rule** @access public** @see PMA_SQP_buildCssData()*/function PMA_SQP_buildCssRule($classname, $property, $value){$str = '.' . $classname . ' {';if ($value != '') {$str .= $property . ': ' . $value . ';';}$str .= '}' . "\n";return $str;} // end of the "PMA_SQP_buildCssRule()" function/*** Builds CSS rules used for html formatted SQL queries** @return string The CSS rules set** @access public** @global array The current PMA configuration** @see PMA_SQP_buildCssRule()*/function PMA_SQP_buildCssData(){global $cfg;$css_string = '';foreach ($cfg['SQP']['fmtColor'] AS $key => $col) {$css_string .= PMA_SQP_buildCssRule('syntax_' . $key, 'color', $col);}for ($i = 0; $i < 8; $i++) {$css_string .= PMA_SQP_buildCssRule('syntax_indent' . $i, 'margin-left', ($i * $cfg['SQP']['fmtInd']) . $cfg['SQP']['fmtIndUnit']);}return $css_string;} // end of the "PMA_SQP_buildCssData()" functionif ( ! defined( 'PMA_MINIMUM_COMMON' ) ) {/*** Gets SQL queries with no format** @param array The SQL queries list** @return string The SQL queries with no format** @access public*/function PMA_SQP_formatNone($arr){$formatted_sql = htmlspecialchars($arr['raw']);$formatted_sql = preg_replace("@((\015\012)|(\015)|(\012)){3,}@", "\n\n", $formatted_sql);return $formatted_sql;} // end of the "PMA_SQP_formatNone()" function/*** Gets SQL queries in text format** @param array The SQL queries list** @return string The SQL queries in text format** @access public*/function PMA_SQP_formatText($arr){/*** TODO WRITE THIS!*/return PMA_SQP_formatNone($arr);} // end of the "PMA_SQP_formatText()" function} // end if: minimal common.lib needed??>