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                         $expression = trim($expression);
 666                         if($expression) $expr[] = $this->process_select_expr($expression);
 667                         return $expr;
 668                 }
 669
 670                 /* This function processes each SELECT clause.  We determine what (if any) alias
 671                    is provided, and we set the type of expression.
 672                 */
 673                 private function process_select_expr($expression) {
 674
 675             if (empty($expression)){
 676                 return '';
 677             }
 678
 679                         $capture = false;
 680                         $alias = "";
 681                         $base_expression = $expression;
 682                         $upper = trim(strtoupper($expression));
 683                         #if necessary, unpack the expression
 684                         if($upper[0] == '(') {
 685                                 #$expression = substr($expression,1,-1);
 686                                 $base_expression = $expression;
 687                         }
 688
 689                         $tokens = $this->split_sql($expression);
 690                         $token_count = count($tokens);
 691
 692                         /* Determine if there is an explicit alias after the AS clause.
 693                         If AS is found, then the next non-whitespace token is captured as the alias.
 694                         The tokens after (and including) the AS are removed.
 695                         */
 696                         $base_expr = "";
 697                         $stripped=array();
 698                         $capture=false;
 699                         $alias = "";
 700                         $processed=false;
 701                         for($i=0;$i<$token_count;++$i) {
 702                                 $token = strtoupper($tokens[$i]);
 703                                 if(trim($token)) {
 704                                         $stripped[] = $tokens[$i];
 705                                 }
 706
 707                                 if($token == 'AS') {
 708                                         unset($tokens[$i]);
 709                                         $capture = true;
 710                                         continue;
 711                                 }
 712
 713                                 if($capture) {
 714                                         if(trim($token)) {
 715                                                 $alias .= $tokens[$i];
 716                                         }
 717                                         unset($tokens[$i]);
 718                                         continue;
 719                                 }
 720                                 $base_expr .= $tokens[$i];
 721                         }
 722
 723                         $stripped = $this->process_expr_list($stripped);
 724                         $last = array_pop($stripped);
 725                         if(!$alias && $last['expr_type'] == 'colref') {
 726                                 $prev = array_pop($stripped);
 727                                 if($prev['expr_type'] == 'operator' ||
 728                                    $prev['expr_type'] == 'const' ||
 729                                    $prev['expr_type'] == 'function' ||
 730                                    $prev['expr_type'] == 'expression' ||
 731                                    #$prev['expr_type'] == 'aggregate_function' ||
 732                                    $prev['expr_type'] == 'subquery' ||
 733                                    $prev['expr_type'] == 'colref') {
 734                                         $alias = $last['base_expr'];
 735
 736                                         #remove the last token
 737                                         array_pop($tokens);
 738
 739                                         $base_expr = join("", $tokens);
 740
 741
 742                                 }
 743                         }
 744
 745                         if(!$alias) {
 746                                 $base_expr=join("", $tokens);
 747                                 $alias = $base_expr;
 748                         }
 749
 750                         /* Properly escape the alias if it is not escaped */
 751                         if ($alias[0] != '`') {
 752                                         $alias = '`' . str_replace('`','``',$alias) . '`';
 753                         }
 754                         $processed = false;
 755                         $type='expression';
 756
 757                         if(substr(trim($base_expr),0,1) == '(') {
 758                                 $base_expr = substr($expression,1,-1);
 759                                 if(preg_match('/^sel/i', $base_expr)) {
 760                                         $type='subquery';
 761                                         $processed = $this->parse($base_expr);
 762                                 }
 763                         }
 764                         if(!$processed) {
 765                                 $processed = $this->process_expr_list($tokens);
 766                         }
 767
 768                         if(count($processed) == 1) {
 769                                 $type = $processed[0]['expr_type'];
 770                                 $processed = false;
 771                         }
 772
 773                         return array('expr_type'=>$type,'alias' => $alias, 'base_expr' => $base_expr, 'sub_tree' => $processed);
 774
 775                 }
 776
 777                 private function trimSubquery($sq)
 778                 {
 779                     $sq = trim($sq);
 780                     if(empty($sq)) return '';
 781             while($sq[0] == '(' && substr($sq, -1) == ')') {
 782                 $sq = substr($sq, 1, -1);
 783             }
 784             return $sq;
 785                 }
 786
 787                 private function process_from(&$tokens) {
 788
 789                         $expression = "";
 790                         $expr = array();
 791                         $token_count=0;
 792                         $table = "";
 793                         $alias = "";
 794
 795                         $skip_next=false;
 796                         $i=0;
 797                         $join_type = '';
 798                         $ref_type="";
 799                         $ref_expr="";
 800                         $base_expr="";
 801                         $sub_tree = false;
 802                         $subquery = "";
 803
 804                         $first_join=true;
 805                         $modifier="";
 806                         $saved_join_type="";
 807
 808                         foreach($tokens as $token) {
 809                                 $base_expr = false;
 810                                 $upper = strtoupper(trim($token));
 811
 812                                 if($skip_next && $token) {
 813                                         $token_count++;
 814                                         $skip_next = false;
 815                                         continue;
 816                                 } else {
 817                                         if($skip_next) {
 818                                                 continue;
 819                                         }
 820                                 }
 821
 822                                 if(preg_match("/^\\s*\\(\\s*select/i",$token)) {
 823                                         $type = 'subquery';
 824                                         $table = "DEPENDENT-SUBQUERY";
 825                                         $sub_tree = $this->parse($this->trimSubquery($token));
 826                                         $subquery = $token;
 827                                 }
 828
 829                                 switch($upper) {
 830                                         case 'OUTER':
 831                                         case 'LEFT':
 832                                         case 'RIGHT':
 833                                         case 'NATURAL':
 834                                         case 'CROSS':
 835                                         case ',':
 836                                         case 'JOIN':
 837                                                 break;
 838
 839                                         default:
 840                                                 $expression .= $token == '' ? " " : $token;
 841                                                 if($ref_type) {
 842                                                         $ref_expr .= $token == '' ? " " : $token;
 843                                                 }
 844                                         break;
 845                                 }
 846
 847                                 switch($upper) {
 848                                         case 'AS':
 849                                                 $token_count++;
 850                                                 $n=1;
 851                                                 $alias = "";
 852                                                 while($alias == "") {
 853                                                         $alias = trim($tokens[$i+$n]);
 854                                                         ++$n;
 855                                                 }
 856
 857                                                 continue;
 858                                         break;
 859
 860                                         case 'INDEX':
 861                                                 if($token_category == 'CREATE') {
 862                                                         $token_category = $upper;
 863                                                         continue 2;
 864                                                 }
 865
 866                                         break;
 867
 868                                         case 'USING':
 869                                         case 'ON':
 870                                                 $ref_type = $upper;
 871                                                 $ref_expr = "";
 872
 873                                         case 'CROSS':
 874                                         case 'USE':
 875                                         case 'FORCE':
 876                                         case 'IGNORE':
 877                                         case 'INNER':
 878                                         case 'OUTER':
 879                                         #       $expression .= $token;
 880                                                 $token_count++;
 881                                                 continue;
 882                                         break;
 883
 884
 885
 886                                         case 'FOR':
 887                                                 $token_count++;
 888                                                 $skip_next = true;
 889                                                 continue;
 890                                         break;
 891
 892                                         case 'LEFT':
 893                                         case 'RIGHT':
 894                                         case 'STRAIGHT_JOIN':
 895                                                 $join_type=$saved_join_type;
 896
 897                                                 $modifier = $upper . " ";
 898                                                 break;
 899
 900
 901                                         case ',':
 902                                                 $modifier = 'CROSS';
 903
 904                                         case 'JOIN':
 905
 906                                                 if($first_join) {
 907                                                         $join_type = 'JOIN';
 908                                                         $saved_join_type = ($modifier ? $modifier : 'JOIN');
 909                                                 }  else {
 910                                                         $new_join_type = ($modifier ? $modifier : 'JOIN');
 911                                                         $join_type = $saved_join_type;
 912                                                         $saved_join_type = $new_join_type;
 913                                                         unset($new_join_type);
 914                                                 }
 915
 916                                                 $first_join = false;
 917
 918                                                 if(!trim($alias)) $alias = $table;
 919
 920                                                 if($subquery) {
 921                                                         $sub_tree = $this->parse(trim($subquery,'()'));
 922                                                         $base_expr=$subquery;
 923                                                 }
 924
 925                                                 if(substr(trim($table),0,1) == '(') {
 926                                                         $base_expr=$this->trimSubquery($table);
 927                                                         $join_type = 'JOIN';
 928                                                         $sub_tree = $this->process_from($this->split_sql($base_expr));
 929                                                         $alias="";
 930                                                 }
 931
 932
 933                                                 if($join_type == "") $join_type='JOIN';
 934                                                 $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);
 935                                                 $modifier = "";
 936                                                 #$join_type=$saved_join_type;
 937
 938
 939                                                 $token_count = 0;
 940                                                 $table = $alias = $expression = $base_expr = $ref_type = $ref_expr = "";
 941                                                 $sub_tree=false;
 942                                                 $subquery = "";
 943
 944                                         break;
 945
 946
 947                                         default:
 948                                                 if($token === false || empty($token) || $token === "") continue;
 949
 950                                                 if($token_count == 0 ) {
 951                                                         if(!$table) {
 952                                                                 $table = $token ;
 953                                                         }
 954                                                 } else if($token_count == 1) {
 955                                                         $alias = $token;
 956                                                 }
 957                                                 $token_count++;
 958                                         break;
 959                                 }
 960                                 ++$i;
 961                         }
 962                         if(substr(trim($table),0,1) == '(') {
 963                                 $base_expr=$this->trimSubquery($table);
 964                                 $join_type = 'JOIN';
 965                                 $sub_tree = $this->process_from($this->split_sql($base_expr));
 966                                 $alias = "";
 967                         } else {
 968                                 if(!trim($alias)) $alias = $table;
 969                         }
 970                         if($join_type == "") $saved_join_type='JOIN';
 971
 972                         $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);
 973
 974
 975                         return $expr;
 976                 }
 977
 978                 private function process_group(&$tokens, &$select) {
 979
 980                         $out=array();
 981                         $expression = "";
 982                         $direction="ASC";
 983                         $type = "expression";
 984                         if(!$tokens) return false;
 985
 986                         foreach($tokens as $token) {
 987                                 switch(strtoupper($token)) {
 988                                         case ',':
 989                                                 $expression = trim($expression);
 990                                                 if($expression[0] != '`' || substr($expression,-1) != '`') {
 991                                                         $escaped = str_replace('`','``',$expression);
 992                                                 } else {
 993                                                         $escaped = $expression;
 994                                                 }
 995                                                 $escaped = '`' . $escaped . '`';
 996
 997                                                 if(is_numeric(trim($expression))) {
 998                                                         $type = 'pos';
 999                                                 } else {
1000
1001                                                         #search to see if the expression matches an alias
1002                                                         foreach($select as $clause) {
1003                                                                 if($clause['alias'] == $escaped) {
1004                                                                         $type = 'alias';
1005                                                                 }
1006                                                         }
1007
1008                                                         if(!$type) $type = "expression";
1009                                                 }
1010
1011                                                 $out[]=array('type'=>$type,'base_expr'=>$expression,'direction'=>$direction);
1012                                                 $escaped = "";
1013                                                 $expression = "";
1014                                                 $direction = "ASC";
1015                                                 $type = "";
1016                                         break;
1017
1018                                         case 'ASC':
1019                                                 $direction = "ASC";
1020                                                 break;
1021                                         case 'DESC':
1022                                                 $direction = "DESC";
1023                                             break;
1024
1025                                         default:
1026                                                 $expression .= $token == '' ? ' ' : $token;
1027
1028
1029                                 }
1030                         }
1031                         if($expression) {
1032                                         $expression = trim($expression);
1033                                         if($expression[0] != '`' || substr($expression,-1) != '`') {
1034                                                 $escaped = str_replace('`','``',$expression);
1035                                         } else {
1036                                                 $escaped = $expression;
1037                                         }
1038                                         $escaped = '`' . $escaped . '`';
1039
1040                                         if(is_numeric(trim($expression))) {
1041                                                 $type = 'pos';
1042                                         } else {
1043
1044                                                 #search to see if the expression matches an alias
1045                                                 if(!$type && $select) {
1046                                                         foreach($select as $clause) {
1047                                                                 if(!is_array($clause)) continue;
1048                                                                 if($clause['alias'] == $escaped) {
1049                                                                         $type = 'alias';
1050                                                                 }
1051                                                         }
1052                                                 } else {
1053                                                         $type="expression";
1054                                                 }
1055
1056                                                 if(!$type) $type = "expression";
1057                                         }
1058
1059                                         $out[]=array('type'=>$type,'base_expr'=>$expression,'direction'=>$direction);
1060                         }
1061                         foreach($out as &$term) {
1062                             if(!empty($term['base_expr'])) {
1063                                 $term['sub_tree'] = array($this->process_select_expr($term['base_expr']));
1064                             }
1065                         }
1066
1067                         return $out;
1068                 }
1069
1070                 /* Some sections are just lists of expressions, like the WHERE and HAVING clauses.  This function
1071                    processes these sections.  Recursive.
1072                 */
1073                 private function process_expr_list($tokens) {
1074                         $expr = "";
1075                         $type = "";
1076                         $prev_token = "";
1077                         $skip_next = false;
1078                         $sub_expr = "";
1079
1080                         $in_lists = array();
1081                         foreach($tokens as $key => $token) {
1082
1083                                 if(strlen(trim($token))==0) continue;
1084                                 if($skip_next) {
1085                                         $skip_next = false;
1086                                         continue;
1087                                 }
1088
1089                                 $processed = false;
1090                                 $upper = strtoupper(trim($token));
1091                                 if(trim($token)) $token=trim($token);
1092
1093                                 /* is it a subquery?*/
1094                                 if(preg_match("/^\\s*\\(\\s*SELECT/i", $token)) {
1095                                         $type = 'subquery';
1096                                         #tokenize and parse the subquery.
1097                                         #we remove the enclosing parenthesis for the tokenizer
1098                                         $processed = $this->parse($this->trimSubquery($token));
1099
1100
1101                                 /* is it an inlist */
1102                                 } elseif( $upper[0] == '(' && substr($upper,-1) == ')' ) {
1103                                         if($prev_token == 'IN') {
1104                                                 $type = "in-list";
1105                                                 $processed = $this->split_sql(substr($token,1,-1));
1106                                                 $list = array();
1107                                                 foreach($processed as $v) {
1108                                                         if($v == ',') continue;
1109                                                         $list[]=$v;
1110                                                 }
1111                                                 $processed = $list;
1112                                                 unset($list);
1113                                                 $prev_token = "";
1114
1115                                        }
1116                                        elseif($prev_token == 'AGAINST') {
1117                                                $type = "match-arguments";
1118                                                $list = $this->split_sql(substr($token,1,-1));
1119                                                if(count($list) > 1){
1120                                                        $match_mode = implode('',array_slice($list,1));
1121                                                        $processed = array($list[0], $match_mode);
1122                                                }
1123                                                else
1124                                                        $processed = $list[0];
1125                                                $prev_token = "";
1126                                         }
1127
1128                                 /* it is either an operator, a colref or a constant */
1129                                 } else {
1130                                         switch($upper) {
1131                                         case 'AND':
1132                                         case '&&':
1133                                         case 'BETWEEN':
1134                                         case 'AND':
1135                                         case 'BINARY':
1136                                         case '&':
1137                                         case '~':
1138                                         case '|':
1139                                         case '^':
1140                                         case 'CASE':
1141                                         case 'WHEN':
1142                                         case 'END':
1143                                         case 'DIV':
1144                                         case '/':
1145                                         case '<=>':
1146                                         case '=':
1147                                         case '>=':
1148                                         case '>':
1149                                         case 'IS':
1150                                         case 'NOT':
1151                                         case 'NULL':
1152                                         case '<<':
1153                                         case '<=':
1154                                         case '<':
1155                                         case 'LIKE':
1156                                         case '-':
1157                                         case '%':
1158                                         case '!=':
1159                                         case '<>':
1160                                         case 'REGEXP':
1161                                         case '!':
1162                                         case '||':
1163                                         case 'OR':
1164                                         case '+':
1165                                         case '>>':
1166                                         case 'RLIKE':
1167                                         case 'SOUNDS':
1168                                         case '*':
1169                                         case '-':
1170                                         case 'XOR':
1171                                         case 'IN':
1172                                                         $processed = false;
1173                                                         $type = "operator";
1174                                                         break;
1175                                         default:
1176                                                 switch($token[0]) {
1177                                                         case "'":
1178                                                         case '"':
1179                                                                         $type = 'const';
1180                                                                         break;
1181                                                         case '`':
1182                                                                         $type = 'colref';
1183                                                                         break;
1184
1185                                                         default:
1186                                                                 if(is_numeric($token)) {
1187                                                                         $type = 'const';
1188                                                                 } else {
1189                                                                         $type = 'colref';
1190                                                                 }
1191                                                         break;
1192
1193                                                 }
1194                                                 #$processed = $token;
1195                                                 $processed = false;
1196                                         }
1197                                 }
1198                                 /* is a reserved word? */
1199                                 if(($type != 'operator' && $type != 'in-list' && $type != 'sub_expr') && in_array($upper, $this->reserved)) {
1200                                         $token = $upper;
1201                                         if(!in_array($upper,$this->functions)) {
1202                                                 $type = 'reserved';
1203                                         } else {
1204                                                 switch($token) {
1205                                                         case 'AVG':
1206                                                         case 'SUM':
1207                                                         case 'COUNT':
1208                                                         case 'MIN':
1209                                                         case 'MAX':
1210                                                         case 'STDDEV':
1211                                                         case 'STDDEV_SAMP':
1212                                                         case 'STDDEV_POP':
1213                                                         case 'VARIANCE':
1214                                                         case 'VAR_SAMP':
1215                                                         case 'VAR_POP':
1216                                                         case 'GROUP_CONCAT':
1217                                                         case 'BIT_AND':
1218                                                         case 'BIT_OR':
1219                                                         case 'BIT_XOR':
1220                                                                 $type = 'aggregate_function';
1221                                                                 if(!empty($tokens[$key+1])) $sub_expr = $tokens[$key+1];
1222                                                                 #$skip_next=true;
1223                                                         break;
1224
1225                                                         default:
1226                                                                 $type = 'function';
1227                                                                 if(!empty($tokens[$key+1])) $sub_expr = $tokens[$key+1]; else $sub_expr="()";
1228                                                                 #$skip_next=true;
1229
1230
1231                                                         break;
1232                                                 }
1233                                         }
1234                                 }
1235
1236                                 if(!$type) {
1237                                         if($upper[0] == '(') {
1238                                                 $local_expr = substr(trim($token),1,-1);
1239                                         } else {
1240                                                 $local_expr = $token;
1241                                         }
1242                     $processed = $this->process_expr_list($this->split_sql($local_expr));
1243                                         $type = 'expression';
1244
1245 //                                      if(count($processed) == 1) {
1246 //                                              $type = $processed[0]['expr_type'];
1247 //                                              $base_expr  = $processed[0]['base_expr'];
1248 //                                              $processed = $processed[0]['sub_tree'];
1249 //                                      }
1250
1251                                 }
1252
1253                                 $sub_expr=trim($sub_expr);
1254                                 $sub_expr = "";
1255
1256                                 $expr[] = array( 'expr_type' => $type, 'base_expr' => $token, 'sub_tree' => $processed);
1257                                 $prev_token = $upper;
1258                                 $expr_type = "";
1259                                 $type = "";
1260                         }
1261                         if($sub_expr) {
1262                                 $processed['sub_tree'] = $this->process_expr_list($this->split_sql(substr($sub_expr,1,-1)));
1263                         }
1264
1265                         if(!is_array($processed)) {
1266                                 $processed = false;
1267                         }
1268
1269                         if($expr_type) {
1270                                 $expr[] = array( 'expr_type' => $type, 'base_expr' => $token, 'sub_tree' => $processed);
1271                         }
1272                         $mod = false;
1273
1274         /*
1275
1276                         for($i=0;$i<count($expr);++$i){
1277                                 if($expr[$i]['expr_type'] == 'function' ||
1278                                    $expr[$i]['expr_type'] == 'aggregate_function') {
1279                                         if(!empty($expr[$i+1])) {
1280                                                 $expr[$i]['sub_tree']=$expr[$i+1]['sub_tree'];
1281                                                 unset($expr[$i+1]);
1282                                                 $mod = 1;
1283                                                 ++$i;  // BAD FORM TO MODIFY THE LOOP COUNTER
1284                                         }
1285                                 }
1286
1287                         }
1288
1289         */
1290
1291                         if($mod) $expr=array_values($expr);
1292
1293
1294                         return $expr;
1295                 }
1296
1297                 private function process_update($tokens) {
1298
1299                 }
1300
1301                 private function process_delete($tokens) {
1302                         $tables = array();
1303                         $del = $tokens['DELETE'];
1304
1305                         foreach($tokens['DELETE'] as $expression) {
1306                                 if ($expression != 'DELETE' && trim($expression,' .*') != "" && $expression != ',') {
1307                                         $tables[] = trim($expression,'.* ');
1308                                 }
1309                         }
1310
1311                         if(empty($tables)) {
1312                                 foreach($tokens['FROM'] as $table) {
1313                                         $tables[] = $table['table'];
1314                                 }
1315                         }
1316
1317                         $tokens['DELETE'] = array('TABLES' => $tables);
1318
1319                         return $tokens;
1320                 }
1321
1322                 private function process_insert($tokens, $token_category = 'INSERT') {
1323                         $table = "";
1324                         $cols = "";
1325
1326                         $into = $tokens['INTO'];
1327                         foreach($into as $token) {
1328                                 if(!trim($token)) continue;
1329                                 if(!$table) {
1330                                         $table = $token;
1331                                 }elseif(!$cols) {
1332                                         $cols = $token;
1333                                 }
1334                         }
1335
1336                         if(!$cols) {
1337                                 $cols = 'ALL';
1338                         } else {
1339                                 $cols = explode(",", $this->trimSubquery($cols));
1340                         }
1341                         unset($tokens['INTO']);
1342                         $tokens[$token_category] =  array('table'=>$table, 'cols'=>$cols);
1343                         return $tokens;
1344
1345                 }
1346
1347
1348                 private function load_reserved_words() {
1349
1350                     $this->functions = array(
1351                         'abs',
1352                         'acos',
1353                         'adddate',
1354                         'addtime',
1355                         'aes_encrypt',
1356                         'aes_decrypt',
1357                         'against',
1358                         'ascii',
1359                         'asin',
1360                         'atan',
1361                         'avg',
1362                         'benchmark',
1363                         'bin',
1364                         'bit_and',
1365                         'bit_or',
1366                         'bitcount',
1367                         'bitlength',
1368                         'cast',
1369                         'ceiling',
1370                         'char',
1371                         'char_length',
1372                         'character_length',
1373                         'charset',
1374                         'coalesce',
1375                         'coercibility',
1376                         'collation',
1377                         'compress',
1378                         'concat',
1379                         'concat_ws',
1380                         'conection_id',
1381                         'conv',
1382                         'convert',
1383                         'convert_tz',
1384                         'cos',
1385                         'cot',
1386                         'count',
1387                         'crc32',
1388                         'curdate',
1389                         'current_user',
1390                         'currval',
1391                         'curtime',
1392                         'database',
1393                         'date_add',
1394                         'date_diff',
1395                         'date_format',
1396                         'date_sub',
1397                         'day',
1398                         'dayname',
1399                         'dayofmonth',
1400                         'dayofweek',
1401                         'dayofyear',
1402                         'decode',
1403                         'default',
1404                         'degrees',
1405                         'des_decrypt',
1406                         'des_encrypt',
1407                         'elt',
1408                         'encode',
1409                         'encrypt',
1410                         'exp',
1411                         'export_set',
1412                         'extract',
1413                         'field',
1414                         'find_in_set',
1415                         'floor',
1416                         'format',
1417                         'found_rows',
1418                         'from_days',
1419                         'from_unixtime',
1420                         'get_format',
1421                         'get_lock',
1422                         'group_concat',
1423                         'greatest',
1424                         'hex',
1425                         'hour',
1426                         'if',
1427                         'ifnull',
1428                         'in',
1429                         'inet_aton',
1430                         'inet_ntoa',
1431                         'insert',
1432                         'instr',
1433                         'interval',
1434                         'is_free_lock',
1435                         'is_used_lock',
1436                         'last_day',
1437                         'last_insert_id',
1438                         'lcase',
1439                         'least',
1440                         'left',
1441                         'length',
1442                         'ln',
1443                         'load_file',
1444                         'localtime',
1445                         'localtimestamp',
1446                         'locate',
1447                         'log',
1448                         'log2',
1449                         'log10',
1450                         'lower',
1451                         'lpad',
1452                         'ltrim',
1453                         'make_set',
1454                         'makedate',
1455                         'maketime',
1456                         'master_pos_wait',
1457                         'match',
1458                         'max',
1459                         'md5',
1460                         'microsecond',
1461                         'mid',
1462                         'min',
1463                         'minute',
1464                         'mod',
1465                         'month',
1466                         'monthname',
1467                         'nextval',
1468                         'now',
1469                         'nullif',
1470                         'oct',
1471                         'octet_length',
1472                         'old_password',
1473                         'ord',
1474                         'password',
1475                         'period_add',
1476                         'period_diff',
1477                         'pi',
1478                         'position',
1479                         'pow',
1480                         'power',
1481                         'quarter',
1482                         'quote',
1483                         'radians',
1484                         'rand',
1485                         'release_lock',
1486                         'repeat',
1487                         'replace',
1488                         'reverse',
1489                         'right',
1490                         'round',
1491                         'row_count',
1492                         'rpad',
1493                         'rtrim',
1494                         'sec_to_time',
1495                         'second',
1496                         'session_user',
1497                         'sha',
1498                         'sha1',
1499                         'sign',
1500                         'soundex',
1501                         'space',
1502                         'sqrt',
1503                         'std',
1504                         'stddev',
1505                         'stddev_pop',
1506                         'stddev_samp',
1507                         'strcmp',
1508                         'str_to_date',
1509                         'subdate',
1510                         'substr',
1511                         'substring',
1512                         'substring_index',
1513                         'subtime',
1514                         'sum',
1515                         'sysdate',
1516                         'system_user',
1517                         'tan',
1518                         'time',
1519                         'timediff',
1520                         'timestamp',
1521                         'timestampadd',
1522                         'timestampdiff',
1523                         'time_format',
1524                         'time_to_sec',
1525                         'to_days',
1526                         'trim',
1527                         'truncate',
1528                         'ucase',
1529                         'uncompress',
1530                         'uncompressed_length',
1531                         'unhex',
1532                         'unix_timestamp',
1533                         'upper',
1534                         'user',
1535                         'utc_date',
1536                         'utc_time',
1537                         'utc_timestamp',
1538                         'uuid',
1539                         'var_pop',
1540                         'var_samp',
1541                         'variance',
1542                         'version',
1543                         'week',
1544                         'weekday',
1545                         'weekofyear',
1546                         'year',
1547                         'yearweek');
1548
1549                 /* includes functions */
1550                 $this->reserved = array(
1551                         'abs',
1552                         'acos',
1553                         'adddate',
1554                         'addtime',
1555                         'aes_encrypt',
1556                         'aes_decrypt',
1557                         'against',
1558                         'ascii',
1559                         'asin',
1560                         'atan',
1561                         'avg',
1562                         'benchmark',
1563                         'bin',
1564                         'bit_and',
1565                         'bit_or',
1566                         'bitcount',
1567                         'bitlength',
1568                         'cast',
1569                         'ceiling',
1570                         'char',
1571                         'char_length',
1572                         'character_length',
1573                         'charset',
1574                         'coalesce',
1575                         'coercibility',
1576                         'collation',
1577                         'compress',
1578                         'concat',
1579                         'concat_ws',
1580                         'conection_id',
1581                         'conv',
1582                         'convert',
1583                         'convert_tz',
1584                         'cos',
1585                         'cot',
1586                         'count',
1587                         'crc32',
1588                         'curdate',
1589                         'current_user',
1590                         'currval',
1591                         'curtime',
1592                         'database',
1593                         'date_add',
1594                         'date_diff',
1595                         'date_format',
1596                         'date_sub',
1597                         'day',
1598                         'dayname',
1599                         'dayofmonth',
1600                         'dayofweek',
1601                         'dayofyear',
1602                         'decode',
1603                         'default',
1604                         'degrees',
1605                         'des_decrypt',
1606                         'des_encrypt',
1607                         'elt',
1608                         'encode',
1609                         'encrypt',
1610                         'exp',
1611                         'export_set',
1612                         'extract',
1613                         'field',
1614                         'find_in_set',
1615                         'floor',
1616                         'format',
1617                         'found_rows',
1618                         'from_days',
1619                         'from_unixtime',
1620                         'get_format',
1621                         'get_lock',
1622                         'group_concat',
1623                         'greatest',
1624                         'hex',
1625                         'hour',
1626                         'if',
1627                         'ifnull',
1628                         'in',
1629                         'inet_aton',
1630                         'inet_ntoa',
1631                         'insert',
1632                         'instr',
1633                         'interval',
1634                         'is_free_lock',
1635                         'is_used_lock',
1636                         'last_day',
1637                         'last_insert_id',
1638                         'lcase',
1639                         'least',
1640                         'left',
1641                         'length',
1642                         'ln',
1643                         'load_file',
1644                         'localtime',
1645                         'localtimestamp',
1646                         'locate',
1647                         'log',
1648                         'log2',
1649                         'log10',
1650                         'lower',
1651                         'lpad',
1652                         'ltrim',
1653                         'make_set',
1654                         'makedate',
1655                         'maketime',
1656                         'master_pos_wait',
1657                         'match',
1658                         'max',
1659                         'md5',
1660                         'microsecond',
1661                         'mid',
1662                         'min',
1663                         'minute',
1664                         'mod',
1665                         'month',
1666                         'monthname',
1667                         'nextval',
1668                         'now',
1669                         'nullif',
1670                         'oct',
1671                         'octet_length',
1672                         'old_password',
1673                         'ord',
1674                         'password',
1675                         'period_add',
1676                         'period_diff',
1677                         'pi',
1678                         'position',
1679                         'pow',
1680                         'power',
1681                         'quarter',
1682                         'quote',
1683                         'radians',
1684                         'rand',
1685                         'release_lock',
1686                         'repeat',
1687                         'replace',
1688                         'reverse',
1689                         'right',
1690                         'round',
1691                         'row_count',
1692                         'rpad',
1693                         'rtrim',
1694                         'sec_to_time',
1695                         'second',
1696                         'session_user',
1697                         'sha',
1698                         'sha1',
1699                         'sign',
1700                         'soundex',
1701                         'space',
1702                         'sqrt',
1703                         'std',
1704                         'stddev',
1705                         'stddev_pop',
1706                         'stddev_samp',
1707                         'strcmp',
1708                         'str_to_date',
1709                         'subdate',
1710                         'substring',
1711                         'substring_index',
1712                         'subtime',
1713                         'sum',
1714                         'sysdate',
1715                         'system_user',
1716                         'tan',
1717                         'time',
1718                         'timediff',
1719                         'timestamp',
1720                         'timestampadd',
1721                         'timestampdiff',
1722                         'time_format',
1723                         'time_to_sec',
1724                         'to_days',
1725                         'trim',
1726                         'truncate',
1727                         'ucase',
1728                         'uncompress',
1729                         'uncompressed_length',
1730                         'unhex',
1731                         'unix_timestamp',
1732                         'upper',
1733                         'user',
1734                         'utc_date',
1735                         'utc_time',
1736                         'utc_timestamp',
1737                         'uuid',
1738                         'var_pop',
1739                         'var_samp',
1740                         'variance',
1741                         'version',
1742                         'week',
1743                         'weekday',
1744                         'weekofyear',
1745                         'year',
1746                         'yearweek',
1747                         'add',
1748                         'all',
1749                         'alter',
1750                         'analyze',
1751                         'and',
1752                         'as',
1753                         'asc',
1754                         'asensitive',
1755                         'auto_increment',
1756                         'bdb',
1757                         'before',
1758                         'berkeleydb',
1759                         'between',
1760                         'bigint',
1761                         'binary',
1762                         'blob',
1763                         'both',
1764                         'by',
1765                         'call',
1766                         'cascade',
1767                         'case',
1768                         'change',
1769                         'char',
1770                         'character',
1771                         'check',
1772                         'collate',
1773                         'column',
1774                         'columns',
1775                         'condition',
1776                         'connection',
1777                         'constraint',
1778                         'continue',
1779                         'create',
1780                         'cross',
1781                         'current_date',
1782                         'current_time',
1783                         'current_timestamp',
1784                         'cursor',
1785                         'database',
1786                         'databases',
1787                         'day_hour',
1788                         'day_microsecond',
1789                         'day_minute',
1790                         'day_second',
1791                         'dec',
1792                         'decimal',
1793                         'declare',
1794                         'default',
1795                         'delayed',
1796                         'delete',
1797                         'desc',
1798                         'describe',
1799                         'deterministic',
1800                         'distinct',
1801                         'distinctrow',
1802                         'div',
1803                         'double',
1804                         'drop',
1805                         'else',
1806                         'elseif',
1807                         'enclosed',
1808                         'escaped',
1809                         'exists',
1810                         'exit',
1811                         'explain',
1812                         'false',
1813                         'fetch',
1814                         'fields',
1815                         'float',
1816                         'for',
1817                         'force',
1818                         'foreign',
1819                         'found',
1820                         'frac_second',
1821                         'from',
1822                         'fulltext',
1823                         'grant',
1824                         'group',
1825                         'having',
1826                         'high_priority',
1827                         'hour_microsecond',
1828                         'hour_minute',
1829                         'hour_second',
1830                         'if',
1831                         'ignore',
1832                         'in',
1833                         'index',
1834                         'infile',
1835                         'inner',
1836                         'innodb',
1837                         'inout',
1838                         'insensitive',
1839                         'insert',
1840                         'int',
1841                         'integer',
1842                         'interval',
1843                         'into',
1844                         'io_thread',
1845                         'is',
1846                         'iterate',
1847                         'join',
1848                         'key',
1849                         'keys',
1850                         'kill',
1851                         'leading',
1852                         'leave',
1853                         'left',
1854                         'like',
1855                         'limit',
1856                         'lines',
1857                         'load',
1858                         'localtime',
1859                         'localtimestamp',
1860                         'lock',
1861                         'long',
1862                         'longblob',
1863                         'longtext',
1864                         'loop',
1865                         'low_priority',
1866                         'master_server_id',
1867                         'match',
1868                         'mediumblob',
1869                         'mediumint',
1870                         'mediumtext',
1871                         'middleint',
1872                         'minute_microsecond',
1873                         'minute_second',
1874                         'mod',
1875                         'natural',
1876                         'not',
1877                         'no_write_to_binlog',
1878                         'null',
1879                         'numeric',
1880                         'on',
1881                         'optimize',
1882                         'option',
1883                         'optionally',
1884                         'or',
1885                         'order',
1886                         'out',
1887                         'outer',
1888                         'outfile',
1889                         'precision',
1890                         'primary',
1891                         'privileges',
1892                         'procedure',
1893                         'purge',
1894                         'read',
1895                         'real',
1896                         'references',
1897                         'regexp',
1898                         'rename',
1899                         'repeat',
1900                         'replace',
1901                         'require',
1902                         'restrict',
1903                         'return',
1904                         'revoke',
1905                         'right',
1906                         'rlike',
1907                         'second_microsecond',
1908                         'select',
1909                         'sensitive',
1910                         'separator',
1911                         'set',
1912                         'show',
1913                         'smallint',
1914                         'some',
1915                         'soname',
1916                         'spatial',
1917                         'specific',
1918                         'sql',
1919                         'sqlexception',
1920                         'sqlstate',
1921                         'sqlwarning',
1922                         'sql_big_result',
1923                         'sql_calc_found_rows',
1924                         'sql_small_result',
1925                         'sql_tsi_day',
1926                         'sql_tsi_frac_second',
1927                         'sql_tsi_hour',
1928                         'sql_tsi_minute',
1929                         'sql_tsi_month',
1930                         'sql_tsi_quarter',
1931                         'sql_tsi_second',
1932                         'sql_tsi_week',
1933                         'sql_tsi_year',
1934                         'ssl',
1935                         'starting',
1936                         'straight_join',
1937                         'striped',
1938                         'table',
1939                         'tables',
1940                         'terminated',
1941                         'then',
1942                         'timestampadd',
1943                         'timestampdiff',
1944                         'tinyblob',
1945                         'tinyint',
1946                         'tinytext',
1947                         'to',
1948                         'trailing',
1949                         'true',
1950                         'undo',
1951                         'union',
1952                         'unique',
1953                         'unlock',
1954                         'unsigned',
1955                         'update',
1956                         'usage',
1957                         'use',
1958                         'user_resources',
1959                         'using',
1960                         'utc_date',
1961                         'utc_time',
1962                         'utc_timestamp',
1963                         'values',
1964                         'varbinary',
1965                         'varchar',
1966                         'varcharacter',
1967                         'varying',
1968                         'when',
1969                         'where',
1970                         'while',
1971                         'with',
1972                         'write',
1973                         'xor',
1974                         'year_month',
1975                         'zerofill'
1976                         );
1977
1978                         for($i=0;$i<count($this->reserved);++$i) {
1979                                 $this->reserved[$i]=strtoupper($this->reserved[$i]);
1980                                 if(!empty($this->functions[$i])) $this->functions[$i] = strtoupper($this->functions[$i]);
1981                         }
1982                 }
1983
1984         } // END CLASS
1985