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