libraries/sqlparser.lib.php3

   1 <?php
   2 /* $Id$ */
   3 // vim: expandtab sw=4 ts=4 sts=4:
   4
   5 /** SQL Parser Functions for phpMyAdmin
   6  *
   7  * Copyright 2002 Robin Johnson <robbat2@users.sourceforge.net>
   8  * http://www.orbis-terrarum.net/?l=people.robbat2
   9  *
  10  * These functions define an SQL parser system, capable of understanding and
  11  * extracting data from a MySQL type SQL query.
  12  *
  13  * The basic procedure for using the new SQL parser:
  14  * On any page that needs to extract data from a query or to pretty-print a
  15  * query, you need code like this up at the top:
  16  *
  17  * ($sql contains the query)
  18  * $parsed_sql = PMA_SQP_parse($sql);
  19  *
  20  * If you want to extract data from it then, you just need to run
  21  * $sql_info = PMA_SQP_analyze($parsed_sql);
  22  *
  23  * lem9: See comments in PMA_SQP_analyze for the returned info
  24  *       from the analyzer.
  25  *
  26  * If you want a pretty-printed version of the query, do:
  27  * $string = PMA_SQP_formatHtml($parsed_sql);
  28  * (note that that you need to have syntax.css.php3 included somehow in your
  29  * page for it to work, I recommend '<link rel="stylesheet" type="text/css"
  30  * href="syntax.css.php3" />' at the moment.)
  31  */
  32
  33
  34 if (!defined('PMA_SQP_LIB_INCLUDED')) {
  35     define('PMA_SQP_LIB_INCLUDED', 1);
  36
  37     /**
  38      * Minimum inclusion? (i.e. for the stylesheet builder)
  39      */
  40
  41     if (!isset($is_minimum_common)) {
  42         $is_minimum_common = FALSE;
  43     }
  44
  45     if ($is_minimum_common == FALSE) {
  46         /**
  47          * Include the string library as we use it heavily
  48          */
  49         if (!defined('PMA_STR_LIB_INCLUDED')) {
  50             include('./libraries/string.lib.php3');
  51         }
  52
  53         /**
  54          * Include data for the SQL Parser
  55          */
  56         if (!defined('PMA_SQP_DATA_INCLUDED')) {
  57             include('./libraries/sqlparser.data.php3');
  58         }
  59
  60         if (!defined('DEBUG_TIMING')) {
  61             function PMA_SQP_arrayAdd(&$arr, $type, $data, &$arrsize)
  62             {
  63                 $arr[] = array('type' => $type, 'data' => $data);
  64                 $arrsize++;
  65             } // end of the "PMA_SQP_arrayAdd()" function
  66         } else {
  67             function PMA_SQP_arrayAdd(&$arr, $type, $data, &$arrsize)
  68             {
  69                 global $timer;
  70
  71                 $t     = $timer;
  72                 $arr[] = array('type' => $type, 'data' => $data , 'time' => $t);
  73                 $timer = microtime();
  74                 $arrsize++;
  75             } // end of the "PMA_SQP_arrayAdd()" function
  76         } // end if... else...
  77
  78
  79         /**
  80          * Reset the error variable for the SQL parser
  81          *
  82          * @access public
  83          */
  84         // Added, Robbat2 - 13 Janurary 2003, 2:59PM
  85         function PMA_SQP_resetError() {
  86             global $SQP_errorString;
  87             $SQP_errorString = '';
  88             unset($SQP_errorString);
  89         }
  90
  91         /**
  92          * Get the contents of the error variable for the SQL parser
  93          *
  94          * @return string Error string from SQL parser
  95          *
  96          * @access public
  97          */
  98         // Added, Robbat2 - 13 Janurary 2003, 2:59PM
  99         function PMA_SQP_getErrorString() {
 100             global $SQP_errorString;
 101             return isset($SQP_errorString) ? $SQP_errorString : '';
 102         }
 103
 104         /**
 105          * Check if the SQL parser hit an error
 106          *
 107          * @return boolean error state
 108          *
 109          * @access public
 110          */
 111         // Added, Robbat2 - 13 Janurary 2003, 2:59PM
 112         function PMA_SQP_isError() {
 113             global $SQP_errorString;
 114             return isset($SQP_errorString) && !empty($SQP_errorString);
 115         }
 116
 117         /**
 118          * Set an error message for the system
 119          *
 120          * @param  string  The error message
 121          * @param  string  The failing SQL query
 122          *
 123          * @access private
 124          * @scope SQL Parser internal
 125          */
 126         // Revised, Robbat2 - 13 Janurary 2003, 2:59PM
 127         function PMA_SQP_throwError($message, $sql)
 128         {
 129
 130             global $SQP_errorString;
 131             $SQP_errorString = '<p>'.$GLOBALS['strSQLParserUserError'] . '</p>' . "\n"
 132                 . '<pre>' . "\n"
 133                 . 'ERROR: ' . $message . "\n"
 134                 . 'SQL: ' . $sql .  "\n"
 135                 . '</pre>' . "\n";
 136
 137             /*
 138             // Removed to solve bug #641765 - Robbat2 - 12 January 2003, 9:46PM
 139             flush();
 140             if (PMA_PHP_INT_VERSION >= 40200 && @function_exists('ob_flush')) {
 141                 ob_flush();
 142             }
 143             */
 144         } // end of the "PMA_SQP_throwError()" function
 145
 146
 147         /**
 148          * Do display the bug report
 149          *
 150          * @param  string  The error message
 151          * @param  string  The failing SQL query
 152          *
 153          * @access public
 154          */
 155         function PMA_SQP_bug($message, $sql)
 156         {
 157             $debugstr = 'ERROR: ' . $message . "\n";
 158             $debugstr .= 'CVS: $Id$' . "\n";
 159             $debugstr .= 'MySQL: '.PMA_MYSQL_STR_VERSION . "\n";
 160             $debugstr .= 'USR OS, AGENT, VER: ' . PMA_USR_OS . ' ' . PMA_USR_BROWSER_AGENT . ' ' . PMA_USR_BROWSER_VER . "\n";
 161             $debugstr .= 'PMA: ' . PMA_VERSION . "\n";
 162             $debugstr .= 'PHP VER,OS: ' . PMA_PHP_STR_VERSION . ' ' . PHP_OS . "\n";
 163             $debugstr .= 'LANG: ' . $GLOBALS['lang'] . "\n";
 164             $debugstr .= 'SQL: ' . $sql;
 165
 166             $encodedstr     = $debugstr;
 167             if (PMA_PHP_INT_VERSION >= 40001 && @function_exists('gzcompress')) {
 168                 $encodedstr = gzcompress($debugstr, 9);
 169             }
 170             $encodedstr     = preg_replace("/(\015\012)|(\015)|(\012)/", '<br />' . "\n", chunk_split(base64_encode($encodedstr)));
 171
 172             echo $GLOBALS['strSQLParserBugMessage'] . '<br />' . "\n"
 173                  . '----' . $GLOBALS['strBeginCut'] . '----' . '<br />' . "\n"
 174                  . $encodedstr . "\n"
 175                  . '----' . $GLOBALS['strEndCut'] . '----' . '<br />' . "\n";
 176
 177             flush();
 178             if (PMA_PHP_INT_VERSION >= 40200 && @function_exists('ob_flush')) {
 179                 ob_flush();
 180             }
 181
 182             echo '----' . $GLOBALS['strBeginRaw'] . '----<br />' . "\n"
 183                  . '<pre>' . "\n"
 184                  . $debugstr
 185                  . '</pre>' . "\n"
 186                  . '----' . $GLOBALS['strEndRaw'] . '----<br />' . "\n";
 187
 188             flush();
 189             if (PMA_PHP_INT_VERSION >= 40200 && @function_exists('ob_flush')) {
 190                 ob_flush();
 191             }
 192         } // end of the "PMA_SQP_bug()" function
 193
 194
 195         /**
 196          * Parses the SQL queries
 197          *
 198          * @param  string   The SQL query list
 199          *
 200          * @return mixed    Most of times, nothing...
 201          *
 202          * @global array    The current PMA configuration
 203          * @global array    MySQL column attributes
 204          * @global array    MySQL reserved words
 205          * @global array    MySQL column types
 206          * @global array    MySQL function names
 207          * @global integer  MySQL column attributes count
 208          * @global integer  MySQL reserved words count
 209          * @global integer  MySQL column types count
 210          * @global integer  MySQL function names count
 211          *
 212          * @access public
 213          */
 214         function PMA_SQP_parse($sql)
 215         {
 216             global $cfg;
 217             global $PMA_SQPdata_column_attrib, $PMA_SQPdata_reserved_word, $PMA_SQPdata_column_type, $PMA_SQPdata_function_name,
 218             $PMA_SQPdata_column_attrib_cnt, $PMA_SQPdata_reserved_word_cnt, $PMA_SQPdata_column_type_cnt, $PMA_SQPdata_function_name_cnt;
 219
 220             // rabus: Convert all line feeds to Unix style
 221             $sql = str_replace("\r\n", "\n", $sql);
 222             $sql = str_replace("\r", "\n", $sql);
 223
 224             $len = $GLOBALS['PMA_strlen']($sql);
 225             if ($len == 0) {
 226                 return array();
 227             }
 228
 229             $sql_array               = array();
 230             $sql_array['raw']        = $sql;
 231             $count1                  = 0;
 232             $count2                  = 0;
 233             $punct_queryend          = ';';
 234             $punct_qualifier         = '.';
 235             $punct_listsep           = ',';
 236             $punct_level_plus        = '(';
 237             $punct_level_minus       = ')';
 238             $digit_floatdecimal      = '.';
 239             $digit_hexset            = 'x';
 240             $bracket_list            = '()[]{}';
 241             $allpunct_list           =  '-,;:!?/.^~\*&%+<=>|';
 242             $allpunct_list_pair      = array (
 243                 0 => '!=',
 244                 1 => '&&',
 245                 2 => ':=',
 246                 3 => '<<',
 247                 4 => '<=',
 248                 5 => '<=>',
 249                 6 => '<>',
 250                 7 => '>=',
 251                 8 => '>>',
 252                 9 => '||'
 253             );
 254             $allpunct_list_pair_size = 10; //count($allpunct_list_pair);
 255             $quote_list              = '\'"`';
 256             $arraysize               = 0;
 257
 258             while ($count2 < $len) {
 259                 $c      = $sql[$count2];
 260                 $count1 = $count2;
 261
 262                 if (($c == "\n")) {
 263                     $count2++;
 264                     PMA_SQP_arrayAdd($sql_array, 'white_newline', '', $arraysize);
 265                     continue;
 266                 }
 267
 268                 // Checks for white space
 269                 if (PMA_STR_isSpace($c)) {
 270                     $count2++;
 271                     continue;
 272                 }
 273
 274                 // Checks for comment lines.
 275                 // MySQL style #
 276                 // C style /* */
 277                 // ANSI style --
 278                 if (($c == '#')
 279                     || (($count2 + 1 < $len) && ($c == '/') && ($sql[$count2 + 1] == '*'))
 280                     || (($count2 + 2 < $len) && ($c == '-') && ($sql[$count2 + 1] == '-') && (($sql[$count2 + 2] == ' ') || ($sql[$count2 + 2] == "\n")))) {
 281                     $count2++;
 282                     $pos  = 0;
 283                     $type = 'bad';
 284                     switch ($c) {
 285                         case '#':
 286                             $type = 'mysql';
 287                         case '-':
 288                             $type = 'ansi';
 289                             $pos  = $GLOBALS['PMA_strpos']($sql, "\n", $count2);
 290                             break;
 291                         case '/':
 292                             $type = 'c';
 293                             $pos  = $GLOBALS['PMA_strpos']($sql, '*/', $count2);
 294                             $pos  += 2;
 295                             break;
 296                         default:
 297                             break;
 298                     } // end switch
 299                     $count2 = ($pos < $count2) ? $len : $pos;
 300                     $str    = $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
 301                     PMA_SQP_arrayAdd($sql_array, 'comment_' . $type, $str, $arraysize);
 302                     continue;
 303                 } // end if
 304
 305                 // Checks for something inside quotation marks
 306                 if (PMA_STR_strInStr($c, $quote_list)) {
 307                     $startquotepos   = $count2;
 308                     $quotetype       = $c;
 309                     $count2++;
 310                     $escaped         = FALSE;
 311                     $escaped_escaped = FALSE;
 312                     $pos             = $count2;
 313                     $oldpos          = 0;
 314                     do {
 315                         $oldpos = $pos;
 316                         $pos    = $GLOBALS['PMA_strpos'](' ' . $sql, $quotetype, $oldpos + 1) - 1;
 317                         // ($pos === FALSE)
 318                         if ($pos < 0) {
 319                             $debugstr = $GLOBALS['strSQPBugUnclosedQuote'] . ' @ ' . $startquotepos. "\n"
 320                                       . 'STR: ' . $quotetype;
 321                             PMA_SQP_throwError($debugstr, $sql);
 322                             return $sql;
 323                         }
 324
 325                         // If the quote is the first character, it can't be
 326                         // escaped, so don't do the rest of the code
 327                         if ($pos == 0) {
 328                             break;
 329                         }
 330
 331                         // Checks for MySQL escaping using a \
 332                         // And checks for ANSI escaping using the $quotetype character
 333                         if (($pos < $len) && PMA_STR_charIsEscaped($sql, $pos)) {
 334                             $pos ++;
 335                             continue;
 336                         } else if (($pos + 1 < $len) && ($sql[$pos] == $quotetype) && ($sql[$pos + 1] == $quotetype)) {
 337                             $pos = $pos + 2;
 338                             continue;
 339                         } else {
 340                             break;
 341                         }
 342                     } while ($len > $pos); // end do
 343
 344                     $count2       = $pos;
 345                     $count2++;
 346                     $type         = 'quote_';
 347                     switch ($quotetype) {
 348                         case '\'':
 349                             $type .= 'single';
 350                             break;
 351                         case '"':
 352                             $type .= 'double';
 353                             break;
 354                         case '`':
 355                             $type .= 'backtick';
 356                             break;
 357                         default:
 358                             break;
 359                     } // end switch
 360                     $data = $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
 361                     PMA_SQP_arrayAdd($sql_array, $type, $data, $arraysize);
 362                     continue;
 363                 }
 364
 365                 // Checks for brackets
 366                 if (PMA_STR_strInStr($c, $bracket_list)) {
 367                     // All bracket tokens are only one item long
 368                     $count2++;
 369                     $type_type     = '';
 370                     if (PMA_STR_strInStr($c, '([{')) {
 371                         $type_type = 'open';
 372                     } else {
 373                         $type_type = 'close';
 374                     }
 375
 376                     $type_style     = '';
 377                     if (PMA_STR_strInStr($c, '()')) {
 378                         $type_style = 'round';
 379                     } elseif (PMA_STR_strInStr($c, '[]')) {
 380                         $type_style = 'square';
 381                     } else {
 382                         $type_style = 'curly';
 383                     }
 384
 385                     $type = 'punct_bracket_' . $type_type . '_' . $type_style;
 386                     PMA_SQP_arrayAdd($sql_array, $type, $c, $arraysize);
 387                     continue;
 388                 }
 389
 390                 // Checks for punct
 391                 if (PMA_STR_strInStr($c, $allpunct_list)) {
 392                     while (($count2 < $len) && PMA_STR_strInStr($sql[$count2], $allpunct_list)) {
 393                         $count2++;
 394                     }
 395                     $l = $count2 - $count1;
 396                     if ($l == 1) {
 397                         $punct_data = $c;
 398                     } else {
 399                         $punct_data = $GLOBALS['PMA_substr']($sql, $count1, $l);
 400                     }
 401
 402                     // Special case, sometimes, althought two characters are
 403                     // adjectent directly, they ACTUALLY need to be seperate
 404                     if ($l == 1) {
 405                         $t_suffix         = '';
 406                         switch ($punct_data) {
 407                             case $punct_queryend:
 408                                 $t_suffix = '_queryend';
 409                                 break;
 410                             case $punct_qualifier:
 411                                 $t_suffix = '_qualifier';
 412                                 break;
 413                             case $punct_listsep:
 414                                 $t_suffix = '_listsep';
 415                                 break;
 416                             default:
 417                                 break;
 418                         }
 419                         PMA_SQP_arrayAdd($sql_array, 'punct' . $t_suffix, $punct_data, $arraysize);
 420                     }
 421                     else if (PMA_STR_binarySearchInArr($punct_data, $allpunct_list_pair, $allpunct_list_pair_size)) {
 422                         // Ok, we have one of the valid combined punct expressions
 423                         PMA_SQP_arrayAdd($sql_array, 'punct', $punct_data, $arraysize);
 424                     }
 425                     else {
 426                         // Bad luck, lets split it up more
 427                         $first  = $punct_data[0];
 428                         $first2 = $punct_data[0] . $punct_data[1];
 429                         $last2  = $punct_data[$l - 2] . $punct_data[$l - 1];
 430                         $last   = $punct_data[$l - 1];
 431                         if (($first == ',') || ($first == ';') || ($first == '.') || ($first = '*')) {
 432                             $count2     = $count1 + 1;
 433                             $punct_data = $first;
 434                         } else if (($last2 == '/*') || ($last2 == '--')) {
 435                             $count2     -= 2;
 436                             $punct_data = $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
 437                         } else if (($last == '-') || ($last == '+') || ($last == '!')) {
 438                             $count2--;
 439                             $punct_data = $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
 440                         } else {
 441                             $debugstr =  $GLOBALS['strSQPBugUnknownPunctuation'] . ' @ ' . ($count1+1) . "\n"
 442                                       . 'STR: ' . $punct_data;
 443                             PMA_SQP_throwError($debugstr, $sql);
 444                             return $sql;
 445                         }
 446                         PMA_SQP_arrayAdd($sql_array, 'punct', $punct_data, $arraysize);
 447                         continue;
 448                     } // end if... else if... else
 449                     continue;
 450                 }
 451
 452                 // Checks for alpha
 453                 if (PMA_STR_isSqlIdentifier($c, FALSE) || ($c == '@')) {
 454                     $count2 ++;
 455
 456                     //TODO: a @ can also be present in expressions like
 457                     // FROM 'user'@'%'
 458                     // in this case, the @ is wrongly marked as alpha_variable
 459
 460                     $is_sql_variable         = ($c == '@');
 461                     $is_digit                = (!$is_sql_variable) && PMA_STR_isDigit($c);
 462                     $is_hex_digit            = ($is_digit) && ($c == '0') && ($count2 < $len) && ($sql[$count2] == 'x');
 463                     $is_float_digit          = FALSE;
 464                     $is_float_digit_exponent = FALSE;
 465
 466                     if ($is_hex_digit) {
 467                         $count2++;
 468                     }
 469
 470                     while (($count2 < $len) && PMA_STR_isSqlIdentifier($sql[$count2], ($is_sql_variable || $is_digit))) {
 471                         $c2 = $sql[$count2];
 472                         if ($is_sql_variable && ($c2 == '.')) {
 473                             $count2++;
 474                             continue;
 475                         }
 476                         if ($is_digit && (!$is_hex_digit) && ($c2 == '.')) {
 477                             $count2++;
 478                             if (!$is_float_digit) {
 479                                 $is_float_digit = TRUE;
 480                                 continue;
 481                             } else {
 482                                 $debugstr = $GLOBALS['strSQPBugInvalidIdentifer'] . ' @ ' . ($count1+1) . "\n"
 483                                           . 'STR: ' . $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1);
 484                                 PMA_SQP_throwError($debugstr, $sql);
 485                                 return $sql;
 486                             }
 487                         }
 488                         if ($is_digit && (!$is_hex_digit) && (($c2 == 'e') || ($c2 == 'E'))) {
 489                             if (!$is_float_digit_exponent) {
 490                                 $is_float_digit_exponent = TRUE;
 491                                 $is_float_digit          = TRUE;
 492                                 $count2++;
 493                                 continue;
 494                             } else {
 495                                 $is_digit                = FALSE;
 496                                 $is_float_digit          = FALSE;
 497                             }
 498                         }
 499                         if (($is_hex_digit && PMA_STR_isHexDigit($c2)) || ($is_digit && PMA_STR_isDigit($c2))) {
 500                             $count2++;
 501                             continue;
 502                         } else {
 503                             $is_digit     = FALSE;
 504                             $is_hex_digit = FALSE;
 505                         }
 506
 507                         $count2++;
 508                     } // end while
 509
 510                     $l    = $count2 - $count1;
 511                     $str  = $GLOBALS['PMA_substr']($sql, $count1, $l);
 512
 513                     $type = '';
 514                     if ($is_digit) {
 515                         $type     = 'digit';
 516                         if ($is_float_digit) {
 517                             $type .= '_float';
 518                         } else if ($is_hex_digit) {
 519                             $type .= '_hex';
 520                         } else {
 521                             $type .= '_integer';
 522                         }
 523                     }
 524                     else {
 525                         if ($is_sql_variable != FALSE) {
 526                             $type = 'alpha_variable';
 527                         } else {
 528                             $type = 'alpha';
 529                         }
 530                     } // end if... else....
 531                     PMA_SQP_arrayAdd($sql_array, $type, $str, $arraysize);
 532
 533                     continue;
 534                 }
 535
 536                 // DEBUG
 537                 $count2++;
 538
 539                 $debugstr = 'C1 C2 LEN: ' . $count1 . ' ' . $count2 . ' ' . $len .  "\n"
 540                           . 'STR: ' . $GLOBALS['PMA_substr']($sql, $count1, $count2 - $count1) . "\n";
 541                 PMA_SQP_bug($debugstr, $sql);
 542                 return $sql;
 543
 544             } // end while ($count2 < $len)
 545
 546
 547             if ($arraysize > 0) {
 548               $t_next       = $sql_array[0]['type'];
 549               $t_prev       = '';
 550               $t_cur        = '';
 551               $d_next       = $sql_array[0]['data'];
 552               $d_prev       = '';
 553               $d_cur        = '';
 554               $d_next_upper = $t_next == 'alpha' ? strtoupper($d_next) : $d_next;
 555               $d_prev_upper = '';
 556               $d_cur_upper  = '';
 557             }
 558
 559             for ($i = 0; $i < $arraysize; $i++) {
 560               $t_prev       = $t_cur;
 561               $t_cur        = $t_next;
 562               $d_prev       = $d_cur;
 563               $d_cur        = $d_next;
 564               $d_prev_upper = $d_cur_upper;
 565               $d_cur_upper  = $d_next_upper;
 566               if (($i + 1) < $arraysize) {
 567                 $t_next = $sql_array[$i + 1]['type'];
 568                 $d_next = $sql_array[$i + 1]['data'];
 569                 $d_next_upper = $t_next == 'alpha' ? strtoupper($d_next) : $d_next;
 570               } else {
 571                 $t_next       = '';
 572                 $d_next       = '';
 573                 $d_next_upper = '';
 574               }
 575               if ($t_cur == 'alpha') {
 576                 $t_suffix     = '_identifier';
 577                 if (($t_next == 'punct_qualifier') || ($t_prev == 'punct_qualifier')) {
 578                   $t_suffix = '_identifier';
 579                 } else if (($t_next == 'punct_bracket_open_round')
 580                 && PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_function_name, $PMA_SQPdata_function_name_cnt)) {
 581                   $t_suffix = '_functionName';
 582                 } else if (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_type, $PMA_SQPdata_column_type_cnt))  {
 583                   $t_suffix = '_columnType';
 584                       // Temporary fix for BUG #621357
 585                   //TODO FIX PROPERLY NEEDS OVERHAUL OF SQL TOKENIZER
 586                   if ($d_cur_upper == 'SET' && $t_next != 'punct_bracket_open_round') {
 587                     $t_suffix = '_reservedWord';
 588                   }
 589                   //END OF TEMPORARY FIX
 590                   // CHARACTER is a synonym for CHAR, but can also be meant as
 591                   // CHARACTER SET. In this case, we have a reserved word.
 592                   if ($d_cur_upper == 'CHARACTER' && $d_next_upper == 'SET') {
 593                     $t_suffix = '_reservedWord';
 594                   }
 595                 } else if (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_reserved_word, $PMA_SQPdata_reserved_word_cnt)) {
 596                   $t_suffix = '_reservedWord';
 597                 } else if (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_attrib, $PMA_SQPdata_column_attrib_cnt)) {
 598                   $t_suffix = '_columnAttrib';
 599                   // INNODB is a MySQL table type, but in "SHOW INNODB STATUS",
 600                   // it should be regarded as a reserved word.
 601                   if ($d_cur_upper == 'INNODB' && $d_prev_upper == 'SHOW' && $d_next_upper == 'STATUS') {
 602                     $t_suffix = '_reservedWord';
 603                   }
 604                 } else {
 605                   // Do nothing
 606                 }
 607                 $sql_array[$i]['type'] .= $t_suffix;
 608               }
 609             } // end for
 610
 611             // Stores the size of the array inside the array, as count() is a slow
 612             // operation.
 613             $sql_array['len'] = $arraysize;
 614
 615             // Sends the data back
 616             return $sql_array;
 617         } // end of the "PMA_SQP_parse()" function
 618
 619        /**
 620         * Checks for token types being what we want...
 621         *
 622         * @param  string String of type that we have
 623         * @param  string String of type that we want
 624         *
 625         * @return boolean result of check
 626         *
 627         * @access private
 628         */
 629         function PMA_SQP_typeCheck($toCheck, $whatWeWant)
 630         {
 631             $typeSeperator = '_';
 632             if(strcmp($whatWeWant, $toCheck) == 0) {
 633                 return TRUE;
 634             } else {
 635                 //if(strpos($whatWeWant, $typeSeperator) === FALSE) {
 636                 // PHP3 compatible (works unless there is a real ff character)
 637                 if(!strpos("\xff" . $whatWeWant, $typeSeperator)) {
 638                     return strncmp($whatWeWant, $toCheck , strpos($toCheck, $typeSeperator)) == 0;
 639                 } else {
 640                     return FALSE;
 641                 }
 642             }
 643         }
 644
 645
 646         /**
 647          * Analyzes SQL queries
 648          *
 649          * @param  array   The SQL queries
 650          *
 651          * @return array   The analyzed SQL queries
 652          *
 653          * @access public
 654          */
 655         function PMA_SQP_analyze($arr)
 656         {
 657             $result          = array();
 658             $size            = $arr['len'];
 659             $subresult       = array(
 660                 'querytype'      => '',
 661                 'select_expr_clause'=> '', // the whole stuff between SELECT and FROM , except DISTINCT
 662                 'from_clause'=> '',
 663                 'group_by_clause'=> '',
 664                 'order_by_clause'=> '',
 665                 'having_clause'  => '',
 666                 'where_clause'   => '',
 667                 'where_clause_identifiers'   => array(),
 668                 'queryflags'     => array(),
 669                 'select_expr'    => array(),
 670                 'table_ref'      => array()
 671             );
 672             $subresult_empty = $subresult;
 673             $seek_queryend         = FALSE;
 674             $seen_end_of_table_ref = FALSE;
 675
 676             // for SELECT EXTRACT(YEAR_MONTH FROM CURDATE())
 677             // we must not use CURDATE as a table_ref
 678             // so we track wether we are in the EXTRACT()
 679             $in_extract          = FALSE;
 680
 681     /* Description of analyzer results
 682      *
 683      * lem9: db, table, column, alias
 684      *      ------------------------
 685      *
 686      * Inside the $subresult array, we create ['select_expr'] and ['table_ref'] arrays.
 687      *
 688      * The SELECT syntax (simplified) is
 689      *
 690      * SELECT
 691      *    select_expression,...
 692      *    [FROM [table_references]
 693      *
 694      *
 695      * ['select_expr'] is filled with each expression, the key represents the
 696      * expression position in the list (0-based) (so we don't lose track of
 697      * multiple occurences of the same column).
 698      *
 699      * ['table_ref'] is filled with each table ref, same thing for the key.
 700      *
 701      * I create all sub-values empty, even if they are
 702      * not present (for example no select_expression alias).
 703      *
 704      * There is a debug section at the end of the main loop, if you want to
 705      * see the exact contents of select_expr and table_ref
 706      *
 707      * lem9: queryflags
 708      *       ----------
 709      *
 710      * In $subresult, array 'queryflags' is filled, according to what we
 711      * find in the query.
 712      *
 713      * Currently, those are generated:
 714      *
 715      * ['queryflags']['need_confirm'] = 1; if the query needs confirmation
 716      * ['queryflags']['select_from'] = 1; if this is a real SELECT...FROM
 717      *
 718      * lem9:  query clauses
 719      *        -------------
 720      *
 721      * The select is splitted in those clauses:
 722      * ['select_expr_clause']
 723      * ['from_clause']
 724      * ['group_by_clause']
 725      * ['order_by_clause']
 726      * ['having_clause']
 727      * ['where_clause']
 728      *
 729      * and the identifiers of the where clause are put into the array
 730      * ['where_clause_identifier']
 731      */
 732
 733             // must be sorted
 734             // TODO: current logic checks for only one word, so I put only the
 735             // first word of the reserved expressions that end a table ref;
 736             // maybe this is not ok (the first word might mean something else)
 737     //        $words_ending_table_ref = array(
 738     //            'FOR UPDATE',
 739     //            'GROUP BY',
 740     //            'HAVING',
 741     //            'LIMIT',
 742     //            'LOCK IN SHARE MODE',
 743     //            'ORDER BY',
 744     //            'PROCEDURE',
 745     //            'UNION',
 746     //            'WHERE'
 747     //        );
 748             $words_ending_table_ref = array(
 749                 'FOR',
 750                 'GROUP',
 751                 'HAVING',
 752                 'LIMIT',
 753                 'LOCK',
 754                 'ORDER',
 755                 'PROCEDURE',
 756                 'UNION',
 757                 'WHERE'
 758             );
 759             $words_ending_table_ref_cnt = 9; //count($words_ending_table_ref);
 760
 761             $words_ending_clauses = array(
 762                 'FOR',
 763                 'LIMIT',
 764                 'LOCK',
 765                 'PROCEDURE',
 766                 'UNION'
 767             );
 768             $words_ending_clauses_cnt = 5; //count($words_ending_clauses);
 769
 770
 771
 772
 773             // must be sorted
 774             $supported_query_types = array(
 775                 'SELECT'
 776                 /*
 777                 // Support for these additional query types will come later on.
 778                 'DELETE',
 779                 'INSERT',
 780                 'REPLACE',
 781                 'TRUNCATE',
 782                 'UPDATE'
 783                 'EXPLAIN',
 784                 'DESCRIBE',
 785                 'SHOW',
 786                 'CREATE',
 787                 'SET',
 788                 'ALTER'
 789                 */
 790             );
 791             $supported_query_types_cnt = count($supported_query_types);
 792
 793             // loop #1 for each token: select_expr, table_ref for SELECT
 794
 795             for ($i = 0; $i < $size; $i++) {
 796     //echo "trace <b>"  . $arr[$i]['data'] . "</b> (" . $arr[$i]['type'] . ")<br>";
 797
 798                 // High speed seek for locating the end of the current query
 799                 if ($seek_queryend == TRUE) {
 800                     if ($arr[$i]['type'] == 'punct_queryend') {
 801                         $seek_queryend = FALSE;
 802                     } else {
 803                         continue;
 804                     } // end if (type == punct_queryend)
 805                 } // end if ($seek_queryend)
 806
 807                 // TODO: when we find a UNION, should we split
 808                 // in another subresult?
 809                 if ($arr[$i]['type'] == 'punct_queryend') {
 810                     $result[]  = $subresult;
 811                     $subresult = $subresult_empty;
 812                     continue;
 813                 } // end if (type == punct_queryend)
 814
 815     // ==============================================================
 816                 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
 817                     if ($in_extract) {
 818                         $number_of_brackets_in_extract++;
 819                     }
 820                 }
 821     // ==============================================================
 822                 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
 823                     if ($in_extract) {
 824                         $number_of_brackets_in_extract--;
 825                         if ($number_of_brackets_in_extract == 0) {
 826                            $in_extract = FALSE;
 827                         }
 828                     }
 829                 }
 830     // ==============================================================
 831                 if ($arr[$i]['type'] == 'alpha_functionName') {
 832                     $upper_data = strtoupper($arr[$i]['data']);
 833                     if ($upper_data =='EXTRACT') {
 834                         $in_extract = TRUE;
 835                         $number_of_brackets_in_extract = 0;
 836                     }
 837                 }
 838
 839     // ==============================================================
 840                 if ($arr[$i]['type'] == 'alpha_reservedWord') {
 841                     // We don't know what type of query yet, so run this
 842                     if ($subresult['querytype'] == '') {
 843                         $subresult['querytype'] = strtoupper($arr[$i]['data']);
 844                     } // end if (querytype was empty)
 845
 846                     // Check if we support this type of query
 847                     if (!PMA_STR_binarySearchInArr($subresult['querytype'], $supported_query_types, $supported_query_types_cnt)) {
 848                         // Skip ahead to the next one if we don't
 849                         $seek_queryend = TRUE;
 850                         continue;
 851                     } // end if (query not supported)
 852
 853                     // upper once
 854                     $upper_data = strtoupper($arr[$i]['data']);
 855                     //TODO: reset for each query?
 856
 857                     if ($upper_data == 'SELECT') {
 858                         $seen_from = FALSE;
 859                         $previous_was_identifier = FALSE;
 860                         $current_select_expr = -1;
 861                         $seen_end_of_table_ref = FALSE;
 862                     } // end if ( data == SELECT)
 863
 864                     if ($upper_data =='FROM' && !$in_extract) {
 865                         $current_table_ref = -1;
 866                         $seen_from = TRUE;
 867                         $previous_was_identifier = FALSE;
 868                         $save_table_ref = TRUE;
 869                     } // end if (data == FROM)
 870
 871                     // here, do not 'continue' the loop, as we have more work for
 872                     // reserved words below
 873                 } // end if (type == alpha_reservedWord)
 874
 875     // ==============================
 876                 if (($arr[$i]['type'] == 'quote_backtick')
 877                  || ($arr[$i]['type'] == 'quote_double')
 878                  || ($arr[$i]['type'] == 'quote_single')
 879                  || ($arr[$i]['type'] == 'alpha_identifier')) {
 880
 881                     switch ($arr[$i]['type']) {
 882                         case 'alpha_identifier':
 883                             $identifier = $arr[$i]['data'];
 884                             break;
 885
 886                     //TODO: check embedded double quotes or backticks?
 887                     // and/or remove just the first and last character?
 888                         case 'quote_backtick':
 889                             $identifier = str_replace('`','',$arr[$i]['data']);
 890                             break;
 891                         case 'quote_double':
 892                             $identifier = str_replace('"','',$arr[$i]['data']);
 893                             break;
 894                         case 'quote_single':
 895                             $identifier = str_replace("'","",$arr[$i]['data']);
 896                             break;
 897                     } // end switch
 898
 899                     if ($subresult['querytype'] == 'SELECT') {
 900                         if (!$seen_from) {
 901                             if ($previous_was_identifier && isset($chain)) {
 902                                 // found alias for this select_expr, save it
 903                                 // but only if we got something in $chain
 904                                 // (for example, SELECT COUNT(*) AS cnt
 905                                 // puts nothing in $chain, so we avoid
 906                                 // setting the alias)
 907                                 $alias_for_select_expr = $identifier;
 908                             } else {
 909                                 $chain[] = $identifier;
 910                                 $previous_was_identifier = TRUE;
 911
 912                             } // end if !$previous_was_identifier
 913                         } else {
 914                             // ($seen_from)
 915                             if ($save_table_ref && !$seen_end_of_table_ref) {
 916                                 if ($previous_was_identifier) {
 917                                     // found alias for table ref
 918                                     // save it for later
 919                                     $alias_for_table_ref = $identifier;
 920                                 } else {
 921                                     $chain[] = $identifier;
 922                                     $previous_was_identifier = TRUE;
 923
 924                                 } // end if ($previous_was_identifier)
 925                             } // end if ($save_table_ref &&!$seen_end_of_table_ref)
 926                         } // end if (!$seen_from)
 927                     } // end if (querytype SELECT)
 928                 } // end if ( quote_backtick or double quote or alpha_identifier)
 929
 930     // ===================================
 931                 if ($arr[$i]['type'] == 'punct_qualifier') {
 932                     // to be able to detect an identifier following another
 933                     $previous_was_identifier = FALSE;
 934                     continue;
 935                 } // end if (punct_qualifier)
 936
 937                 // TODO: check if 3 identifiers following one another -> error
 938
 939                 //    s a v e    a    s e l e c t    e x p r
 940                 // finding a list separator or FROM
 941                 // means that we must save the current chain of identifiers
 942                 // into a select expression
 943
 944                 // for now, we only save a select expression if it contains
 945                 // at least one identifier, as we are interested in checking
 946                 // the columns and table names, so in "select * from persons",
 947                 // the "*" is not saved
 948
 949                 if (isset($chain) && !$seen_end_of_table_ref
 950                    && (   (!$seen_from
 951                        && $arr[$i]['type'] == 'punct_listsep')
 952                       || ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data == 'FROM')) ) {
 953                     $size_chain = count($chain);
 954                     $current_select_expr++;
 955                     $subresult['select_expr'][$current_select_expr] = array(
 956                       'expr' => '',
 957                       'alias' => '',
 958                       'db'   => '',
 959                       'table_name' => '',
 960                       'table_true_name' => '',
 961                       'column' => ''
 962                      );
 963
 964                     if (!empty($alias_for_select_expr)) {
 965                         // we had found an alias for this select expression
 966                         $subresult['select_expr'][$current_select_expr]['alias'] = $alias_for_select_expr;
 967                         unset($alias_for_select_expr);
 968                     }
 969                     // there is at least a column
 970                     $subresult['select_expr'][$current_select_expr]['column'] = $chain[$size_chain - 1];
 971                     $subresult['select_expr'][$current_select_expr]['expr'] = $chain[$size_chain - 1];
 972
 973                     // maybe a table
 974                     if ($size_chain > 1) {
 975                         $subresult['select_expr'][$current_select_expr]['table_name'] = $chain[$size_chain - 2];
 976                         // we assume for now that this is also the true name
 977                         $subresult['select_expr'][$current_select_expr]['table_true_name'] = $chain[$size_chain - 2];
 978                         $subresult['select_expr'][$current_select_expr]['expr']
 979                          = $subresult['select_expr'][$current_select_expr]['table_name']
 980                           . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
 981                     } // end if ($size_chain > 1)
 982
 983                     // maybe a db
 984                     if ($size_chain > 2) {
 985                         $subresult['select_expr'][$current_select_expr]['db'] = $chain[$size_chain - 3];
 986                         $subresult['select_expr'][$current_select_expr]['expr']
 987                          = $subresult['select_expr'][$current_select_expr]['db']
 988                           . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
 989                     } // end if ($size_chain > 2)
 990                     unset($chain);
 991
 992                     // TODO: explain this:
 993                     if (($arr[$i]['type'] == 'alpha_reservedWord')
 994                      && ($upper_data != 'FROM')) {
 995                         $previous_was_identifier = TRUE;
 996                     }
 997
 998                 } // end if (save a select expr)
 999
1000
1001                 //======================================
1002                 //    s a v e    a    t a b l e    r e f
1003                 //======================================
1004
1005                 // maybe we just saw the end of table refs
1006                 // but the last table ref has to be saved
1007                 // or we are at the last token (TODO: there could be another
1008                 // query after this one)
1009                 // or we just got a reserved word
1010
1011                 if (isset($chain) && $seen_from && $save_table_ref
1012                  && ($arr[$i]['type'] == 'punct_listsep'
1013                    || ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data!="AS")
1014                    || $seen_end_of_table_ref
1015                    || $i==$size-1 )) {
1016
1017                     $size_chain = count($chain);
1018                     $current_table_ref++;
1019                     $subresult['table_ref'][$current_table_ref] = array(
1020                       'expr'            => '',
1021                       'db'              => '',
1022                       'table_name'      => '',
1023                       'table_alias'     => '',
1024                       'table_true_name' => ''
1025                      );
1026                     if (!empty($alias_for_table_ref)) {
1027                         $subresult['table_ref'][$current_table_ref]['table_alias'] = $alias_for_table_ref;
1028                         unset($alias_for_table_ref);
1029                     }
1030                     $subresult['table_ref'][$current_table_ref]['table_name'] = $chain[$size_chain - 1];
1031                     // we assume for now that this is also the true name
1032                     $subresult['table_ref'][$current_table_ref]['table_true_name'] = $chain[$size_chain - 1];
1033                     $subresult['table_ref'][$current_table_ref]['expr']
1034                          = $subresult['table_ref'][$current_table_ref]['table_name'];
1035                     // maybe a db
1036                     if ($size_chain > 1) {
1037                         $subresult['table_ref'][$current_table_ref]['db'] = $chain[$size_chain - 2];
1038                         $subresult['table_ref'][$current_table_ref]['expr']
1039                          = $subresult['table_ref'][$current_table_ref]['db']
1040                           . '.' . $subresult['table_ref'][$current_table_ref]['expr'];
1041                     } // end if ($size_chain > 1)
1042
1043                     // add the table alias into the whole expression
1044                     $subresult['table_ref'][$current_table_ref]['expr']
1045                      .= ' ' . $subresult['table_ref'][$current_table_ref]['table_alias'];
1046
1047                     unset($chain);
1048                     $previous_was_identifier = TRUE;
1049                     //continue;
1050
1051                 } // end if (save a table ref)
1052
1053
1054                 // when we have found all table refs,
1055                 // for each table_ref alias, put the true name of the table
1056                 // in the corresponding select expressions
1057
1058                 if (isset($current_table_ref) && ($seen_end_of_table_ref || $i == $size-1)) {
1059                     for ($tr=0; $tr <= $current_table_ref; $tr++) {
1060                         $alias = $subresult['table_ref'][$tr]['table_alias'];
1061                         $truename = $subresult['table_ref'][$tr]['table_true_name'];
1062                         for ($se=0; $se <= $current_select_expr; $se++) {
1063                             if (!empty($alias) && $subresult['select_expr'][$se]['table_true_name']
1064                                == $alias) {
1065                                 $subresult['select_expr'][$se]['table_true_name']
1066                                  = $truename;
1067                             } // end if (found the alias)
1068                         } // end for (select expressions)
1069
1070                     } // end for (table refs)
1071                 } // end if (set the true names)
1072
1073
1074                // e n d i n g    l o o p  #1
1075                // set the $previous_was_identifier to FALSE if the current
1076                // token is not an identifier
1077                if (($arr[$i]['type'] != 'alpha_identifier')
1078                 && ($arr[$i]['type'] != 'quote_double')
1079                 && ($arr[$i]['type'] != 'quote_single')
1080                 && ($arr[$i]['type'] != 'quote_backtick')) {
1081                    $previous_was_identifier = FALSE;
1082                } // end if
1083
1084                // however, if we are on AS, we must keep the $previous_was_identifier
1085                if (($arr[$i]['type'] == 'alpha_reservedWord')
1086                 && ($upper_data == 'AS'))  {
1087                    $previous_was_identifier = TRUE;
1088                }
1089
1090                if (($arr[$i]['type'] == 'alpha_reservedWord')
1091                 && ($upper_data =='ON' || $upper_data =='USING')) {
1092                    $save_table_ref = FALSE;
1093                } // end if (data == ON)
1094
1095                if (($arr[$i]['type'] == 'alpha_reservedWord')
1096                 && ($upper_data =='JOIN' || $upper_data =='FROM')) {
1097                    $save_table_ref = TRUE;
1098                } // end if (data == JOIN)
1099
1100                // no need to check the end of table ref if we already did
1101                // TODO: maybe add "&& $seen_from"
1102                if (!$seen_end_of_table_ref) {
1103                    // if this is the last token, it implies that we have
1104                    // seen the end of table references
1105                    // Check for the end of table references
1106                    if (($i == $size-1)
1107                    || ($arr[$i]['type'] == 'alpha_reservedWord'
1108                       && PMA_STR_binarySearchInArr($upper_data, $words_ending_table_ref, $words_ending_table_ref_cnt))) {
1109                        $seen_end_of_table_ref = TRUE;
1110
1111                        // to be able to save the last table ref, but do not
1112                        // set it true if we found a word like "ON" that has
1113                        // already set it to false
1114                        if (isset($save_table_ref) && $save_table_ref != FALSE) {
1115                           $save_table_ref = TRUE;
1116                        } //end if
1117
1118                    } // end if (check for end of table ref)
1119                } //end if (!$seen_end_of_table_ref)
1120
1121                if ($seen_end_of_table_ref) {
1122                    $save_table_ref = FALSE;
1123                } // end if
1124
1125             } // end for $i (loop #1)
1126
1127             // -------------------------------------------------------
1128             // This is a big hunk of debugging code by Marc for this.
1129             // -------------------------------------------------------
1130             /*
1131               if (isset($current_select_expr)) {
1132                for ($trace=0; $trace<=$current_select_expr; $trace++) {
1133
1134                echo "<br>";
1135                reset ($subresult['select_expr'][$trace]);
1136                while (list ($key, $val) = each ($subresult['select_expr'][$trace]))
1137                echo "sel expr $trace $key => $val<br />\n";
1138                }
1139               }
1140
1141               if (isset($current_table_ref)) {
1142                for ($trace=0; $trace<=$current_table_ref; $trace++) {
1143
1144                echo "<br>";
1145                reset ($subresult['table_ref'][$trace]);
1146                while (list ($key, $val) = each ($subresult['table_ref'][$trace]))
1147                echo "table ref $trace $key => $val<br />\n";
1148                }
1149               }
1150             */
1151             // -------------------------------------------------------
1152
1153
1154             // loop #2: for queryflags
1155             //          ,querytype (for queries != 'SELECT')
1156             //
1157             // This is not in the loop 1 to keep logic simple
1158
1159             // we will also need this queryflag in loop 2
1160             // so set it here
1161             if (isset($current_table_ref) && $current_table_ref > -1) {
1162                 $subresult['queryflags']['select_from'] = 1;
1163             }
1164
1165             $seen_reserved_word = FALSE;
1166             $seen_group = FALSE;
1167             $seen_order = FALSE;
1168             $in_group_by = FALSE; // true when we are into the GROUP BY clause
1169             $in_order_by = FALSE; // true when we are into the ORDER BY clause
1170             $in_having = FALSE; // true when we are into the HAVING clause
1171             $in_select_expr = FALSE; // true when we are into the select expr clause
1172             $in_where = FALSE; // true when we are into the WHERE clause
1173             $in_from = FALSE;
1174
1175             for ($i = 0; $i < $size; $i++) {
1176     //echo "trace loop2 <b>"  . $arr[$i]['data'] . "</b> (" . $arr[$i]['type'] . ")<br>";
1177
1178                // need_confirm
1179                //
1180                // check for reserved words that will have to generate
1181                // a confirmation request later in sql.php3
1182                // the cases are:
1183                //   DROP TABLE
1184                //   DROP DATABASE
1185                //   ALTER TABLE... DROP
1186                //   DELETE FROM...
1187                //
1188                // this code is not used for confirmations coming from functions.js
1189
1190                // TODO: check for punct_queryend
1191
1192                if ($arr[$i]['type'] == 'alpha_reservedWord') {
1193                    $upper_data = strtoupper($arr[$i]['data']);
1194                    if (!$seen_reserved_word) {
1195                        $first_reserved_word = $upper_data;
1196                        $subresult['querytype'] = $upper_data;
1197                        $seen_reserved_word = TRUE;
1198
1199                        // if the first reserved word is DROP or DELETE,
1200                        // we know this is a query that needs to be confirmed
1201                        if ($first_reserved_word=='DROP'
1202                            || $first_reserved_word == 'DELETE') {
1203                           $subresult['queryflags']['need_confirm'] = 1;
1204                        }
1205                    } else {
1206                        if ($upper_data=='DROP' && $first_reserved_word=='ALTER') {
1207                           $subresult['queryflags']['need_confirm'] = 1;
1208                        }
1209                    }
1210
1211                    if ($upper_data == 'SELECT') {
1212                        $in_select_expr = TRUE;
1213                        $select_expr_clause = '';
1214                    }
1215
1216                    // if this is a real SELECT...FROM
1217                    if ($upper_data == 'FROM' && isset($subresult['queryflags']['select_from']) && $subresult['queryflags']['select_from'] == 1) {
1218                        $in_from = TRUE;
1219                        $from_clause = '';
1220                        $in_select_expr = FALSE;
1221                    }
1222
1223
1224                    // (we could have less resetting of variables to FALSE
1225                    // if we trust that the query respects the standard
1226                    // MySQL order for clauses)
1227
1228                    // we use $seen_group and $seen_order because we are looking
1229                    // for the BY
1230                    if ($upper_data == 'GROUP') {
1231                        $seen_group = TRUE;
1232                        $seen_order = FALSE;
1233                        $in_having = FALSE;
1234                        $in_order_by = FALSE;
1235                        $in_where = FALSE;
1236                        $in_select_expr = FALSE;
1237                        $in_from = FALSE;
1238                    }
1239                    if ($upper_data == 'ORDER') {
1240                        $seen_order = TRUE;
1241                        $seen_group = FALSE;
1242                        $in_having = FALSE;
1243                        $in_group_by = FALSE;
1244                        $in_where = FALSE;
1245                        $in_select_expr = FALSE;
1246                        $in_from = FALSE;
1247                    }
1248                    if ($upper_data == 'HAVING') {
1249                        $in_having = TRUE;
1250                        $having_clause = '';
1251                        $seen_group = FALSE;
1252                        $seen_order = FALSE;
1253                        $in_group_by = FALSE;
1254                        $in_order_by = FALSE;
1255                        $in_where = FALSE;
1256                        $in_select_expr = FALSE;
1257                        $in_from = FALSE;
1258                    }
1259
1260                    if ($upper_data == 'WHERE') {
1261                        $in_where = TRUE;
1262                        $where_clause = '';
1263                        $where_clause_identifiers = array();
1264                        $seen_group = FALSE;
1265                        $seen_order = FALSE;
1266                        $in_group_by = FALSE;
1267                        $in_order_by = FALSE;
1268                        $in_having = FALSE;
1269                        $in_select_expr = FALSE;
1270                        $in_from = FALSE;
1271                    }
1272
1273                    if ($upper_data == 'BY') {
1274                        if ($seen_group) {
1275                            $in_group_by = TRUE;
1276                            $group_by_clause = '';
1277                        }
1278                        if ($seen_order) {
1279                            $in_order_by = TRUE;
1280                            $order_by_clause = '';
1281                        }
1282                    }
1283
1284                    // if we find one of the words that could end the clause
1285                    if (PMA_STR_binarySearchInArr($upper_data, $words_ending_clauses, $words_ending_clauses_cnt)) {
1286
1287                        $in_group_by = FALSE;
1288                        $in_order_by = FALSE;
1289                        $in_having   = FALSE;
1290                        $in_where    = FALSE;
1291                        $in_select_expr = FALSE;
1292                        $in_from = FALSE;
1293                    }
1294
1295                } // endif (reservedWord)
1296
1297
1298                // do not add a blank after a function name
1299
1300                $sep=' ';
1301                if ($arr[$i]['type'] == 'alpha_functionName') {
1302                    $sep='';
1303                }
1304
1305                if ($in_select_expr && $upper_data != 'SELECT' && $upper_data != 'DISTINCT') {
1306                    $select_expr_clause .= $arr[$i]['data'] . $sep;
1307                }
1308                if ($in_from && $upper_data != 'FROM') {
1309                    $from_clause .= $arr[$i]['data'] . $sep;
1310                }
1311                if ($in_group_by && $upper_data != 'GROUP' && $upper_data != 'BY') {
1312                    $group_by_clause .= $arr[$i]['data'] . $sep;
1313                }
1314                if ($in_order_by && $upper_data != 'ORDER' && $upper_data != 'BY') {
1315                    $order_by_clause .= $arr[$i]['data'] . $sep;
1316                }
1317                if ($in_having && $upper_data != 'HAVING') {
1318                    $having_clause .= $arr[$i]['data'] . $sep;
1319                }
1320                if ($in_where && $upper_data != 'WHERE') {
1321                    $where_clause .= $arr[$i]['data'] . $sep;
1322
1323                    if (($arr[$i]['type'] == 'quote_backtick')
1324                     || ($arr[$i]['type'] == 'alpha_identifier')) {
1325                        $where_clause_identifiers[] = $arr[$i]['data'];
1326                    }
1327                }
1328
1329                // clear $upper_data for next iteration
1330                $upper_data='';
1331
1332             } // end for $i (loop #2)
1333
1334             if (isset($select_expr_clause)) {
1335                 $subresult['select_expr_clause'] = $select_expr_clause;
1336             }
1337             if (isset($from_clause)) {
1338                 $subresult['from_clause'] = $from_clause;
1339             }
1340             if (isset($group_by_clause)) {
1341                 $subresult['group_by_clause'] = $group_by_clause;
1342             }
1343             if (isset($order_by_clause)) {
1344                 $subresult['order_by_clause'] = $order_by_clause;
1345             }
1346             if (isset($having_clause)) {
1347                 $subresult['having_clause'] = $having_clause;
1348             }
1349             if (isset($where_clause)) {
1350                 $subresult['where_clause'] = $where_clause;
1351             }
1352             if (isset($where_clause_identifiers)) {
1353                 $subresult['where_clause_identifiers'] = $where_clause_identifiers;
1354             }
1355
1356
1357             // They are naughty and didn't have a trailing semi-colon,
1358             // then still handle it properly
1359             if ($subresult['querytype'] != '') {
1360                 $result[] = $subresult;
1361             }
1362             return $result;
1363         } // end of the "PMA_SQP_analyze()" function
1364
1365
1366         /**
1367          * Colorizes SQL queries html formatted
1368          *
1369          * @param  array   The SQL queries html formatted
1370          *
1371          * @return array   The colorized SQL queries
1372          *
1373          * @access public
1374          */
1375         function PMA_SQP_formatHtml_colorize($arr)
1376         {
1377             $i         = $GLOBALS['PMA_strpos']($arr['type'], '_');
1378             $class     = '';
1379             if ($i > 0) {
1380                 $class = 'syntax_' . $GLOBALS['PMA_substr']($arr['type'], 0, $i) . ' ';
1381             }
1382
1383             $class     .= 'syntax_' . $arr['type'];
1384
1385             //TODO: check why adding a "\n" after the </span> would cause extra
1386             //      blanks to be displayed:
1387             //      SELECT p . person_name
1388
1389             return '<span class="' . $class . '">' . htmlspecialchars($arr['data']) . '</span>';
1390         } // end of the "PMA_SQP_formatHtml_colorize()" function
1391
1392
1393         /**
1394          * Formats SQL queries to html
1395          *
1396          * @param  array   The SQL queries
1397          *
1398          * @return string  The formatted SQL queries
1399          *
1400          * @access public
1401          */
1402         function PMA_SQP_formatHtml($arr, $mode='color')
1403         {
1404             // first check for the SQL parser having hit an error
1405             if (PMA_SQP_isError()) {
1406                 return $arr;
1407             }
1408             // then check for an array
1409             if (!is_array($arr)) {
1410                 return $arr;
1411             }
1412             // else do it properly
1413             switch ($mode) {
1414                 case 'color':
1415                     $str                                = '<span class="syntax">';
1416                     $html_line_break                    = '<br />';
1417                     break;
1418                 case 'query_only':
1419                     $str                                = '';
1420                     $html_line_break                    = "\n";
1421                     break;
1422                 case 'text':
1423                     $str                                = '';
1424                     $html_line_break                    = '<br />';
1425                     break;
1426             } // end switch
1427             $indent                                     = 0;
1428             $bracketlevel                               = 0;
1429             $functionlevel                              = 0;
1430             $infunction                                 = FALSE;
1431             $space_punct_listsep                        = ' ';
1432             $space_punct_listsep_function_name          = ' ';
1433             // $space_alpha_reserved_word = '<br />'."\n";
1434             $space_alpha_reserved_word                  = ' ';
1435
1436             $keywords_with_brackets_1before            = array(
1437                 'INDEX',
1438                 'KEY',
1439                 'ON',
1440                 'USING'
1441             );
1442             $keywords_with_brackets_1before_cnt        = 4;
1443
1444             $keywords_with_brackets_2before            = array(
1445                 'IGNORE',
1446                 'INDEX',
1447                 'INTO',
1448                 'KEY',
1449                 'PRIMARY',
1450                 'PROCEDURE',
1451                 'REFERENCES',
1452                 'UNIQUE',
1453                 'USE'
1454             );
1455             // $keywords_with_brackets_2before_cnt = count($keywords_with_brackets_2before);
1456             $keywords_with_brackets_2before_cnt        = 9;
1457
1458             // These reserved words do NOT get a newline placed near them.
1459             $keywords_no_newline               = array(
1460                 'AND',
1461                 'AS',
1462                 'ASC',
1463                 'DESC',
1464                 'IS',
1465                 'NOT',
1466                 'NULL',
1467                 'ON',
1468                 'OR'
1469             );
1470             $keywords_no_newline_cnt           = 9;
1471
1472             $arraysize = $arr['len'];
1473             $typearr   = array();
1474             if ($arraysize >= 0) {
1475                 $typearr[0] = '';
1476                 $typearr[1] = '';
1477                 $typearr[2] = '';
1478                 $typearr[3] = $arr[0]['type'];
1479             }
1480
1481             for ($i = 0; $i < $arraysize; $i++) {
1482                 // DEBUG echo "<b>" . $arr[$i]['data'] . "</b> " . $arr[$i]['type'] . "<br />";
1483                 $before = '';
1484                 $after  = '';
1485                 $indent = 0;
1486                 // array_shift($typearr);
1487                 /*
1488                 0 prev2
1489                 1 prev
1490                 2 current
1491                 3 next
1492                 */
1493                 if (($i + 1) < $arraysize) {
1494                     // array_push($typearr, $arr[$i + 1]['type']);
1495                     $typearr[4] = $arr[$i + 1]['type'];
1496                 } else {
1497                     //array_push($typearr, NULL);
1498                     $typearr[4] = '';
1499                 }
1500
1501                 for ($j=0; $j<4; $j++) {
1502                     $typearr[$j] = $typearr[$j + 1];
1503                 }
1504
1505                 switch ($typearr[2]) {
1506                     case 'white_newline':
1507     //                    $after      = '<br />';
1508                         $before     = '';
1509                         break;
1510                     case 'punct_bracket_open_round':
1511                         $bracketlevel++;
1512                         $infunction = FALSE;
1513                         // Make sure this array is sorted!
1514                         if (($typearr[1] == 'alpha_functionName') || ($typearr[1] == 'alpha_columnType') || ($typearr[1] == 'punct')
1515                             || ($typearr[3] == 'digit_integer') || ($typearr[3] == 'digit_hex') || ($typearr[3] == 'digit_float')
1516                             || (($typearr[0] == 'alpha_reservedWord')
1517                                 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 2]['data']), $keywords_with_brackets_2before, $keywords_with_brackets_2before_cnt))
1518                             || (($typearr[1] == 'alpha_reservedWord')
1519                                 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_with_brackets_1before, $keywords_with_brackets_1before_cnt))
1520                             ) {
1521                             $functionlevel++;
1522                             $infunction = TRUE;
1523                             $after      .= ' ';
1524                         } else {
1525                             $indent++;
1526                             $after      .= ($mode != 'query_only' ? '<div class="syntax_indent' . $indent . '">' : ' ');
1527                         }
1528                         break;
1529                     case 'alpha_identifier':
1530                         if (($typearr[1] == 'punct_qualifier') || ($typearr[3] == 'punct_qualifier')) {
1531                             $after      = '';
1532                             $before     = '';
1533                         }
1534                         if (($typearr[3] == 'alpha_columnType') || ($typearr[3] == 'alpha_identifier')) {
1535                             $after      .= ' ';
1536                         }
1537                         break;
1538                     case 'punct_qualifier':
1539                         $before         = '';
1540                         $after          = '';
1541                         break;
1542                     case 'punct_listsep':
1543                         if ($infunction == TRUE) {
1544                             $after      .= $space_punct_listsep_function_name;
1545                         } else {
1546                             $after      .= $space_punct_listsep;
1547                         }
1548                         break;
1549                     case 'punct_queryend':
1550                         if (($typearr[3] != 'comment_mysql') && ($typearr[3] != 'comment_ansi')) {
1551                             $after     .= $html_line_break;
1552                             $after     .= $html_line_break;
1553                         }
1554                         $space_punct_listsep               = ' ';
1555                         $space_punct_listsep_function_name = ' ';
1556                         $space_alpha_reserved_word         = ' ';
1557                         break;
1558                     case 'comment_mysql':
1559                     case 'comment_ansi':
1560                         $after         .= $html_line_break;
1561                         break;
1562                     case 'punct':
1563                         $after         .= ' ';
1564                         $before        .= ' ';
1565                         break;
1566                     case 'punct_bracket_close_round':
1567                         $bracketlevel--;
1568                         if ($infunction == TRUE) {
1569                             $functionlevel--;
1570                             $after     .= ' ';
1571                             $before    .= ' ';
1572                         } else {
1573                             $indent--;
1574                             $before    .= ($mode != 'query_only' ? '</div>' : ' ');
1575                         }
1576                         $infunction    = ($functionlevel > 0) ? TRUE : FALSE;
1577                         break;
1578                     case 'alpha_columnType':
1579                         if ($typearr[3] == 'alpha_columnAttrib') {
1580                             $after     .= ' ';
1581                         }
1582                         if ($typearr[1] == 'alpha_columnType') {
1583                             $before    .= ' ';
1584                         }
1585                         break;
1586                     case 'alpha_columnAttrib':
1587
1588                         // ALTER TABLE tbl_name AUTO_INCREMENT = 1
1589                         if ($typearr[1] == 'alpha_identifier') {
1590                             $before .= ' ';
1591                         }
1592                         if (($typearr[3] == 'alpha_columnAttrib') || ($typearr[3] == 'quote_single')) {
1593                             $after     .= ' ';
1594                         }
1595                         break;
1596                     case 'alpha_reservedWord':
1597                         //$upper         = $arr[$i]['data'];
1598                         $arr[$i]['data'] = strtoupper($arr[$i]['data']);
1599                         if ((($typearr[1] != 'alpha_reservedWord')
1600                             || (($typearr[1] == 'alpha_reservedWord')
1601                                 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_no_newline, $keywords_no_newline_cnt)))
1602                             && ($typearr[1] != 'punct_level_plus')
1603                             && (!PMA_STR_binarySearchInArr($arr[$i]['data'], $keywords_no_newline, $keywords_no_newline_cnt))) {
1604                             // do not put a space before the first token, because
1605                             // we use a lot of eregi() checking for the first
1606                             // reserved word at beginning of query
1607                             if ($i > 0) {
1608                                 $before    .= $space_alpha_reserved_word;
1609                             }
1610                         } else {
1611                             $before    .= ' ';
1612                         }
1613
1614                         switch ($arr[$i]['data']) {
1615                             case 'CREATE':
1616                                 $space_punct_listsep       = $html_line_break;
1617                                 $space_alpha_reserved_word = ' ';
1618                                 break;
1619                             case 'EXPLAIN':
1620                             case 'DESCRIBE':
1621                             case 'SET':
1622                             case 'ALTER':
1623                             case 'DELETE':
1624                             case 'SHOW':
1625                             case 'DROP':
1626                             case 'UPDATE':
1627                             case 'TRUNCATE':
1628                             case 'ANALYZE':
1629                             case 'ANALYSE':
1630                                 $space_punct_listsep       = $html_line_break;
1631                                 $space_alpha_reserved_word = ' ';
1632                                 break;
1633                             case 'INSERT':
1634                             case 'REPLACE':
1635                                 $space_punct_listsep       = $html_line_break;
1636                                 $space_alpha_reserved_word = $html_line_break;
1637                                 break;
1638                             case 'VALUES':
1639                                 $space_punct_listsep       = ' ';
1640                                 $space_alpha_reserved_word = $html_line_break;
1641                                 break;
1642                             case 'SELECT':
1643                                 $space_punct_listsep       = ' ';
1644                                 $space_alpha_reserved_word = $html_line_break;
1645                                 break;
1646                             default:
1647                                 break;
1648                         } // end switch ($arr[$i]['data'])
1649
1650                         $after         .= ' ';
1651                         break;
1652                     case 'digit_integer':
1653                     case 'digit_float':
1654                     case 'digit_hex':
1655                         //TODO: could there be other types preceding a digit?
1656                         if ($typearr[1] == 'alpha_reservedWord') {
1657                             $after .= ' ';
1658                         }
1659                         if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
1660                             $after     .= ' ';
1661                         }
1662                         break;
1663                     case 'quote_double':
1664                     case 'quote_single':
1665                         // workaround: for the query
1666                         // REVOKE SELECT ON `base2\_db`.* FROM 'user'@'%'
1667                         // the @ is incorrectly marked as alpha_variable
1668                         // in the parser, and here, the '%' gets a blank before,
1669                         // which is a syntax error
1670                         if ($typearr[1]!='alpha_variable') {
1671                             $before        .= ' ';
1672                         }
1673                         if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
1674                             $after     .= ' ';
1675                         }
1676                         break;
1677                     case 'quote_backtick':
1678                         if ($typearr[3] != 'punct_qualifier') {
1679                             $after     .= ' ';
1680                         }
1681                         if ($typearr[1] != 'punct_qualifier') {
1682                             $before    .= ' ';
1683                         }
1684                         break;
1685                     default:
1686                         break;
1687                 } // end switch ($typearr[2])
1688
1689     /*
1690                 if ($typearr[3] != 'punct_qualifier') {
1691                     $after             .= ' ';
1692                 }
1693                 $after                 .= "\n";
1694     */
1695                 $str .= $before . ($mode=='color' ? PMA_SQP_formatHTML_colorize($arr[$i]) : $arr[$i]['data']). $after;
1696             } // end for
1697             if ($mode=='color') {
1698                 $str .= '</span>';
1699             }
1700
1701             return $str;
1702         } // end of the "PMA_SQP_formatHtml()" function
1703     }
1704
1705     /**
1706      * Builds a CSS rule used for html formatted SQL queries
1707      *
1708      * @param  string  The class name
1709      * @param  string  The property name
1710      * @param  string  The property value
1711      *
1712      * @return string  The CSS rule
1713      *
1714      * @access public
1715      *
1716      * @see    PMA_SQP_buildCssData()
1717      */
1718     function PMA_SQP_buildCssRule($classname, $property, $value)
1719     {
1720         $str     = '.' . $classname . ' {';
1721         if ($value != '') {
1722             $str .= $property . ': ' . $value . ';';
1723         }
1724         $str     .= '}' . "\n";
1725
1726         return $str;
1727     } // end of the "PMA_SQP_buildCssRule()" function
1728
1729
1730     /**
1731      * Builds CSS rules used for html formatted SQL queries
1732      *
1733      * @return string  The CSS rules set
1734      *
1735      * @access public
1736      *
1737      * @global array   The current PMA configuration
1738      *
1739      * @see    PMA_SQP_buildCssRule()
1740      */
1741     function PMA_SQP_buildCssData()
1742     {
1743         global $cfg;
1744
1745         $css_string     = '';
1746         while (list($key, $col) = each($cfg['SQP']['fmtColor'])) {
1747             $css_string .= PMA_SQP_buildCssRule('syntax_' . $key, 'color', $col);
1748         }
1749         for ($i = 0; $i < 8; $i++) {
1750             $css_string .= PMA_SQP_buildCssRule('syntax_indent' . $i, 'margin-left', ($i * $cfg['SQP']['fmtInd']) . $cfg['SQP']['fmtIndUnit']);
1751         }
1752
1753         return $css_string;
1754     } // end of the "PMA_SQP_buildCssData()" function
1755
1756     if ($is_minimum_common == FALSE) {
1757         /**
1758          * Gets SQL queries with no format
1759          *
1760          * @param  array   The SQL queries list
1761          *
1762          * @return string  The SQL queries with no format
1763          *
1764          * @access public
1765          */
1766         function PMA_SQP_formatNone($arr)
1767         {
1768             $formatted_sql = htmlspecialchars($arr['raw']);
1769             $formatted_sql = ereg_replace("((\015\012)|(\015)|(\012)){3,}", "\n\n", $formatted_sql);
1770
1771             return $formatted_sql;
1772         } // end of the "PMA_SQP_formatNone()" function
1773
1774
1775         /**
1776          * Gets SQL queries in text format
1777          *
1778          * @param  array   The SQL queries list
1779          *
1780          * @return string  The SQL queries in text format
1781          *
1782          * @access public
1783          */
1784         function PMA_SQP_formatText($arr)
1785         {
1786             /**
1787              * TODO WRITE THIS!
1788              */
1789              return PMA_SQP_formatNone($arr);
1790         } // end of the "PMA_SQP_formatText()" function
1791     } // end if: minimal common.lib needed?
1792 } // $__PMA_SQP_LIB__
1793 ?>