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