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