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             }
 552
 553             for ($i = 0; $i < $arraysize; $i++) {
 554               $t_prev     = $t_cur;
 555               $t_cur      = $t_next;
 556               if (($i + 1) < $arraysize) {
 557                 $t_next = $sql_array[$i + 1]['type'];
 558               } else {
 559                 $t_next = '';
 560               }
 561               if ($t_cur == 'alpha') {
 562                 $t_suffix     = '_identifier';
 563                 $d_cur_upper  = strtoupper($sql_array[$i]['data']);
 564                 if (($t_next == 'punct_qualifier') || ($t_prev == 'punct_qualifier')) {
 565                   $t_suffix = '_identifier';
 566                 } else if (($t_next == 'punct_bracket_open_round')
 567                 && PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_function_name, $PMA_SQPdata_function_name_cnt)) {
 568                   $t_suffix = '_functionName';
 569                 } else if (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_type, $PMA_SQPdata_column_type_cnt))  {
 570                   $t_suffix = '_columnType';
 571                       // Temporary fix for BUG #621357
 572                   //TODO FIX PROPERLY NEEDS OVERHAUL OF SQL TOKENIZER
 573                   if($d_cur_upper == 'SET' && $t_next != 'punct_bracket_open_round') {
 574                     $t_suffix = '_reservedWord';
 575                   }
 576                   //END OF TEMPORARY FIX
 577                 } else if (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_reserved_word, $PMA_SQPdata_reserved_word_cnt)) {
 578                   $t_suffix = '_reservedWord';
 579                 } else if (PMA_STR_binarySearchInArr($d_cur_upper, $PMA_SQPdata_column_attrib, $PMA_SQPdata_column_attrib_cnt)) {
 580                   $t_suffix = '_columnAttrib';
 581                 } else {
 582                   // Do nothing
 583                 }
 584                 $sql_array[$i]['type'] .= $t_suffix;
 585               }
 586             } // end for
 587
 588             // Stores the size of the array inside the array, as count() is a slow
 589             // operation.
 590             $sql_array['len'] = $arraysize;
 591
 592             // Sends the data back
 593             return $sql_array;
 594         } // end of the "PMA_SQP_parse()" function
 595
 596        /**
 597         * Checks for token types being what we want...
 598         *
 599         * @param  string String of type that we have
 600         * @param  string String of type that we want
 601         *
 602         * @return boolean result of check
 603         *
 604         * @access private
 605         */
 606         function PMA_SQP_typeCheck($toCheck, $whatWeWant)
 607         {
 608             $typeSeperator = '_';
 609             if(strcmp($whatWeWant, $toCheck) == 0) {
 610                 return TRUE;
 611             } else {
 612                 //if(strpos($whatWeWant, $typeSeperator) === FALSE) {
 613                 // PHP3 compatible (works unless there is a real ff character)
 614                 if(!strpos("\xff" . $whatWeWant, $typeSeperator)) {
 615                     return strncmp($whatWeWant, $toCheck , strpos($toCheck, $typeSeperator)) == 0;
 616                 } else {
 617                     return FALSE;
 618                 }
 619             }
 620         }
 621
 622
 623         /**
 624          * Analyzes SQL queries
 625          *
 626          * @param  array   The SQL queries
 627          *
 628          * @return array   The analyzed SQL queries
 629          *
 630          * @access public
 631          */
 632         function PMA_SQP_analyze($arr)
 633         {
 634             $result          = array();
 635             $size            = $arr['len'];
 636             $subresult       = array(
 637                 'querytype'      => '',
 638                 'select_expr_clause'=> '', // the whole stuff between SELECT and FROM , except DISTINCT
 639                 'from_clause'=> '',
 640                 'group_by_clause'=> '',
 641                 'order_by_clause'=> '',
 642                 'having_clause'  => '',
 643                 'where_clause'   => '',
 644                 'where_clause_identifiers'   => array(),
 645                 'queryflags'     => array(),
 646                 'select_expr'    => array(),
 647                 'table_ref'      => array()
 648             );
 649             $subresult_empty = $subresult;
 650             $seek_queryend         = FALSE;
 651             $seen_end_of_table_ref = FALSE;
 652
 653             // for SELECT EXTRACT(YEAR_MONTH FROM CURDATE())
 654             // we must not use CURDATE as a table_ref
 655             // so we track wether we are in the EXTRACT()
 656             $in_extract          = FALSE;
 657
 658     /* Description of analyzer results
 659      *
 660      * lem9: db, table, column, alias
 661      *      ------------------------
 662      *
 663      * Inside the $subresult array, we create ['select_expr'] and ['table_ref'] arrays.
 664      *
 665      * The SELECT syntax (simplified) is
 666      *
 667      * SELECT
 668      *    select_expression,...
 669      *    [FROM [table_references]
 670      *
 671      *
 672      * ['select_expr'] is filled with each expression, the key represents the
 673      * expression position in the list (0-based) (so we don't lose track of
 674      * multiple occurences of the same column).
 675      *
 676      * ['table_ref'] is filled with each table ref, same thing for the key.
 677      *
 678      * I create all sub-values empty, even if they are
 679      * not present (for example no select_expression alias).
 680      *
 681      * There is a debug section at the end of the main loop, if you want to
 682      * see the exact contents of select_expr and table_ref
 683      *
 684      * lem9: queryflags
 685      *       ----------
 686      *
 687      * In $subresult, array 'queryflags' is filled, according to what we
 688      * find in the query.
 689      *
 690      * Currently, those are generated:
 691      *
 692      * ['queryflags']['need_confirm'] = 1; if the query needs confirmation
 693      * ['queryflags']['select_from'] = 1; if this is a real SELECT...FROM
 694      *
 695      * lem9:  query clauses
 696      *        -------------
 697      *
 698      * The select is splitted in those clauses:
 699      * ['select_expr_clause']
 700      * ['from_clause']
 701      * ['group_by_clause']
 702      * ['order_by_clause']
 703      * ['having_clause']
 704      * ['where_clause']
 705      *
 706      * and the identifiers of the where clause are put into the array
 707      * ['where_clause_identifier']
 708      */
 709
 710             // must be sorted
 711             // TODO: current logic checks for only one word, so I put only the
 712             // first word of the reserved expressions that end a table ref;
 713             // maybe this is not ok (the first word might mean something else)
 714     //        $words_ending_table_ref = array(
 715     //            'FOR UPDATE',
 716     //            'GROUP BY',
 717     //            'HAVING',
 718     //            'LIMIT',
 719     //            'LOCK IN SHARE MODE',
 720     //            'ORDER BY',
 721     //            'PROCEDURE',
 722     //            'UNION',
 723     //            'WHERE'
 724     //        );
 725             $words_ending_table_ref = array(
 726                 'FOR',
 727                 'GROUP',
 728                 'HAVING',
 729                 'LIMIT',
 730                 'LOCK',
 731                 'ORDER',
 732                 'PROCEDURE',
 733                 'UNION',
 734                 'WHERE'
 735             );
 736             $words_ending_table_ref_cnt = 9; //count($words_ending_table_ref);
 737
 738             $words_ending_clauses = array(
 739                 'FOR',
 740                 'LIMIT',
 741                 'LOCK',
 742                 'PROCEDURE',
 743                 'UNION'
 744             );
 745             $words_ending_clauses_cnt = 5; //count($words_ending_clauses);
 746
 747
 748
 749
 750             // must be sorted
 751             $supported_query_types = array(
 752                 'SELECT'
 753                 /*
 754                 // Support for these additional query types will come later on.
 755                 'DELETE',
 756                 'INSERT',
 757                 'REPLACE',
 758                 'TRUNCATE',
 759                 'UPDATE'
 760                 'EXPLAIN',
 761                 'DESCRIBE',
 762                 'SHOW',
 763                 'CREATE',
 764                 'SET',
 765                 'ALTER'
 766                 */
 767             );
 768             $supported_query_types_cnt = count($supported_query_types);
 769
 770             // loop #1 for each token: select_expr, table_ref for SELECT
 771
 772             for ($i = 0; $i < $size; $i++) {
 773     //echo "trace <b>"  . $arr[$i]['data'] . "</b> (" . $arr[$i]['type'] . ")<br>";
 774
 775                 // High speed seek for locating the end of the current query
 776                 if ($seek_queryend == TRUE) {
 777                     if ($arr[$i]['type'] == 'punct_queryend') {
 778                         $seek_queryend = FALSE;
 779                     } else {
 780                         continue;
 781                     } // end if (type == punct_queryend)
 782                 } // end if ($seek_queryend)
 783
 784                 // TODO: when we find a UNION, should we split
 785                 // in another subresult?
 786                 if ($arr[$i]['type'] == 'punct_queryend') {
 787                     $result[]  = $subresult;
 788                     $subresult = $subresult_empty;
 789                     continue;
 790                 } // end if (type == punct_queryend)
 791
 792     // ==============================================================
 793                 if ($arr[$i]['type'] == 'punct_bracket_open_round') {
 794                     if ($in_extract) {
 795                         $number_of_brackets_in_extract++;
 796                     }
 797                 }
 798     // ==============================================================
 799                 if ($arr[$i]['type'] == 'punct_bracket_close_round') {
 800                     if ($in_extract) {
 801                         $number_of_brackets_in_extract--;
 802                         if ($number_of_brackets_in_extract == 0) {
 803                            $in_extract = FALSE;
 804                         }
 805                     }
 806                 }
 807     // ==============================================================
 808                 if ($arr[$i]['type'] == 'alpha_functionName') {
 809                     $upper_data = strtoupper($arr[$i]['data']);
 810                     if ($upper_data =='EXTRACT') {
 811                         $in_extract = TRUE;
 812                         $number_of_brackets_in_extract = 0;
 813                     }
 814                 }
 815
 816     // ==============================================================
 817                 if ($arr[$i]['type'] == 'alpha_reservedWord') {
 818                     // We don't know what type of query yet, so run this
 819                     if ($subresult['querytype'] == '') {
 820                         $subresult['querytype'] = strtoupper($arr[$i]['data']);
 821                     } // end if (querytype was empty)
 822
 823                     // Check if we support this type of query
 824                     if (!PMA_STR_binarySearchInArr($subresult['querytype'], $supported_query_types, $supported_query_types_cnt)) {
 825                         // Skip ahead to the next one if we don't
 826                         $seek_queryend = TRUE;
 827                         continue;
 828                     } // end if (query not supported)
 829
 830                     // upper once
 831                     $upper_data = strtoupper($arr[$i]['data']);
 832                     //TODO: reset for each query?
 833
 834                     if ($upper_data == 'SELECT') {
 835                         $seen_from = FALSE;
 836                         $previous_was_identifier = FALSE;
 837                         $current_select_expr = -1;
 838                         $seen_end_of_table_ref = FALSE;
 839                     } // end if ( data == SELECT)
 840
 841                     if ($upper_data =='FROM' && !$in_extract) {
 842                         $current_table_ref = -1;
 843                         $seen_from = TRUE;
 844                         $previous_was_identifier = FALSE;
 845                         $save_table_ref = TRUE;
 846                     } // end if (data == FROM)
 847
 848                     // here, do not 'continue' the loop, as we have more work for
 849                     // reserved words below
 850                 } // end if (type == alpha_reservedWord)
 851
 852     // ==============================
 853                 if (($arr[$i]['type'] == 'quote_backtick')
 854                  || ($arr[$i]['type'] == 'quote_double')
 855                  || ($arr[$i]['type'] == 'quote_single')
 856                  || ($arr[$i]['type'] == 'alpha_identifier')) {
 857
 858                     switch ($arr[$i]['type']) {
 859                         case 'alpha_identifier':
 860                             $identifier = $arr[$i]['data'];
 861                             break;
 862
 863                     //TODO: check embedded double quotes or backticks?
 864                     // and/or remove just the first and last character?
 865                         case 'quote_backtick':
 866                             $identifier = str_replace('`','',$arr[$i]['data']);
 867                             break;
 868                         case 'quote_double':
 869                             $identifier = str_replace('"','',$arr[$i]['data']);
 870                             break;
 871                         case 'quote_single':
 872                             $identifier = str_replace("'","",$arr[$i]['data']);
 873                             break;
 874                     } // end switch
 875
 876                     if ($subresult['querytype'] == 'SELECT') {
 877                         if (!$seen_from) {
 878                             if ($previous_was_identifier) {
 879                                 // found alias for this select_expr, save it
 880                                 $alias_for_select_expr = $identifier;
 881                             } else {
 882                                 $chain[] = $identifier;
 883                                 $previous_was_identifier = TRUE;
 884
 885                             } // end if !$previous_was_identifier
 886                         } else {
 887                             // ($seen_from)
 888                             if ($save_table_ref && !$seen_end_of_table_ref) {
 889                                 if ($previous_was_identifier) {
 890                                     // found alias for table ref
 891                                     // save it for later
 892                                     $alias_for_table_ref = $identifier;
 893                                 } else {
 894                                     $chain[] = $identifier;
 895                                     $previous_was_identifier = TRUE;
 896
 897                                 } // end if ($previous_was_identifier)
 898                             } // end if ($save_table_ref &&!$seen_end_of_table_ref)
 899                         } // end if (!$seen_from)
 900                     } // end if (querytype SELECT)
 901                 } // end if ( quote_backtick or double quote or alpha_identifier)
 902
 903     // ===================================
 904                 if ($arr[$i]['type'] == 'punct_qualifier') {
 905                     // to be able to detect an identifier following another
 906                     $previous_was_identifier = FALSE;
 907                     continue;
 908                 } // end if (punct_qualifier)
 909
 910                 // TODO: check if 3 identifiers following one another -> error
 911
 912                 //    s a v e    a    s e l e c t    e x p r
 913                 // finding a list separator or FROM
 914                 // means that we must save the current chain of identifiers
 915                 // into a select expression
 916
 917                 // for now, we only save a select expression if it contains
 918                 // at least one identifier, as we are interested in checking
 919                 // the columns and table names, so in "select * from persons",
 920                 // the "*" is not saved
 921
 922                 if (isset($chain) && !$seen_end_of_table_ref
 923                    && (   (!$seen_from
 924                        && $arr[$i]['type'] == 'punct_listsep')
 925                       || ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data == 'FROM')) ) {
 926                     $size_chain = count($chain);
 927                     $current_select_expr++;
 928                     $subresult['select_expr'][$current_select_expr] = array(
 929                       'expr' => '',
 930                       'alias' => '',
 931                       'db'   => '',
 932                       'table_name' => '',
 933                       'table_true_name' => '',
 934                       'column' => ''
 935                      );
 936
 937                     if (!empty($alias_for_select_expr)) {
 938                         // we had found an alias for this select expression
 939                         $subresult['select_expr'][$current_select_expr]['alias'] = $alias_for_select_expr;
 940                         unset($alias_for_select_expr);
 941                     }
 942                     // there is at least a column
 943                     $subresult['select_expr'][$current_select_expr]['column'] = $chain[$size_chain - 1];
 944                     $subresult['select_expr'][$current_select_expr]['expr'] = $chain[$size_chain - 1];
 945
 946                     // maybe a table
 947                     if ($size_chain > 1) {
 948                         $subresult['select_expr'][$current_select_expr]['table_name'] = $chain[$size_chain - 2];
 949                         // we assume for now that this is also the true name
 950                         $subresult['select_expr'][$current_select_expr]['table_true_name'] = $chain[$size_chain - 2];
 951                         $subresult['select_expr'][$current_select_expr]['expr']
 952                          = $subresult['select_expr'][$current_select_expr]['table_name']
 953                           . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
 954                     } // end if ($size_chain > 1)
 955
 956                     // maybe a db
 957                     if ($size_chain > 2) {
 958                         $subresult['select_expr'][$current_select_expr]['db'] = $chain[$size_chain - 3];
 959                         $subresult['select_expr'][$current_select_expr]['expr']
 960                          = $subresult['select_expr'][$current_select_expr]['db']
 961                           . '.' . $subresult['select_expr'][$current_select_expr]['expr'];
 962                     } // end if ($size_chain > 2)
 963                     unset($chain);
 964
 965                     // TODO: explain this:
 966                     if (($arr[$i]['type'] == 'alpha_reservedWord')
 967                      && ($upper_data != 'FROM')) {
 968                         $previous_was_identifier = TRUE;
 969                     }
 970
 971                 } // end if (save a select expr)
 972
 973
 974                 //======================================
 975                 //    s a v e    a    t a b l e    r e f
 976                 //======================================
 977
 978                 // maybe we just saw the end of table refs
 979                 // but the last table ref has to be saved
 980                 // or we are at the last token (TODO: there could be another
 981                 // query after this one)
 982                 // or we just got a reserved word
 983
 984                 if (isset($chain) && $seen_from && $save_table_ref
 985                  && ($arr[$i]['type'] == 'punct_listsep'
 986                    || ($arr[$i]['type'] == 'alpha_reservedWord' && $upper_data!="AS")
 987                    || $seen_end_of_table_ref
 988                    || $i==$size-1 )) {
 989
 990                     $size_chain = count($chain);
 991                     $current_table_ref++;
 992                     $subresult['table_ref'][$current_table_ref] = array(
 993                       'expr'            => '',
 994                       'db'              => '',
 995                       'table_name'      => '',
 996                       'table_alias'     => '',
 997                       'table_true_name' => ''
 998                      );
 999                     if (!empty($alias_for_table_ref)) {
1000                         $subresult['table_ref'][$current_table_ref]['table_alias'] = $alias_for_table_ref;
1001                         unset($alias_for_table_ref);
1002                     }
1003                     $subresult['table_ref'][$current_table_ref]['table_name'] = $chain[$size_chain - 1];
1004                     // we assume for now that this is also the true name
1005                     $subresult['table_ref'][$current_table_ref]['table_true_name'] = $chain[$size_chain - 1];
1006                     $subresult['table_ref'][$current_table_ref]['expr']
1007                          = $subresult['table_ref'][$current_table_ref]['table_name'];
1008                     // maybe a db
1009                     if ($size_chain > 1) {
1010                         $subresult['table_ref'][$current_table_ref]['db'] = $chain[$size_chain - 2];
1011                         $subresult['table_ref'][$current_table_ref]['expr']
1012                          = $subresult['table_ref'][$current_table_ref]['db']
1013                           . '.' . $subresult['table_ref'][$current_table_ref]['expr'];
1014                     } // end if ($size_chain > 1)
1015
1016                     // add the table alias into the whole expression
1017                     $subresult['table_ref'][$current_table_ref]['expr']
1018                      .= ' ' . $subresult['table_ref'][$current_table_ref]['table_alias'];
1019
1020                     unset($chain);
1021                     $previous_was_identifier = TRUE;
1022                     //continue;
1023
1024                 } // end if (save a table ref)
1025
1026
1027                 // when we have found all table refs,
1028                 // for each table_ref alias, put the true name of the table
1029                 // in the corresponding select expressions
1030
1031                 if (isset($current_table_ref) && ($seen_end_of_table_ref || $i == $size-1)) {
1032                     for ($tr=0; $tr <= $current_table_ref; $tr++) {
1033                         $alias = $subresult['table_ref'][$tr]['table_alias'];
1034                         $truename = $subresult['table_ref'][$tr]['table_true_name'];
1035                         for ($se=0; $se <= $current_select_expr; $se++) {
1036                             if (!empty($alias) && $subresult['select_expr'][$se]['table_true_name']
1037                                == $alias) {
1038                                 $subresult['select_expr'][$se]['table_true_name']
1039                                  = $truename;
1040                             } // end if (found the alias)
1041                         } // end for (select expressions)
1042
1043                     } // end for (table refs)
1044                 } // end if (set the true names)
1045
1046
1047                // e n d i n g    l o o p  #1
1048                // set the $previous_was_identifier to FALSE if the current
1049                // token is not an identifier
1050                if (($arr[$i]['type'] != 'alpha_identifier')
1051                 && ($arr[$i]['type'] != 'quote_double')
1052                 && ($arr[$i]['type'] != 'quote_single')
1053                 && ($arr[$i]['type'] != 'quote_backtick')) {
1054                    $previous_was_identifier = FALSE;
1055                } // end if
1056
1057                // however, if we are on AS, we must keep the $previous_was_identifier
1058                if (($arr[$i]['type'] == 'alpha_reservedWord')
1059                 && ($upper_data == 'AS'))  {
1060                    $previous_was_identifier = TRUE;
1061                }
1062
1063                if (($arr[$i]['type'] == 'alpha_reservedWord')
1064                 && ($upper_data =='ON' || $upper_data =='USING')) {
1065                    $save_table_ref = FALSE;
1066                } // end if (data == ON)
1067
1068                if (($arr[$i]['type'] == 'alpha_reservedWord')
1069                 && ($upper_data =='JOIN' || $upper_data =='FROM')) {
1070                    $save_table_ref = TRUE;
1071                } // end if (data == JOIN)
1072
1073                // no need to check the end of table ref if we already did
1074                // TODO: maybe add "&& $seen_from"
1075                if (!$seen_end_of_table_ref) {
1076                    // if this is the last token, it implies that we have
1077                    // seen the end of table references
1078                    // Check for the end of table references
1079                    if (($i == $size-1)
1080                    || ($arr[$i]['type'] == 'alpha_reservedWord'
1081                       && PMA_STR_binarySearchInArr($upper_data, $words_ending_table_ref, $words_ending_table_ref_cnt))) {
1082                        $seen_end_of_table_ref = TRUE;
1083
1084                        // to be able to save the last table ref, but do not
1085                        // set it true if we found a word like "ON" that has
1086                        // already set it to false
1087                        if (isset($save_table_ref) && $save_table_ref != FALSE) {
1088                           $save_table_ref = TRUE;
1089                        } //end if
1090
1091                    } // end if (check for end of table ref)
1092                } //end if (!$seen_end_of_table_ref)
1093
1094                if ($seen_end_of_table_ref) {
1095                    $save_table_ref = FALSE;
1096                } // end if
1097
1098             } // end for $i (loop #1)
1099
1100             // -------------------------------------------------------
1101             // This is a big hunk of debugging code by Marc for this.
1102             // -------------------------------------------------------
1103             /*
1104               if (isset($current_select_expr)) {
1105                for ($trace=0; $trace<=$current_select_expr; $trace++) {
1106
1107                echo "<br>";
1108                reset ($subresult['select_expr'][$trace]);
1109                while (list ($key, $val) = each ($subresult['select_expr'][$trace]))
1110                echo "sel expr $trace $key => $val<br />\n";
1111                }
1112               }
1113
1114               if (isset($current_table_ref)) {
1115                for ($trace=0; $trace<=$current_table_ref; $trace++) {
1116
1117                echo "<br>";
1118                reset ($subresult['table_ref'][$trace]);
1119                while (list ($key, $val) = each ($subresult['table_ref'][$trace]))
1120                echo "table ref $trace $key => $val<br />\n";
1121                }
1122               }
1123             */
1124             // -------------------------------------------------------
1125
1126
1127             // loop #2: for queryflags
1128             //          ,querytype (for queries != 'SELECT')
1129             //
1130             // This is not in the loop 1 to keep logic simple
1131
1132             // we will also need this queryflag in loop 2
1133             // so set it here
1134             if (isset($current_table_ref) && $current_table_ref > -1) {
1135                 $subresult['queryflags']['select_from'] = 1;
1136             }
1137
1138             $seen_reserved_word = FALSE;
1139             $seen_group = FALSE;
1140             $seen_order = FALSE;
1141             $in_group_by = FALSE; // true when we are into the GROUP BY clause
1142             $in_order_by = FALSE; // true when we are into the ORDER BY clause
1143             $in_having = FALSE; // true when we are into the HAVING clause
1144             $in_select_expr = FALSE; // true when we are into the select expr clause
1145             $in_where = FALSE; // true when we are into the WHERE clause
1146             $in_from = FALSE;
1147
1148             for ($i = 0; $i < $size; $i++) {
1149     //echo "trace loop2 <b>"  . $arr[$i]['data'] . "</b> (" . $arr[$i]['type'] . ")<br>";
1150
1151                // need_confirm
1152                //
1153                // check for reserved words that will have to generate
1154                // a confirmation request later in sql.php3
1155                // the cases are:
1156                //   DROP TABLE
1157                //   DROP DATABASE
1158                //   ALTER TABLE... DROP
1159                //   DELETE FROM...
1160                //
1161                // this code is not used for confirmations coming from functions.js
1162
1163                // TODO: check for punct_queryend
1164
1165                if ($arr[$i]['type'] == 'alpha_reservedWord') {
1166                    $upper_data = strtoupper($arr[$i]['data']);
1167                    if (!$seen_reserved_word) {
1168                        $first_reserved_word = $upper_data;
1169                        $subresult['querytype'] = $upper_data;
1170                        $seen_reserved_word = TRUE;
1171
1172                        // if the first reserved word is DROP or DELETE,
1173                        // we know this is a query that needs to be confirmed
1174                        if ($first_reserved_word=='DROP'
1175                            || $first_reserved_word == 'DELETE') {
1176                           $subresult['queryflags']['need_confirm'] = 1;
1177                        }
1178                    } else {
1179                        if ($upper_data=='DROP' && $first_reserved_word=='ALTER') {
1180                           $subresult['queryflags']['need_confirm'] = 1;
1181                        }
1182                    }
1183
1184                    if ($upper_data == 'SELECT') {
1185                        $in_select_expr = TRUE;
1186                        $select_expr_clause = '';
1187                    }
1188
1189                    // if this is a real SELECT...FROM
1190                    if ($upper_data == 'FROM' && isset($subresult['queryflags']['select_from']) && $subresult['queryflags']['select_from'] == 1) {
1191                        $in_from = TRUE;
1192                        $from_clause = '';
1193                        $in_select_expr = FALSE;
1194                    }
1195
1196
1197                    // (we could have less resetting of variables to FALSE
1198                    // if we trust that the query respects the standard
1199                    // MySQL order for clauses)
1200
1201                    // we use $seen_group and $seen_order because we are looking
1202                    // for the BY
1203                    if ($upper_data == 'GROUP') {
1204                        $seen_group = TRUE;
1205                        $seen_order = FALSE;
1206                        $in_having = FALSE;
1207                        $in_order_by = FALSE;
1208                        $in_where = FALSE;
1209                        $in_select_expr = FALSE;
1210                        $in_from = FALSE;
1211                    }
1212                    if ($upper_data == 'ORDER') {
1213                        $seen_order = TRUE;
1214                        $seen_group = FALSE;
1215                        $in_having = FALSE;
1216                        $in_group_by = FALSE;
1217                        $in_where = FALSE;
1218                        $in_select_expr = FALSE;
1219                        $in_from = FALSE;
1220                    }
1221                    if ($upper_data == 'HAVING') {
1222                        $in_having = TRUE;
1223                        $having_clause = '';
1224                        $seen_group = FALSE;
1225                        $seen_order = FALSE;
1226                        $in_group_by = FALSE;
1227                        $in_order_by = FALSE;
1228                        $in_where = FALSE;
1229                        $in_select_expr = FALSE;
1230                        $in_from = FALSE;
1231                    }
1232
1233                    if ($upper_data == 'WHERE') {
1234                        $in_where = TRUE;
1235                        $where_clause = '';
1236                        $where_clause_identifiers = array();
1237                        $seen_group = FALSE;
1238                        $seen_order = FALSE;
1239                        $in_group_by = FALSE;
1240                        $in_order_by = FALSE;
1241                        $in_having = FALSE;
1242                        $in_select_expr = FALSE;
1243                        $in_from = FALSE;
1244                    }
1245
1246                    if ($upper_data == 'BY') {
1247                        if ($seen_group) {
1248                            $in_group_by = TRUE;
1249                            $group_by_clause = '';
1250                        }
1251                        if ($seen_order) {
1252                            $in_order_by = TRUE;
1253                            $order_by_clause = '';
1254                        }
1255                    }
1256
1257                    // if we find one of the words that could end the clause
1258                    if (PMA_STR_binarySearchInArr($upper_data, $words_ending_clauses, $words_ending_clauses_cnt)) {
1259
1260                        $in_group_by = FALSE;
1261                        $in_order_by = FALSE;
1262                        $in_having   = FALSE;
1263                        $in_where    = FALSE;
1264                        $in_select_expr = FALSE;
1265                        $in_from = FALSE;
1266                    }
1267
1268                } // endif (reservedWord)
1269
1270
1271                // do not add a blank after a function name
1272
1273                $sep=' ';
1274                if ($arr[$i]['type'] == 'alpha_functionName') {
1275                    $sep='';
1276                }
1277
1278                if ($in_select_expr && $upper_data != 'SELECT' && $upper_data != 'DISTINCT') {
1279                    $select_expr_clause .= $arr[$i]['data'] . $sep;
1280                }
1281                if ($in_from && $upper_data != 'FROM') {
1282                    $from_clause .= $arr[$i]['data'] . $sep;
1283                }
1284                if ($in_group_by && $upper_data != 'GROUP' && $upper_data != 'BY') {
1285                    $group_by_clause .= $arr[$i]['data'] . $sep;
1286                }
1287                if ($in_order_by && $upper_data != 'ORDER' && $upper_data != 'BY') {
1288                    $order_by_clause .= $arr[$i]['data'] . $sep;
1289                }
1290                if ($in_having && $upper_data != 'HAVING') {
1291                    $having_clause .= $arr[$i]['data'] . $sep;
1292                }
1293                if ($in_where && $upper_data != 'WHERE') {
1294                    $where_clause .= $arr[$i]['data'] . $sep;
1295
1296                    if (($arr[$i]['type'] == 'quote_backtick')
1297                     || ($arr[$i]['type'] == 'alpha_identifier')) {
1298                        $where_clause_identifiers[] = $arr[$i]['data'];
1299                    }
1300                }
1301
1302                // clear $upper_data for next iteration
1303                $upper_data='';
1304
1305             } // end for $i (loop #2)
1306
1307             if (isset($select_expr_clause)) {
1308                 $subresult['select_expr_clause'] = $select_expr_clause;
1309             }
1310             if (isset($from_clause)) {
1311                 $subresult['from_clause'] = $from_clause;
1312             }
1313             if (isset($group_by_clause)) {
1314                 $subresult['group_by_clause'] = $group_by_clause;
1315             }
1316             if (isset($order_by_clause)) {
1317                 $subresult['order_by_clause'] = $order_by_clause;
1318             }
1319             if (isset($having_clause)) {
1320                 $subresult['having_clause'] = $having_clause;
1321             }
1322             if (isset($where_clause)) {
1323                 $subresult['where_clause'] = $where_clause;
1324             }
1325             if (isset($where_clause_identifiers)) {
1326                 $subresult['where_clause_identifiers'] = $where_clause_identifiers;
1327             }
1328
1329
1330             // They are naughty and didn't have a trailing semi-colon,
1331             // then still handle it properly
1332             if ($subresult['querytype'] != '') {
1333                 $result[] = $subresult;
1334             }
1335             return $result;
1336         } // end of the "PMA_SQP_analyze()" function
1337
1338
1339         /**
1340          * Colorizes SQL queries html formatted
1341          *
1342          * @param  array   The SQL queries html formatted
1343          *
1344          * @return array   The colorized SQL queries
1345          *
1346          * @access public
1347          */
1348         function PMA_SQP_formatHtml_colorize($arr)
1349         {
1350             $i         = $GLOBALS['PMA_strpos']($arr['type'], '_');
1351             $class     = '';
1352             if ($i > 0) {
1353                 $class = 'syntax_' . $GLOBALS['PMA_substr']($arr['type'], 0, $i) . ' ';
1354             }
1355
1356             $class     .= 'syntax_' . $arr['type'];
1357
1358             //TODO: check why adding a "\n" after the </span> would cause extra
1359             //      blanks to be displayed:
1360             //      SELECT p . person_name
1361
1362             return '<span class="' . $class . '">' . htmlspecialchars($arr['data']) . '</span>';
1363         } // end of the "PMA_SQP_formatHtml_colorize()" function
1364
1365
1366         /**
1367          * Formats SQL queries to html
1368          *
1369          * @param  array   The SQL queries
1370          *
1371          * @return string  The formatted SQL queries
1372          *
1373          * @access public
1374          */
1375         function PMA_SQP_formatHtml($arr, $mode='color')
1376         {
1377             // first check for the SQL parser having hit an error
1378             if (PMA_SQP_isError()) {
1379                 return $arr;
1380             }
1381             // then check for an array
1382             if (!is_array($arr)) {
1383                 return $arr;
1384             }
1385             // else do it properly
1386             switch ($mode) {
1387                 case 'color':
1388                     $str                                = '<span class="syntax">';
1389                     $html_line_break                    = '<br />';
1390                     break;
1391                 case 'query_only':
1392                     $str                                = '';
1393                     $html_line_break                    = "\n";
1394                     break;
1395                 case 'text':
1396                     $str                                = '';
1397                     $html_line_break                    = '<br />';
1398                     break;
1399             } // end switch
1400             $indent                                     = 0;
1401             $bracketlevel                               = 0;
1402             $functionlevel                              = 0;
1403             $infunction                                 = FALSE;
1404             $space_punct_listsep                        = ' ';
1405             $space_punct_listsep_function_name          = ' ';
1406             // $space_alpha_reserved_word = '<br />'."\n";
1407             $space_alpha_reserved_word                  = ' ';
1408
1409             $keywords_with_brackets_1before            = array(
1410                 'INDEX',
1411                 'KEY',
1412                 'ON',
1413                 'USING'
1414             );
1415             $keywords_with_brackets_1before_cnt        = 4;
1416
1417             $keywords_with_brackets_2before            = array(
1418                 'IGNORE',
1419                 'INDEX',
1420                 'INTO',
1421                 'KEY',
1422                 'PRIMARY',
1423                 'PROCEDURE',
1424                 'REFERENCES',
1425                 'UNIQUE',
1426                 'USE'
1427             );
1428             // $keywords_with_brackets_2before_cnt = count($keywords_with_brackets_2before);
1429             $keywords_with_brackets_2before_cnt        = 9;
1430
1431             // These reserved words do NOT get a newline placed near them.
1432             $keywords_no_newline               = array(
1433                 'AND',
1434                 'AS',
1435                 'ASC',
1436                 'DESC',
1437                 'IS',
1438                 'NOT',
1439                 'NULL',
1440                 'ON',
1441                 'OR'
1442             );
1443             $keywords_no_newline_cnt           = 9;
1444
1445             $arraysize = $arr['len'];
1446             $typearr   = array();
1447             if ($arraysize >= 0) {
1448                 $typearr[0] = '';
1449                 $typearr[1] = '';
1450                 $typearr[2] = '';
1451                 $typearr[3] = $arr[0]['type'];
1452             }
1453
1454             for ($i = 0; $i < $arraysize; $i++) {
1455                 // DEBUG echo "<b>" . $arr[$i]['data'] . "</b> " . $arr[$i]['type'] . "<br />";
1456                 $before = '';
1457                 $after  = '';
1458                 $indent = 0;
1459                 // array_shift($typearr);
1460                 /*
1461                 0 prev2
1462                 1 prev
1463                 2 current
1464                 3 next
1465                 */
1466                 if (($i + 1) < $arraysize) {
1467                     // array_push($typearr, $arr[$i + 1]['type']);
1468                     $typearr[4] = $arr[$i + 1]['type'];
1469                 } else {
1470                     //array_push($typearr, NULL);
1471                     $typearr[4] = '';
1472                 }
1473
1474                 for ($j=0; $j<4; $j++) {
1475                     $typearr[$j] = $typearr[$j + 1];
1476                 }
1477
1478                 switch ($typearr[2]) {
1479                     case 'white_newline':
1480     //                    $after      = '<br />';
1481                         $before     = '';
1482                         break;
1483                     case 'punct_bracket_open_round':
1484                         $bracketlevel++;
1485                         $infunction = FALSE;
1486                         // Make sure this array is sorted!
1487                         if (($typearr[1] == 'alpha_functionName') || ($typearr[1] == 'alpha_columnType') || ($typearr[1] == 'punct')
1488                             || ($typearr[3] == 'digit_integer') || ($typearr[3] == 'digit_hex') || ($typearr[3] == 'digit_float')
1489                             || (($typearr[0] == 'alpha_reservedWord')
1490                                 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 2]['data']), $keywords_with_brackets_2before, $keywords_with_brackets_2before_cnt))
1491                             || (($typearr[1] == 'alpha_reservedWord')
1492                                 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_with_brackets_1before, $keywords_with_brackets_1before_cnt))
1493                             ) {
1494                             $functionlevel++;
1495                             $infunction = TRUE;
1496                             $after      .= ' ';
1497                         } else {
1498                             $indent++;
1499                             $after      .= ($mode != 'query_only' ? '<div class="syntax_indent' . $indent . '">' : ' ');
1500                         }
1501                         break;
1502                     case 'alpha_identifier':
1503                         if (($typearr[1] == 'punct_qualifier') || ($typearr[3] == 'punct_qualifier')) {
1504                             $after      = '';
1505                             $before     = '';
1506                         }
1507                         if (($typearr[3] == 'alpha_columnType') || ($typearr[3] == 'alpha_identifier')) {
1508                             $after      .= ' ';
1509                         }
1510                         break;
1511                     case 'punct_qualifier':
1512                         $before         = '';
1513                         $after          = '';
1514                         break;
1515                     case 'punct_listsep':
1516                         if ($infunction == TRUE) {
1517                             $after      .= $space_punct_listsep_function_name;
1518                         } else {
1519                             $after      .= $space_punct_listsep;
1520                         }
1521                         break;
1522                     case 'punct_queryend':
1523                         if (($typearr[3] != 'comment_mysql') && ($typearr[3] != 'comment_ansi')) {
1524                             $after     .= $html_line_break;
1525                             $after     .= $html_line_break;
1526                         }
1527                         $space_punct_listsep               = ' ';
1528                         $space_punct_listsep_function_name = ' ';
1529                         $space_alpha_reserved_word         = ' ';
1530                         break;
1531                     case 'comment_mysql':
1532                     case 'comment_ansi':
1533                         $after         .= $html_line_break;
1534                         break;
1535                     case 'punct':
1536                         $after         .= ' ';
1537                         $before        .= ' ';
1538                         break;
1539                     case 'punct_bracket_close_round':
1540                         $bracketlevel--;
1541                         if ($infunction == TRUE) {
1542                             $functionlevel--;
1543                             $after     .= ' ';
1544                             $before    .= ' ';
1545                         } else {
1546                             $indent--;
1547                             $before    .= ($mode != 'query_only' ? '</div>' : ' ');
1548                         }
1549                         $infunction    = ($functionlevel > 0) ? TRUE : FALSE;
1550                         break;
1551                     case 'alpha_columnType':
1552                         if ($typearr[3] == 'alpha_columnAttrib') {
1553                             $after     .= ' ';
1554                         }
1555                         if ($typearr[1] == 'alpha_columnType') {
1556                             $before    .= ' ';
1557                         }
1558                         break;
1559                     case 'alpha_columnAttrib':
1560
1561                         // ALTER TABLE tbl_name AUTO_INCREMENT = 1
1562                         if ($typearr[1] == 'alpha_identifier') {
1563                             $before .= ' ';
1564                         }
1565                         if (($typearr[3] == 'alpha_columnAttrib') || ($typearr[3] == 'quote_single')) {
1566                             $after     .= ' ';
1567                         }
1568                         break;
1569                     case 'alpha_reservedWord':
1570                         //$upper         = $arr[$i]['data'];
1571                         $arr[$i]['data'] = strtoupper($arr[$i]['data']);
1572                         if ((($typearr[1] != 'alpha_reservedWord')
1573                             || (($typearr[1] == 'alpha_reservedWord')
1574                                 && PMA_STR_binarySearchInArr(strtoupper($arr[$i - 1]['data']), $keywords_no_newline, $keywords_no_newline_cnt)))
1575                             && ($typearr[1] != 'punct_level_plus')
1576                             && (!PMA_STR_binarySearchInArr($arr[$i]['data'], $keywords_no_newline, $keywords_no_newline_cnt))) {
1577                             // do not put a space before the first token, because
1578                             // we use a lot of eregi() checking for the first
1579                             // reserved word at beginning of query
1580                             if ($i > 0) {
1581                                 $before    .= $space_alpha_reserved_word;
1582                             }
1583                         } else {
1584                             $before    .= ' ';
1585                         }
1586
1587                         switch ($arr[$i]['data']) {
1588                             case 'CREATE':
1589                                 $space_punct_listsep       = $html_line_break;
1590                                 $space_alpha_reserved_word = ' ';
1591                                 break;
1592                             case 'EXPLAIN':
1593                             case 'DESCRIBE':
1594                             case 'SET':
1595                             case 'ALTER':
1596                             case 'DELETE':
1597                             case 'SHOW':
1598                             case 'DROP':
1599                             case 'UPDATE':
1600                             case 'TRUNCATE':
1601                             case 'ANALYZE':
1602                             case 'ANALYSE':
1603                                 $space_punct_listsep       = $html_line_break;
1604                                 $space_alpha_reserved_word = ' ';
1605                                 break;
1606                             case 'INSERT':
1607                             case 'REPLACE':
1608                                 $space_punct_listsep       = $html_line_break;
1609                                 $space_alpha_reserved_word = $html_line_break;
1610                                 break;
1611                             case 'VALUES':
1612                                 $space_punct_listsep       = ' ';
1613                                 $space_alpha_reserved_word = $html_line_break;
1614                                 break;
1615                             case 'SELECT':
1616                                 $space_punct_listsep       = ' ';
1617                                 $space_alpha_reserved_word = $html_line_break;
1618                                 break;
1619                             default:
1620                                 break;
1621                         } // end switch ($arr[$i]['data'])
1622
1623                         $after         .= ' ';
1624                         break;
1625                     case 'digit_integer':
1626                     case 'digit_float':
1627                     case 'digit_hex':
1628                         //TODO: could there be other types preceding a digit?
1629                         if ($typearr[1] == 'alpha_reservedWord') {
1630                             $after .= ' ';
1631                         }
1632                         if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
1633                             $after     .= ' ';
1634                         }
1635                         break;
1636                     case 'quote_double':
1637                     case 'quote_single':
1638                         // workaround: for the query
1639                         // REVOKE SELECT ON `base2\_db`.* FROM 'user'@'%'
1640                         // the @ is incorrectly marked as alpha_variable
1641                         // in the parser, and here, the '%' gets a blank before,
1642                         // which is a syntax error
1643                         if ($typearr[1]!='alpha_variable') {
1644                             $before        .= ' ';
1645                         }
1646                         if ($infunction && $typearr[3] == 'punct_bracket_close_round') {
1647                             $after     .= ' ';
1648                         }
1649                         break;
1650                     case 'quote_backtick':
1651                         if ($typearr[3] != 'punct_qualifier') {
1652                             $after     .= ' ';
1653                         }
1654                         if ($typearr[1] != 'punct_qualifier') {
1655                             $before    .= ' ';
1656                         }
1657                         break;
1658                     default:
1659                         break;
1660                 } // end switch ($typearr[2])
1661
1662     /*
1663                 if ($typearr[3] != 'punct_qualifier') {
1664                     $after             .= ' ';
1665                 }
1666                 $after                 .= "\n";
1667     */
1668                 $str .= $before . ($mode=='color' ? PMA_SQP_formatHTML_colorize($arr[$i]) : $arr[$i]['data']). $after;
1669             } // end for
1670             if ($mode=='color') {
1671                 $str .= '</span>';
1672             }
1673
1674             return $str;
1675         } // end of the "PMA_SQP_formatHtml()" function
1676     }
1677
1678     /**
1679      * Builds a CSS rule used for html formatted SQL queries
1680      *
1681      * @param  string  The class name
1682      * @param  string  The property name
1683      * @param  string  The property value
1684      *
1685      * @return string  The CSS rule
1686      *
1687      * @access public
1688      *
1689      * @see    PMA_SQP_buildCssData()
1690      */
1691     function PMA_SQP_buildCssRule($classname, $property, $value)
1692     {
1693         $str     = '.' . $classname . ' {';
1694         if ($value != '') {
1695             $str .= $property . ': ' . $value . ';';
1696         }
1697         $str     .= '}' . "\n";
1698
1699         return $str;
1700     } // end of the "PMA_SQP_buildCssRule()" function
1701
1702
1703     /**
1704      * Builds CSS rules used for html formatted SQL queries
1705      *
1706      * @return string  The CSS rules set
1707      *
1708      * @access public
1709      *
1710      * @global array   The current PMA configuration
1711      *
1712      * @see    PMA_SQP_buildCssRule()
1713      */
1714     function PMA_SQP_buildCssData()
1715     {
1716         global $cfg;
1717
1718         $css_string     = '';
1719         while (list($key, $col) = each($cfg['SQP']['fmtColor'])) {
1720             $css_string .= PMA_SQP_buildCssRule('syntax_' . $key, 'color', $col);
1721         }
1722         for ($i = 0; $i < 8; $i++) {
1723             $css_string .= PMA_SQP_buildCssRule('syntax_indent' . $i, 'margin-left', ($i * $cfg['SQP']['fmtInd']) . $cfg['SQP']['fmtIndUnit']);
1724         }
1725
1726         return $css_string;
1727     } // end of the "PMA_SQP_buildCssData()" function
1728
1729     if ($is_minimum_common == FALSE) {
1730         /**
1731          * Gets SQL queries with no format
1732          *
1733          * @param  array   The SQL queries list
1734          *
1735          * @return string  The SQL queries with no format
1736          *
1737          * @access public
1738          */
1739         function PMA_SQP_formatNone($arr)
1740         {
1741             $formatted_sql = htmlspecialchars($arr['raw']);
1742             $formatted_sql = ereg_replace("((\015\012)|(\015)|(\012)){3,}", "\n\n", $formatted_sql);
1743
1744             return $formatted_sql;
1745         } // end of the "PMA_SQP_formatNone()" function
1746
1747
1748         /**
1749          * Gets SQL queries in text format
1750          *
1751          * @param  array   The SQL queries list
1752          *
1753          * @return string  The SQL queries in text format
1754          *
1755          * @access public
1756          */
1757         function PMA_SQP_formatText($arr)
1758         {
1759             /**
1760              * TODO WRITE THIS!
1761              */
1762              return PMA_SQP_formatNone($arr);
1763         } // end of the "PMA_SQP_formatText()" function
1764     } // end if: minimal common.lib needed?
1765 } // $__PMA_SQP_LIB__
1766 ?>