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