libraries/sqlparser.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /** SQL Parser Functions for phpMyAdmin
   4  *
   5  * These functions define an SQL parser system, capable of understanding and
   6  * extracting data from a MySQL type SQL query.
   7  *
   8  * The basic procedure for using the new SQL parser:
   9  * On any page that needs to extract data from a query or to pretty-print a
  10  * query, you need code like this up at the top:
  11  *
  12  * ($sql contains the query)
  13  * $parsed_sql = PMA_SQP_parse($sql);
  14  *
  15  * If you want to extract data from it then, you just need to run
  16  * $sql_info = PMA_SQP_analyze($parsed_sql);
  17  *
  18  * See comments in PMA_SQP_analyze for the returned info
  19  * from the analyzer.
  20  *
  21  * If you want a pretty-printed version of the query, do:
  22  * $string = PMA_SQP_formatHtml($parsed_sql);
  23  * (note that that you need to have syntax.css.php included somehow in your
  24  * page for it to work, I recommend '<link rel="stylesheet" type="text/css"
  25  * href="syntax.css.php" />' at the moment.)
  26  *
  27  * @version $Id$
  28  * @package phpMyAdmin
  29  */
  30 if (! defined('PHPMYADMIN')) {
  31     exit;
  32 }
  33
  34 /**
  35  * Minimum inclusion? (i.e. for the stylesheet builder)
  36  */
  37 if (! defined('PMA_MINIMUM_COMMON')) {
  38     /**
  39      * Include the string library as we use it heavily
  40      */
  41     require_once './libraries/string.lib.php';
  42
  43     /**
  44      * Include data for the SQL Parser
  45      */
  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;
  53     }
  54
  55     if (!defined('DEBUG_TIMING')) {
  56         /**
  57          * currently we don't need the $pos (token position in query)
  58          * for other purposes than LIMIT clause verification,
  59          * so many calls to this function do not include the 4th parameter
  60          */
  61         function PMA_SQP_arrayAdd(&$arr, $type, $data, &$arrsize, $pos = 0)
  62         {
  63             $arr[] = array('type' => $type, 'data' => $data, 'pos' => $pos);
  64             $arrsize++;
  65         } // end of the "PMA_SQP_arrayAdd()" function
  66     } else {
  67         /**
  68          * This is debug variant of above.
  69          * @ignore
  70          */
  71         function PMA_SQP_arrayAdd(&$arr, $type, $data, &$arrsize, $pos = 0)
  72         {
  73             global $timer;
  74
  75             $t     = $timer;
  76             $arr[] = array('type' => $type, 'data' => $data, 'pos' => $pos, 'time' => $t);
  77             $timer = microtime();
  78             $arrsize++;
  79         } // end of the "PMA_SQP_arrayAdd()" function
  80     } // end if... else...
  81
  82
  83     /**
  84      * Reset the error variable for the SQL parser
  85      *
  86      * @access public
  87      */
  88     // Added, Robbat2 - 13 Janurary 2003, 2:59PM
  89     function PMA_SQP_resetError()
  90     {
  91         global $SQP_errorString;
  92         $SQP_errorString = '';
  93         unset($SQP_errorString);
  94     }
  95
  96     /**
  97      * Get the contents of the error variable for the SQL parser
  98      *
  99      * @return string Error string from SQL parser
 100      *
 101      * @access public
 102      */
 103     // Added, Robbat2 - 13 Janurary 2003, 2:59PM
 104     function PMA_SQP_getErrorString()
 105     {
 106         global $SQP_errorString;
 107         return isset($SQP_errorString) ? $SQP_errorString : '';
 108     }
 109
 110     /**
 111      * Check if the SQL parser hit an error
 112      *
 113      * @return boolean error state
 114      *
 115      * @access public
 116      */
 117     // Added, Robbat2 - 13 Janurary 2003, 2:59PM
 118     function PMA_SQP_isError()
 119     {
 120         global $SQP_errorString;
 121         return isset($SQP_errorString) && !empty($SQP_errorString);
 122     }
 123
 124     /**
 125      * Set an error message for the system
 126      *
 127      * @param  string  The error message
 128      * @param  string  The failing SQL query
 129      *
 130      * @access private
 131      * @scope SQL Parser internal
 132      */
 133     // Revised, Robbat2 - 13 Janurary 2003, 2:59PM
 134     function PMA_SQP_throwError($message, $sql)
 135     {
 136         global $SQP_errorString;
 137         $SQP_errorString = '<p>'.$GLOBALS['strSQLParserUserError'] . '</p>' . "\n"
 138             . '<pre>' . "\n"
 139             . 'ERROR: ' . $message . "\n"
 140             . 'SQL: ' . htmlspecialchars($sql) .  "\n"
 141             . '</pre>' . "\n";
 142
 143     } // end of the "PMA_SQP_throwError()" function
 144
 145
 146     /**
 147      * Do display the bug report
 148      *
 149      * @param  string  The error message
 150      * @param  string  The failing SQL query
 151      *
 152      * @access public
 153      */
 154     function PMA_SQP_bug($message, $sql)
 155     {
 156         global $SQP_errorString;
 157         $debugstr = 'ERROR: ' . $message . "\n";
 158         $debugstr .= 'SVN: $Id$' . "\n";
 159         $debugstr .= 'MySQL: '.PMA_MYSQL_STR_VERSION . "\n";
 160         $debugstr .= 'USR OS, AGENT, VER: ' . PMA_USR_OS . ' ' . PMA_USR_BROWSER_AGENT . ' ' . PMA_USR_BROWSER_VER . "\n";
 161         $debugstr .= 'PMA: ' . PMA_VERSION . "\n";
 162         $debugstr .= 'PHP VER,OS: ' . PMA_PHP_STR_VERSION . ' ' . PHP_OS . "\n";
 163         $debugstr .= 'LANG: ' . $GLOBALS['lang'] . "\n";
 164         $debugstr .= 'SQL: ' . htmlspecialchars($sql);
 165
 166         $encodedstr     = $debugstr;
 167         if (@function_exists('gzcompress')) {
 168             $encodedstr = gzcompress($debugstr, 9);
 169         }
 170         $encodedstr     = preg_replace("/(\015\012)|(\015)|(\012)/", '<br />' . "\n", chunk_split(base64_encode($encodedstr)));
 171
 172         $SQP_errorString .= $GLOBALS['strSQLParserBugMessage'] . '<br />' . "\n"
 173              . '----' . $GLOBALS['strBeginCut'] . '----' . '<br />' . "\n"
 174              . $encodedstr . "\n"
 175              . '----' . $GLOBALS['strEndCut'] . '----' . '<br />' . "\n";
 176
 177         $SQP_errorString .= '----' . $GLOBALS['strBeginRaw'] . '----<br />' . "\n"
 178              . '<pre>' . "\n"
 179              . $debugstr
 180              . '</pre>' . "\n"
 181              . '----' . $GLOBALS['strEndRaw'] . '----<br />' . "\n";
 182
 183     } // end of the "PMA_SQP_bug()" function
 184
 185
 186     /**
 187      * Parses the SQL queries
 188      *
 189      * @param  string   The SQL query list
 190      *
 191      * @return mixed    Most of times, nothing...
 192      *
 193      * @global array    The current PMA configuration
 194      * @global array    MySQL column attributes
 195      * @global array    MySQL reserved words
 196      * @global array    MySQL column types
 197      * @global array    MySQL function names
 198      * @global integer  MySQL column attributes count
 199      * @global integer  MySQL reserved words count
 200      * @global integer  MySQL column types count
 201      * @global integer  MySQL function names count
 202      * @global array    List of available character sets
 203      * @global array    List of available collations
 204      * @global integer  Character sets count
 205      * @global integer  Collations count
 206      *
 207      * @access public
 208      */
 209     function PMA_SQP_parse($sql)
 210     {
 211         global $cfg;
 212         global $PMA_SQPdata_column_attrib, $PMA_SQPdata_reserved_word, $PMA_SQPdata_column_type, $PMA_SQPdata_function_name,
 213                $PMA_SQPdata_column_attrib_cnt, $PMA_SQPdata_reserved_word_cnt, $PMA_SQPdata_column_type_cnt, $PMA_SQPdata_function_name_cnt;
 214         global $mysql_charsets, $mysql_collations_flat, $mysql_charsets_count, $mysql_collations_count;
 215         global $PMA_SQPdata_forbidden_word, $PMA_SQPdata_forbidden_word_cnt;
 216
 217         // Convert all line feeds to Unix style
 218         $sql = str_replace("\r\n", "\n", $sql);
 219         $sql = str_replace("\r", "\n", $sql);
 220
 221         $len = PMA_strlen($sql);
 222         if ($len == 0) {
 223             return array();
 224         }
 225
 226         $sql_array               = array();
 227         $sql_array['raw']        = $sql;
 228         $count1                  = 0;
 229         $count2                  = 0;
 230         $punct_queryend          = ';';
 231         $punct_qualifier         = '.';
 232         $punct_listsep           = ',';
 233         $punct_level_plus        = '(';
 234         $punct_level_minus       = ')';
 235         $punct_user              = '@';
 236         $digit_floatdecimal      = '.';
 237         $digit_hexset            = 'x';
 238         $bracket_list            = '()[]{}';
 239         $allpunct_list           =  '-,;:!?/.^~\*&%+<=>|';
 240         $allpunct_list_pair      = array (
 241             0 => '!=',
 242             1 => '&&',
 243             2 => ':=',
 244             3 => '<<',
 245             4 => '<=',
 246             5 => '<=>',
 247             6 => '<>',
 248             7 => '>=',
 249             8 => '>>',
 250             9 => '||'
 251         );
 252         $allpunct_list_pair_size = 10; //count($allpunct_list_pair);
 253         $quote_list              = '\'"`';
 254         $arraysize               = 0;
 255
 256         $previous_was_space   = false;
 257         $this_was_space       = false;
 258         $previous_was_bracket = false;
 259         $this_was_bracket     = false;
 260         $previous_was_punct   = false;
 261         $this_was_punct       = false;
 262         $previous_was_listsep = false;
 263         $this_was_listsep     = false;
 264         $previous_was_quote   = false;
 265         $this_was_quote       = false;
 266
 267         while ($count2 < $len) {
 268             $c      = $GLOBALS['PMA_substr']($sql, $count2, 1);
 269             $count1 = $count2;
 270
 271             $previous_was_space = $this_was_space;
 272             $this_was_space = false;
 273             $previous_was_bracket = $this_was_bracket;
 274             $this_was_bracket = false;
 275             $previous_was_punct = $this_was_punct;
 276             $this_was_punct = false;
 277             $previous_was_listsep = $this_was_listsep;
 278             $this_was_listsep = false;
 279             $previous_was_quote = $this_was_quote;
 280             $this_was_quote = false;
 281
 282             if (($c == "\n")) {
 283                 $this_was_space = true;
 284                 $count2++;
 285                 PMA_SQP_arrayAdd($sql_array, 'white_newline', '', $arraysize);
 286                 continue;
 287             }
 288
 289             // Checks for white space
 290             if ($GLOBALS['PMA_STR_isSpace']($c)) {
 291                 $this_was_space = true;
 292                 $count2++;
 293                 continue;
 294             }
 295
 296             // Checks for comment lines.
 297             // MySQL style #
 298             // C style /* */
 299             // ANSI style --
 300             if (($c == '#')
 301                 || (($count2 + 1 < $len) && ($c == '/') && ($GLOBALS['PMA_substr']($sql, $count2 + 1, 1) == '*'))
 302                 || (($count2 + 2 == $len) && ($c == '-') && ($GLOBALS['PMA_substr']($sql, $count2 + 1, 1) == '-'))
 303                 || (($count2 + 2 < $len) && ($c == '-') && ($GLOBALS['PMA_substr']($sql, $count2 + 1, 1) == '-') && (($GLOBALS['PMA_substr']($sql, $count2 + 2, 1) <= ' ')))) {
 304                 $count2++;
 305                 $pos  = 0;
 306                 $type = 'bad';
 307                 switch ($c) {
 308                     case '#':
 309                         $type = 'mysql';
 310                     case '-':
 311                         $type = 'ansi';
 312                         $pos  = $GLOBALS['PMA_strpos']($sql, "\n", $count2);
 313                         break;
 314                     case '/':
 315                         $type = 'c';
 316                         $pos  = $GLOBALS['PMA_strpos']($sql, '*/', $count2);
 317                         $pos  += 2;
 318                         break;
 319                     default:
 320                         break;
 321                 } // end switch
 322                 $count2 = ($pos < $count2) ? $len : $pos;
 323                 $str    = $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
 324                 PMA_SQP_arrayAdd($sql_array, 'comment_' . $type, $str, $arraysize);
 325                 continue;
 326             } // end if
 327
 328             // Checks for something inside quotation marks
 329             if ($GLOBALS['PMA_strpos']($quote_list, $c) !== false) {
 330                 $startquotepos   = $count2;
 331                 $quotetype       = $c;
 332                 $count2++;
 333                 $escaped         = FALSE;
 334                 $escaped_escaped = FALSE;
 335                 $pos             = $count2;
 336                 $oldpos          = 0;
 337                 do {
 338                     $oldpos = $pos;
 339                     $pos    = $GLOBALS['PMA_strpos'](' ' . $sql, $quotetype, $oldpos + 1) - 1;
 340                     // ($pos === FALSE)
 341                     if ($pos < 0) {
 342                         $debugstr = $GLOBALS['strSQPBugUnclosedQuote'] . ' @ ' . $startquotepos. "\n"
 343                                   . 'STR: ' . htmlspecialchars($quotetype);
 344                         PMA_SQP_throwError($debugstr, $sql);
 345                         return $sql_array;
 346                     }
 347
 348                     // If the quote is the first character, it can't be
 349                     // escaped, so don't do the rest of the code
 350                     if ($pos == 0) {
 351                         break;
 352                     }
 353
 354                     // Checks for MySQL escaping using a \
 355                     // And checks for ANSI escaping using the $quotetype character
 356                     if (($pos < $len) && PMA_STR_charIsEscaped($sql, $pos)) {
 357                         $pos ++;
 358                         continue;
 359                     } elseif (($pos + 1 < $len) && ($GLOBALS['PMA_substr']($sql, $pos, 1) == $quotetype) && ($GLOBALS['PMA_substr']($sql, $pos + 1, 1) == $quotetype)) {
 360                         $pos = $pos + 2;
 361                         continue;
 362                     } else {
 363                         break;
 364                     }
 365                 } while ($len > $pos); // end do
 366
 367                 $count2       = $pos;
 368                 $count2++;
 369                 $type         = 'quote_';
 370                 switch ($quotetype) {
 371                     case '\'':
 372                         $type .= 'single';
 373                         $this_was_quote = true;
 374                         break;
 375                     case '"':
 376                         $type .= 'double';
 377                         $this_was_quote = true;
 378                         break;
 379                     case '`':
 380                         $type .= 'backtick';
 381                         $this_was_quote = true;
 382                         break;
 383                     default:
 384                         break;
 385                 } // end switch
 386                 $data = $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
 387                 PMA_SQP_arrayAdd($sql_array, $type, $data, $arraysize);
 388                 continue;
 389             }
 390
 391             // Checks for brackets
 392             if ($GLOBALS['PMA_strpos']($bracket_list, $c) !== false) {
 393                 // All bracket tokens are only one item long
 394                 $this_was_bracket = true;
 395                 $count2++;
 396                 $type_type     = '';
 397                 if ($GLOBALS['PMA_strpos']('([{', $c) !== false) {
 398                     $type_type = 'open';
 399                 } else {
 400                     $type_type = 'close';
 401                 }
 402
 403                 $type_style     = '';
 404                 if ($GLOBALS['PMA_strpos']('()', $c) !== false) {
 405                     $type_style = 'round';
 406                 } elseif ($GLOBALS['PMA_strpos']('[]', $c) !== false) {
 407                     $type_style = 'square';
 408                 } else {
 409                     $type_style = 'curly';
 410                 }
 411
 412                 $type = 'punct_bracket_' . $type_type . '_' . $type_style;
 413                 PMA_SQP_arrayAdd($sql_array, $type, $c, $arraysize);
 414                 continue;
 415             }
 416
 417             /* DEBUG
 418             echo '<pre>1';
 419             var_dump(PMA_STR_isSqlIdentifier($c, false));
 420             var_dump($c == '@');
 421             var_dump($c == '.');
 422             var_dump(PMA_STR_isDigit(PMA_substr($sql, $count2 + 1, 1)));
 423             var_dump($previous_was_space);
 424             var_dump($previous_was_bracket);
 425             var_dump($previous_was_listsep);
 426             echo '</pre>';
 427             */
 428
 429             // Checks for identifier (alpha or numeric)
 430             if (PMA_STR_isSqlIdentifier($c, false)
 431              || $c == '@'
 432              || ($c == '.'
 433               && $GLOBALS['PMA_STR_isDigit']($GLOBALS['PMA_substr']($sql, $count2 + 1, 1))
 434               && ($previous_was_space || $previous_was_bracket || $previous_was_listsep))) {
 435
 436                 /* DEBUG
 437                 echo PMA_substr($sql, $count2);
 438                 echo '<hr />';
 439                 */
 440
 441                 $count2++;
 442
 443                 /**
 444                  * @todo a @ can also be present in expressions like
 445                  * FROM 'user'@'%' or  TO 'user'@'%'
 446                  * in this case, the @ is wrongly marked as alpha_variable
 447                  */
 448                 $is_identifier           = $previous_was_punct;
 449                 $is_sql_variable         = $c == '@' && ! $previous_was_quote;
 450                 $is_user                 = $c == '@' && $previous_was_quote;
 451                 $is_digit                = !$is_identifier && !$is_sql_variable && $GLOBALS['PMA_STR_isDigit']($c);
 452                 $is_hex_digit            = $is_digit && $c == '0' && $count2 < $len && $GLOBALS['PMA_substr']($sql, $count2, 1) == 'x';
 453                 $is_float_digit          = $c == '.';
 454                 $is_float_digit_exponent = FALSE;
 455
 456                 /* DEBUG
 457                 echo '<pre>2';
 458                 var_dump($is_identifier);
 459                 var_dump($is_sql_variable);
 460                 var_dump($is_digit);
 461                 var_dump($is_float_digit);
 462                 echo '</pre>';
 463                  */
 464
 465                 // Nijel: Fast skip is especially needed for huge BLOB data, requires PHP at least 4.3.0:
 466                 if ($is_hex_digit) {
 467                     $count2++;
 468                     $pos = strspn($sql, '0123456789abcdefABCDEF', $count2);
 469                     if ($pos > $count2) {
 470                         $count2 = $pos;
 471                     }
 472                     unset($pos);
 473                 } elseif ($is_digit) {
 474                     $pos = strspn($sql, '0123456789', $count2);
 475                     if ($pos > $count2) {
 476                         $count2 = $pos;
 477                     }
 478                     unset($pos);
 479                 }
 480
 481                 while (($count2 < $len) && PMA_STR_isSqlIdentifier($GLOBALS['PMA_substr']($sql, $count2, 1), ($is_sql_variable || $is_digit))) {
 482                     $c2 = $GLOBALS['PMA_substr']($sql, $count2, 1);
 483                     if ($is_sql_variable && ($c2 == '.')) {
 484                         $count2++;
 485                         continue;
 486                     }
 487                     if ($is_digit && (!$is_hex_digit) && ($c2 == '.')) {
 488                         $count2++;
 489                         if (!$is_float_digit) {
 490                             $is_float_digit = TRUE;
 491                             continue;
 492                         } else {
 493                             $debugstr = $GLOBALS['strSQPBugInvalidIdentifer'] . ' @ ' . ($count1+1) . "\n"
 494                                       . 'STR: ' . htmlspecialchars(PMA_substr($sql, $count1, $count2 - $count1));
 495                             PMA_SQP_throwError($debugstr, $sql);
 496                             return $sql_array;
 497                         }
 498                     }
 499                     if ($is_digit && (!$is_hex_digit) && (($c2 == 'e') || ($c2 == 'E'))) {
 500                         if (!$is_float_digit_exponent) {
 501                             $is_float_digit_exponent = TRUE;
 502                             $is_float_digit          = TRUE;
 503                             $count2++;
 504                             continue;
 505                         } else {
 506                             $is_digit                = FALSE;
 507                             $is_float_digit          = FALSE;
 508                         }
 509                     }
 510                     if (($is_hex_digit && PMA_STR_isHexDigit($c2)) || ($is_digit && $GLOBALS['PMA_STR_isDigit']($c2))) {
 511                         $count2++;
 512                         continue;
 513                     } else {
 514                         $is_digit     = FALSE;
 515                         $is_hex_digit = FALSE;
 516                     }
 517
 518                     $count2++;
 519                 } // end while
 520
 521                 $l    = $count2 - $count1;
 522                 $str  = $GLOBALS['PMA_substr']($sql, $count1, $l);
 523
 524                 $type = '';
 525                 if ($is_digit || $is_float_digit || $is_hex_digit) {
 526                     $type     = 'digit';
 527                     if ($is_float_digit) {
 528                         $type .= '_float';
 529                     } elseif ($is_hex_digit) {
 530                         $type .= '_hex';
 531                     } else {
 532                         $type .= '_integer';
 533                     }
 534                 } elseif ($is_user) {
 535                     $type = 'punct_user';
 536                 } elseif ($is_sql_variable != FALSE) {
 537                     $type = 'alpha_variable';
 538                 } else {
 539                     $type = 'alpha';
 540                 } // end if... else....
 541                 PMA_SQP_arrayAdd($sql_array, $type, $str, $arraysize, $count2);
 542
 543                 continue;
 544             }
 545
 546             // Checks for punct
 547             if ($GLOBALS['PMA_strpos']($allpunct_list, $c) !== false) {
 548                 while (($count2 < $len) && $GLOBALS['PMA_strpos']($allpunct_list, $GLOBALS['PMA_substr']($sql, $count2, 1)) !== false) {
 549                     $count2++;
 550                 }
 551                 $l = $count2 - $count1;
 552                 if ($l == 1) {
 553                     $punct_data = $c;
 554                 } else {
 555                     $punct_data = $GLOBALS['PMA_substr']($sql, $count1, $l);
 556                 }
 557
 558                 // Special case, sometimes, althought two characters are
 559                 // adjectent directly, they ACTUALLY need to be seperate
 560                 /* DEBUG
 561                 echo '<pre>';
 562                 var_dump($l);
 563                 var_dump($punct_data);
 564                 echo '</pre>';
 565                 */
 566
 567                 if ($l == 1) {
 568                     $t_suffix         = '';
 569                     switch ($punct_data) {
 570                         case $punct_queryend:
 571                             $t_suffix = '_queryend';
 572                             break;
 573                         case $punct_qualifier:
 574                             $t_suffix = '_qualifier';
 575                             $this_was_punct = true;
 576                             break;
 577                         case $punct_listsep:
 578                             $this_was_listsep = true;
 579                             $t_suffix = '_listsep';
 580                             break;
 581                         default:
 582                             break;
 583                     }
 584                     PMA_SQP_arrayAdd($sql_array, 'punct' . $t_suffix, $punct_data, $arraysize);
 585                 } elseif ($punct_data == $GLOBALS['sql_delimiter'] || PMA_STR_binarySearchInArr($punct_data, $allpunct_list_pair, $allpunct_list_pair_size)) {
 586                     // Ok, we have one of the valid combined punct expressions
 587                     PMA_SQP_arrayAdd($sql_array, 'punct', $punct_data, $arraysize);
 588                 } else {
 589                     // Bad luck, lets split it up more
 590                     $first  = $punct_data[0];
 591                     $first2 = $punct_data[0] . $punct_data[1];
 592                     $last2  = $punct_data[$l - 2] . $punct_data[$l - 1];
 593                     $last   = $punct_data[$l - 1];
 594                     if (($first == ',') || ($first == ';') || ($first == '.') || ($first == '*')) {
 595                         $count2     = $count1 + 1;
 596                         $punct_data = $first;
 597                     } elseif (($last2 == '/*') || (($last2 == '--') && ($count2 == $len || $GLOBALS['PMA_substr']($sql, $count2, 1) <= ' '))) {
 598                         $count2     -= 2;
 599                         $punct_data = $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
 600                     } elseif (($last == '-') || ($last == '+') || ($last == '!')) {
 601                         $count2--;
 602                         $punct_data = $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
 603                     /**
 604                      * @todo for negation operator, split in 2 tokens ?
 605                      * "select x&~1 from t"
 606                      * becomes "select x & ~ 1 from t" ?
 607                      */
 608
 609                     } elseif ($last != '~') {
 610                         $debugstr =  $GLOBALS['strSQPBugUnknownPunctuation'] . ' @ ' . ($count1+1) . "\n"
 611                                   . 'STR: ' . htmlspecialchars($punct_data);
 612                         PMA_SQP_throwError($debugstr, $sql);
 613                         return $sql_array;
 614                     }
 615                     PMA_SQP_arrayAdd($sql_array, 'punct', $punct_data, $arraysize);
 616                     continue;
 617                 } // end if... elseif... else
 618                 continue;
 619             }
 620
 621             // DEBUG
 622             $count2++;
 623
 624             $debugstr = 'C1 C2 LEN: ' . $count1 . ' ' . $count2 . ' ' . $len .  "\n"
 625                       . 'STR: ' . $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1) . "\n";
 626             PMA_SQP_bug($debugstr, $sql);
 627             return $sql_array;
 628
 629         } // end while ($count2 < $len)
 630
 631         /*
 632         echo '<pre>';
 633         print_r($sql_array);
 634         echo '</pre>';
 635         */
 636
 637         if ($arraysize > 0) {
 638             $t_next           = $sql_array[0]['type'];
 639             $t_prev           = '';
 640             $t_bef_prev       = '';
 641             $t_cur            = '';
 642             $d_next           = $sql_array[0]['data'];
 643             $d_prev           = '';
 644             $d_bef_prev       = '';
 645             $d_cur            = '';
 646             $d_next_upper     = $t_next == 'alpha' ? strtoupper($d_next) : $d_next;
 647             $d_prev_upper     = '';
 648             $d_bef_prev_upper = '';
 649             $d_cur_upper      = '';
 650         }
 651
 652         for ($i = 0; $i < $arraysize; $i++) {
 653             $t_bef_prev       = $t_prev;
 654             $t_prev           = $t_cur;
 655             $t_cur            = $t_next;
 656             $d_bef_prev       = $d_prev;
 657             $d_prev           = $d_cur;
 658             $d_cur            = $d_next;
 659             $d_bef_prev_upper = $d_prev_upper;
 660             $d_prev_upper     = $d_cur_upper;
 661             $d_cur_upper      = $d_next_upper;
 662             if (($i + 1) < $arraysize) {
 663                 $t_next = $sql_array[$i + 1]['type'];
 664                 $d_next = $sql_array[$i + 1]['data'];
 665                 $d_next_upper = $t_next == 'alpha' ? strtoupper($d_next) : $d_next;
 666             } else {
 667                 $t_next       = '';
 668                 $d_next       = '';
 669                 $d_next_upper = '';
 670             }
 671
 672             //DEBUG echo "[prev: <strong>".$d_prev."</strong> ".$t_prev."][cur: <strong>".$d_cur."</strong> ".$t_cur."][next: <strong>".$d_next."</strong> ".$t_next."]<br />";
 673
 674             if ($t_cur == 'alpha') {
 675                 $t_suffix     = '_identifier';
 676                 // for example: `thebit` bit(8) NOT NULL DEFAULT b'0'
 677                 if ($t_prev == 'alpha' && $d_prev == 'DEFAULT' && $d_cur == 'b' && $t_next == 'quote_single') {
 678                     $t_suffix = '_bitfield_constant_introducer';
 679                 } elseif (($t_next == 'punct_qualifier') || ($t_prev == 'punct_qualifier')) {
 680                     $t_suffix = '_identifier';
 681                 } elseif (($t_next == 'punct_bracket_open_round')
 682                   && PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_function_name, $PMA_SQPdata_function_name_cnt)) {
 683                     /**
 684                      * @todo 2005-10-16: in the case of a CREATE TABLE containing
 685                      * a TIMESTAMP, since TIMESTAMP() is also a function, it's
 686                      * found here and the token is wrongly marked as alpha_functionName.
 687                      * But we compensate for this when analysing for timestamp_not_null
 688                      * later in this script.
 689                      *
 690                      * Same applies to CHAR vs. CHAR() function.
 691                      */
 692                     $t_suffix = '_functionName';
 693                     /* There are functions which might be as well column types */
 694                 } elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_type, $PMA_SQPdata_column_type_cnt)) {
 695                     $t_suffix = '_columnType';
 696
 697                     /**
 698                      * Temporary fix for BUG #621357 and #2027720
 699                      *
 700                      * @todo FIX PROPERLY NEEDS OVERHAUL OF SQL TOKENIZER
 701                      */
 702                     if (($d_cur_upper == 'SET' || $d_cur_upper == 'BINARY') && $t_next != 'punct_bracket_open_round') {
 703                         $t_suffix = '_reservedWord';
 704                     }
 705                     //END OF TEMPORARY FIX
 706
 707                     // CHARACTER is a synonym for CHAR, but can also be meant as
 708                     // CHARACTER SET. In this case, we have a reserved word.
 709                     if ($d_cur_upper == 'CHARACTER' && $d_next_upper == 'SET') {
 710                         $t_suffix = '_reservedWord';
 711                     }
 712
 713                     // experimental
 714                     // current is a column type, so previous must not be
 715                     // a reserved word but an identifier
 716                     // CREATE TABLE SG_Persons (first varchar(64))
 717
 718                     //if ($sql_array[$i-1]['type'] =='alpha_reservedWord') {
 719                     //    $sql_array[$i-1]['type'] = 'alpha_identifier';
 720                     //}
 721
 722                 } elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_reserved_word, $PMA_SQPdata_reserved_word_cnt)) {
 723                     $t_suffix = '_reservedWord';
 724                 } elseif (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_attrib, $PMA_SQPdata_column_attrib_cnt)) {
 725                     $t_suffix = '_columnAttrib';
 726                     // INNODB is a MySQL table type, but in "SHOW INNODB STATUS",
 727                     // it should be regarded as a reserved word.
 728                     if ($d_cur_upper == 'INNODB' && $d_prev_upper == 'SHOW' && $d_next_upper == 'STATUS') {
 729                         $t_suffix = '_reservedWord';
 730                     }
 731
 732                     if ($d_cur_upper == 'DEFAULT' && $d_next_upper == 'CHARACTER') {
 733                         $t_suffix = '_reservedWord';
 734                     }
 735                     // Binary as character set
 736                     if ($d_cur_upper == 'BINARY' && (
 737                       ($d_bef_prev_upper == 'CHARACTER' && $d_prev_upper == 'SET')
 738                       || ($d_bef_prev_upper == 'SET' && $d_prev_upper == '=')
 739                       || ($d_bef_prev_upper == 'CHARSET' && $d_prev_upper == '=')
 740                       || $d_prev_upper == 'CHARSET'
 741                       ) && PMA_STR_binarySearchInArr($d_cur, $mysql_charsets, count($mysql_charsets))) {
 742                         $t_suffix = '_charset';
 743                     }
 744                 } elseif (PMA_STR_binarySearchInArr($d_cur, $mysql_charsets, $mysql_charsets_count)
 745                   || PMA_STR_binarySearchInArr($d_cur, $mysql_collations_flat, $mysql_collations_count)
 746                   || ($d_cur{0} == '_' && PMA_STR_binarySearchInArr(substr($d_cur, 1), $mysql_charsets, $mysql_charsets_count))) {
 747                     $t_suffix = '_charset';
 748                 } else {
 749                     // Do nothing
 750                 }
 751                 // check if present in the list of forbidden words
 752                 if ($t_suffix == '_reservedWord' && PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_forbidden_word, $PMA_SQPdata_forbidden_word_cnt)) {
 753                     $sql_array[$i]['forbidden'] = TRUE;
 754                 } else {
 755                     $sql_array[$i]['forbidden'] = FALSE;
 756                 }
 757                 $sql_array[$i]['type'] .= $t_suffix;
 758             }
 759         } // end for
 760
 761         // Stores the size of the array inside the array, as count() is a slow
 762         // operation.
 763         $sql_array['len'] = $arraysize;
 764
 765         // DEBUG echo 'After parsing<pre>'; print_r($sql_array); echo '</pre>';
 766         // Sends the data back
 767         return $sql_array;
 768     } // end of the "PMA_SQP_parse()" function
 769
 770    /**
 771     * Checks for token types being what we want...
 772     *
 773     * @param  string String of type that we have
 774     * @param  string String of type that we want
 775     *
 776     * @return boolean result of check
 777     *
 778     * @access private
 779     */
 780     function PMA_SQP_typeCheck($toCheck, $whatWeWant)
 781     {
 782         $typeSeperator = '_';
 783         if (strcmp($whatWeWant, $toCheck) == 0) {
 784             return TRUE;
 785         } else {
 786             if (strpos($whatWeWant, $typeSeperator) === FALSE) {
 787                 return strncmp($whatWeWant, $toCheck, strpos($toCheck, $typeSeperator)) == 0;
 788             } else {
 789                 return FALSE;
 790             }
 791         }
 792     }
 793
 794
 795     /**
 796      * Analyzes SQL queries
 797      *
 798      * @param  array   The SQL queries
 799      *
 800      * @return array   The analyzed SQL queries
 801      *
 802      * @access public
 803      */
 804     function PMA_SQP_analyze($arr)
 805     {
 806         if ($arr == array()) {
 807             return array();
 808         }
 809         $result          = array();
 810         $size            = $arr['len'];
 811         $subresult       = array(
 812             'querytype'      => '',
 813             'select_expr_clause'=> '', // the whole stuff between SELECT and FROM , except DISTINCT
 814             'position_of_first_select' => '', // the array index
 815             'from_clause'=> '',
 816             'group_by_clause'=> '',
 817             'order_by_clause'=> '',
 818             'having_clause'  => '',
 819             'limit_clause'  => '',
 820             'where_clause'   => '',
 821             'where_clause_identifiers'   => array(),
 822             'unsorted_query' => '',
 823             'queryflags'     => array(),
 824             'select_expr'    => array(),
 825             'table_ref'      => array(),
 826             'foreign_keys'   => array(),
 827             'create_table_fields' => array()
 828         );
 829         $subresult_empty = $subresult;
 830         $seek_queryend         = FALSE;
 831         $seen_end_of_table_ref = FALSE;
 832         $number_of_brackets_in_extract = 0;
 833         $number_of_brackets_in_group_concat = 0;
 834
 835         $number_of_brackets = 0;
 836         $in_subquery = false;
 837         $seen_subquery = false;
 838         $seen_from = false;
 839
 840         // for SELECT EXTRACT(YEAR_MONTH FROM CURDATE())
 841         // we must not use CURDATE as a table_ref
 842         // so we track whether we are in the EXTRACT()
 843         $in_extract          = FALSE;
 844
 845         // for GROUP_CONCAT(...)
 846         $in_group_concat     = FALSE;
 847
 848 /* Description of analyzer results
 849  *
 850  * db, table, column, alias
 851  * ------------------------
 852  *
 853  * Inside the $subresult array, we create ['select_expr'] and ['table_ref'] arrays.
 854  *
 855  * The SELECT syntax (simplified) is
 856  *
 857  * SELECT
 858  *    select_expression,...
 859  *    [FROM [table_references]
 860  *
 861  *
 862  * ['select_expr'] is filled with each expression, the key represents the
 863  * expression position in the list (0-based) (so we don't lose track of
 864  * multiple occurences of the same column).
 865  *
 866  * ['table_ref'] is filled with each table ref, same thing for the key.
 867  *
 868  * I create all sub-values empty, even if they are
 869  * not present (for example no select_expression alias).
 870  *
 871  * There is a debug section at the end of loop #1, if you want to
 872  * see the exact contents of select_expr and table_ref
 873  *
 874  * queryflags
 875  * ----------
 876  *
 877  * In $subresult, array 'queryflags' is filled, according to what we
 878  * find in the query.
 879  *
 880  * Currently, those are generated:
 881  *
 882  * ['queryflags']['need_confirm'] = 1; if the query needs confirmation
 883  * ['queryflags']['select_from'] = 1;  if this is a real SELECT...FROM
 884  * ['queryflags']['distinct'] = 1;     for a DISTINCT
 885  * ['queryflags']['union'] = 1;        for a UNION
 886  * ['queryflags']['join'] = 1;         for a JOIN
 887  * ['queryflags']['offset'] = 1;       for the presence of OFFSET
 888  * ['queryflags']['procedure'] = 1;    for the presence of PROCEDURE
 889  *
 890  * query clauses
 891  * -------------
 892  *
 893  * The select is splitted in those clauses:
 894  * ['select_expr_clause']
 895  * ['from_clause']
 896  * ['group_by_clause']
 897  * ['order_by_clause']
 898  * ['having_clause']
 899  * ['limit_clause']
 900  * ['where_clause']
 901  *
 902  * The identifiers of the WHERE clause are put into the array
 903  * ['where_clause_identifier']
 904  *
 905  * For a SELECT, the whole query without the ORDER BY clause is put into
 906  * ['unsorted_query']
 907  *
 908  * foreign keys
 909  * ------------
 910  * The CREATE TABLE may contain FOREIGN KEY clauses, so they get
 911  * analyzed and ['foreign_keys'] is an array filled with
 912  * the constraint name, the index list,
 913  * the REFERENCES table name and REFERENCES index list,
 914  * and ON UPDATE | ON DELETE clauses
 915  *
 916  * position_of_first_select
 917  * ------------------------
 918  *
 919  * The array index of the first SELECT we find. Will be used to
 920  * insert a SQL_CALC_FOUND_ROWS.
 921  *
 922  * create_table_fields
 923  * -------------------
 924  *
 925  * Used to detect the DEFAULT CURRENT_TIMESTAMP and
 926  * ON UPDATE CURRENT_TIMESTAMP clauses of the CREATE TABLE query.
 927  * Also used to store the default value of the field.
 928  * An array, each element is the identifier name.
 929  * Note that for now, the timestamp_not_null element is created
 930  * even for non-TIMESTAMP fields.
 931  *
 932  * Sub-elements: ['type'] which contains the column type
 933  *               optional (currently they are never false but can be absent):
 934  *               ['default_current_timestamp'] boolean
 935  *               ['on_update_current_timestamp'] boolean
 936  *               ['timestamp_not_null'] boolean
 937  *
 938  * section_before_limit, section_after_limit
 939  * -----------------------------------------
 940  *
 941  * Marks the point of the query where we can insert a LIMIT clause;
 942  * so the section_before_limit will contain the left part before
 943  * a possible LIMIT clause
 944  *
 945  *
 946  * End of description of analyzer results
 947  */
 948
 949         // must be sorted
 950         // TODO: current logic checks for only one word, so I put only the
 951         // first word of the reserved expressions that end a table ref;
 952         // maybe this is not ok (the first word might mean something else)
 953 //        $words_ending_table_ref = array(
 954 //            'FOR UPDATE',
 955 //            'GROUP BY',
 956 //            'HAVING',
 957 //            'LIMIT',
 958 //            'LOCK IN SHARE MODE',
 959 //            'ORDER BY',
 960 //            'PROCEDURE',
 961 //            'UNION',
 962 //            'WHERE'
 963 //        );
 964         $words_ending_table_ref = array(
 965             'FOR',
 966             'GROUP',
 967             'HAVING',
 968             'LIMIT',
 969             'LOCK',
 970             'ORDER',
 971             'PROCEDURE',
 972             'UNION',
 973             'WHERE'
 974         );
 975         $words_ending_table_ref_cnt = 9; //count($words_ending_table_ref);
 976
 977         $words_ending_clauses = array(
 978             'FOR',
 979             'LIMIT',
 980             'LOCK',
 981             'PROCEDURE',
 982             'UNION'
 983         );
 984         $words_ending_clauses_cnt = 5; //count($words_ending_clauses);
 985
 986
 987
 988
 989         // must be sorted
 990         $supported_query_types = array(
 991             'SELECT'
 992             /*
 993             // Support for these additional query types will come later on.
 994             'DELETE',
 995             'INSERT',
 996             'REPLACE',
 997             'TRUNCATE',
 998             'UPDATE'
 999             'EXPLAIN',
1000             'DESCRIBE',
1001             'SHOW',
1002             'CREATE',
1003             'SET',
1004             'ALTER'
1005             */
1006         );
1007         $supported_query_types_cnt = count($supported_query_types);
1008
1009         // loop #1 for each token: select_expr, table_ref for SELECT
1010
1011         for ($i = 0; $i < $size; $i++) {
1012 //DEBUG echo "Loop1 <strong>"  . $arr[$i]['data'] . "</strong> (" . $arr[$i]['type'] . ")<br />";
1013
1014             // High speed seek for locating the end of the current query
1015             if ($seek_queryend == TRUE) {
1016                 if ($arr[$i]['type'] == 'punct_queryend') {
1017                     $seek_queryend = FALSE;
1018                 } else {
1019                     continue;
1020                 } // end if (type == punct_queryend)
1021             } // end if ($seek_queryend)
1022
1023             /**
1024              * Note: do not split if this is a punct_queryend for the first and only query
1025              * @todo when we find a UNION, should we split in another subresult?
1026              */
1027             if ($arr[$i]['type'] == 'punct_queryend' && ($i + 1 != $size)) {
1028                 $result[]  = $subresult;
1029                 $subresult = $subresult_empty;
1030                 continue;
1031             } // end if (type == punct_queryend)
1032
1033 // ==============================================================
1034             if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1035                 $number_of_brackets++;
1036                 if ($in_extract) {
1037                     $number_of_brackets_in_extract++;
1038                 }
1039                 if ($in_group_concat) {
1040                     $number_of_brackets_in_group_concat++;
1041                 }
1042             }
1043 // ==============================================================
1044             if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1045                 $number_of_brackets--;
1046                 if ($number_of_brackets == 0) {
1047                     $in_subquery = false;
1048                 }
1049                 if ($in_extract) {
1050                     $number_of_brackets_in_extract--;
1051                     if ($number_of_brackets_in_extract == 0) {
1052                        $in_extract = FALSE;
1053                     }
1054                 }
1055                 if ($in_group_concat) {
1056                     $number_of_brackets_in_group_concat--;
1057                     if ($number_of_brackets_in_group_concat == 0) {
1058                        $in_group_concat = FALSE;
1059                     }
1060                 }
1061             }
1062
1063             if ($in_subquery) {
1064                 /**
1065                  * skip the subquery to avoid setting
1066                  * select_expr or table_ref with the contents
1067                  * of this subquery; this is to avoid a bug when
1068                  * trying to edit the results of
1069                  * select * from child where not exists (select id from
1070                  * parent where child.parent_id = parent.id);
1071                  */
1072                 continue;
1073             }
1074 // ==============================================================
1075             if ($arr[$i]['type'] == 'alpha_functionName') {
1076                 $upper_data = strtoupper($arr[$i]['data']);
1077                 if ($upper_data =='EXTRACT') {
1078                     $in_extract = TRUE;
1079                     $number_of_brackets_in_extract = 0;
1080                 }
1081                 if ($upper_data =='GROUP_CONCAT') {
1082                     $in_group_concat = TRUE;
1083                     $number_of_brackets_in_group_concat = 0;
1084                 }
1085             }
1086
1087 // ==============================================================
1088             if ($arr[$i]['type'] == 'alpha_reservedWord'
1089 //             && $arr[$i]['forbidden'] == FALSE) {
1090             ) {
1091                 // We don't know what type of query yet, so run this
1092                 if ($subresult['querytype'] == '') {
1093                     $subresult['querytype'] = strtoupper($arr[$i]['data']);
1094                 } // end if (querytype was empty)
1095
1096                 // Check if we support this type of query
1097                 if (!PMA_STR_binarySearchInArr($subresult['querytype'], $supported_query_types, $supported_query_types_cnt)) {
1098                     // Skip ahead to the next one if we don't
1099                     $seek_queryend = TRUE;
1100                     continue;
1101                 } // end if (query not supported)
1102
1103                 // upper once
1104                 $upper_data = strtoupper($arr[$i]['data']);
1105                 /**
1106                  * @todo reset for each query?
1107                  */
1108
1109                 if ($upper_data == 'SELECT') {
1110                     if ($number_of_brackets > 0) {
1111                         $in_subquery = true;
1112                         $seen_subquery = true;
1113                         // this is a subquery so do not analyze inside it
1114                         continue;
1115                     }
1116                     $seen_from = FALSE;
1117                     $previous_was_identifier = FALSE;
1118                     $current_select_expr = -1;
1119                     $seen_end_of_table_ref = FALSE;
1120                 } // end if (data == SELECT)
1121
1122                 if ($upper_data =='FROM' && !$in_extract) {
1123                     $current_table_ref = -1;
1124                     $seen_from = TRUE;
1125                     $previous_was_identifier = FALSE;
1126                     $save_table_ref = TRUE;
1127                 } // end if (data == FROM)
1128
1129                 // here, do not 'continue' the loop, as we have more work for
1130                 // reserved words below
1131             } // end if (type == alpha_reservedWord)
1132
1133 // ==============================
1134             if ($arr[$i]['type'] == 'quote_backtick'
1135              || $arr[$i]['type'] == 'quote_double'
1136              || $arr[$i]['type'] == 'quote_single'
1137              || $arr[$i]['type'] == 'alpha_identifier'
1138              || ($arr[$i]['type'] == 'alpha_reservedWord'
1139                 && $arr[$i]['forbidden'] == FALSE)) {
1140
1141                 switch ($arr[$i]['type']) {
1142                     case 'alpha_identifier':
1143                     case 'alpha_reservedWord':
1144                         /**
1145                          * this is not a real reservedWord, because it's not
1146                          * present in the list of forbidden words, for example
1147                          * "storage" which can be used as an identifier
1148                          *
1149                          * @todo avoid the pretty printing in color in this case
1150                          */
1151                         $identifier = $arr[$i]['data'];
1152                         break;
1153
1154                     case 'quote_backtick':
1155                     case 'quote_double':
1156                     case 'quote_single':
1157                         $identifier = PMA_unQuote($arr[$i]['data']);
1158                         break;
1159                 } // end switch
1160
1161                 if ($subresult['querytype'] == 'SELECT'
1162                  && ! $in_group_concat
1163                  && ! ($seen_subquery && $arr[$i - 1]['type'] == 'punct_bracket_close_round')) {
1164                     if (!$seen_from) {
1165                         if ($previous_was_identifier && isset($chain)) {
1166                             // found alias for this select_expr, save it
1167                             // but only if we got something in $chain
1168                             // (for example, SELECT COUNT(*) AS cnt
1169                             // puts nothing in $chain, so we avoid
1170                             // setting the alias)
1171                             $alias_for_select_expr = $identifier;
1172                         } else {
1173                             $chain[] = $identifier;
1174                             $previous_was_identifier = TRUE;
1175
1176                         } // end if !$previous_was_identifier
1177                     } else {
1178                         // ($seen_from)
1179                         if ($save_table_ref && !$seen_end_of_table_ref) {
1180                             if ($previous_was_identifier) {
1181                                 // found alias for table ref
1182                                 // save it for later
1183                                 $alias_for_table_ref = $identifier;
1184                             } else {
1185                                 $chain[] = $identifier;
1186                                 $previous_was_identifier = TRUE;
1187
1188                             } // end if ($previous_was_identifier)
1189                         } // end if ($save_table_ref &&!$seen_end_of_table_ref)
1190                     } // end if (!$seen_from)
1191                 } // end if (querytype SELECT)
1192             } // end if (quote_backtick or double quote or alpha_identifier)
1193
1194 // ===================================
1195             if ($arr[$i]['type'] == 'punct_qualifier') {
1196                 // to be able to detect an identifier following another
1197                 $previous_was_identifier = FALSE;
1198                 continue;
1199             } // end if (punct_qualifier)
1200
1201             /**
1202              * @todo check if 3 identifiers following one another -> error
1203              */
1204
1205             //    s a v e    a    s e l e c t    e x p r
1206             // finding a list separator or FROM
1207             // means that we must save the current chain of identifiers
1208             // into a select expression
1209
1210             // for now, we only save a select expression if it contains
1211             // at least one identifier, as we are interested in checking
1212             // the columns and table names, so in "select * from persons",
1213             // the "*" is not saved
1214
1215             if (isset($chain) && !$seen_end_of_table_ref
1216              && ((!$seen_from && $arr[$i]['type'] == 'punct_listsep')
1217               || ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data == 'FROM'))) {
1218                 $size_chain = count($chain);
1219                 $current_select_expr++;
1220                 $subresult['select_expr'][$current_select_expr] = array(
1221                   'expr' => '',
1222                   'alias' => '',
1223                   'db'   => '',
1224                   'table_name' => '',
1225                   'table_true_name' => '',
1226                   'column' => ''
1227                  );
1228
1229                 if (isset($alias_for_select_expr) && strlen($alias_for_select_expr)) {
1230                     // we had found an alias for this select expression
1231                     $subresult['select_expr'][$current_select_expr]['alias'] = $alias_for_select_expr;
1232                     unset($alias_for_select_expr);
1233                 }
1234                 // there is at least a column
1235                 $subresult['select_expr'][$current_select_expr]['column'] = $chain[$size_chain - 1];
1236                 $subresult['select_expr'][$current_select_expr]['expr'] = $chain[$size_chain - 1];
1237
1238                 // maybe a table
1239                 if ($size_chain > 1) {
1240                     $subresult['select_expr'][$current_select_expr]['table_name'] = $chain[$size_chain - 2];
1241                     // we assume for now that this is also the true name
1242                     $subresult['select_expr'][$current_select_expr]['table_true_name'] = $chain[$size_chain - 2];
1243                     $subresult['select_expr'][$current_select_expr]['expr']
1244                      = $subresult['select_expr'][$current_select_expr]['table_name']
1245                       . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
1246                 } // end if ($size_chain > 1)
1247
1248                 // maybe a db
1249                 if ($size_chain > 2) {
1250                     $subresult['select_expr'][$current_select_expr]['db'] = $chain[$size_chain - 3];
1251                     $subresult['select_expr'][$current_select_expr]['expr']
1252                      = $subresult['select_expr'][$current_select_expr]['db']
1253                       . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
1254                 } // end if ($size_chain > 2)
1255                 unset($chain);
1256
1257                 /**
1258                  * @todo explain this:
1259                  */
1260                 if (($arr[$i]['type'] == 'alpha_reservedWord')
1261                  && ($upper_data != 'FROM')) {
1262                     $previous_was_identifier = TRUE;
1263                 }
1264
1265             } // end if (save a select expr)
1266
1267
1268             //======================================
1269             //    s a v e    a    t a b l e    r e f
1270             //======================================
1271
1272             // maybe we just saw the end of table refs
1273             // but the last table ref has to be saved
1274             // or we are at the last token
1275             // or we just got a reserved word
1276             /**
1277              * @todo there could be another query after this one
1278              */
1279
1280             if (isset($chain) && $seen_from && $save_table_ref
1281              && ($arr[$i]['type'] == 'punct_listsep'
1282                || ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data!="AS")
1283                || $seen_end_of_table_ref
1284                || $i==$size-1)) {
1285
1286                 $size_chain = count($chain);
1287                 $current_table_ref++;
1288                 $subresult['table_ref'][$current_table_ref] = array(
1289                   'expr'            => '',
1290                   'db'              => '',
1291                   'table_name'      => '',
1292                   'table_alias'     => '',
1293                   'table_true_name' => ''
1294                  );
1295                 if (isset($alias_for_table_ref) && strlen($alias_for_table_ref)) {
1296                     $subresult['table_ref'][$current_table_ref]['table_alias'] = $alias_for_table_ref;
1297                     unset($alias_for_table_ref);
1298                 }
1299                 $subresult['table_ref'][$current_table_ref]['table_name'] = $chain[$size_chain - 1];
1300                 // we assume for now that this is also the true name
1301                 $subresult['table_ref'][$current_table_ref]['table_true_name'] = $chain[$size_chain - 1];
1302                 $subresult['table_ref'][$current_table_ref]['expr']
1303                      = $subresult['table_ref'][$current_table_ref]['table_name'];
1304                 // maybe a db
1305                 if ($size_chain > 1) {
1306                     $subresult['table_ref'][$current_table_ref]['db'] = $chain[$size_chain - 2];
1307                     $subresult['table_ref'][$current_table_ref]['expr']
1308                      = $subresult['table_ref'][$current_table_ref]['db']
1309                       . '.' . $subresult['table_ref'][$current_table_ref]['expr'];
1310                 } // end if ($size_chain > 1)
1311
1312                 // add the table alias into the whole expression
1313                 $subresult['table_ref'][$current_table_ref]['expr']
1314                  .= ' ' . $subresult['table_ref'][$current_table_ref]['table_alias'];
1315
1316                 unset($chain);
1317                 $previous_was_identifier = TRUE;
1318                 //continue;
1319
1320             } // end if (save a table ref)
1321
1322
1323             // when we have found all table refs,
1324             // for each table_ref alias, put the true name of the table
1325             // in the corresponding select expressions
1326
1327             if (isset($current_table_ref) && ($seen_end_of_table_ref || $i == $size-1) && $subresult != $subresult_empty) {
1328                 for ($tr=0; $tr <= $current_table_ref; $tr++) {
1329                     $alias = $subresult['table_ref'][$tr]['table_alias'];
1330                     $truename = $subresult['table_ref'][$tr]['table_true_name'];
1331                     for ($se=0; $se <= $current_select_expr; $se++) {
1332                         if (isset($alias) && strlen($alias) && $subresult['select_expr'][$se]['table_true_name']
1333                            == $alias) {
1334                             $subresult['select_expr'][$se]['table_true_name']
1335                              = $truename;
1336                         } // end if (found the alias)
1337                     } // end for (select expressions)
1338
1339                 } // end for (table refs)
1340             } // end if (set the true names)
1341
1342
1343             // e n d i n g    l o o p  #1
1344             // set the $previous_was_identifier to FALSE if the current
1345             // token is not an identifier
1346             if (($arr[$i]['type'] != 'alpha_identifier')
1347              && ($arr[$i]['type'] != 'quote_double')
1348              && ($arr[$i]['type'] != 'quote_single')
1349              && ($arr[$i]['type'] != 'quote_backtick')) {
1350                 $previous_was_identifier = FALSE;
1351             } // end if
1352
1353             // however, if we are on AS, we must keep the $previous_was_identifier
1354             if (($arr[$i]['type'] == 'alpha_reservedWord')
1355              && ($upper_data == 'AS'))  {
1356                 $previous_was_identifier = TRUE;
1357             }
1358
1359             if (($arr[$i]['type'] == 'alpha_reservedWord')
1360              && ($upper_data =='ON' || $upper_data =='USING')) {
1361                 $save_table_ref = FALSE;
1362             } // end if (data == ON)
1363
1364             if (($arr[$i]['type'] == 'alpha_reservedWord')
1365              && ($upper_data =='JOIN' || $upper_data =='FROM')) {
1366                 $save_table_ref = TRUE;
1367             } // end if (data == JOIN)
1368
1369             /**
1370              * no need to check the end of table ref if we already did
1371              *
1372              * @todo maybe add "&& $seen_from"
1373              */
1374             if (!$seen_end_of_table_ref) {
1375                 // if this is the last token, it implies that we have
1376                 // seen the end of table references
1377                 // Check for the end of table references
1378                 //
1379                 // Note: if we are analyzing a GROUP_CONCAT clause,
1380                 // we might find a word that seems to indicate that
1381                 // we have found the end of table refs (like ORDER)
1382                 // but it's a modifier of the GROUP_CONCAT so
1383                 // it's not the real end of table refs
1384                 if (($i == $size-1)
1385                  || ($arr[$i]['type'] == 'alpha_reservedWord'
1386                  && !$in_group_concat
1387                  && PMA_STR_binarySearchInArr($upper_data, $words_ending_table_ref, $words_ending_table_ref_cnt))) {
1388                     $seen_end_of_table_ref = TRUE;
1389                     // to be able to save the last table ref, but do not
1390                     // set it true if we found a word like "ON" that has
1391                     // already set it to false
1392                     if (isset($save_table_ref) && $save_table_ref != FALSE) {
1393                         $save_table_ref = TRUE;
1394                     } //end if
1395
1396                 } // end if (check for end of table ref)
1397             } //end if (!$seen_end_of_table_ref)
1398
1399             if ($seen_end_of_table_ref) {
1400                 $save_table_ref = FALSE;
1401             } // end if
1402
1403         } // end for $i (loop #1)
1404
1405         //DEBUG
1406         /*
1407           if (isset($current_select_expr)) {
1408            for ($trace=0; $trace<=$current_select_expr; $trace++) {
1409                echo "<br />";
1410                reset ($subresult['select_expr'][$trace]);
1411                while (list ($key, $val) = each ($subresult['select_expr'][$trace]))
1412                    echo "sel expr $trace $key => $val<br />\n";
1413                }
1414           }
1415
1416           if (isset($current_table_ref)) {
1417            echo "current_table_ref = " . $current_table_ref . "<br>";
1418            for ($trace=0; $trace<=$current_table_ref; $trace++) {
1419
1420                echo "<br />";
1421                reset ($subresult['table_ref'][$trace]);
1422                while (list ($key, $val) = each ($subresult['table_ref'][$trace]))
1423                echo "table ref $trace $key => $val<br />\n";
1424                }
1425           }
1426         */
1427         // -------------------------------------------------------
1428
1429
1430         // loop #2: - queryflags
1431         //          - querytype (for queries != 'SELECT')
1432         //          - section_before_limit, section_after_limit
1433         //
1434         // we will also need this queryflag in loop 2
1435         // so set it here
1436         if (isset($current_table_ref) && $current_table_ref > -1) {
1437             $subresult['queryflags']['select_from'] = 1;
1438         }
1439
1440         $section_before_limit = '';
1441         $section_after_limit = ''; // truly the section after the limit clause
1442         $seen_reserved_word = FALSE;
1443         $seen_group = FALSE;
1444         $seen_order = FALSE;
1445         $seen_order_by = FALSE;
1446         $in_group_by = FALSE; // true when we are inside the GROUP BY clause
1447         $in_order_by = FALSE; // true when we are inside the ORDER BY clause
1448         $in_having = FALSE; // true when we are inside the HAVING clause
1449         $in_select_expr = FALSE; // true when we are inside the select expr clause
1450         $in_where = FALSE; // true when we are inside the WHERE clause
1451         $seen_limit = FALSE; // true if we have seen a LIMIT clause
1452         $in_limit = FALSE; // true when we are inside the LIMIT clause
1453         $after_limit = FALSE; // true when we are after the LIMIT clause
1454         $in_from = FALSE; // true when we are in the FROM clause
1455         $in_group_concat = FALSE;
1456         $first_reserved_word = '';
1457         $current_identifier = '';
1458         $unsorted_query = $arr['raw']; // in case there is no ORDER BY
1459         $number_of_brackets = 0;
1460         $in_subquery = false;
1461
1462         for ($i = 0; $i < $size; $i++) {
1463 //DEBUG echo "Loop2 <strong>"  . $arr[$i]['data'] . "</strong> (" . $arr[$i]['type'] . ")<br />";
1464
1465             // need_confirm
1466             //
1467             // check for reserved words that will have to generate
1468             // a confirmation request later in sql.php
1469             // the cases are:
1470             //   DROP TABLE
1471             //   DROP DATABASE
1472             //   ALTER TABLE... DROP
1473             //   DELETE FROM...
1474             //
1475             // this code is not used for confirmations coming from functions.js
1476
1477             if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1478                 $number_of_brackets++;
1479             }
1480
1481             if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1482                 $number_of_brackets--;
1483                 if ($number_of_brackets == 0) {
1484                     $in_subquery = false;
1485                 }
1486             }
1487
1488             if ($arr[$i]['type'] == 'alpha_reservedWord') {
1489                 $upper_data = strtoupper($arr[$i]['data']);
1490
1491                 if ($upper_data == 'SELECT' && $number_of_brackets > 0) {
1492                     $in_subquery = true;
1493                 }
1494
1495                 if (!$seen_reserved_word) {
1496                     $first_reserved_word = $upper_data;
1497                     $subresult['querytype'] = $upper_data;
1498                     $seen_reserved_word = TRUE;
1499
1500                     // if the first reserved word is DROP or DELETE,
1501                     // we know this is a query that needs to be confirmed
1502                     if ($first_reserved_word=='DROP'
1503                      || $first_reserved_word == 'DELETE'
1504                      || $first_reserved_word == 'TRUNCATE') {
1505                         $subresult['queryflags']['need_confirm'] = 1;
1506                     }
1507
1508                     if ($first_reserved_word=='SELECT'){
1509                         $position_of_first_select = $i;
1510                     }
1511
1512                 } else {
1513                     if ($upper_data == 'DROP' && $first_reserved_word == 'ALTER') {
1514                         $subresult['queryflags']['need_confirm'] = 1;
1515                     }
1516                 }
1517
1518                 if ($upper_data == 'LIMIT' && ! $in_subquery) {
1519                     $section_before_limit = substr($arr['raw'], 0, $arr[$i]['pos'] - 5);
1520                     $in_limit = TRUE;
1521                     $seen_limit = TRUE;
1522                     $limit_clause = '';
1523                     $in_order_by = FALSE; // @todo maybe others to set FALSE
1524                 }
1525
1526                 if ($upper_data == 'PROCEDURE') {
1527                     $subresult['queryflags']['procedure'] = 1;
1528                     $in_limit = FALSE;
1529                     $after_limit = TRUE;
1530                 }
1531                 /**
1532                  * @todo set also to FALSE if we find FOR UPDATE or LOCK IN SHARE MODE
1533                  */
1534                 if ($upper_data == 'SELECT') {
1535                     $in_select_expr = TRUE;
1536                     $select_expr_clause = '';
1537                 }
1538                 if ($upper_data == 'DISTINCT' && !$in_group_concat) {
1539                     $subresult['queryflags']['distinct'] = 1;
1540                 }
1541
1542                 if ($upper_data == 'UNION') {
1543                     $subresult['queryflags']['union'] = 1;
1544                 }
1545
1546                 if ($upper_data == 'JOIN') {
1547                     $subresult['queryflags']['join'] = 1;
1548                 }
1549
1550                 if ($upper_data == 'OFFSET') {
1551                     $subresult['queryflags']['offset'] = 1;
1552                 }
1553
1554                 // if this is a real SELECT...FROM
1555                 if ($upper_data == 'FROM' && isset($subresult['queryflags']['select_from']) && $subresult['queryflags']['select_from'] == 1) {
1556                     $in_from = TRUE;
1557                     $from_clause = '';
1558                     $in_select_expr = FALSE;
1559                 }
1560
1561
1562                 // (we could have less resetting of variables to FALSE
1563                 // if we trust that the query respects the standard
1564                 // MySQL order for clauses)
1565
1566                 // we use $seen_group and $seen_order because we are looking
1567                 // for the BY
1568                 if ($upper_data == 'GROUP') {
1569                     $seen_group = TRUE;
1570                     $seen_order = FALSE;
1571                     $in_having = FALSE;
1572                     $in_order_by = FALSE;
1573                     $in_where = FALSE;
1574                     $in_select_expr = FALSE;
1575                     $in_from = FALSE;
1576                 }
1577                 if ($upper_data == 'ORDER' && !$in_group_concat) {
1578                     $seen_order = TRUE;
1579                     $seen_group = FALSE;
1580                     $in_having = FALSE;
1581                     $in_group_by = FALSE;
1582                     $in_where = FALSE;
1583                     $in_select_expr = FALSE;
1584                     $in_from = FALSE;
1585                 }
1586                 if ($upper_data == 'HAVING') {
1587                     $in_having = TRUE;
1588                     $having_clause = '';
1589                     $seen_group = FALSE;
1590                     $seen_order = FALSE;
1591                     $in_group_by = FALSE;
1592                     $in_order_by = FALSE;
1593                     $in_where = FALSE;
1594                     $in_select_expr = FALSE;
1595                     $in_from = FALSE;
1596                 }
1597
1598                 if ($upper_data == 'WHERE') {
1599                     $in_where = TRUE;
1600                     $where_clause = '';
1601                     $where_clause_identifiers = array();
1602                     $seen_group = FALSE;
1603                     $seen_order = FALSE;
1604                     $in_group_by = FALSE;
1605                     $in_order_by = FALSE;
1606                     $in_having = FALSE;
1607                     $in_select_expr = FALSE;
1608                     $in_from = FALSE;
1609                 }
1610
1611                 if ($upper_data == 'BY') {
1612                     if ($seen_group) {
1613                         $in_group_by = TRUE;
1614                         $group_by_clause = '';
1615                     }
1616                     if ($seen_order) {
1617                         $seen_order_by = TRUE;
1618                         // Here we assume that the ORDER BY keywords took
1619                         // exactly 8 characters.
1620                         // We use PMA_substr() to be charset-safe; otherwise
1621                         // if the table name contains accents, the unsorted
1622                         // query would be missing some characters.
1623                         $unsorted_query = PMA_substr($arr['raw'], 0, $arr[$i]['pos'] - 8);
1624                         $in_order_by = TRUE;
1625                         $order_by_clause = '';
1626                     }
1627                 }
1628
1629                 // if we find one of the words that could end the clause
1630                 if (PMA_STR_binarySearchInArr($upper_data, $words_ending_clauses, $words_ending_clauses_cnt)) {
1631
1632                     $in_group_by = FALSE;
1633                     $in_order_by = FALSE;
1634                     $in_having   = FALSE;
1635                     $in_where    = FALSE;
1636                     $in_select_expr = FALSE;
1637                     $in_from = FALSE;
1638                 }
1639
1640             } // endif (reservedWord)
1641
1642
1643             // do not add a space after a function name
1644             /**
1645              * @todo can we combine loop 2 and loop 1? some code is repeated here...
1646              */
1647
1648             $sep = ' ';
1649             if ($arr[$i]['type'] == 'alpha_functionName') {
1650                 $sep='';
1651                 $upper_data = strtoupper($arr[$i]['data']);
1652                 if ($upper_data =='GROUP_CONCAT') {
1653                     $in_group_concat = TRUE;
1654                     $number_of_brackets_in_group_concat = 0;
1655                 }
1656             }
1657
1658             if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1659                 if ($in_group_concat) {
1660                     $number_of_brackets_in_group_concat++;
1661                 }
1662             }
1663             if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1664                 if ($in_group_concat) {
1665                     $number_of_brackets_in_group_concat--;
1666                     if ($number_of_brackets_in_group_concat == 0) {
1667                         $in_group_concat = FALSE;
1668                     }
1669                 }
1670             }
1671
1672             // do not add a space after an identifier if followed by a dot
1673             if ($arr[$i]['type'] == 'alpha_identifier' && $i < $size - 1 && $arr[$i + 1]['data'] == '.') {
1674                 $sep = '';
1675             }
1676
1677             // do not add a space after a dot if followed by an identifier
1678             if ($arr[$i]['data'] == '.' && $i < $size - 1 && $arr[$i + 1]['type'] == 'alpha_identifier') {
1679                 $sep = '';
1680             }
1681
1682             if ($in_select_expr && $upper_data != 'SELECT' && $upper_data != 'DISTINCT') {
1683                 $select_expr_clause .= $arr[$i]['data'] . $sep;
1684             }
1685             if ($in_from && $upper_data != 'FROM') {
1686                 $from_clause .= $arr[$i]['data'] . $sep;
1687             }
1688             if ($in_group_by && $upper_data != 'GROUP' && $upper_data != 'BY') {
1689                 $group_by_clause .= $arr[$i]['data'] . $sep;
1690             }
1691             if ($in_order_by && $upper_data != 'ORDER' && $upper_data != 'BY') {
1692                 // add a space only before ASC or DESC
1693                 // not around the dot between dbname and tablename
1694                 if ($arr[$i]['type'] == 'alpha_reservedWord') {
1695                     $order_by_clause .= $sep;
1696                 }
1697                 $order_by_clause .= $arr[$i]['data'];
1698             }
1699             if ($in_having && $upper_data != 'HAVING') {
1700                 $having_clause .= $arr[$i]['data'] . $sep;
1701             }
1702             if ($in_where && $upper_data != 'WHERE') {
1703                 $where_clause .= $arr[$i]['data'] . $sep;
1704
1705                 if (($arr[$i]['type'] == 'quote_backtick')
1706                  || ($arr[$i]['type'] == 'alpha_identifier')) {
1707                     $where_clause_identifiers[] = $arr[$i]['data'];
1708                 }
1709             }
1710
1711             // to grab the rest of the query after the ORDER BY clause
1712             if (isset($subresult['queryflags']['select_from'])
1713              && $subresult['queryflags']['select_from'] == 1
1714              && ! $in_order_by
1715              && $seen_order_by
1716              && $upper_data != 'BY') {
1717                 $unsorted_query .= $arr[$i]['data'];
1718                 if ($arr[$i]['type'] != 'punct_bracket_open_round'
1719                  && $arr[$i]['type'] != 'punct_bracket_close_round'
1720                  && $arr[$i]['type'] != 'punct') {
1721                     $unsorted_query .= $sep;
1722                 }
1723             }
1724
1725                 if ($in_limit) {
1726                 if ($upper_data == 'OFFSET') {
1727                     $limit_clause .= $sep;
1728                 }
1729                         $limit_clause .= $arr[$i]['data'];
1730                 if ($upper_data == 'LIMIT' || $upper_data == 'OFFSET') {
1731                     $limit_clause .= $sep;
1732                 }
1733             }
1734             if ($after_limit && $seen_limit) {
1735                 $section_after_limit .= $arr[$i]['data'] . $sep;
1736             }
1737
1738             // clear $upper_data for next iteration
1739             $upper_data='';
1740         } // end for $i (loop #2)
1741         if (empty($section_before_limit)) {
1742             $section_before_limit = $arr['raw'];
1743         }
1744
1745         // -----------------------------------------------------
1746         // loop #3: foreign keys and MySQL 4.1.2+ TIMESTAMP options
1747         // (for now, check only the first query)
1748         // (for now, identifiers are assumed to be backquoted)
1749
1750         // If we find that we are dealing with a CREATE TABLE query,
1751         // we look for the next punct_bracket_open_round, which
1752         // introduces the fields list. Then, when we find a
1753         // quote_backtick, it must be a field, so we put it into
1754         // the create_table_fields array. Even if this field is
1755         // not a timestamp, it will be useful when logic has been
1756         // added for complete field attributes analysis.
1757
1758         $seen_foreign = FALSE;
1759         $seen_references = FALSE;
1760         $seen_constraint = FALSE;
1761         $foreign_key_number = -1;
1762         $seen_create_table = FALSE;
1763         $seen_create = FALSE;
1764         $seen_alter = FALSE;
1765         $in_create_table_fields = FALSE;
1766         $brackets_level = 0;
1767         $in_timestamp_options = FALSE;
1768         $seen_default = FALSE;
1769
1770         for ($i = 0; $i < $size; $i++) {
1771         // DEBUG echo "Loop 3 <strong>" . $arr[$i]['data'] . "</strong> " . $arr[$i]['type'] . "<br />";
1772
1773             if ($arr[$i]['type'] == 'alpha_reservedWord') {
1774                 $upper_data = strtoupper($arr[$i]['data']);
1775
1776                 if ($upper_data == 'NOT' && $in_timestamp_options) {
1777                     $create_table_fields[$current_identifier]['timestamp_not_null'] = TRUE;
1778
1779                 }
1780
1781                 if ($upper_data == 'CREATE') {
1782                     $seen_create = TRUE;
1783                 }
1784
1785                 if ($upper_data == 'ALTER') {
1786                     $seen_alter = TRUE;
1787                 }
1788
1789                 if ($upper_data == 'TABLE' && $seen_create) {
1790                     $seen_create_table = TRUE;
1791                     $create_table_fields = array();
1792                 }
1793
1794                 if ($upper_data == 'CURRENT_TIMESTAMP') {
1795                     if ($in_timestamp_options) {
1796                         if ($seen_default) {
1797                             $create_table_fields[$current_identifier]['default_current_timestamp'] = TRUE;
1798                         }
1799                     }
1800                 }
1801
1802                 if ($upper_data == 'CONSTRAINT') {
1803                     $foreign_key_number++;
1804                     $seen_foreign = FALSE;
1805                     $seen_references = FALSE;
1806                     $seen_constraint = TRUE;
1807                 }
1808                 if ($upper_data == 'FOREIGN') {
1809                     $seen_foreign = TRUE;
1810                     $seen_references = FALSE;
1811                     $seen_constraint = FALSE;
1812                 }
1813                 if ($upper_data == 'REFERENCES') {
1814                     $seen_foreign = FALSE;
1815                     $seen_references = TRUE;
1816                     $seen_constraint = FALSE;
1817                 }
1818
1819
1820                 // Cases covered:
1821
1822                 // [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
1823                 // [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
1824
1825                 // but we set ['on_delete'] or ['on_cascade'] to
1826                 // CASCADE | SET_NULL | NO_ACTION | RESTRICT
1827
1828                 // ON UPDATE CURRENT_TIMESTAMP
1829
1830                 if ($upper_data == 'ON') {
1831                     if (isset($arr[$i+1]) && $arr[$i+1]['type'] == 'alpha_reservedWord') {
1832                         $second_upper_data = strtoupper($arr[$i+1]['data']);
1833                         if ($second_upper_data == 'DELETE') {
1834                             $clause = 'on_delete';
1835                         }
1836                         if ($second_upper_data == 'UPDATE') {
1837                             $clause = 'on_update';
1838                         }
1839                         if (isset($clause)
1840                         && ($arr[$i+2]['type'] == 'alpha_reservedWord'
1841
1842                 // ugly workaround because currently, NO is not
1843                 // in the list of reserved words in sqlparser.data
1844                 // (we got a bug report about not being able to use
1845                 // 'no' as an identifier)
1846                            || ($arr[$i+2]['type'] == 'alpha_identifier'
1847                               && strtoupper($arr[$i+2]['data'])=='NO'))
1848                           ) {
1849                             $third_upper_data = strtoupper($arr[$i+2]['data']);
1850                             if ($third_upper_data == 'CASCADE'
1851                             || $third_upper_data == 'RESTRICT') {
1852                                 $value = $third_upper_data;
1853                             } elseif ($third_upper_data == 'SET'
1854                               || $third_upper_data == 'NO') {
1855                                 if ($arr[$i+3]['type'] == 'alpha_reservedWord') {
1856                                     $value = $third_upper_data . '_' . strtoupper($arr[$i+3]['data']);
1857                                 }
1858                             } elseif ($third_upper_data == 'CURRENT_TIMESTAMP') {
1859                                 if ($clause == 'on_update'
1860                                 && $in_timestamp_options) {
1861                                     $create_table_fields[$current_identifier]['on_update_current_timestamp'] = TRUE;
1862                                     $seen_default = FALSE;
1863                                 }
1864
1865                             } else {
1866                                 $value = '';
1867                             }
1868                             if (!empty($value)) {
1869                                 $foreign[$foreign_key_number][$clause] = $value;
1870                             }
1871                             unset($clause);
1872                         } // endif (isset($clause))
1873                     }
1874                 }
1875
1876             } // end of reserved words analysis
1877
1878
1879             if ($arr[$i]['type'] == 'punct_bracket_open_round') {
1880                 $brackets_level++;
1881                 if ($seen_create_table && $brackets_level == 1) {
1882                     $in_create_table_fields = TRUE;
1883                 }
1884             }
1885
1886
1887             if ($arr[$i]['type'] == 'punct_bracket_close_round') {
1888                 $brackets_level--;
1889                 if ($seen_references) {
1890                     $seen_references = FALSE;
1891                 }
1892                 if ($seen_create_table && $brackets_level == 0) {
1893                     $in_create_table_fields = FALSE;
1894                 }
1895             }
1896
1897             if (($arr[$i]['type'] == 'alpha_columnAttrib')) {
1898                 $upper_data = strtoupper($arr[$i]['data']);
1899                 if ($seen_create_table && $in_create_table_fields) {
1900                     if ($upper_data == 'DEFAULT') {
1901                         $seen_default = TRUE;
1902                         $create_table_fields[$current_identifier]['default_value'] = $arr[$i + 1]['data'];
1903                     }
1904                 }
1905             }
1906
1907             /**
1908              * @see @todo 2005-10-16 note: the "or" part here is a workaround for a bug
1909              */
1910             if (($arr[$i]['type'] == 'alpha_columnType') || ($arr[$i]['type'] == 'alpha_functionName' && $seen_create_table)) {
1911                 $upper_data = strtoupper($arr[$i]['data']);
1912                 if ($seen_create_table && $in_create_table_fields && isset($current_identifier)) {
1913                     $create_table_fields[$current_identifier]['type'] = $upper_data;
1914                     if ($upper_data == 'TIMESTAMP') {
1915                         $arr[$i]['type'] = 'alpha_columnType';
1916                         $in_timestamp_options = TRUE;
1917                     } else {
1918                         $in_timestamp_options = FALSE;
1919                         if ($upper_data == 'CHAR') {
1920                             $arr[$i]['type'] = 'alpha_columnType';
1921                         }
1922                     }
1923                 }
1924             }
1925
1926
1927             if ($arr[$i]['type'] == 'quote_backtick' || $arr[$i]['type'] == 'alpha_identifier') {
1928
1929                 if ($arr[$i]['type'] == 'quote_backtick') {
1930                     // remove backquotes
1931                     $identifier = PMA_unQuote($arr[$i]['data']);
1932                 } else {
1933                     $identifier = $arr[$i]['data'];
1934                 }
1935
1936                 if ($seen_create_table && $in_create_table_fields) {
1937                     $current_identifier = $identifier;
1938                     // warning: we set this one even for non TIMESTAMP type
1939                     $create_table_fields[$current_identifier]['timestamp_not_null'] = FALSE;
1940                 }
1941
1942                 if ($seen_constraint) {
1943                     $foreign[$foreign_key_number]['constraint'] = $identifier;
1944                 }
1945
1946                 if ($seen_foreign && $brackets_level > 0) {
1947                     $foreign[$foreign_key_number]['index_list'][] = $identifier;
1948                 }
1949
1950                 if ($seen_references) {
1951                     if ($seen_alter && $brackets_level > 0) {
1952                         $foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
1953                     // here, the first bracket level corresponds to the
1954                     // bracket of CREATE TABLE
1955                     // so if we are on level 2, it must be the index list
1956                     // of the foreign key REFERENCES
1957                     } elseif ($brackets_level > 1) {
1958                         $foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
1959                     } elseif ($arr[$i+1]['type'] == 'punct_qualifier') {
1960                         // identifier is `db`.`table`
1961                         // the first pass will pick the db name
1962                         // the next pass will pick the table name
1963                         $foreign[$foreign_key_number]['ref_db_name'] = $identifier;
1964                     } else {
1965                         // identifier is `table`
1966                         $foreign[$foreign_key_number]['ref_table_name'] = $identifier;
1967                     }
1968                 }
1969             }
1970         } // end for $i (loop #3)
1971
1972
1973         // Fill the $subresult array
1974
1975         if (isset($create_table_fields)) {
1976             $subresult['create_table_fields'] = $create_table_fields;
1977         }
1978
1979         if (isset($foreign)) {
1980             $subresult['foreign_keys'] = $foreign;
1981         }
1982
1983         if (isset($select_expr_clause)) {
1984             $subresult['select_expr_clause'] = $select_expr_clause;
1985         }
1986         if (isset($from_clause)) {
1987             $subresult['from_clause'] = $from_clause;
1988         }
1989         if (isset($group_by_clause)) {
1990             $subresult['group_by_clause'] = $group_by_clause;
1991         }
1992         if (isset($order_by_clause)) {
1993             $subresult['order_by_clause'] = $order_by_clause;
1994         }
1995         if (isset($having_clause)) {
1996             $subresult['having_clause'] = $having_clause;
1997         }
1998         if (isset($limit_clause)) {
1999             $subresult['limit_clause'] = $limit_clause;
2000         }
2001         if (isset($where_clause)) {
2002             $subresult['where_clause'] = $where_clause;
2003         }
2004         if (isset($unsorted_query) && !empty($unsorted_query)) {
2005             $subresult['unsorted_query'] = $unsorted_query;
2006         }
2007         if (isset($where_clause_identifiers)) {
2008             $subresult['where_clause_identifiers'] = $where_clause_identifiers;
2009         }
2010
2011         if (isset($position_of_first_select)) {
2012             $subresult['position_of_first_select'] = $position_of_first_select;
2013             $subresult['section_before_limit'] = $section_before_limit;
2014             $subresult['section_after_limit'] = $section_after_limit;
2015         }
2016
2017         // They are naughty and didn't have a trailing semi-colon,
2018         // then still handle it properly
2019         if ($subresult['querytype'] != '') {
2020             $result[] = $subresult;
2021         }
2022         return $result;
2023     } // end of the "PMA_SQP_analyze()" function
2024
2025
2026     /**
2027      * Colorizes SQL queries html formatted
2028      *
2029      * @todo check why adding a "\n" after the </span> would cause extra blanks
2030      * to be displayed: SELECT p . person_name
2031      * @param  array   The SQL queries html formatted
2032      *
2033      * @return array   The colorized SQL queries
2034      *
2035      * @access public
2036      */
2037     function PMA_SQP_formatHtml_colorize($arr)
2038     {
2039         $i         = $GLOBALS['PMA_strpos']($arr['type'], '_');
2040         $class     = '';
2041         if ($i > 0) {
2042             $class = 'syntax_' . PMA_substr($arr['type'], 0, $i) . ' ';
2043         }
2044
2045         $class     .= 'syntax_' . $arr['type'];
2046
2047         return '<span class="' . $class . '">' . htmlspecialchars($arr['data']) . '</span>';
2048     } // end of the "PMA_SQP_formatHtml_colorize()" function
2049
2050
2051     /**
2052      * Formats SQL queries to html
2053      *
2054      * @param  array   The SQL queries
2055      * @param  string  mode
2056      * @param  integer starting token
2057      * @param  integer number of tokens to format, -1 = all
2058      *
2059      * @return string  The formatted SQL queries
2060      *
2061      * @access public
2062      */
2063     function PMA_SQP_formatHtml($arr, $mode='color', $start_token=0,
2064         $number_of_tokens=-1)
2065     {
2066         //DEBUG echo 'in Format<pre>'; print_r($arr); echo '</pre>';
2067         // then check for an array
2068         if (!is_array($arr)) {
2069             return htmlspecialchars($arr);
2070         }
2071         // first check for the SQL parser having hit an error
2072         if (PMA_SQP_isError()) {
2073             return htmlspecialchars($arr['raw']);
2074         }
2075         // else do it properly
2076         switch ($mode) {
2077             case 'color':
2078                 $str                                = '<span class="syntax">';
2079                 $html_line_break                    = '<br />';
2080                 $docu                               = TRUE;
2081                 break;
2082             case 'query_only':
2083                 $str                                = '';
2084                 $html_line_break                    = "\n";
2085                 $docu                               = FALSE;
2086                 break;
2087             case 'text':
2088                 $str                                = '';
2089                 $html_line_break                    = '<br />';
2090                 $docu                               = TRUE;
2091                 break;
2092         } // end switch
2093         $close_docu_link = false;
2094         $indent                                     = 0;
2095         $bracketlevel                               = 0;
2096         $functionlevel                              = 0;
2097         $infunction                                 = FALSE;
2098         $space_punct_listsep                        = ' ';
2099         $space_punct_listsep_function_name          = ' ';
2100         // $space_alpha_reserved_word = '<br />'."\n";
2101         $space_alpha_reserved_word                  = ' ';
2102
2103         $keywords_with_brackets_1before            = array(
2104             'INDEX',
2105             'KEY',
2106             'ON',
2107             'USING'
2108         );
2109         $keywords_with_brackets_1before_cnt        = 4;
2110
2111         $keywords_with_brackets_2before            = array(
2112             'IGNORE',
2113             'INDEX',
2114             'INTO',
2115             'KEY',
2116             'PRIMARY',
2117             'PROCEDURE',
2118             'REFERENCES',
2119             'UNIQUE',
2120             'USE'
2121         );
2122         // $keywords_with_brackets_2before_cnt = count($keywords_with_brackets_2before);
2123         $keywords_with_brackets_2before_cnt        = 9;
2124
2125         // These reserved words do NOT get a newline placed near them.
2126         $keywords_no_newline               = array(
2127             'AS',
2128             'ASC',
2129             'DESC',
2130             'DISTINCT',
2131             'DUPLICATE',
2132             'HOUR',
2133             'INTERVAL',
2134             'IS',
2135             'LIKE',
2136             'NOT',
2137             'NULL',
2138             'ON',
2139             'REGEXP'
2140         );
2141         $keywords_no_newline_cnt           = 12;
2142
2143         // These reserved words introduce a privilege list
2144         $keywords_priv_list                = array(
2145             'GRANT',
2146             'REVOKE'
2147         );
2148         $keywords_priv_list_cnt            = 2;
2149
2150         if ($number_of_tokens == -1) {
2151             $arraysize = $arr['len'];
2152         } else {
2153             $arraysize = $number_of_tokens;
2154         }
2155         $typearr   = array();
2156         if ($arraysize >= 0) {
2157             $typearr[0] = '';
2158             $typearr[1] = '';
2159             $typearr[2] = '';
2160             //$typearr[3] = $arr[0]['type'];
2161             $typearr[3] = $arr[$start_token]['type'];
2162         }
2163
2164         $in_priv_list = FALSE;
2165         for ($i = $start_token; $i < $arraysize; $i++) {
2166 // DEBUG echo "Loop format <strong>" . $arr[$i]['data'] . "</strong> " . $arr[$i]['type'] . "<br />";
2167             $before = '';
2168             $after  = '';
2169             $indent = 0;
2170             // array_shift($typearr);
2171             /*
2172             0 prev2
2173             1 prev
2174             2 current
2175             3 next
2176             */
2177             if (($i + 1) < $arraysize) {
2178                 // array_push($typearr, $arr[$i + 1]['type']);
2179                 $typearr[4] = $arr[$i + 1]['type'];
2180             } else {
2181                 //array_push($typearr, null);
2182                 $typearr[4] = '';
2183             }
2184
2185             for ($j=0; $j<4; $j++) {
2186                 $typearr[$j] = $typearr[$j + 1];
2187             }
2188
2189             switch ($typearr[2]) {
2190                 case 'alpha_bitfield_constant_introducer':
2191                     $before     = ' ';
2192                     $after      = '';
2193                     break;
2194                 case 'white_newline':
2195                     $before     = '';
2196                     break;
2197                 case 'punct_bracket_open_round':
2198                     $bracketlevel++;
2199                     $infunction = FALSE;
2200                     // Make sure this array is sorted!
2201                     if (($typearr[1] == 'alpha_functionName') || ($typearr[1] == 'alpha_columnType') || ($typearr[1] == 'punct')
2202                         || ($typearr[3] == 'digit_integer') || ($typearr[3] == 'digit_hex') || ($typearr[3] == 'digit_float')
2203                         || (($typearr[0] == 'alpha_reservedWord')
2204                             && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 2]['data']), $keywords_with_brackets_2before, $keywords_with_brackets_2before_cnt))
2205                         || (($typearr[1] == 'alpha_reservedWord')
2206                             && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_with_brackets_1before, $keywords_with_brackets_1before_cnt))
2207                         ) {
2208                         $functionlevel++;
2209                         $infunction = TRUE;
2210                         $after      .= ' ';
2211                     } else {
2212                         $indent++;
2213                         $after      .= ($mode != 'query_only' ? '<div class="syntax_indent' . $indent . '">' : ' ');
2214                     }
2215                     break;
2216                 case 'alpha_identifier':
2217                     if (($typearr[1] == 'punct_qualifier') || ($typearr[3] == 'punct_qualifier')) {
2218                         $after      = '';
2219                         $before     = '';
2220                     }
2221                     if (($typearr[3] == 'alpha_columnType') || ($typearr[3] == 'alpha_identifier')) {
2222                         $after      .= ' ';
2223                     }
2224                     break;
2225                 case 'punct_user':
2226                 case 'punct_qualifier':
2227                     $before         = '';
2228                     $after          = '';
2229                     break;
2230                 case 'punct_listsep':
2231                     if ($infunction == TRUE) {
2232                         $after      .= $space_punct_listsep_function_name;
2233                     } else {
2234                         $after      .= $space_punct_listsep;
2235                     }
2236                     break;
2237                 case 'punct_queryend':
2238                     if (($typearr[3] != 'comment_mysql') && ($typearr[3] != 'comment_ansi') && $typearr[3] != 'comment_c') {
2239                         $after     .= $html_line_break;
2240                         $after     .= $html_line_break;
2241                     }
2242                     $space_punct_listsep               = ' ';
2243                     $space_punct_listsep_function_name = ' ';
2244                     $space_alpha_reserved_word         = ' ';
2245                     $in_priv_list                      = FALSE;
2246                     break;
2247                 case 'comment_mysql':
2248                 case 'comment_ansi':
2249                     $after         .= $html_line_break;
2250                     break;
2251                 case 'punct':
2252                     $before         .= ' ';
2253                     // workaround for
2254                     // select * from mytable limit 0,-1
2255                     // (a side effect of this workaround is that
2256                     // select 20 - 9
2257                     // becomes
2258                     // select 20 -9
2259                     // )
2260                     if ($typearr[3] != 'digit_integer') {
2261                        $after        .= ' ';
2262                     }
2263                     break;
2264                 case 'punct_bracket_close_round':
2265                     $bracketlevel--;
2266                     if ($infunction == TRUE) {
2267                         $functionlevel--;
2268                         $after     .= ' ';
2269                         $before    .= ' ';
2270                     } else {
2271                         $indent--;
2272                         $before    .= ($mode != 'query_only' ? '</div>' : ' ');
2273                     }
2274                     $infunction    = ($functionlevel > 0) ? TRUE : FALSE;
2275                     break;
2276                 case 'alpha_columnType':
2277                     if ($docu) {
2278                         switch ($arr[$i]['data']) {
2279                             case 'tinyint':
2280                             case 'smallint':
2281                             case 'mediumint':
2282                             case 'int':
2283                             case 'bigint':
2284                             case 'decimal':
2285                             case 'float':
2286                             case 'double':
2287                             case 'real':
2288                             case 'bit':
2289                             case 'boolean':
2290                             case 'serial':
2291                                 $before .= PMA_showMySQLDocu('data-types', 'numeric-types', false, '', true);
2292                                 $after = '</a>' . $after;
2293                                 break;
2294                             case 'date':
2295                             case 'datetime':
2296                             case 'timestamp':
2297                             case 'time':
2298                             case 'year':
2299                                 $before .= PMA_showMySQLDocu('data-types', 'date-and-time-types', false, '', true);
2300                                 $after = '</a>' . $after;
2301                                 break;
2302                             case 'char':
2303                             case 'varchar':
2304                             case 'tinytext':
2305                             case 'text':
2306                             case 'mediumtext':
2307                             case 'longtext':
2308                             case 'binary':
2309                             case 'varbinary':
2310                             case 'tinyblob':
2311                             case 'mediumblob':
2312                             case 'blob':
2313                             case 'longblob':
2314                             case 'enum':
2315                             case 'set':
2316                                 $before .= PMA_showMySQLDocu('data-types', 'string-types', false, '', true);
2317                                 $after = '</a>' . $after;
2318                                 break;
2319                         }
2320                     }
2321                     if ($typearr[3] == 'alpha_columnAttrib') {
2322                         $after     .= ' ';
2323                     }
2324                     if ($typearr[1] == 'alpha_columnType') {
2325                         $before    .= ' ';
2326                     }
2327                     break;
2328                 case 'alpha_columnAttrib':
2329
2330                     // ALTER TABLE tbl_name AUTO_INCREMENT = 1
2331                     // COLLATE LATIN1_GENERAL_CI DEFAULT
2332                     if ($typearr[1] == 'alpha_identifier' || $typearr[1] == 'alpha_charset') {
2333                         $before .= ' ';
2334                     }
2335                     if (($typearr[3] == 'alpha_columnAttrib') || ($typearr[3] == 'quote_single') || ($typearr[3] == 'digit_integer')) {
2336                         $after     .= ' ';
2337                     }
2338                     // workaround for
2339                     // AUTO_INCREMENT = 31DEFAULT_CHARSET = utf-8
2340
2341                     if ($typearr[2] == 'alpha_columnAttrib' && $typearr[3] == 'alpha_reservedWord') {
2342                         $before .= ' ';
2343                     }
2344                     // workaround for
2345                     // select * from mysql.user where binary user="root"
2346                     // binary is marked as alpha_columnAttrib
2347                     // but should be marked as a reserved word
2348                     if (strtoupper($arr[$i]['data']) == 'BINARY'
2349                       && $typearr[3] == 'alpha_identifier') {
2350                         $after     .= ' ';
2351                     }
2352                     break;
2353                 case 'alpha_reservedWord':
2354                     // do not uppercase the reserved word if we are calling
2355                     // this function in query_only mode, because we need
2356                     // the original query (otherwise we get problems with
2357                     // semi-reserved words like "storage" which is legal
2358                     // as an identifier name)
2359
2360                     if ($mode != 'query_only') {
2361                         $arr[$i]['data'] = strtoupper($arr[$i]['data']);
2362                     }
2363
2364                     if ((($typearr[1] != 'alpha_reservedWord')
2365                         || (($typearr[1] == 'alpha_reservedWord')
2366                             && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_no_newline, $keywords_no_newline_cnt)))
2367                         && ($typearr[1] != 'punct_level_plus')
2368                         && (!PMA_STR_binarySearchInArr($arr[$i]['data'], $keywords_no_newline, $keywords_no_newline_cnt))) {
2369                         // do not put a space before the first token, because
2370                         // we use a lot of pattern matching checking for the
2371                         // first reserved word at beginning of query
2372                         // so do not put a newline before
2373                         //
2374                         // also we must not be inside a privilege list
2375                         if ($i > 0) {
2376                             // the alpha_identifier exception is there to
2377                             // catch cases like
2378                             // GRANT SELECT ON mydb.mytable TO myuser@localhost
2379                             // (else, we get mydb.mytableTO)
2380                             //
2381                             // the quote_single exception is there to
2382                             // catch cases like
2383                             // GRANT ... TO 'marc'@'domain.com' IDENTIFIED...
2384                             /**
2385                              * @todo fix all cases and find why this happens
2386                              */
2387
2388                             if (!$in_priv_list || $typearr[1] == 'alpha_identifier' || $typearr[1] == 'quote_single' || $typearr[1] == 'white_newline') {
2389                                 $before    .= $space_alpha_reserved_word;
2390                             }
2391                         } else {
2392                         // on first keyword, check if it introduces a
2393                         // privilege list
2394                             if (PMA_STR_binarySearchInArr($arr[$i]['data'], $keywords_priv_list, $keywords_priv_list_cnt)) {
2395                                 $in_priv_list = TRUE;
2396                             }
2397                         }
2398                     } else {
2399                         $before    .= ' ';
2400                     }
2401
2402                     switch ($arr[$i]['data']) {
2403                         case 'CREATE':
2404                         case 'ALTER':
2405                         case 'DROP':
2406                         case 'RENAME';
2407                         case 'TRUNCATE':
2408                         case 'ANALYZE':
2409                         case 'ANALYSE':
2410                         case 'OPTIMIZE':
2411                             if ($docu) {
2412                                 switch ($arr[$i + 1]['data']) {
2413                                     case 'EVENT':
2414                                     case 'TABLE':
2415                                     case 'TABLESPACE':
2416                                     case 'FUNCTION':
2417                                     case 'INDEX':
2418                                     case 'PROCEDURE':
2419                                     case 'TRIGGER':
2420                                     case 'SERVER':
2421                                     case 'DATABASE':
2422                                     case 'VIEW':
2423                                         $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'] . '_' . $arr[$i + 1]['data'], false, '', true);
2424                                         $close_docu_link = true;
2425                                         break;
2426                                 }
2427                                 if ($arr[$i + 1]['data'] == 'LOGFILE' && $arr[$i + 2]['data'] == 'GROUP') {
2428                                     $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'] . '_LOGFILE_GROUP', false, '', true);
2429                                     $close_docu_link = true;
2430                                 }
2431                             }
2432                             if (!$in_priv_list) {
2433                                 $space_punct_listsep       = $html_line_break;
2434                                 $space_alpha_reserved_word = ' ';
2435                             }
2436                             break;
2437                         case 'EVENT':
2438                         case 'TABLESPACE':
2439                         case 'TABLE':
2440                         case 'FUNCTION':
2441                         case 'INDEX':
2442                         case 'PROCEDURE':
2443                         case 'SERVER':
2444                         case 'TRIGGER':
2445                         case 'DATABASE':
2446                         case 'VIEW':
2447                         case 'GROUP':
2448                             if ($close_docu_link) {
2449                                 $after = '</a>' . $after;
2450                                 $close_docu_link = false;
2451                             }
2452                             break;
2453                         case 'SET':
2454                             if ($docu && ($i == 0 || $arr[$i - 1]['data'] != 'CHARACTER')) {
2455                                 $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'], false, '', true);
2456                                 $after = '</a>' . $after;
2457                             }
2458                             if (!$in_priv_list) {
2459                                 $space_punct_listsep       = $html_line_break;
2460                                 $space_alpha_reserved_word = ' ';
2461                             }
2462                             break;
2463                         case 'EXPLAIN':
2464                         case 'DESCRIBE':
2465                         case 'DELETE':
2466                         case 'SHOW':
2467                         case 'UPDATE':
2468                             if ($docu) {
2469                                 $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'], false, '', true);
2470                                 $after = '</a>' . $after;
2471                             }
2472                             if (!$in_priv_list) {
2473                                 $space_punct_listsep       = $html_line_break;
2474                                 $space_alpha_reserved_word = ' ';
2475                             }
2476                             break;
2477                         case 'INSERT':
2478                         case 'REPLACE':
2479                             if ($docu) {
2480                                 $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'], false, '', true);
2481                                 $after = '</a>' . $after;
2482                             }
2483                             if (!$in_priv_list) {
2484                                 $space_punct_listsep       = $html_line_break;
2485                                 $space_alpha_reserved_word = $html_line_break;
2486                             }
2487                             break;
2488                         case 'VALUES':
2489                             $space_punct_listsep       = ' ';
2490                             $space_alpha_reserved_word = $html_line_break;
2491                             break;
2492                         case 'SELECT':
2493                             if ($docu) {
2494                                 $before .= PMA_showMySQLDocu('SQL-Syntax', 'SELECT', false, '', true);
2495                                 $after = '</a>' . $after;
2496                             }
2497                             $space_punct_listsep       = ' ';
2498                             $space_alpha_reserved_word = $html_line_break;
2499                             break;
2500                         case 'CALL':
2501                         case 'DO':
2502                         case 'HANDLER':
2503                             if ($docu) {
2504                                 $before .= PMA_showMySQLDocu('SQL-Syntax', $arr[$i]['data'], false, '', true);
2505                                 $after = '</a>' . $after;
2506                             }
2507                             break;
2508                         default:
2509                             break;
2510                     } // end switch ($arr[$i]['data'])
2511
2512                     $after         .= ' ';
2513                     break;
2514                 case 'digit_integer':
2515                 case 'digit_float':
2516                 case 'digit_hex':
2517                     /**
2518                      * @todo could there be other types preceding a digit?
2519                      */
2520                     if ($typearr[1] == 'alpha_reservedWord') {
2521                         $after .= ' ';
2522                     }
2523                     if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
2524                         $after     .= ' ';
2525                     }
2526                     if ($typearr[1] == 'alpha_columnAttrib') {
2527                         $before .= ' ';
2528                     }
2529                     break;
2530                 case 'alpha_variable':
2531                     $after      = ' ';
2532                     break;
2533                 case 'quote_double':
2534                 case 'quote_single':
2535                     // workaround: for the query
2536                     // REVOKE SELECT ON `base2\_db`.* FROM 'user'@'%'
2537                     // the @ is incorrectly marked as alpha_variable
2538                     // in the parser, and here, the '%' gets a blank before,
2539                     // which is a syntax error
2540                     if ($typearr[1] != 'punct_user' && $typearr[1] != 'alpha_bitfield_constant_introducer') {
2541                         $before        .= ' ';
2542                     }
2543                     if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
2544                         $after     .= ' ';
2545                     }
2546                     break;
2547                 case 'quote_backtick':
2548                     // here we check for punct_user to handle correctly
2549                     // DEFINER = `username`@`%`
2550                     // where @ is the punct_user and `%` is the quote_backtick
2551                     if ($typearr[3] != 'punct_qualifier' && $typearr[3] != 'alpha_variable' && $typearr[3] != 'punct_user') {
2552                         $after     .= ' ';
2553                     }
2554                     if ($typearr[1] != 'punct_qualifier' && $typearr[1] != 'alpha_variable' && $typearr[1] != 'punct_user') {
2555                         $before    .= ' ';
2556                     }
2557                     break;
2558                 default:
2559                     break;
2560             } // end switch ($typearr[2])
2561
2562 /*
2563             if ($typearr[3] != 'punct_qualifier') {
2564                 $after             .= ' ';
2565             }
2566             $after                 .= "\n";
2567 */
2568             $str .= $before . ($mode=='color' ? PMA_SQP_formatHTML_colorize($arr[$i]) : $arr[$i]['data']). $after;
2569         } // end for
2570         /* End possibly unclosed documentation link */
2571         if ($close_docu_link) {
2572             $str .= '</a>';
2573             $close_docu_link = false;
2574         }
2575         if ($mode=='color') {
2576             $str .= '</span>';
2577         }
2578
2579         return $str;
2580     } // end of the "PMA_SQP_formatHtml()" function
2581 }
2582
2583 /**
2584  * Builds a CSS rule used for html formatted SQL queries
2585  *
2586  * @param  string  The class name
2587  * @param  string  The property name
2588  * @param  string  The property value
2589  *
2590  * @return string  The CSS rule
2591  *
2592  * @access public
2593  *
2594  * @see    PMA_SQP_buildCssData()
2595  */
2596 function PMA_SQP_buildCssRule($classname, $property, $value)
2597 {
2598     $str     = '.' . $classname . ' {';
2599     if ($value != '') {
2600         $str .= $property . ': ' . $value . ';';
2601     }
2602     $str     .= '}' . "\n";
2603
2604     return $str;
2605 } // end of the "PMA_SQP_buildCssRule()" function
2606
2607
2608 /**
2609  * Builds CSS rules used for html formatted SQL queries
2610  *
2611  * @return string  The CSS rules set
2612  *
2613  * @access public
2614  *
2615  * @global array   The current PMA configuration
2616  *
2617  * @see    PMA_SQP_buildCssRule()
2618  */
2619 function PMA_SQP_buildCssData()
2620 {
2621     global $cfg;
2622
2623     $css_string     = '';
2624     foreach ($cfg['SQP']['fmtColor'] AS $key => $col) {
2625         $css_string .= PMA_SQP_buildCssRule('syntax_' . $key, 'color', $col);
2626     }
2627
2628     for ($i = 0; $i < 8; $i++) {
2629         $css_string .= PMA_SQP_buildCssRule('syntax_indent' . $i, 'margin-left', ($i * $cfg['SQP']['fmtInd']) . $cfg['SQP']['fmtIndUnit']);
2630     }
2631
2632     return $css_string;
2633 } // end of the "PMA_SQP_buildCssData()" function
2634
2635 if (! defined('PMA_MINIMUM_COMMON')) {
2636     /**
2637      * Gets SQL queries with no format
2638      *
2639      * @param  array   The SQL queries list
2640      *
2641      * @return string  The SQL queries with no format
2642      *
2643      * @access public
2644      */
2645     function PMA_SQP_formatNone($arr)
2646     {
2647         $formatted_sql = htmlspecialchars($arr['raw']);
2648         $formatted_sql = preg_replace("@((\015\012)|(\015)|(\012)){3,}@", "\n\n", $formatted_sql);
2649
2650         return $formatted_sql;
2651     } // end of the "PMA_SQP_formatNone()" function
2652
2653
2654     /**
2655      * Gets SQL queries in text format
2656      *
2657      * @todo WRITE THIS!
2658      * @param  array   The SQL queries list
2659      *
2660      * @return string  The SQL queries in text format
2661      *
2662      * @access public
2663      */
2664     function PMA_SQP_formatText($arr)
2665     {
2666          return PMA_SQP_formatNone($arr);
2667     } // end of the "PMA_SQP_formatText()" function
2668 } // end if: minimal common.lib needed?
2669
2670 ?>