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