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