include/php-sql-parser.php

   1 <?php
   2
   3 /**
   4  * SQL Parser from: http://code.google.com/p/php-sql-parser/
   5  * License: New BSD
   6  */
   7 class PHPSQLParser
   8 {
   9                 var $reserved = array();
  10                 var $functions = array();
  11                 function __construct($sql = false) {
  12                         #LOAD THE LIST OF RESERVED WORDS
  13                         $this->load_reserved_words();
  14                         if($sql) $this->parse($sql);
  15                 }
  16
  17                 function parse($sql) {
  18                         $sql = trim($sql);
  19
  20                         #lex the SQL statement
  21                         $in = $this->split_sql($sql);
  22
  23                         #sometimes the parser needs to skip ahead until a particular
  24                         #token is found
  25                         $skip_until = false;
  26
  27                         #this is the output tree which is being parsed
  28                         $out = array();
  29
  30                         #This is the last type of union used (UNION or UNION ALL)
  31                         #indicates a) presence of at least one union in this query
  32                         #          b) the type of union if this is the first or last query
  33                         $union = false;
  34
  35                         #Sometimes a "query" consists of more than one query (like a UNION query)
  36                         #this array holds all the queries
  37                         $queries=array();
  38
  39                         #This is the highest level lexical analysis.  This is the part of the
  40                         #code which finds UNION and UNION ALL query parts
  41                         foreach($in as $key => $token) {
  42                                 $token=trim($token);
  43
  44                                 if($skip_until) {
  45                                         if($token) {
  46                                                 if(strtoupper($token) == $skip_until) {
  47                                                         $skip_until = false;
  48                                                         continue;
  49                                                 }
  50                                         } else {
  51                                                 continue;
  52                                         }
  53                                 }
  54
  55                                 if(strtoupper($token) == "UNION") {
  56                                         $union = 'UNION';
  57                                         for($i=$key+1;$i<count($in);++$i) {
  58                                                 if(trim($in[$i]) == '') continue;
  59                                                 if(strtoupper($in[$i]) == 'ALL')  {
  60                                                         $skip_until = 'ALL';
  61                                                         $union = 'UNION ALL';
  62                                                         continue ;
  63                                                 } else {
  64                                                         break;
  65                                                 }
  66                                         }
  67
  68                                         $queries[$union][] = $out;
  69                                         $out = array();
  70
  71                                 } else {
  72                                         $out[]=$token;
  73                                 }
  74
  75                         }
  76
  77                         if(!empty($out)) {
  78                                 if ($union) {
  79                                         $queries[$union][] = $out;
  80                                 } else {
  81                                         $queries[] = $out;
  82                                 }
  83                         }
  84
  85
  86                         /*MySQL supports a special form of UNION:
  87                         (select ...)
  88                         union
  89                         (select ...)
  90
  91                         This block handles this query syntax.  Only one such subquery
  92                         is supported in each UNION block.  (select)(select)union(select) is not legal.
  93                         The extra queries will be silently ignored.
  94                         */
  95                         $union_types = array('UNION','UNION ALL');
  96                         foreach($union_types as $union_type) {
  97                                 if(!empty($queries[$union_type])) {
  98                                         foreach($queries[$union_type] as $i => $tok_list) {
  99                                                 foreach($tok_list as $z => $tok) {
 100                                                         $tok = trim($tok);
 101                                                         if(!$tok) continue;
 102                                                         if(preg_match('/^\\(\\s*select\\s*/i', $tok)) {
 103                                                                 $queries[$union_type][$i] = $this->parse(substr($tok,1,-1));
 104                                                                 break;
 105                                                         } else {
 106                                                                 $queries[$union_type][$i] = $this->process_sql($queries[$union_type][$i]);
 107                                                                 break;
 108                                                         }
 109                                                 }
 110                                         }
 111                                 }
 112                         }
 113
 114
 115                         /* If there was no UNION or UNION ALL in the query, then the query is
 116                         stored at $queries[0].
 117                         */
 118                         if(!empty($queries[0])) {
 119                                 $queries[0] = $this->process_sql($queries[0]);
 120
 121                         }
 122
 123                         if(count($queries) == 1 && !$union) {
 124                                 $queries = $queries[0];
 125                         }
 126
 127                         $this->parsed = $queries;
 128                         return $this->parsed;
 129                 }
 130
 131                 #This function counts open and close parenthesis and
 132                 #returns their location.  This might be faster as a regex
 133                 private function count_paren($token,$chars=array('(',')')) {
 134                         $len = strlen($token);
 135                         $open=array();
 136                         $close=array();
 137                         for($i=0;$i<$len;++$i){
 138                                 if($token[$i] == $chars[0]) {
 139                                         $open[] = $i;
 140                                 } elseif($token[$i] == $chars[1]) {
 141                                         $close[] = $i;
 142                                 }
 143
 144                         }
 145                         return array('open' => $open, 'close' => $close, 'balanced' =>( count($close) - count($open)));
 146                 }
 147
 148                 #This function counts open and close parenthesis and
 149                 #returns their location.  This might be faster as a regex
 150                 private function count_backtick($token) {
 151                         $len = strlen($token);
 152                         $cnt=0;
 153                         for($i=0;$i<$len;++$i){
 154                                 if($token[$i] == '`') ++$cnt;
 155                         }
 156                         return $cnt;
 157                 }
 158
 159                 #This is the lexer
 160                 #this function splits up a SQL statement into easy to "parse"
 161                 #tokens for the SQL processor
 162                 private function split_sql($sql) {
 163
 164                                 if(!is_string($sql)) {
 165                                     return false;
 166                                 }
 167
 168                                 $sql = str_replace(array('\\\'','\\"',"\r\n","\n","()"),array("''",'""'," "," "," "), $sql);
 169                                 $regex=<<<EOREGEX
 170 /(`(?:[^`]|``)`|[@A-Za-z0-9_.`-]+(?:\(\s*\)){0,1})
 171 |(\+|-|\*|\/|!=|>=|<=|<>|>|<|&&|\|\||=|\^)
 172 |(\(.*?\))   # Match FUNCTION(...) OR BAREWORDS
 173 |('(?:[^']|'')*'+)
 174 |("(?:[^"]|"")*"+)
 175 |([^ ,]+)
 176 /ix
 177 EOREGEX
 178 ;
 179
 180                         $tokens = preg_split($regex, $sql,-1, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE);
 181                         $token_count = count($tokens);
 182
 183                         /* The above regex has one problem, because the parenthetical match is not greedy.
 184                            Thus, when matching grouped expressions such as ( (a and b) or c) the
 185                            tokenizer will produce "( (a and b)", " ", "or", " " , "c,")"
 186
 187                            This block detects the number of open/close parentheses in the given token.  If the parentheses are balanced
 188                            (balanced == 0) then we don't need to do anything.
 189
 190                            otherwise, we need to balance the expression.
 191                         */
 192                         $reset = false;
 193                         for($i=0;$i<$token_count;++$i) {
 194
 195                                 if(empty($tokens[$i])) continue;
 196
 197                                 $token = $tokens[$i];
 198                                 $trim = trim($token);
 199                                 if($trim) {
 200                                         if($trim[0] != '('
 201                                                 && substr($trim,-1) == ')') {
 202                                                 $trim=trim(substr($trim,0,
 203                                                                 strpos($trim,'(')));
 204                                         }
 205                                         $tokens[$i]=$trim;
 206                                         $token=$trim;
 207                                 }
 208
 209                                 if($token && $token[0] == '(') {
 210                                         $info = $this->count_paren($token);
 211                                         if($info['balanced'] == 0) {
 212                                                 continue;
 213                                         }
 214
 215                                         // We need to find this many closing parentheses.
 216                                         $needed = abs($info['balanced']);
 217                                         $n = $i;
 218                                         while($needed > 0 && $n <$token_count-1) {
 219                                                 ++$n;
 220                                                 #echo "LOOKING FORWARD TO $n [ " . $tokens[$n] . "]\n";
 221                                                 $token2 = $tokens[$n];
 222                                                 $info2 = $this->count_paren($token2);
 223                                                 $closes = count($info2['close']);
 224                                                 if($closes != $needed) {
 225                                                         $tokens[$i] .= $tokens[$n];
 226                                                         unset($tokens[$n]);
 227                                                         $reset = true;
 228                                                         $info2 = $this->count_paren($tokens[$i]);
 229                                                         $needed = abs($info2['balanced']);
 230                                                 #       echo "CLOSES LESS THAN NEEDED (still need $needed)\n";
 231                                                 } else {
 232                                                         /*get the string pos of the last close parenthesis we need*/
 233                                                         $pos = $info2['close'][count($info2['close'])-1];
 234                                                         $str1 = $str2 = "";
 235                                                         if($pos == 0) {
 236                                                                 $str1 = ')';
 237                                                         } else {
 238                                                                 $str1 = substr($tokens[$n],0,$pos) . ')';
 239                                                                 $str2 = substr($tokens[$n],$pos+1);
 240                                                         }
 241                                                         #echo "CLOSES FOUND AT $n, offset:$pos  [$str1] [$str2]\n";
 242                                                         if(strlen($str2) > 0) {
 243                                                                 $tokens[$n] = $str2;
 244                                                         } else {
 245                                                                 unset($tokens[$n]);
 246                                                                 $reset = true;
 247                                                         }
 248                                                         $tokens[$i] .= $str1;
 249                                                         $info2 = $this->count_paren($tokens[$i]);
 250                                                         $needed = abs($info2['balanced']);
 251
 252                                                 }
 253                                         }
 254                                 }
 255                         }
 256
 257                         #the same problem appears with backticks :(
 258
 259                         /* reset the array if we deleted any tokens above */
 260                         if ($reset) $tokens = array_values($tokens);
 261
 262                         $token_count=count($tokens);
 263                         for($i=0;$i<$token_count;++$i) {
 264                                 if(empty($tokens[$i])) continue;
 265                                 $token=$tokens[$i];
 266                                 $needed=true;
 267                                 $reset=false;
 268                                 if($needed && $token && strpos($token,'`') !== false) {
 269                                         $info = $this->count_backtick($token);
 270                                         if($info %2 == 0) { #even number of backticks means we are balanced
 271                                                 continue;
 272                                         }
 273                                         $needed=1;
 274
 275                                         $n = $i;
 276                                         while($needed && $n <$token_count-1) {
 277                                                 $reset=true;
 278                                                 #echo "BACKTICK COUNT[$i]: $info old: {$tokens[$i]}, new: ($token)\n";
 279                                                 ++$n;
 280                                                 $token .= $tokens[$n];
 281                                                 unset($tokens[$n]);
 282                                                 $needed = $this->count_backtick($token) % 2;
 283                                         }
 284                                 }
 285                                 if($reset) $tokens[$i] = $token;
 286
 287                         }
 288                         /* reset the array if we deleted any tokens above */
 289                         $tokens = array_values($tokens);
 290
 291                         return $tokens;
 292
 293                 }
 294
 295                 /* This function breaks up the SQL statement into logical sections.
 296                    Some sections are then further handled by specialized functions.
 297                 */
 298                 private function process_sql(&$tokens,$start_at = 0, $stop_at = false) {
 299                         $prev_category = "";
 300                         $start = microtime(true);
 301                         $token_category = "";
 302
 303                         $skip_next=false;
 304                         $token_count = count($tokens);
 305
 306                         if(!$stop_at) {
 307                                 $stop_at = $token_count;
 308                         }
 309
 310                         $out = false;
 311
 312                         for($token_number = $start_at;$token_number<$stop_at;++$token_number) {
 313                                 $token = trim($tokens[$token_number]);
 314                                 if($token && $token[0] == '(' && $token_category == "") {
 315                                         $token_category = 'SELECT';
 316                                 }
 317
 318                         /* If it isn't obvious, when $skip_next is set, then we ignore the next real
 319                                 token, that is we ignore whitespace.
 320                                 */
 321                                 if($skip_next) {
 322                                         #whitespace does not count as a next token
 323                                         if($token == "") {
 324                                                 continue;
 325                                         }
 326
 327                             #to skip the token we replace it with whitespace
 328                                         $new_token = "";
 329                                         $skip_next = false;
 330                                 }
 331
 332                                 $upper = strtoupper($token);
 333                                 switch($upper) {
 334
 335                                         /* Tokens that get their own sections. These keywords have subclauses. */
 336                                         case 'SELECT':
 337                                         case 'ORDER':
 338                                         case 'LIMIT':
 339                                         case 'SET':
 340                                         case 'DUPLICATE':
 341                                         case 'VALUES':
 342                                         case 'GROUP':
 343                                         case 'ORDER':
 344                                         case 'HAVING':
 345                                         case 'INTO':
 346                                         case 'WHERE':
 347                                         case 'RENAME':
 348                                         case 'CALL':
 349                                         case 'PROCEDURE':
 350                                         case 'FUNCTION':
 351                                         case 'DATABASE':
 352                                         case 'SERVER':
 353                                         case 'LOGFILE':
 354                                         case 'DEFINER':
 355                                         case 'RETURNS':
 356                                         case 'EVENT':
 357                                         case 'TABLESPACE':
 358                                         case 'VIEW':
 359                                         case 'TRIGGER':
 360                                         case 'DATA':
 361                                         case 'DO':
 362                                         case 'PASSWORD':
 363                                         case 'USER':
 364                                         case 'PLUGIN':
 365                                         case 'FROM':
 366                                         case 'FLUSH':
 367                                         case 'KILL':
 368                                         case 'RESET':
 369                                         case 'START':
 370                                         case 'STOP':
 371                                         case 'PURGE':
 372                                         case 'EXECUTE':
 373                                         case 'PREPARE':
 374                                         case 'DEALLOCATE':
 375                                                 if($token == 'DEALLOCATE') {
 376                                                         $skip_next = true;
 377                                                 }
 378                                                 /* this FROM is different from FROM in other DML (not join related) */
 379                                                 if($token_category == 'PREPARE' && $upper == 'FROM') {
 380                                                         continue 2;
 381                                                 }
 382
 383                                                 $token_category = $upper;
 384                                                 #$join_type = 'JOIN';
 385                                                 if($upper == 'FROM' && $token_category == 'FROM') {
 386                                                         /* DO NOTHING*/
 387                                                 } else {
 388                                                         continue 2;
 389
 390                                                 }
 391                                         break;
 392
 393                                         /* These tokens get their own section, but have no subclauses.
 394                                            These tokens identify the statement but have no specific subclauses of their own. */
 395                                         case 'DELETE':
 396                                         case 'ALTER':
 397                                         case 'INSERT':
 398                                         case 'REPLACE':
 399                                         case 'TRUNCATE':
 400                                         case 'CREATE':
 401                                         case 'TRUNCATE':
 402                                         case 'OPTIMIZE':
 403                                         case 'GRANT':
 404                                         case 'REVOKE':
 405                                         case 'SHOW':
 406                                         case 'HANDLER':
 407                                         case 'LOAD':
 408                                         case 'ROLLBACK':
 409                                         case 'SAVEPOINT':
 410                                         case 'UNLOCK':
 411                                         case 'INSTALL':
 412                                         case 'UNINSTALL':
 413                                         case 'ANALZYE':
 414                                         case 'BACKUP':
 415                                         case 'CHECK':
 416                                         case 'CHECKSUM':
 417                                         case 'REPAIR':
 418                                         case 'RESTORE':
 419                                         case 'CACHE':
 420                                         case 'DESCRIBE':
 421                                         case 'EXPLAIN':
 422                                         case 'USE':
 423                                         case 'HELP':
 424                                                 $token_category = $upper; /* set the category in case these get subclauses
 425                                                                              in a future version of MySQL */
 426                                                 $out[$upper][0] = $upper;
 427                                                 continue 2;
 428                                         break;
 429
 430                                         /* This is either LOCK TABLES or SELECT ... LOCK IN SHARE MODE*/
 431                                         case 'LOCK':
 432                                                 if($token_category == "") {
 433                                                         $token_category = $upper;
 434                                                         $out[$upper][0] = $upper;
 435                                                 } else {
 436                                                         $token = 'LOCK IN SHARE MODE';
 437                                                         $skip_next=true;
 438                                                         $out['OPTIONS'][] = $token;
 439                                                 }
 440                                                 continue 2;
 441                                         break;
 442
 443                                         case 'USING':
 444                                                 /* USING in FROM clause is different from USING w/ prepared statement*/
 445                                                 if($token_category == 'EXECUTE') {
 446                                                         $token_category=$upper;
 447                                                         continue 2;
 448                                                 }
 449                                                 if($token_category == 'FROM' && !empty($out['DELETE'])) {
 450                                                         $token_category=$upper;
 451                                                         continue 2;
 452                                                 }
 453                                         break;
 454
 455                                         /* DROP TABLE is different from ALTER TABLE DROP ... */
 456                                         case 'DROP':
 457                                                 if($token_category != 'ALTER') {
 458                                                         $token_category = $upper;
 459                                                         $out[$upper][0] = $upper;
 460                                                         continue 2;
 461                                                 }
 462                                         break;
 463
 464                                         case 'FOR':
 465                                                 $skip_next=true;
 466                                                 $out['OPTIONS'][] = 'FOR UPDATE';
 467                                                 continue 2;
 468                                         break;
 469
 470
 471                                         case 'UPDATE':
 472                                                 if($token_category == "" ) {
 473                                                         $token_category = $upper;
 474                                                         continue 2;
 475
 476                                                 }
 477                                                 if($token_category == 'DUPLICATE') {
 478                                                         continue 2;
 479                                                 }
 480                                                 break;
 481                                         break;
 482
 483                                         case 'START':
 484                                                 $token = "BEGIN";
 485                                                 $out[$upper][0] = $upper;
 486                                                 $skip_next = true;
 487                                         break;
 488
 489                                         /* These tokens are ignored. */
 490                                         case 'BY':
 491                                         case 'ALL':
 492                                         case 'SHARE':
 493                                         case 'MODE':
 494                                         case 'TO':
 495
 496                                         case ';':
 497                                                 continue 2;
 498                                                 break;
 499
 500                                         case 'KEY':
 501                                                 if($token_category == 'DUPLICATE') {
 502                                                         continue 2;
 503                                                 }
 504                                         break;
 505
 506                                         /* These tokens set particular options for the statement.  They never stand alone.*/
 507                                         case 'DISTINCTROW':
 508                                                 $token='DISTINCT';
 509                                         case 'DISTINCT':
 510                                         case 'HIGH_PRIORITY':
 511                                         case 'LOW_PRIORITY':
 512                                         case 'DELAYED':
 513                                         case 'IGNORE':
 514                                         case 'FORCE':
 515                                         case 'STRAIGHT_JOIN':
 516                                         case 'SQL_SMALL_RESULT':
 517                                         case 'SQL_BIG_RESULT':
 518                                         case 'QUICK':
 519                                         case 'SQL_BUFFER_RESULT':
 520                                         case 'SQL_CACHE':
 521                                         case 'SQL_NO_CACHE':
 522                                         case 'SQL_CALC_FOUND_ROWS':
 523                                                 $out['OPTIONS'][] = $upper;
 524                                                 continue 2;
 525                                         break;
 526
 527                                         case 'WITH':
 528                                                 if($token_category == 'GROUP') {
 529                                                         $skip_next=true;
 530                                                         $out['OPTIONS'][] = 'WITH ROLLUP';
 531                                                         continue 2;
 532                                                 }
 533                                         break;
 534
 535
 536                                         case 'AS':
 537                                         break;
 538
 539                                         case '':
 540                                         case ',':
 541                                         case ';':
 542                                                 break;
 543
 544                                         default:
 545                                                 break;
 546                                 }
 547
 548                                 if($prev_category == $token_category) {
 549                                         $out[$token_category][] = $token;
 550                                 }
 551
 552                                 $prev_category = $token_category;
 553                         }
 554
 555                         if(!$out) return false;
 556
 557
 558                         #process the SELECT clause
 559                         if(!empty($out['SELECT'])) $out['SELECT'] = $this->process_select($out['SELECT']);
 560
 561                         if(!empty($out['FROM']))   $out['FROM'] = $this->process_from($out['FROM']);
 562                         if(!empty($out['USING']))   $out['USING'] = $this->process_from($out['USING']);
 563                         if(!empty($out['UPDATE']))  $out['UPDATE'] = $this->process_from($out['UPDATE']);
 564
 565                         if(!empty($out['GROUP']))  $out['GROUP'] = $this->process_group($out['GROUP'], $out['SELECT']);
 566                         if(!empty($out['ORDER']))  $out['ORDER'] = $this->process_group($out['ORDER'], $out['SELECT']);
 567
 568                         if(!empty($out['LIMIT']))  $out['LIMIT'] = $this->process_limit($out['LIMIT']);
 569
 570                         if(!empty($out['WHERE']))  $out['WHERE'] = $this->process_expr_list($out['WHERE']);
 571                         if(!empty($out['HAVING']))  $out['HAVING'] = $this->process_expr_list($out['HAVING']);
 572                         if(!empty($out['SET']))  $out['SET'] = $this->process_set_list($out['SET']);
 573                         if(!empty($out['DUPLICATE'])) {
 574                                 $out['ON DUPLICATE KEY UPDATE'] = $this->process_set_list($out['DUPLICATE']);
 575                                 unset($out['DUPLICATE']);
 576                         }
 577                         if(!empty($out['INSERT']))  $out = $this->process_insert($out);
 578                         if(!empty($out['REPLACE']))  $out = $this->process_insert($out,'REPLACE');
 579                         if(!empty($out['DELETE']))  $out = $this->process_delete($out);
 580
 581                         return $out;
 582
 583                 }
 584
 585                 /* A SET list is simply a list of key = value expressions separated by comma (,).
 586                 This function produces a list of the key/value expressions.
 587                 */
 588                 private function process_set_list($tokens) {
 589                         $column="";
 590                         $expression="";
 591                         foreach($tokens as $token) {
 592                                 $token=trim($token);
 593                                 if(!$column) {
 594                                         if($token === false || empty($token)) continue;
 595                                         $column .= $token;
 596                                         continue;
 597                                 }
 598
 599                                 if($token == '=') continue;
 600
 601                                 if($token == ',') {
 602                                         $expr[] = array('column' => trim($column), 'expr' => trim($expression));
 603                                         $expression = $column = "";
 604                                         continue;
 605                                 }
 606
 607                                 $expression .= $token;
 608                         }
 609                         if($expression) {
 610                                 $expr[] = array('column' => trim($column), 'expr' => trim($expression));
 611                         }
 612
 613                         return $expr;
 614                 }
 615
 616                 /* This function processes the LIMIT section.
 617                    start,end are set.  If only end is provided in the query
 618                    then start is set to 0.
 619                 */
 620                 private function process_limit($tokens) {
 621                         $start = 0;
 622                         $end = 0;
 623
 624                         if($pos = array_search(',',$tokens)) {
 625                                 for($i=0;$i<$pos;++$i) {
 626                                         if($tokens[$i] != '') {
 627                                                 $start = $tokens[$i];
 628                                                 break;
 629                                         }
 630                                 }
 631                                 $pos = $pos + 1;
 632
 633                         } else {
 634                                 $pos = 0;
 635                         }
 636
 637                         for($i=$pos;$i<count($tokens);++$i) {
 638                                 if($tokens[$i] != '') {
 639                                         $end = $tokens[$i];
 640                                         break;
 641                                 }
 642                         }
 643
 644                         return array('start' => $start, 'end' => $end);
 645                 }
 646
 647                 /* This function processes the SELECT section.  It splits the clauses at the commas.
 648                    Each clause is then processed by process_select_expr() and the results are added to
 649                    the expression list.
 650
 651                    Finally, at the end, the expression list is returned.
 652                 */
 653                 private function process_select(&$tokens) {
 654                         $expression = "";
 655                         $expr = array();
 656                         foreach($tokens as $token) {
 657                                 if($token == ',') {
 658                                         $expr[] = $this->process_select_expr(trim($expression));
 659                                         $expression = "";
 660                                 } else {
 661                                         if($token === "" || $token===false) $token=" ";
 662                                         $expression .= $token ;
 663                                 }
 664                         }
 665                         if($expression) $expr[] = $this->process_select_expr(trim($expression));
 666                         return $expr;
 667                 }
 668
 669                 /* This function processes each SELECT clause.  We determine what (if any) alias
 670                    is provided, and we set the type of expression.
 671                 */
 672                 private function process_select_expr($expression) {
 673                         $capture = false;
 674                         $alias = "";
 675                         $base_expression = $expression;
 676                         $upper = trim(strtoupper($expression));
 677                         #if necessary, unpack the expression
 678                         if($upper[0] == '(') {
 679                                 #$expression = substr($expression,1,-1);
 680                                 $base_expression = $expression;
 681                         }
 682
 683                         $tokens = $this->split_sql($expression);
 684                         $token_count = count($tokens);
 685
 686                         /* Determine if there is an explicit alias after the AS clause.
 687                         If AS is found, then the next non-whitespace token is captured as the alias.
 688                         The tokens after (and including) the AS are removed.
 689                         */
 690                         $base_expr = "";
 691                         $stripped=array();
 692                         $capture=false;
 693                         $alias = "";
 694                         $processed=false;
 695                         for($i=0;$i<$token_count;++$i) {
 696                                 $token = strtoupper($tokens[$i]);
 697                                 if(trim($token)) {
 698                                         $stripped[] = $tokens[$i];
 699                                 }
 700
 701                                 if($token == 'AS') {
 702                                         unset($tokens[$i]);
 703                                         $capture = true;
 704                                         continue;
 705                                 }
 706
 707                                 if($capture) {
 708                                         if(trim($token)) {
 709                                                 $alias .= $tokens[$i];
 710                                         }
 711                                         unset($tokens[$i]);
 712                                         continue;
 713                                 }
 714                                 $base_expr .= $tokens[$i];
 715                         }
 716
 717                         $stripped = $this->process_expr_list($stripped);
 718                         $last = array_pop($stripped);
 719                         if(!$alias && $last['expr_type'] == 'colref') {
 720                                 $prev = array_pop($stripped);
 721                                 if($prev['expr_type'] == 'operator' ||
 722                                    $prev['expr_type'] == 'const' ||
 723                                    $prev['expr_type'] == 'function' ||
 724                                    $prev['expr_type'] == 'expression' ||
 725                                    #$prev['expr_type'] == 'aggregate_function' ||
 726                                    $prev['expr_type'] == 'subquery' ||
 727                                    $prev['expr_type'] == 'colref') {
 728                                         $alias = $last['base_expr'];
 729
 730                                         #remove the last token
 731                                         array_pop($tokens);
 732
 733                                         $base_expr = join("", $tokens);
 734
 735
 736                                 }
 737                         }
 738
 739                         if(!$alias) {
 740                                 $base_expr=join("", $tokens);
 741                                 $alias = $base_expr;
 742                         }
 743
 744                         /* Properly escape the alias if it is not escaped */
 745                         if ($alias[0] != '`') {
 746                                         $alias = '`' . str_replace('`','``',$alias) . '`';
 747                         }
 748                         $processed = false;
 749                         $type='expression';
 750
 751                         if(substr(trim($base_expr),0,1) == '(') {
 752                                 $base_expr = substr($expression,1,-1);
 753                                 if(preg_match('/^sel/i', $base_expr)) {
 754                                         $type='subquery';
 755                                         $processed = $this->parse($base_expr);
 756                                 }
 757                         }
 758                         if(!$processed) {
 759                                 $processed = $this->process_expr_list($tokens);
 760                         }
 761
 762                         if(count($processed) == 1) {
 763                                 $type = $processed[0]['expr_type'];
 764                                 $processed = false;
 765                         }
 766
 767                         return array('expr_type'=>$type,'alias' => $alias, 'base_expr' => $base_expr, 'sub_tree' => $processed);
 768
 769                 }
 770
 771                 private function trimSubquery($sq)
 772                 {
 773                     $sq = trim($sq);
 774                     if(empty($sq)) return '';
 775             while($sq[0] == '(' && substr($sq, -1) == ')') {
 776                 $sq = substr($sq, 1, -1);
 777             }
 778             return $sq;
 779                 }
 780
 781                 private function process_from(&$tokens) {
 782
 783                         $expression = "";
 784                         $expr = array();
 785                         $token_count=0;
 786                         $table = "";
 787                         $alias = "";
 788
 789                         $skip_next=false;
 790                         $i=0;
 791                         $join_type = '';
 792                         $ref_type="";
 793                         $ref_expr="";
 794                         $base_expr="";
 795                         $sub_tree = false;
 796                         $subquery = "";
 797
 798                         $first_join=true;
 799                         $modifier="";
 800                         $saved_join_type="";
 801
 802                         foreach($tokens as $token) {
 803                                 $base_expr = false;
 804                                 $upper = strtoupper(trim($token));
 805
 806                                 if($skip_next && $token) {
 807                                         $token_count++;
 808                                         $skip_next = false;
 809                                         continue;
 810                                 } else {
 811                                         if($skip_next) {
 812                                                 continue;
 813                                         }
 814                                 }
 815
 816                                 if(preg_match("/^\\s*\\(\\s*select/i",$token)) {
 817                                         $type = 'subquery';
 818                                         $table = "DEPENDENT-SUBQUERY";
 819                                         $sub_tree = $this->parse($this->trimSubquery($token));
 820                                         $subquery = $token;
 821                                 }
 822
 823                                 switch($upper) {
 824                                         case 'OUTER':
 825                                         case 'LEFT':
 826                                         case 'RIGHT':
 827                                         case 'NATURAL':
 828                                         case 'CROSS':
 829                                         case ',':
 830                                         case 'JOIN':
 831                                                 break;
 832
 833                                         default:
 834                                                 $expression .= $token == '' ? " " : $token;
 835                                                 if($ref_type) {
 836                                                         $ref_expr .= $token == '' ? " " : $token;
 837                                                 }
 838                                         break;
 839                                 }
 840
 841                                 switch($upper) {
 842                                         case 'AS':
 843                                                 $token_count++;
 844                                                 $n=1;
 845                                                 $alias = "";
 846                                                 while($alias == "") {
 847                                                         $alias = trim($tokens[$i+$n]);
 848                                                         ++$n;
 849                                                 }
 850
 851                                                 continue;
 852                                         break;
 853
 854                                         case 'INDEX':
 855                                                 if($token_category == 'CREATE') {
 856                                                         $token_category = $upper;
 857                                                         continue 2;
 858                                                 }
 859
 860                                         break;
 861
 862                                         case 'USING':
 863                                         case 'ON':
 864                                                 $ref_type = $upper;
 865                                                 $ref_expr = "";
 866
 867                                         case 'CROSS':
 868                                         case 'USE':
 869                                         case 'FORCE':
 870                                         case 'IGNORE':
 871                                         case 'INNER':
 872                                         case 'OUTER':
 873                                         #       $expression .= $token;
 874                                                 $token_count++;
 875                                                 continue;
 876                                         break;
 877
 878
 879
 880                                         case 'FOR':
 881                                                 $token_count++;
 882                                                 $skip_next = true;
 883                                                 continue;
 884                                         break;
 885
 886                                         case 'LEFT':
 887                                         case 'RIGHT':
 888                                         case 'STRAIGHT_JOIN':
 889                                                 $join_type=$saved_join_type;
 890
 891                                                 $modifier = $upper . " ";
 892                                                 break;
 893
 894
 895                                         case ',':
 896                                                 $modifier = 'CROSS';
 897
 898                                         case 'JOIN':
 899
 900                                                 if($first_join) {
 901                                                         $join_type = 'JOIN';
 902                                                         $saved_join_type = ($modifier ? $modifier : 'JOIN');
 903                                                 }  else {
 904                                                         $new_join_type = ($modifier ? $modifier : 'JOIN');
 905                                                         $join_type = $saved_join_type;
 906                                                         $saved_join_type = $new_join_type;
 907                                                         unset($new_join_type);
 908                                                 }
 909
 910                                                 $first_join = false;
 911
 912                                                 if(!trim($alias)) $alias = $table;
 913
 914                                                 if($subquery) {
 915                                                         $sub_tree = $this->parse(trim($subquery,'()'));
 916                                                         $base_expr=$subquery;
 917                                                 }
 918
 919                                                 if(substr(trim($table),0,1) == '(') {
 920                                                         $base_expr=$this->trimSubquery(trim);
 921                                                         $join_type = 'JOIN';
 922                                                         $sub_tree = $this->process_from($this->split_sql($base_expr));
 923                                                         $alias="";
 924                                                 }
 925
 926
 927                                                 if($join_type == "") $join_type='JOIN';
 928                                                 $expr[] = array('table'=>$table, 'alias'=>$alias,'join_type'=>$join_type,'ref_type'=> $ref_type,'ref_clause'=>$this->trimSubquery($ref_expr), 'base_expr' => $base_expr, 'sub_tree' => $sub_tree);
 929                                                 $modifier = "";
 930                                                 #$join_type=$saved_join_type;
 931
 932
 933                                                 $token_count = 0;
 934                                                 $table = $alias = $expression = $base_expr = $ref_type = $ref_expr = "";
 935                                                 $sub_tree=false;
 936                                                 $subquery = "";
 937
 938                                         break;
 939
 940
 941                                         default:
 942                                                 if($token === false || empty($token) || $token === "") continue;
 943
 944                                                 if($token_count == 0 ) {
 945                                                         if(!$table) {
 946                                                                 $table = $token ;
 947                                                         }
 948                                                 } else if($token_count == 1) {
 949                                                         $alias = $token;
 950                                                 }
 951                                                 $token_count++;
 952                                         break;
 953                                 }
 954                                 ++$i;
 955                         }
 956                         if(substr(trim($table),0,1) == '(') {
 957                                 $base_expr=$this->trimSubquery($table);
 958                                 $join_type = 'JOIN';
 959                                 $sub_tree = $this->process_from($this->split_sql($base_expr));
 960                                 $alias = "";
 961                         } else {
 962                                 if(!trim($alias)) $alias = $table;
 963                         }
 964                         if($join_type == "") $saved_join_type='JOIN';
 965
 966                         $expr[] = array('table'=>$table, 'alias'=>$alias,'join_type'=>$saved_join_type,'ref_type'=> $ref_type,'ref_clause'=> $this->trimSubquery($ref_expr), 'base_expr' => $base_expr, 'sub_tree' => $sub_tree);
 967
 968
 969                         return $expr;
 970                 }
 971
 972                 private function process_group(&$tokens, &$select) {
 973
 974                         $out=array();
 975                         $expression = "";
 976                         $direction="ASC";
 977                         $type = "expression";
 978                         if(!$tokens) return false;
 979
 980                         foreach($tokens as $token) {
 981                                 switch(strtoupper($token)) {
 982                                         case ',':
 983                                                 $expression = trim($expression);
 984                                                 if($expression[0] != '`' || substr($expression,-1) != '`') {
 985                                                         $escaped = str_replace('`','``',$expression);
 986                                                 } else {
 987                                                         $escaped = $expression;
 988                                                 }
 989                                                 $escaped = '`' . $escaped . '`';
 990
 991                                                 if(is_numeric(trim($expression))) {
 992                                                         $type = 'pos';
 993                                                 } else {
 994
 995                                                         #search to see if the expression matches an alias
 996                                                         foreach($select as $clause) {
 997                                                                 if($clause['alias'] == $escaped) {
 998                                                                         $type = 'alias';
 999                                                                 }
1000                                                         }
1001
1002                                                         if(!$type) $type = "expression";
1003                                                 }
1004
1005                                                 $out[]=array('type'=>$type,'base_expr'=>$expression,'direction'=>$direction);
1006                                                 $escaped = "";
1007                                                 $expression = "";
1008                                                 $direction = "ASC";
1009                                                 $type = "";
1010                                         break;
1011
1012                                         case 'ASC':
1013                                                 $direction = "ASC";
1014                                                 break;
1015                                         case 'DESC':
1016                                                 $direction = "DESC";
1017                                             break;
1018
1019                                         default:
1020                                                 $expression .= $token == '' ? ' ' : $token;
1021
1022
1023                                 }
1024                         }
1025                         if($expression) {
1026                                         $expression = trim($expression);
1027                                         if($expression[0] != '`' || substr($expression,-1) != '`') {
1028                                                 $escaped = str_replace('`','``',$expression);
1029                                         } else {
1030                                                 $escaped = $expression;
1031                                         }
1032                                         $escaped = '`' . $escaped . '`';
1033
1034                                         if(is_numeric(trim($expression))) {
1035                                                 $type = 'pos';
1036                                         } else {
1037
1038                                                 #search to see if the expression matches an alias
1039                                                 if(!$type && $select) {
1040                                                         foreach($select as $clause) {
1041                                                                 if(!is_array($clause)) continue;
1042                                                                 if($clause['alias'] == $escaped) {
1043                                                                         $type = 'alias';
1044                                                                 }
1045                                                         }
1046                                                 } else {
1047                                                         $type="expression";
1048                                                 }
1049
1050                                                 if(!$type) $type = "expression";
1051                                         }
1052
1053                                         $out[]=array('type'=>$type,'base_expr'=>$expression,'direction'=>$direction);
1054                         }
1055                         foreach($out as &$term) {
1056                             if(!empty($term['base_expr'])) {
1057                                 $term['sub_tree'] = array($this->process_select_expr($term['base_expr']));
1058                             }
1059                         }
1060
1061                         return $out;
1062                 }
1063
1064                 /* Some sections are just lists of expressions, like the WHERE and HAVING clauses.  This function
1065                    processes these sections.  Recursive.
1066                 */
1067                 private function process_expr_list($tokens) {
1068                         $expr = "";
1069                         $type = "";
1070                         $prev_token = "";
1071                         $skip_next = false;
1072                         $sub_expr = "";
1073
1074                         $in_lists = array();
1075                         foreach($tokens as $key => $token) {
1076
1077                                 if(strlen(trim($token))==0) continue;
1078                                 if($skip_next) {
1079                                         $skip_next = false;
1080                                         continue;
1081                                 }
1082
1083                                 $processed = false;
1084                                 $upper = strtoupper(trim($token));
1085                                 if(trim($token)) $token=trim($token);
1086
1087                                 /* is it a subquery?*/
1088                                 if(preg_match("/^\\s*\\(\\s*SELECT/i", $token)) {
1089                                         $type = 'subquery';
1090                                         #tokenize and parse the subquery.
1091                                         #we remove the enclosing parenthesis for the tokenizer
1092                                         $processed = $this->parse($this->trimSubquery($token));
1093
1094
1095                                 /* is it an inlist */
1096                                 } elseif( $upper[0] == '(' && substr($upper,-1) == ')' ) {
1097                                         if($prev_token == 'IN') {
1098                                                 $type = "in-list";
1099                                                 $processed = $this->split_sql(substr($token,1,-1));
1100                                                 $list = array();
1101                                                 foreach($processed as $v) {
1102                                                         if($v == ',') continue;
1103                                                         $list[]=$v;
1104                                                 }
1105                                                 $processed = $list;
1106                                                 unset($list);
1107                                                 $prev_token = "";
1108
1109                                        }
1110                                        elseif($prev_token == 'AGAINST') {
1111                                                $type = "match-arguments";
1112                                                $list = $this->split_sql(substr($token,1,-1));
1113                                                if(count($list) > 1){
1114                                                        $match_mode = implode('',array_slice($list,1));
1115                                                        $processed = array($list[0], $match_mode);
1116                                                }
1117                                                else
1118                                                        $processed = $list[0];
1119                                                $prev_token = "";
1120                                         }
1121
1122                                 /* it is either an operator, a colref or a constant */
1123                                 } else {
1124                                         switch($upper) {
1125                                         case 'AND':
1126                                         case '&&':
1127                                         case 'BETWEEN':
1128                                         case 'AND':
1129                                         case 'BINARY':
1130                                         case '&':
1131                                         case '~':
1132                                         case '|':
1133                                         case '^':
1134                                         case 'CASE':
1135                                         case 'WHEN':
1136                                         case 'END':
1137                                         case 'DIV':
1138                                         case '/':
1139                                         case '<=>':
1140                                         case '=':
1141                                         case '>=':
1142                                         case '>':
1143                                         case 'IS':
1144                                         case 'NOT':
1145                                         case 'NULL':
1146                                         case '<<':
1147                                         case '<=':
1148                                         case '<':
1149                                         case 'LIKE':
1150                                         case '-':
1151                                         case '%':
1152                                         case '!=':
1153                                         case '<>':
1154                                         case 'REGEXP':
1155                                         case '!':
1156                                         case '||':
1157                                         case 'OR':
1158                                         case '+':
1159                                         case '>>':
1160                                         case 'RLIKE':
1161                                         case 'SOUNDS':
1162                                         case '*':
1163                                         case '-':
1164                                         case 'XOR':
1165                                         case 'IN':
1166                                                         $processed = false;
1167                                                         $type = "operator";
1168                                                         break;
1169                                         default:
1170                                                 switch($token[0]) {
1171                                                         case "'":
1172                                                         case '"':
1173                                                                         $type = 'const';
1174                                                                         break;
1175                                                         case '`':
1176                                                                         $type = 'colref';
1177                                                                         break;
1178
1179                                                         default:
1180                                                                 if(is_numeric($token)) {
1181                                                                         $type = 'const';
1182                                                                 } else {
1183                                                                         $type = 'colref';
1184                                                                 }
1185                                                         break;
1186
1187                                                 }
1188                                                 #$processed = $token;
1189                                                 $processed = false;
1190                                         }
1191                                 }
1192                                 /* is a reserved word? */
1193                                 if(($type != 'operator' && $type != 'in-list' && $type != 'sub_expr') && in_array($upper, $this->reserved)) {
1194                                         $token = $upper;
1195                                         if(!in_array($upper,$this->functions)) {
1196                                                 $type = 'reserved';
1197                                         } else {
1198                                                 switch($token) {
1199                                                         case 'AVG':
1200                                                         case 'SUM':
1201                                                         case 'COUNT':
1202                                                         case 'MIN':
1203                                                         case 'MAX':
1204                                                         case 'STDDEV':
1205                                                         case 'STDDEV_SAMP':
1206                                                         case 'STDDEV_POP':
1207                                                         case 'VARIANCE':
1208                                                         case 'VAR_SAMP':
1209                                                         case 'VAR_POP':
1210                                                         case 'GROUP_CONCAT':
1211                                                         case 'BIT_AND':
1212                                                         case 'BIT_OR':
1213                                                         case 'BIT_XOR':
1214                                                                 $type = 'aggregate_function';
1215                                                                 if(!empty($tokens[$key+1])) $sub_expr = $tokens[$key+1];
1216                                                                 #$skip_next=true;
1217                                                         break;
1218
1219                                                         default:
1220                                                                 $type = 'function';
1221                                                                 if(!empty($tokens[$key+1])) $sub_expr = $tokens[$key+1]; else $sub_expr="()";
1222                                                                 #$skip_next=true;
1223
1224
1225                                                         break;
1226                                                 }
1227                                         }
1228                                 }
1229
1230                                 if(!$type) {
1231                                         if($upper[0] == '(') {
1232                                                 $local_expr = substr(trim($token),1,-1);
1233                                         } else {
1234                                                 $local_expr = $token;
1235                                         }
1236                     $processed = $this->process_expr_list($this->split_sql($local_expr));
1237                                         $type = 'expression';
1238
1239 //                                      if(count($processed) == 1) {
1240 //                                              $type = $processed[0]['expr_type'];
1241 //                                              $base_expr  = $processed[0]['base_expr'];
1242 //                                              $processed = $processed[0]['sub_tree'];
1243 //                                      }
1244
1245                                 }
1246
1247                                 $sub_expr=trim($sub_expr);
1248                                 $sub_expr = "";
1249
1250                                 $expr[] = array( 'expr_type' => $type, 'base_expr' => $token, 'sub_tree' => $processed);
1251                                 $prev_token = $upper;
1252                                 $expr_type = "";
1253                                 $type = "";
1254                         }
1255                         if($sub_expr) {
1256                                 $processed['sub_tree'] = $this->process_expr_list($this->split_sql(substr($sub_expr,1,-1)));
1257                         }
1258
1259                         if(!is_array($processed)) {
1260                                 $processed = false;
1261                         }
1262
1263                         if($expr_type) {
1264                                 $expr[] = array( 'expr_type' => $type, 'base_expr' => $token, 'sub_tree' => $processed);
1265                         }
1266                         $mod = false;
1267
1268         /*
1269
1270                         for($i=0;$i<count($expr);++$i){
1271                                 if($expr[$i]['expr_type'] == 'function' ||
1272                                    $expr[$i]['expr_type'] == 'aggregate_function') {
1273                                         if(!empty($expr[$i+1])) {
1274                                                 $expr[$i]['sub_tree']=$expr[$i+1]['sub_tree'];
1275                                                 unset($expr[$i+1]);
1276                                                 $mod = 1;
1277                                                 ++$i;  // BAD FORM TO MODIFY THE LOOP COUNTER
1278                                         }
1279                                 }
1280
1281                         }
1282
1283         */
1284
1285                         if($mod) $expr=array_values($expr);
1286
1287
1288                         return $expr;
1289                 }
1290
1291                 private function process_update($tokens) {
1292
1293                 }
1294
1295                 private function process_delete($tokens) {
1296                         $tables = array();
1297                         $del = $tokens['DELETE'];
1298
1299                         foreach($tokens['DELETE'] as $expression) {
1300                                 if ($expression != 'DELETE' && trim($expression,' .*') != "" && $expression != ',') {
1301                                         $tables[] = trim($expression,'.* ');
1302                                 }
1303                         }
1304
1305                         if(empty($tables)) {
1306                                 foreach($tokens['FROM'] as $table) {
1307                                         $tables[] = $table['table'];
1308                                 }
1309                         }
1310
1311                         $tokens['DELETE'] = array('TABLES' => $tables);
1312
1313                         return $tokens;
1314                 }
1315
1316                 private function process_insert($tokens, $token_category = 'INSERT') {
1317                         $table = "";
1318                         $cols = "";
1319
1320                         $into = $tokens['INTO'];
1321                         foreach($into as $token) {
1322                                 if(!trim($token)) continue;
1323                                 if(!$table) {
1324                                         $table = $token;
1325                                 }elseif(!$cols) {
1326                                         $cols = $token;
1327                                 }
1328                         }
1329
1330                         if(!$cols) {
1331                                 $cols = 'ALL';
1332                         } else {
1333                                 $cols = explode(",", $this->trimSubquery($cols));
1334                         }
1335                         unset($tokens['INTO']);
1336                         $tokens[$token_category] =  array('table'=>$table, 'cols'=>$cols);
1337                         return $tokens;
1338
1339                 }
1340
1341
1342                 private function load_reserved_words() {
1343
1344                     $this->functions = array(
1345                         'abs',
1346                         'acos',
1347                         'adddate',
1348                         'addtime',
1349                         'aes_encrypt',
1350                         'aes_decrypt',
1351                         'against',
1352                         'ascii',
1353                         'asin',
1354                         'atan',
1355                         'avg',
1356                         'benchmark',
1357                         'bin',
1358                         'bit_and',
1359                         'bit_or',
1360                         'bitcount',
1361                         'bitlength',
1362                         'cast',
1363                         'ceiling',
1364                         'char',
1365                         'char_length',
1366                         'character_length',
1367                         'charset',
1368                         'coalesce',
1369                         'coercibility',
1370                         'collation',
1371                         'compress',
1372                         'concat',
1373                         'concat_ws',
1374                         'conection_id',
1375                         'conv',
1376                         'convert',
1377                         'convert_tz',
1378                         'cos',
1379                         'cot',
1380                         'count',
1381                         'crc32',
1382                         'curdate',
1383                         'current_user',
1384                         'currval',
1385                         'curtime',
1386                         'database',
1387                         'date_add',
1388                         'date_diff',
1389                         'date_format',
1390                         'date_sub',
1391                         'day',
1392                         'dayname',
1393                         'dayofmonth',
1394                         'dayofweek',
1395                         'dayofyear',
1396                         'decode',
1397                         'default',
1398                         'degrees',
1399                         'des_decrypt',
1400                         'des_encrypt',
1401                         'elt',
1402                         'encode',
1403                         'encrypt',
1404                         'exp',
1405                         'export_set',
1406                         'extract',
1407                         'field',
1408                         'find_in_set',
1409                         'floor',
1410                         'format',
1411                         'found_rows',
1412                         'from_days',
1413                         'from_unixtime',
1414                         'get_format',
1415                         'get_lock',
1416                         'group_concat',
1417                         'greatest',
1418                         'hex',
1419                         'hour',
1420                         'if',
1421                         'ifnull',
1422                         'in',
1423                         'inet_aton',
1424                         'inet_ntoa',
1425                         'insert',
1426                         'instr',
1427                         'interval',
1428                         'is_free_lock',
1429                         'is_used_lock',
1430                         'last_day',
1431                         'last_insert_id',
1432                         'lcase',
1433                         'least',
1434                         'left',
1435                         'length',
1436                         'ln',
1437                         'load_file',
1438                         'localtime',
1439                         'localtimestamp',
1440                         'locate',
1441                         'log',
1442                         'log2',
1443                         'log10',
1444                         'lower',
1445                         'lpad',
1446                         'ltrim',
1447                         'make_set',
1448                         'makedate',
1449                         'maketime',
1450                         'master_pos_wait',
1451                         'match',
1452                         'max',
1453                         'md5',
1454                         'microsecond',
1455                         'mid',
1456                         'min',
1457                         'minute',
1458                         'mod',
1459                         'month',
1460                         'monthname',
1461                         'nextval',
1462                         'now',
1463                         'nullif',
1464                         'oct',
1465                         'octet_length',
1466                         'old_password',
1467                         'ord',
1468                         'password',
1469                         'period_add',
1470                         'period_diff',
1471                         'pi',
1472                         'position',
1473                         'pow',
1474                         'power',
1475                         'quarter',
1476                         'quote',
1477                         'radians',
1478                         'rand',
1479                         'release_lock',
1480                         'repeat',
1481                         'replace',
1482                         'reverse',
1483                         'right',
1484                         'round',
1485                         'row_count',
1486                         'rpad',
1487                         'rtrim',
1488                         'sec_to_time',
1489                         'second',
1490                         'session_user',
1491                         'sha',
1492                         'sha1',
1493                         'sign',
1494                         'soundex',
1495                         'space',
1496                         'sqrt',
1497                         'std',
1498                         'stddev',
1499                         'stddev_pop',
1500                         'stddev_samp',
1501                         'strcmp',
1502                         'str_to_date',
1503                         'subdate',
1504                         'substr',
1505                         'substring',
1506                         'substring_index',
1507                         'subtime',
1508                         'sum',
1509                         'sysdate',
1510                         'system_user',
1511                         'tan',
1512                         'time',
1513                         'timediff',
1514                         'timestamp',
1515                         'timestampadd',
1516                         'timestampdiff',
1517                         'time_format',
1518                         'time_to_sec',
1519                         'to_days',
1520                         'trim',
1521                         'truncate',
1522                         'ucase',
1523                         'uncompress',
1524                         'uncompressed_length',
1525                         'unhex',
1526                         'unix_timestamp',
1527                         'upper',
1528                         'user',
1529                         'utc_date',
1530                         'utc_time',
1531                         'utc_timestamp',
1532                         'uuid',
1533                         'var_pop',
1534                         'var_samp',
1535                         'variance',
1536                         'version',
1537                         'week',
1538                         'weekday',
1539                         'weekofyear',
1540                         'year',
1541                         'yearweek');
1542
1543                 /* includes functions */
1544                 $this->reserved = array(
1545                         'abs',
1546                         'acos',
1547                         'adddate',
1548                         'addtime',
1549                         'aes_encrypt',
1550                         'aes_decrypt',
1551                         'against',
1552                         'ascii',
1553                         'asin',
1554                         'atan',
1555                         'avg',
1556                         'benchmark',
1557                         'bin',
1558                         'bit_and',
1559                         'bit_or',
1560                         'bitcount',
1561                         'bitlength',
1562                         'cast',
1563                         'ceiling',
1564                         'char',
1565                         'char_length',
1566                         'character_length',
1567                         'charset',
1568                         'coalesce',
1569                         'coercibility',
1570                         'collation',
1571                         'compress',
1572                         'concat',
1573                         'concat_ws',
1574                         'conection_id',
1575                         'conv',
1576                         'convert',
1577                         'convert_tz',
1578                         'cos',
1579                         'cot',
1580                         'count',
1581                         'crc32',
1582                         'curdate',
1583                         'current_user',
1584                         'currval',
1585                         'curtime',
1586                         'database',
1587                         'date_add',
1588                         'date_diff',
1589                         'date_format',
1590                         'date_sub',
1591                         'day',
1592                         'dayname',
1593                         'dayofmonth',
1594                         'dayofweek',
1595                         'dayofyear',
1596                         'decode',
1597                         'default',
1598                         'degrees',
1599                         'des_decrypt',
1600                         'des_encrypt',
1601                         'elt',
1602                         'encode',
1603                         'encrypt',
1604                         'exp',
1605                         'export_set',
1606                         'extract',
1607                         'field',
1608                         'find_in_set',
1609                         'floor',
1610                         'format',
1611                         'found_rows',
1612                         'from_days',
1613                         'from_unixtime',
1614                         'get_format',
1615                         'get_lock',
1616                         'group_concat',
1617                         'greatest',
1618                         'hex',
1619                         'hour',
1620                         'if',
1621                         'ifnull',
1622                         'in',
1623                         'inet_aton',
1624                         'inet_ntoa',
1625                         'insert',
1626                         'instr',
1627                         'interval',
1628                         'is_free_lock',
1629                         'is_used_lock',
1630                         'last_day',
1631                         'last_insert_id',
1632                         'lcase',
1633                         'least',
1634                         'left',
1635                         'length',
1636                         'ln',
1637                         'load_file',
1638                         'localtime',
1639                         'localtimestamp',
1640                         'locate',
1641                         'log',
1642                         'log2',
1643                         'log10',
1644                         'lower',
1645                         'lpad',
1646                         'ltrim',
1647                         'make_set',
1648                         'makedate',
1649                         'maketime',
1650                         'master_pos_wait',
1651                         'match',
1652                         'max',
1653                         'md5',
1654                         'microsecond',
1655                         'mid',
1656                         'min',
1657                         'minute',
1658                         'mod',
1659                         'month',
1660                         'monthname',
1661                         'nextval',
1662                         'now',
1663                         'nullif',
1664                         'oct',
1665                         'octet_length',
1666                         'old_password',
1667                         'ord',
1668                         'password',
1669                         'period_add',
1670                         'period_diff',
1671                         'pi',
1672                         'position',
1673                         'pow',
1674                         'power',
1675                         'quarter',
1676                         'quote',
1677                         'radians',
1678                         'rand',
1679                         'release_lock',
1680                         'repeat',
1681                         'replace',
1682                         'reverse',
1683                         'right',
1684                         'round',
1685                         'row_count',
1686                         'rpad',
1687                         'rtrim',
1688                         'sec_to_time',
1689                         'second',
1690                         'session_user',
1691                         'sha',
1692                         'sha1',
1693                         'sign',
1694                         'soundex',
1695                         'space',
1696                         'sqrt',
1697                         'std',
1698                         'stddev',
1699                         'stddev_pop',
1700                         'stddev_samp',
1701                         'strcmp',
1702                         'str_to_date',
1703                         'subdate',
1704                         'substring',
1705                         'substring_index',
1706                         'subtime',
1707                         'sum',
1708                         'sysdate',
1709                         'system_user',
1710                         'tan',
1711                         'time',
1712                         'timediff',
1713                         'timestamp',
1714                         'timestampadd',
1715                         'timestampdiff',
1716                         'time_format',
1717                         'time_to_sec',
1718                         'to_days',
1719                         'trim',
1720                         'truncate',
1721                         'ucase',
1722                         'uncompress',
1723                         'uncompressed_length',
1724                         'unhex',
1725                         'unix_timestamp',
1726                         'upper',
1727                         'user',
1728                         'utc_date',
1729                         'utc_time',
1730                         'utc_timestamp',
1731                         'uuid',
1732                         'var_pop',
1733                         'var_samp',
1734                         'variance',
1735                         'version',
1736                         'week',
1737                         'weekday',
1738                         'weekofyear',
1739                         'year',
1740                         'yearweek',
1741                         'add',
1742                         'all',
1743                         'alter',
1744                         'analyze',
1745                         'and',
1746                         'as',
1747                         'asc',
1748                         'asensitive',
1749                         'auto_increment',
1750                         'bdb',
1751                         'before',
1752                         'berkeleydb',
1753                         'between',
1754                         'bigint',
1755                         'binary',
1756                         'blob',
1757                         'both',
1758                         'by',
1759                         'call',
1760                         'cascade',
1761                         'case',
1762                         'change',
1763                         'char',
1764                         'character',
1765                         'check',
1766                         'collate',
1767                         'column',
1768                         'columns',
1769                         'condition',
1770                         'connection',
1771                         'constraint',
1772                         'continue',
1773                         'create',
1774                         'cross',
1775                         'current_date',
1776                         'current_time',
1777                         'current_timestamp',
1778                         'cursor',
1779                         'database',
1780                         'databases',
1781                         'day_hour',
1782                         'day_microsecond',
1783                         'day_minute',
1784                         'day_second',
1785                         'dec',
1786                         'decimal',
1787                         'declare',
1788                         'default',
1789                         'delayed',
1790                         'delete',
1791                         'desc',
1792                         'describe',
1793                         'deterministic',
1794                         'distinct',
1795                         'distinctrow',
1796                         'div',
1797                         'double',
1798                         'drop',
1799                         'else',
1800                         'elseif',
1801                         'enclosed',
1802                         'escaped',
1803                         'exists',
1804                         'exit',
1805                         'explain',
1806                         'false',
1807                         'fetch',
1808                         'fields',
1809                         'float',
1810                         'for',
1811                         'force',
1812                         'foreign',
1813                         'found',
1814                         'frac_second',
1815                         'from',
1816                         'fulltext',
1817                         'grant',
1818                         'group',
1819                         'having',
1820                         'high_priority',
1821                         'hour_microsecond',
1822                         'hour_minute',
1823                         'hour_second',
1824                         'if',
1825                         'ignore',
1826                         'in',
1827                         'index',
1828                         'infile',
1829                         'inner',
1830                         'innodb',
1831                         'inout',
1832                         'insensitive',
1833                         'insert',
1834                         'int',
1835                         'integer',
1836                         'interval',
1837                         'into',
1838                         'io_thread',
1839                         'is',
1840                         'iterate',
1841                         'join',
1842                         'key',
1843                         'keys',
1844                         'kill',
1845                         'leading',
1846                         'leave',
1847                         'left',
1848                         'like',
1849                         'limit',
1850                         'lines',
1851                         'load',
1852                         'localtime',
1853                         'localtimestamp',
1854                         'lock',
1855                         'long',
1856                         'longblob',
1857                         'longtext',
1858                         'loop',
1859                         'low_priority',
1860                         'master_server_id',
1861                         'match',
1862                         'mediumblob',
1863                         'mediumint',
1864                         'mediumtext',
1865                         'middleint',
1866                         'minute_microsecond',
1867                         'minute_second',
1868                         'mod',
1869                         'natural',
1870                         'not',
1871                         'no_write_to_binlog',
1872                         'null',
1873                         'numeric',
1874                         'on',
1875                         'optimize',
1876                         'option',
1877                         'optionally',
1878                         'or',
1879                         'order',
1880                         'out',
1881                         'outer',
1882                         'outfile',
1883                         'precision',
1884                         'primary',
1885                         'privileges',
1886                         'procedure',
1887                         'purge',
1888                         'read',
1889                         'real',
1890                         'references',
1891                         'regexp',
1892                         'rename',
1893                         'repeat',
1894                         'replace',
1895                         'require',
1896                         'restrict',
1897                         'return',
1898                         'revoke',
1899                         'right',
1900                         'rlike',
1901                         'second_microsecond',
1902                         'select',
1903                         'sensitive',
1904                         'separator',
1905                         'set',
1906                         'show',
1907                         'smallint',
1908                         'some',
1909                         'soname',
1910                         'spatial',
1911                         'specific',
1912                         'sql',
1913                         'sqlexception',
1914                         'sqlstate',
1915                         'sqlwarning',
1916                         'sql_big_result',
1917                         'sql_calc_found_rows',
1918                         'sql_small_result',
1919                         'sql_tsi_day',
1920                         'sql_tsi_frac_second',
1921                         'sql_tsi_hour',
1922                         'sql_tsi_minute',
1923                         'sql_tsi_month',
1924                         'sql_tsi_quarter',
1925                         'sql_tsi_second',
1926                         'sql_tsi_week',
1927                         'sql_tsi_year',
1928                         'ssl',
1929                         'starting',
1930                         'straight_join',
1931                         'striped',
1932                         'table',
1933                         'tables',
1934                         'terminated',
1935                         'then',
1936                         'timestampadd',
1937                         'timestampdiff',
1938                         'tinyblob',
1939                         'tinyint',
1940                         'tinytext',
1941                         'to',
1942                         'trailing',
1943                         'true',
1944                         'undo',
1945                         'union',
1946                         'unique',
1947                         'unlock',
1948                         'unsigned',
1949                         'update',
1950                         'usage',
1951                         'use',
1952                         'user_resources',
1953                         'using',
1954                         'utc_date',
1955                         'utc_time',
1956                         'utc_timestamp',
1957                         'values',
1958                         'varbinary',
1959                         'varchar',
1960                         'varcharacter',
1961                         'varying',
1962                         'when',
1963                         'where',
1964                         'while',
1965                         'with',
1966                         'write',
1967                         'xor',
1968                         'year_month',
1969                         'zerofill'
1970                         );
1971
1972                         for($i=0;$i<count($this->reserved);++$i) {
1973                                 $this->reserved[$i]=strtoupper($this->reserved[$i]);
1974                                 if(!empty($this->functions[$i])) $this->functions[$i] = strtoupper($this->functions[$i]);
1975                         }
1976                 }
1977
1978         } // END CLASS
1979