t3lib/class.t3lib_sqlparser.php

   1 <?php
   2 /***************************************************************
   3 *  Copyright notice
   4 *
   5 *  (c) 2004 Kasper Skaarhoj (kasperYYYY@typo3.com)
   6 *  All rights reserved
   7 *
   8 *  This script is part of the TYPO3 project. The TYPO3 project is
   9 *  free software; you can redistribute it and/or modify
  10 *  it under the terms of the GNU General Public License as published by
  11 *  the Free Software Foundation; either version 2 of the License, or
  12 *  (at your option) any later version.
  13 *
  14 *  The GNU General Public License can be found at
  15 *  http://www.gnu.org/copyleft/gpl.html.
  16 *  A copy is found in the textfile GPL.txt and important notices to the license
  17 *  from the author is found in LICENSE.txt distributed with these scripts.
  18 *
  19 *
  20 *  This script is distributed in the hope that it will be useful,
  21 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
  22 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  23 *  GNU General Public License for more details.
  24 *
  25 *  This copyright notice MUST APPEAR in all copies of the script!
  26 ***************************************************************/
  27 /**
  28  * TYPO3 SQL parser
  29  *
  30  * $Id$
  31  *
  32  * @author      Kasper Skaarhoj <kasperYYYY@typo3.com>
  33  */
  34 /**
  35  * [CLASS/FUNCTION INDEX of SCRIPT]
  36  *
  37  *
  38  *
  39  *  106: class t3lib_sqlparser
  40  *
  41  *              SECTION: SQL Parsing, full queries
  42  *  128:     function parseSQL($parseString)
  43  *  188:     function parseSELECT($parseString)
  44  *  257:     function parseUPDATE($parseString)
  45  *  311:     function parseINSERT($parseString)
  46  *  371:     function parseDELETE($parseString)
  47  *  409:     function parseEXPLAIN($parseString)
  48  *  431:     function parseCREATETABLE($parseString)
  49  *  503:     function parseALTERTABLE($parseString)
  50  *  572:     function parseDROPTABLE($parseString)
  51  *
  52  *              SECTION: SQL Parsing, helper functions for parts of queries
  53  *  631:     function parseFieldList(&$parseString, $stopRegex='')
  54  *  749:     function parseFromTables(&$parseString, $stopRegex='')
  55  *  816:     function parseWhereClause(&$parseString, $stopRegex='')
  56  *  924:     function parseFieldDef(&$parseString, $stopRegex='')
  57  *
  58  *              SECTION: Parsing: Helper functions
  59  *  985:     function nextPart(&$parseString,$regex,$trimAll=FALSE)
  60  *  999:     function getValue(&$parseString,$comparator='')
  61  * 1054:     function getValueInQuotes(&$parseString,$quote)
  62  * 1079:     function parseStripslashes($str)
  63  * 1093:     function compileAddslashes($str)
  64  * 1107:     function parseError($msg,$restQuery)
  65  * 1121:     function trimSQL($str)
  66  *
  67  *              SECTION: Compiling queries
  68  * 1149:     function compileSQL($components)
  69  * 1187:     function compileSELECT($components)
  70  * 1218:     function compileUPDATE($components)
  71  * 1246:     function compileINSERT($components)
  72  * 1286:     function compileDELETE($components)
  73  * 1306:     function compileCREATETABLE($components)
  74  * 1337:     function compileALTERTABLE($components)
  75  *
  76  *              SECTION: Compiling queries, helper functions for parts of queries
  77  * 1390:     function compileFieldList($selectFields)
  78  * 1432:     function compileFromTables($tablesArray)
  79  * 1468:     function compileWhereClause($clauseArray)
  80  * 1522:     function compileFieldCfg($fieldCfg)
  81  *
  82  *              SECTION: Debugging
  83  * 1571:     function debug_parseSQLpart($part,$str)
  84  * 1593:     function debug_parseSQLpartCompare($str,$newStr,$caseInsensitive=FALSE)
  85  * 1626:     function debug_testSQL($SQLquery)
  86  *
  87  * TOTAL FUNCTIONS: 34
  88  * (This index is automatically created/updated by the extension "extdeveval")
  89  *
  90  */
  91
  92
  93
  94
  95
  96
  97
  98
  99 /**
 100  * TYPO3 SQL parser class.
 101  *
 102  * @author      Kasper Skaarhoj <kasperYYYY@typo3.com>
 103  * @package TYPO3
 104  * @subpackage t3lib
 105  */
 106 class t3lib_sqlparser {
 107
 108                 // Parser:
 109         var $parse_error = '';                                          // Parsing error string
 110         var $lastStopKeyWord = '';                                      // Last stop keyword used.
 111
 112
 113
 114
 115         /*************************************
 116          *
 117          * SQL Parsing, full queries
 118          *
 119          **************************************/
 120
 121         /**
 122          * Parses any single SQL query
 123          *
 124          * @param       string          SQL query
 125          * @return      array           Result array with all the parts in - or error message string
 126          * @see compileSQL(), debug_testSQL()
 127          */
 128         function parseSQL($parseString) {
 129
 130                         // Prepare variables:
 131                 $parseString = $this->trimSQL($parseString);
 132                 $this->parse_error = '';
 133                 $result = array();
 134
 135                         // Finding starting keyword of string:
 136                 $_parseString = $parseString;   // Protecting original string...
 137                 $keyword = $this->nextPart($_parseString, '^(SELECT|UPDATE|INSERT[[:space:]]+INTO|DELETE[[:space:]]+FROM|EXPLAIN|DROP[[:space:]]+TABLE|CREATE[[:space:]]+TABLE|ALTER[[:space:]]+TABLE)[[:space:]]+');
 138                 $keyword = strtoupper(ereg_replace('[[:space:]]*','',$keyword));
 139
 140                 switch($keyword)        {
 141                         case 'SELECT':
 142                                         // Parsing SELECT query:
 143                                 $result = $this->parseSELECT($parseString);
 144                         break;
 145                         case 'UPDATE':
 146                                         // Parsing UPDATE query:
 147                                 $result = $this->parseUPDATE($parseString);
 148                         break;
 149                         case 'INSERTINTO':
 150                                         // Parsing INSERT query:
 151                                 $result = $this->parseINSERT($parseString);
 152                         break;
 153                         case 'DELETEFROM':
 154                                         // Parsing DELETE query:
 155                                 $result = $this->parseDELETE($parseString);
 156                         break;
 157                         case 'EXPLAIN':
 158                                         // Parsing EXPLAIN SELECT query:
 159                                 $result = $this->parseEXPLAIN($parseString);
 160                         break;
 161                         case 'DROPTABLE':
 162                                         // Parsing DROP TABLE query:
 163                                 $result = $this->parseDROPTABLE($parseString);
 164                         break;
 165                         case 'ALTERTABLE':
 166                                         // Parsing ALTER TABLE query:
 167                                 $result = $this->parseALTERTABLE($parseString);
 168                         break;
 169                         case 'CREATETABLE':
 170                                         // Parsing CREATE TABLE query:
 171                                 $result = $this->parseCREATETABLE($parseString);
 172                         break;
 173                         default:
 174                                 return $this->parseError('"'.$keyword.'" is not a keyword',$parseString);
 175                         break;
 176                 }
 177
 178                 return $result;
 179         }
 180
 181         /**
 182          * Parsing SELECT query
 183          *
 184          * @param       string          SQL string with SELECT query to parse
 185          * @return      mixed           Returns array with components of SELECT query on success, otherwise an error message string.
 186          * @see compileSELECT()
 187          */
 188         function parseSELECT($parseString)      {
 189
 190                         // Removing SELECT:
 191                 $parseString = $this->trimSQL($parseString);
 192                 $parseString = eregi_replace('^SELECT[[:space:]]+','',$parseString);
 193
 194                         // Init output variable:
 195                 $result = array();
 196                 $result['type'] = 'SELECT';
 197
 198                         // Looking for STRAIGHT_JOIN keyword:
 199                 $result['STRAIGHT_JOIN'] = $this->nextPart($parseString, '^(STRAIGHT_JOIN)[[:space:]]+');
 200
 201                         // Select fields:
 202                 $result['SELECT'] = $this->parseFieldList($parseString, '^(FROM)[[:space:]]+');
 203                 if ($this->parse_error) { return $this->parse_error; }
 204
 205                         // Continue if string is not ended:
 206                 if ($parseString)       {
 207
 208                                 // Get table list:
 209                         $result['FROM'] = $this->parseFromTables($parseString, '^(WHERE)[[:space:]]+');
 210                         if ($this->parse_error) { return $this->parse_error; }
 211
 212                                 // If there are more than just the tables (a WHERE clause that would be...)
 213                         if ($parseString)       {
 214
 215                                         // Get WHERE clause:
 216                                 $result['WHERE'] = $this->parseWhereClause($parseString, '^(GROUP[[:space:]]+BY|ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
 217                                 if ($this->parse_error) { return $this->parse_error; }
 218
 219                                         // If the WHERE clause parsing was stopped by GROUP BY, ORDER BY or LIMIT, then proceed with parsing:
 220                                 if ($this->lastStopKeyWord)     {
 221
 222                                                 // GROUP BY parsing:
 223                                         if ($this->lastStopKeyWord == 'GROUPBY')        {
 224                                                 $result['GROUPBY'] = $this->parseFieldList($parseString, '^(ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
 225                                                 if ($this->parse_error) { return $this->parse_error; }
 226                                         }
 227
 228                                                 // ORDER BY parsing:
 229                                         if ($this->lastStopKeyWord == 'ORDERBY')        {
 230                                                 $result['ORDERBY'] = $this->parseFieldList($parseString, '^(LIMIT)[[:space:]]+');
 231                                                 if ($this->parse_error) { return $this->parse_error; }
 232                                         }
 233
 234                                                 // LIMIT parsing:
 235                                         if ($this->lastStopKeyWord == 'LIMIT')  {
 236                                                 if (ereg('^([0-9]+|[0-9]+[[:space:]]*,[[:space:]]*[0-9]+)$',trim($parseString)))        {
 237                                                         $result['LIMIT'] = $parseString;
 238                                                 } else {
 239                                                         return $this->parseError('No value for limit!',$parseString);
 240                                                 }
 241                                         }
 242                                 }
 243                         }
 244                 } else return $this->parseError('No table to select from!',$parseString);
 245
 246                         // Return result:
 247                 return $result;
 248         }
 249
 250         /**
 251          * Parsing UPDATE query
 252          *
 253          * @param       string          SQL string with UPDATE query to parse
 254          * @return      mixed           Returns array with components of UPDATE query on success, otherwise an error message string.
 255          * @see compileUPDATE()
 256          */
 257         function parseUPDATE($parseString)      {
 258
 259                         // Removing UPDATE
 260                 $parseString = $this->trimSQL($parseString);
 261                 $parseString = eregi_replace('^UPDATE[[:space:]]+','',$parseString);
 262
 263                         // Init output variable:
 264                 $result = array();
 265                 $result['type'] = 'UPDATE';
 266
 267                         // Get table:
 268                 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
 269
 270                         // Continue if string is not ended:
 271                 if ($result['TABLE'])   {
 272                         if ($parseString && $this->nextPart($parseString, '^(SET)[[:space:]]+'))        {
 273
 274                                 $comma = TRUE;
 275
 276                                         // Get field/value pairs:
 277                                 while($comma)   {
 278                                         if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]*=')) {
 279                                                 $this->nextPart($parseString,'^(=)');   // Strip of "=" sign.
 280                                                 $value = $this->getValue($parseString);
 281                                                 $result['FIELDS'][$fieldName] = $value;
 282                                         } else return $this->parseError('No fieldname found',$parseString);
 283
 284                                         $comma = $this->nextPart($parseString,'^(,)');
 285                                 }
 286
 287                                         // WHERE
 288                                 if ($this->nextPart($parseString,'^(WHERE)'))   {
 289                                         $result['WHERE'] = $this->parseWhereClause($parseString);
 290                                         if ($this->parse_error) { return $this->parse_error; }
 291                                 }
 292                         } else return $this->parseError('Query missing SET...',$parseString);
 293                 } else return $this->parseError('No table found!',$parseString);
 294
 295                         // Should be no more content now:
 296                 if ($parseString)       {
 297                         return $this->parseError('Still content in clause after parsing!',$parseString);
 298                 }
 299
 300                         // Return result:
 301                 return $result;
 302         }
 303
 304         /**
 305          * Parsing INSERT query
 306          *
 307          * @param       string          SQL string with INSERT query to parse
 308          * @return      mixed           Returns array with components of INSERT query on success, otherwise an error message string.
 309          * @see compileINSERT()
 310          */
 311         function parseINSERT($parseString)      {
 312
 313                         // Removing INSERT
 314                 $parseString = $this->trimSQL($parseString);
 315                 $parseString = eregi_replace('^INSERT[[:space:]]+INTO[[:space:]]+','',$parseString);
 316
 317                         // Init output variable:
 318                 $result = array();
 319                 $result['type'] = 'INSERT';
 320
 321                         // Get table:
 322                 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()');
 323
 324                 if ($result['TABLE'])   {
 325
 326                         if ($this->nextPart($parseString,'^(VALUES)[[:space:]]+'))      {       // In this case there are no field names mentioned in the SQL!
 327                                         // Get values/fieldnames (depending...)
 328                                 $result['VALUES_ONLY'] = $this->getValue($parseString,'IN');
 329                                 if ($this->parse_error) { return $this->parse_error; }
 330                         } else {        // There are apparently fieldnames listed:
 331                                 $fieldNames = $this->getValue($parseString,'_LIST');
 332                                 if ($this->parse_error) { return $this->parse_error; }
 333
 334                                 if ($this->nextPart($parseString,'^(VALUES)[[:space:]]+'))      {       // "VALUES" keyword binds the fieldnames to values:
 335
 336                                         $values = $this->getValue($parseString,'IN');   // Using the "getValue" function to get the field list...
 337                                         if ($this->parse_error) { return $this->parse_error; }
 338
 339                                         foreach($fieldNames as $k => $fN)       {
 340                                                 if (ereg('^[[:alnum:]_]+$',$fN))        {
 341                                                         if (isset($values[$k])) {
 342                                                                 if (!isset($result['FIELDS'][$fN]))     {
 343                                                                         $result['FIELDS'][$fN] = $values[$k];
 344                                                                 } else return $this->parseError('Fieldname ("'.$fN.'") already found in list!',$parseString);
 345                                                         } else return $this->parseError('No value set!',$parseString);
 346                                                 } else return $this->parseError('Invalid fieldname ("'.$fN.'")',$parseString);
 347                                         }
 348                                         if (isset($values[$k+1]))       {
 349                                                 return $this->parseError('Too many values in list!',$parseString);
 350                                         }
 351                                 } else return $this->parseError('VALUES keyword expected',$parseString);
 352                         }
 353                 }  else return $this->parseError('No table found!',$parseString);
 354
 355                         // Should be no more content now:
 356                 if ($parseString)       {
 357                         return $this->parseError('Still content after parsing!',$parseString);
 358                 }
 359
 360                         // Return result
 361                 return $result;
 362         }
 363
 364         /**
 365          * Parsing DELETE query
 366          *
 367          * @param       string          SQL string with DELETE query to parse
 368          * @return      mixed           Returns array with components of DELETE query on success, otherwise an error message string.
 369          * @see compileDELETE()
 370          */
 371         function parseDELETE($parseString)      {
 372
 373                         // Removing DELETE
 374                 $parseString = $this->trimSQL($parseString);
 375                 $parseString = eregi_replace('^DELETE[[:space:]]+FROM[[:space:]]+','',$parseString);
 376
 377                         // Init output variable:
 378                 $result = array();
 379                 $result['type'] = 'DELETE';
 380
 381                         // Get table:
 382                 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
 383
 384                 if ($result['TABLE'])   {
 385
 386                                 // WHERE
 387                         if ($this->nextPart($parseString,'^(WHERE)'))   {
 388                                 $result['WHERE'] = $this->parseWhereClause($parseString);
 389                                 if ($this->parse_error) { return $this->parse_error; }
 390                         }
 391                 } else return $this->parseError('No table found!',$parseString);
 392
 393                         // Should be no more content now:
 394                 if ($parseString)       {
 395                         return $this->parseError('Still content in clause after parsing!',$parseString);
 396                 }
 397
 398                         // Return result:
 399                 return $result;
 400         }
 401
 402         /**
 403          * Parsing EXPLAIN query
 404          *
 405          * @param       string          SQL string with EXPLAIN query to parse
 406          * @return      mixed           Returns array with components of EXPLAIN query on success, otherwise an error message string.
 407          * @see parseSELECT()
 408          */
 409         function parseEXPLAIN($parseString)     {
 410
 411                         // Removing EXPLAIN
 412                 $parseString = $this->trimSQL($parseString);
 413                 $parseString = eregi_replace('^EXPLAIN[[:space:]]+','',$parseString);
 414
 415                         // Init output variable:
 416                 $result = $this->parseSELECT($parseString);
 417                 if (is_array($result))  {
 418                         $result['type'] = 'EXPLAIN';
 419                 }
 420
 421                 return $result;
 422         }
 423
 424         /**
 425          * Parsing CREATE TABLE query
 426          *
 427          * @param       string          SQL string starting with CREATE TABLE
 428          * @return      mixed           Returns array with components of CREATE TABLE query on success, otherwise an error message string.
 429          * @see compileCREATETABLE()
 430          */
 431         function parseCREATETABLE($parseString) {
 432
 433                         // Removing CREATE TABLE
 434                 $parseString = $this->trimSQL($parseString);
 435                 $parseString = eregi_replace('^CREATE[[:space:]]+TABLE[[:space:]]+','',$parseString);
 436
 437                         // Init output variable:
 438                 $result = array();
 439                 $result['type'] = 'CREATETABLE';
 440
 441                         // Get table:
 442                 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*\(',TRUE);
 443
 444                 if ($result['TABLE'])   {
 445
 446                                 // While the parseString is not yet empty:
 447                         while(strlen($parseString)>0)   {
 448                                 if ($key = $this->nextPart($parseString, '^(KEY|PRIMARY KEY)([[:space:]]+|\()'))        {       // Getting key
 449                                         $key = strtoupper(ereg_replace('[[:space:]]','',$key));
 450
 451                                         switch($key)    {
 452                                                 case 'PRIMARYKEY':
 453                                                         $result['KEYS'][$key] = $this->getValue($parseString,'_LIST');
 454                                                         if ($this->parse_error) { return $this->parse_error; }
 455                                                 break;
 456                                                 case 'KEY':
 457                                                         if ($keyName = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()'))      {
 458                                                                 $result['KEYS'][$keyName] = $this->getValue($parseString,'_LIST');
 459                                                                 if ($this->parse_error) { return $this->parse_error; }
 460                                                         } else return $this->parseError('No keyname found',$parseString);
 461                                                 break;
 462                                         }
 463                                 } elseif ($fieldName = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+'))   {       // Getting field:
 464                                         $result['FIELDS'][$fieldName]['definition'] = $this->parseFieldDef($parseString);
 465                                         if ($this->parse_error) { return $this->parse_error; }
 466                                 }
 467
 468                                         // Finding delimiter:
 469                                 $delim = $this->nextPart($parseString, '^(,|\))');
 470                                 if (!$delim)    {
 471                                         return $this->parseError('No delimiter found',$parseString);
 472                                 } elseif ($delim==')')  {
 473                                         break;
 474                                 }
 475                         }
 476
 477                                 // Finding what is after the table definition - table type in MySQL
 478                         if ($delim==')')        {
 479                                 if ($this->nextPart($parseString, '^(TYPE[[:space:]]*=)'))      {
 480                                         $result['tableType'] = $parseString;
 481                                         $parseString = '';
 482                                 }
 483                         } else return $this->parseError('No fieldname found!',$parseString);
 484
 485                                 // Getting table type
 486                 } else return $this->parseError('No table found!',$parseString);
 487
 488                         // Should be no more content now:
 489                 if ($parseString)       {
 490                         return $this->parseError('Still content in clause after parsing!',$parseString);
 491                 }
 492
 493                 return $result;
 494         }
 495
 496         /**
 497          * Parsing ALTER TABLE query
 498          *
 499          * @param       string          SQL string starting with ALTER TABLE
 500          * @return      mixed           Returns array with components of ALTER TABLE query on success, otherwise an error message string.
 501          * @see compileALTERTABLE()
 502          */
 503         function parseALTERTABLE($parseString)  {
 504
 505                         // Removing ALTER TABLE
 506                 $parseString = $this->trimSQL($parseString);
 507                 $parseString = eregi_replace('^ALTER[[:space:]]+TABLE[[:space:]]+','',$parseString);
 508
 509                         // Init output variable:
 510                 $result = array();
 511                 $result['type'] = 'ALTERTABLE';
 512
 513                         // Get table:
 514                 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
 515
 516                 if ($result['TABLE'])   {
 517                         if ($result['action'] = $this->nextPart($parseString, '^(CHANGE|DROP[[:space:]]+KEY|DROP[[:space:]]+PRIMARY[[:space:]]+KEY|ADD[[:space:]]+KEY|ADD[[:space:]]+PRIMARY[[:space:]]+KEY|DROP|ADD|RENAME)([[:space:]]+|\()'))        {
 518                                 $actionKey = strtoupper(ereg_replace('[[:space:]]','',$result['action']));
 519
 520                                         // Getting field:
 521                                 if (t3lib_div::inList('ADDPRIMARYKEY,DROPPRIMARYKEY',$actionKey) || $fieldKey = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+'))  {
 522
 523                                         switch($actionKey)      {
 524                                                 case 'ADD':
 525                                                         $result['FIELD'] = $fieldKey;
 526                                                         $result['definition'] = $this->parseFieldDef($parseString);
 527                                                         if ($this->parse_error) { return $this->parse_error; }
 528                                                 break;
 529                                                 case 'DROP':
 530                                                 case 'RENAME':
 531                                                         $result['FIELD'] = $fieldKey;
 532                                                 break;
 533                                                 case 'CHANGE':
 534                                                         $result['FIELD'] = $fieldKey;
 535                                                         if ($result['newField'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+'))        {
 536                                                                 $result['definition'] = $this->parseFieldDef($parseString);
 537                                                                 if ($this->parse_error) { return $this->parse_error; }
 538                                                         } else return $this->parseError('No NEW field name found',$parseString);
 539                                                 break;
 540
 541                                                 case 'ADDKEY':
 542                                                 case 'ADDPRIMARYKEY':
 543                                                         $result['KEY'] = $fieldKey;
 544                                                         $result['fields'] = $this->getValue($parseString,'_LIST');
 545                                                         if ($this->parse_error) { return $this->parse_error; }
 546                                                 break;
 547                                                 case 'DROPKEY':
 548                                                         $result['KEY'] = $fieldKey;
 549                                                 break;
 550                                                 case 'DROPPRIMARYKEY':
 551                                                         // ??? todo!
 552                                                 break;
 553                                         }
 554                                 } else return $this->parseError('No field name found',$parseString);
 555                         } else return $this->parseError('No action CHANGE, DROP or ADD found!',$parseString);
 556                 } else return $this->parseError('No table found!',$parseString);
 557
 558                         // Should be no more content now:
 559                 if ($parseString)       {
 560                         return $this->parseError('Still content in clause after parsing!',$parseString);
 561                 }
 562
 563                 return $result;
 564         }
 565
 566         /**
 567          * Parsing DROP TABLE query
 568          *
 569          * @param       string          SQL string starting with DROP TABLE
 570          * @return      mixed           Returns array with components of DROP TABLE query on success, otherwise an error message string.
 571          */
 572         function parseDROPTABLE($parseString)   {
 573
 574                         // Removing DROP TABLE
 575                 $parseString = $this->trimSQL($parseString);
 576                 $parseString = eregi_replace('^DROP[[:space:]]+TABLE[[:space:]]+','',$parseString);
 577
 578                         // Init output variable:
 579                 $result = array();
 580                 $result['type'] = 'DROPTABLE';
 581
 582                         // IF EXISTS
 583                 $result['ifExists']     = $this->nextPart($parseString, '^(IF[[:space:]]+EXISTS[[:space:]]+)');
 584
 585                         // Get table:
 586                 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
 587
 588                 if ($result['TABLE'])   {
 589
 590                                 // Should be no more content now:
 591                         if ($parseString)       {
 592                                 return $this->parseError('Still content in clause after parsing!',$parseString);
 593                         }
 594
 595                         return $result;
 596                 } else return $this->parseError('No table found!',$parseString);
 597         }
 598
 599
 600
 601
 602
 603
 604
 605
 606
 607
 608
 609
 610
 611
 612
 613
 614
 615         /**************************************
 616          *
 617          * SQL Parsing, helper functions for parts of queries
 618          *
 619          **************************************/
 620
 621         /**
 622          * Parsing the fields in the "SELECT [$selectFields] FROM" part of a query into an array.
 623          * The output from this function can be compiled back into a field list with ->compileFieldList()
 624          * Will detect the keywords "DESC" and "ASC" after the table name; thus is can be used for parsing the more simply ORDER BY and GROUP BY field lists as well!
 625          *
 626          * @param       string          The string with fieldnames, eg. "title, uid AS myUid, max(tstamp), count(*)" etc. NOTICE: passed by reference!
 627          * @param       string          Regular expressing to STOP parsing, eg. '^(FROM)([[:space:]]*)'
 628          * @return      array           If successful parsing, returns an array, otherwise an error string.
 629          * @see compileFieldList()
 630          */
 631         function parseFieldList(&$parseString, $stopRegex='')   {
 632
 633                         // Prepare variables:
 634                 $parseString = $this->trimSQL($parseString);
 635                 $this->lastStopKeyWord = '';
 636                 $this->parse_error = '';
 637
 638
 639                 $stack = array();       // Contains the parsed content
 640                 $pnt = 0;                       // Pointer to positions in $stack
 641                 $level = 0;                     // Indicates the parenthesis level we are at.
 642                 $loopExit = 0;          // Recursivity brake.
 643
 644                         // $parseString is continously shortend by the process and we keep parsing it till it is zero:
 645                 while (strlen($parseString)) {
 646
 647                                 // Checking if we are inside / outside parenthesis (in case of a function like count(), max(), min() etc...):
 648                         if ($level>0)   {       // Inside parenthesis here (does NOT detect if values in quotes are used, the only token is ")" or "("):
 649
 650                                         // Accumulate function content until next () parenthesis:
 651                                 $funcContent = $this->nextPart($parseString,'^([^()]*.)');
 652                                 $stack[$pnt]['func_content.'][] = array(
 653                                         'level' => $level,
 654                                         'func_content' => substr($funcContent,0,-1)
 655                                 );
 656                                 $stack[$pnt]['func_content'].= $funcContent;
 657
 658                                         // Detecting ( or )
 659                                 switch(substr($stack[$pnt]['func_content'],-1)) {
 660                                         case '(':
 661                                                 $level++;
 662                                         break;
 663                                         case ')':
 664                                                 $level--;
 665                                                 if (!$level)    {       // If this was the last parenthesis:
 666                                                         $stack[$pnt]['func_content'] = substr($stack[$pnt]['func_content'],0,-1);
 667                                                         $parseString = ltrim($parseString);     // Remove any whitespace after the parenthesis.
 668                                                 }
 669                                         break;
 670                                 }
 671                         } else {        // Outside parenthesis, looking for next field:
 672
 673                                         // Looking for a known function (only known functions supported)
 674                                 $func = $this->nextPart($parseString,'^(count|max|min|floor|sum|avg)[[:space:]]*\(');
 675                                 if ($func)      {
 676                                         $parseString = trim(substr($parseString,1));    // Strip of "("
 677                                         $stack[$pnt]['type'] = 'function';
 678                                         $stack[$pnt]['function'] = $func;
 679                                         $level++;       // increse parenthesis level counter.
 680                                 } else {
 681                                                 // Otherwise, look for regular fieldname:
 682                                         if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]\*._]+)(,|[[:space:]]+)'))   {
 683                                                 $stack[$pnt]['type'] = 'field';
 684
 685                                                         // Explode fieldname into field and table:
 686                                                 $tableField = explode('.',$fieldName,2);
 687                                                 if (count($tableField)==2)      {
 688                                                         $stack[$pnt]['table'] = $tableField[0];
 689                                                         $stack[$pnt]['field'] = $tableField[1];
 690                                                 } else {
 691                                                         $stack[$pnt]['table'] = '';
 692                                                         $stack[$pnt]['field'] = $tableField[0];
 693                                                 }
 694                                         } else {
 695                                                 return $this->parseError('No field name found as expected',$parseString);
 696                                         }
 697                                 }
 698                         }
 699
 700                                 // After a function or field we look for "AS" alias and a comma to separate to the next field in the list:
 701                         if (!$level)    {
 702
 703                                         // Looking for "AS" alias:
 704                                 if ($as = $this->nextPart($parseString,'^(AS)[[:space:]]+'))    {
 705                                         $stack[$pnt]['as'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)');
 706                                         $stack[$pnt]['as_keyword'] = $as;
 707                                 }
 708
 709                                         // Looking for "ASC" or "DESC" keywords (for ORDER BY)
 710                                 if ($sDir = $this->nextPart($parseString,'^(ASC|DESC)([[:space:]]+|,)'))        {
 711                                         $stack[$pnt]['sortDir'] = $sDir;
 712                                 }
 713
 714                                         // Looking for stop-keywords:
 715                                 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))   {
 716                                         $this->lastStopKeyWord = strtoupper(ereg_replace('[[:space:]]*','',$this->lastStopKeyWord));
 717                                         return $stack;
 718                                 }
 719
 720                                         // Looking for comma (since the stop-keyword did not trigger a return...)
 721                                 if (strlen($parseString) && !$this->nextPart($parseString,'^(,)'))      {
 722                                         return $this->parseError('No comma found as expected',$parseString);
 723                                 }
 724
 725                                         // Increasing pointer:
 726                                 $pnt++;
 727                         }
 728
 729                                 // Check recursivity brake:
 730                         $loopExit++;
 731                         if ($loopExit>500)      {
 732                                 return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
 733                         }
 734                 }
 735
 736                         // Return result array:
 737                 return $stack;
 738         }
 739
 740         /**
 741          * Parsing the tablenames in the "FROM [$parseString] WHERE" part of a query into an array.
 742          * The success of this parsing determines if that part of the query is supported by TYPO3.
 743          *
 744          * @param       string          list of tables, eg. "pages, tt_content" or "pages A, pages B". NOTICE: passed by reference!
 745          * @param       string          Regular expressing to STOP parsing, eg. '^(WHERE)([[:space:]]*)'
 746          * @return      array           If successful parsing, returns an array, otherwise an error string.
 747          * @see compileFromTables()
 748          */
 749         function parseFromTables(&$parseString, $stopRegex='')  {
 750
 751                         // Prepare variables:
 752                 $parseString = $this->trimSQL($parseString);
 753                 $this->lastStopKeyWord = '';
 754                 $this->parse_error = '';
 755
 756                 $stack = array();       // Contains the parsed content
 757                 $pnt = 0;                       // Pointer to positions in $stack
 758                 $loopExit = 0;          // Recursivity brake.
 759
 760                         // $parseString is continously shortend by the process and we keep parsing it till it is zero:
 761                 while (strlen($parseString)) {
 762
 763                                 // Looking for the table:
 764                         if ($stack[$pnt]['table'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)'))   {
 765                                 if ($as = $this->nextPart($parseString,'^(AS)[[:space:]]+'))    {
 766                                         $stack[$pnt]['as'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)');
 767                                         $stack[$pnt]['as_keyword'] = $as;
 768                                 }
 769                         } else return $this->parseError('No table name found as expected!',$parseString);
 770
 771                                 // Looking for JOIN
 772                         if ($join = $this->nextPart($parseString,'^(JOIN|LEFT[[:space:]]+JOIN)[[:space:]]+'))   {
 773                                 $stack[$pnt]['JOIN']['type'] = $join;
 774                                 if ($stack[$pnt]['JOIN']['withTable'] = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]+ON[[:space:]]+',1))   {
 775                                         $field1 = $this->nextPart($parseString,'^([[:alnum:]_.]+)[[:space:]]*=[[:space:]]*',1);
 776                                         $field2 = $this->nextPart($parseString,'^([[:alnum:]_.]+)[[:space:]]+');
 777                                         if ($field1 && $field2) {
 778                                                 $stack[$pnt]['JOIN']['ON'] = array($field1,$field2);
 779                                         } else return $this->parseError('No join fields found!',$parseString);
 780                                 } else  return $this->parseError('No join table found!',$parseString);
 781                         }
 782
 783                                 // Looking for stop-keywords:
 784                         if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))   {
 785                                 $this->lastStopKeyWord = strtoupper(ereg_replace('[[:space:]]*','',$this->lastStopKeyWord));
 786                                 return $stack;
 787                         }
 788
 789                                 // Looking for comma:
 790                         if (strlen($parseString) && !$this->nextPart($parseString,'^(,)'))      {
 791                                 return $this->parseError('No comma found as expected',$parseString);
 792                         }
 793
 794                                 // Increasing pointer:
 795                         $pnt++;
 796
 797                                 // Check recursivity brake:
 798                         $loopExit++;
 799                         if ($loopExit>500)      {
 800                                 return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
 801                         }
 802                 }
 803
 804                         // Return result array:
 805                 return $stack;
 806         }
 807
 808         /**
 809          * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array.
 810          * The success of this parsing determines if that part of the query is supported by TYPO3.
 811          *
 812          * @param       string          WHERE clause to parse. NOTICE: passed by reference!
 813          * @param       string          Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)'
 814          * @return      mixed           If successful parsing, returns an array, otherwise an error string.
 815          */
 816         function parseWhereClause(&$parseString, $stopRegex='') {
 817
 818                         // Prepare variables:
 819                 $parseString = $this->trimSQL($parseString);
 820                 $this->lastStopKeyWord = '';
 821                 $this->parse_error = '';
 822
 823                 $stack = array(0 => array());   // Contains the parsed content
 824                 $pnt = array(0 => 0);                   // Pointer to positions in $stack
 825                 $level = 0;                                             // Determines parenthesis level
 826                 $loopExit = 0;                                  // Recursivity brake.
 827
 828                         // $parseString is continously shortend by the process and we keep parsing it till it is zero:
 829                 while (strlen($parseString)) {
 830
 831                                 // Look for next parenthesis level:
 832                         $newLevel = $this->nextPart($parseString,'^([(])');
 833                         if ($newLevel=='(')     {                       // If new level is started, manage stack/pointers:
 834                                 $level++;                                       // Increase level
 835                                 $pnt[$level] = 0;                       // Reset pointer for this level
 836                                 $stack[$level] = array();       // Reset stack for this level
 837                         } else {        // If no new level is started, just parse the current level:
 838
 839                                         // Find "modifyer", eg. "NOT or !"
 840                                 $stack[$level][$pnt[$level]]['modifier'] = trim($this->nextPart($parseString,'^(!|NOT[[:space:]]+)'));
 841
 842                                         // Fieldname:
 843                                 if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]._]+)([[:space:]]+|&|<=|>=|<|>|=|!=|IS)'))   {
 844
 845                                                 // Parse field name into field and table:
 846                                         $tableField = explode('.',$fieldName,2);
 847                                         if (count($tableField)==2)      {
 848                                                 $stack[$level][$pnt[$level]]['table'] = $tableField[0];
 849                                                 $stack[$level][$pnt[$level]]['field'] = $tableField[1];
 850                                         } else {
 851                                                 $stack[$level][$pnt[$level]]['table'] = '';
 852                                                 $stack[$level][$pnt[$level]]['field'] = $tableField[0];
 853                                         }
 854                                 } else {
 855                                         return $this->parseError('No field name found as expected',$parseString);
 856                                 }
 857
 858                                         // See if the value is calculated. Support only for "&" (boolean AND) at the moment:
 859                                 $stack[$level][$pnt[$level]]['calc'] = $this->nextPart($parseString,'^(&)');
 860                                 if (strlen($stack[$level][$pnt[$level]]['calc']))       {
 861                                                 // Finding value for calculation:
 862                                         $stack[$level][$pnt[$level]]['calc_value'] = $this->getValue($parseString);
 863                                 }
 864
 865                                         // Find "comparator":
 866                                 $stack[$level][$pnt[$level]]['comparator'] = $this->nextPart($parseString,'^(<=|>=|<|>|=|!=|NOT[[:space:]]+IN|IN|NOT[[:space:]]+LIKE|LIKE|IS)');
 867                                 if (strlen($stack[$level][$pnt[$level]]['comparator'])) {
 868                                                 // Finding value for comparator:
 869                                         $stack[$level][$pnt[$level]]['value'] = $this->getValue($parseString,$stack[$level][$pnt[$level]]['comparator']);
 870                                         if ($this->parse_error) { return $this->parse_error; }
 871                                 }
 872
 873                                         // Finished, increase pointer:
 874                                 $pnt[$level]++;
 875
 876                                         // Checking if the current level is ended, in that case do stack management:
 877                                 while ($this->nextPart($parseString,'^([)])'))  {
 878                                         $level--;               // Decrease level:
 879                                         $stack[$level][$pnt[$level]]['sub'] = $stack[$level+1];         // Copy stack
 880                                         $pnt[$level]++; // Increase pointer of the new level
 881
 882                                                 // Make recursivity check:
 883                                         $loopExit++;
 884                                         if ($loopExit>500)      {
 885                                                 return $this->parseError('More than 500 loops (in search for exit parenthesis), exiting prematurely...',$parseString);
 886                                         }
 887                                 }
 888
 889                                         // Detecting the operator for the next level; support for AND, OR and &&):
 890                                 $op = $this->nextPart($parseString,'^(AND|OR|AND[[:space:]]+NOT)(\(|[[:space:]]+)');
 891                                 if ($op)        {
 892                                         $stack[$level][$pnt[$level]]['operator'] = $op;
 893                                 } elseif (strlen($parseString)) {
 894
 895                                                 // Looking for stop-keywords:
 896                                         if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))   {
 897                                                 $this->lastStopKeyWord = strtoupper(ereg_replace('[[:space:]]*','',$this->lastStopKeyWord));
 898                                                 return $stack[0];
 899                                         } else {
 900                                                 return $this->parseError('No operator, but parsing not finished.',$parseString);
 901                                         }
 902                                 }
 903                         }
 904
 905                                 // Make recursivity check:
 906                         $loopExit++;
 907                         if ($loopExit>500)      {
 908                                 return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
 909                         }
 910                 }
 911
 912                         // Return the stacks lowest level:
 913                 return $stack[0];
 914         }
 915
 916         /**
 917          * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array.
 918          * The success of this parsing determines if that part of the query is supported by TYPO3.
 919          *
 920          * @param       string          WHERE clause to parse. NOTICE: passed by reference!
 921          * @param       string          Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)'
 922          * @return      mixed           If successful parsing, returns an array, otherwise an error string.
 923          */
 924         function parseFieldDef(&$parseString, $stopRegex='')    {
 925                         // Prepare variables:
 926                 $parseString = $this->trimSQL($parseString);
 927                 $this->lastStopKeyWord = '';
 928                 $this->parse_error = '';
 929
 930                 $result = array();
 931
 932                         // Field type:
 933                 if ($result['fieldType'] =  $this->nextPart($parseString,'^(int|smallint|tinyint|mediumint|double|varchar|char|text|tinytext|mediumtext|blob|tinyblob|mediumblob|longblob)([[:space:]]+|\()'))  {
 934
 935                                 // Looking for value:
 936                         if (substr($parseString,0,1)=='(')      {
 937                                 $parseString = substr($parseString,1);
 938                                 if ($result['value'] =  $this->nextPart($parseString,'^([^)]*)'))       {
 939                                         $parseString = ltrim(substr($parseString,1));
 940                                 } else return $this->parseError('No end-parenthesis for value found!',$parseString);
 941                         }
 942
 943                                 // Looking for keywords
 944                         while($keyword = $this->nextPart($parseString,'^(DEFAULT|NOT[[:space:]]+NULL|AUTO_INCREMENT|UNSIGNED)([[:space:]]+|,|\))'))     {
 945                                 $keywordCmp = strtoupper(ereg_replace('[[:space:]]*','',$keyword));
 946
 947                                 $result['featureIndex'][$keywordCmp]['keyword'] = $keyword;
 948
 949                                 switch($keywordCmp)     {
 950                                         case 'DEFAULT':
 951                                                 $result['featureIndex'][$keywordCmp]['value'] = $this->getValue($parseString);
 952                                         break;
 953                                 }
 954                         }
 955                 } else return $this->parseError('Field type unknown!',$parseString);
 956
 957                 return $result;
 958         }
 959
 960
 961
 962
 963
 964
 965
 966
 967
 968
 969
 970         /************************************
 971          *
 972          * Parsing: Helper functions
 973          *
 974          ************************************/
 975
 976         /**
 977          * Strips of a part of the parseString and returns the matching part.
 978          * Helper function for the parsing methods.
 979          *
 980          * @param       string          Parse string; if $regex finds anything the value of the first () level will be stripped of the string in the beginning. Further $parseString is left-trimmed (on success). Notice; parsestring is passed by reference.
 981          * @param       string          Regex to find a matching part in the beginning of the string. Rules: You MUST start the regex with "^" (finding stuff in the beginning of string) and the result of the first parenthesis is what will be returned to you (and stripped of the string). Eg. '^(AND|OR|&&)[[:space:]]+' will return AND, OR or && if found and having one of more whitespaces after it, plus shorten $parseString with that match and any space after (by ltrim())
 982          * @param       boolean         If set the full match of the regex is stripped of the beginning of the string!
 983          * @return      string          The value of the first parenthesis level of the REGEX.
 984          */
 985         function nextPart(&$parseString,$regex,$trimAll=FALSE)  {
 986                 if (eregi($regex,$parseString.' ', $reg))       {       // Adding space char because [[:space:]]+ is often a requirement in regex's
 987                         $parseString = ltrim(substr($parseString,strlen($reg[$trimAll?0:1])));
 988                         return $reg[1];
 989                 }
 990         }
 991
 992         /**
 993          * Finds value in beginning of $parseString, returns result and strips it of parseString
 994          *
 995          * @param       string          The parseString, eg. "(0,1,2,3) ..." or "('asdf','qwer') ..." or "1234 ..." or "'My string value here' ..."
 996          * @param       string          The comparator used before. If "NOT IN" or "IN" then the value is expected to be a list of values. Otherwise just an integer (un-quoted) or string (quoted)
 997          * @return      string          The value (string/integer). Otherwise an array with error message in first key (0)
 998          */
 999         function getValue(&$parseString,$comparator='') {
1000                 if (t3lib_div::inList('NOTIN,IN,_LIST',strtoupper(ereg_replace('[[:space:]]','',$comparator)))) {       // List of values:
1001                         if ($this->nextPart($parseString,'^([(])'))     {
1002                                 $listValues = array();
1003                                 $comma=',';
1004
1005                                 while($comma==',')      {
1006                                         $listValues[] = $this->getValue($parseString);
1007                                         $comma = $this->nextPart($parseString,'^([,])');
1008                                 }
1009
1010                                 $out = $this->nextPart($parseString,'^([)])');
1011                                 if ($out)       {
1012                                         if ($comparator=='_LIST')       {
1013                                                 $kVals = array();
1014                                                 foreach ($listValues as $vArr)  {
1015                                                         $kVals[] = $vArr[0];
1016                                                 }
1017                                                 return $kVals;
1018                                         } else {
1019                                                 return $listValues;
1020                                         }
1021                                 } else return array($this->parseError('No ) parenthesis in list',$parseString));
1022                         } else return array($this->parseError('No ( parenthesis starting the list',$parseString));
1023
1024                 } else {        // Just plain string value, in quotes or not:
1025
1026                                 // Quote?
1027                         $firstChar = substr($parseString,0,1);
1028
1029                         switch($firstChar)      {
1030                                 case '"':
1031                                         return array($this->getValueInQuotes($parseString,'"'),'"');
1032                                 break;
1033                                 case "'":
1034                                         return array($this->getValueInQuotes($parseString,"'"),"'");
1035                                 break;
1036                                 default:
1037                                         if (eregi('^([[:alnum:]._-]+)',$parseString, $reg))     {
1038                                                 $parseString = ltrim(substr($parseString,strlen($reg[0])));
1039                                                 return array($reg[1]);
1040                                         }
1041                                 break;
1042                         }
1043                 }
1044         }
1045
1046         /**
1047          * Get value in quotes from $parseString.
1048          * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1049          *
1050          * @param       string          String from which to find value in quotes. Notice that $parseString is passed by reference and is shortend by the output of this function.
1051          * @param       string          The quote used; input either " or '
1052          * @return      string          The value, passed through stripslashes() !
1053          */
1054         function getValueInQuotes(&$parseString,$quote) {
1055
1056                 $parts = explode($quote,substr($parseString,1));
1057                 $buffer = '';
1058                 foreach($parts as $k => $v)     {
1059                         $buffer.=$v;
1060
1061                         unset($reg);
1062                         ereg('[\]*$',$v,$reg);
1063                         if (strlen($reg[0])%2)  {
1064                                 $buffer.=$quote;
1065                         } else {
1066                                 $parseString = ltrim(substr($parseString,strlen($buffer)+2));
1067                                 return $this->parseStripslashes($buffer);
1068                         }
1069                 }
1070         }
1071
1072         /**
1073          * Strip slashes function used for parsing
1074          * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1075          *
1076          * @param       string          Input string
1077          * @return      string          Output string
1078          */
1079         function parseStripslashes($str)        {
1080                 $search = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
1081                 $replace = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
1082
1083                 return str_replace($search, $replace, $str);
1084         }
1085
1086         /**
1087          * Add slashes function used for compiling queries
1088          * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1089          *
1090          * @param       string          Input string
1091          * @return      string          Output string
1092          */
1093         function compileAddslashes($str)        {
1094                 $search = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
1095                 $replace = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
1096
1097                 return str_replace($search, $replace, $str);
1098         }
1099
1100         /**
1101          * Setting the internal error message value, $this->parse_error and returns that value.
1102          *
1103          * @param       string          Input error message
1104          * @param       string          Remaining query to parse.
1105          * @return      string          Error message.
1106          */
1107         function parseError($msg,$restQuery)    {
1108                 $this->parse_error = 'SQL engine parse ERROR: '.$msg.': near "'.substr($restQuery,0,50).'"';
1109                 return $this->parse_error;
1110         }
1111
1112         /**
1113          * Trimming SQL as preparation for parsing.
1114          * ";" in the end is stripped of.
1115          * White space is trimmed away around the value
1116          * A single space-char is added in the end
1117          *
1118          * @param       string          Input string
1119          * @return      string          Output string
1120          */
1121         function trimSQL($str)  {
1122                 return trim(ereg_replace('[[:space:];]*$','',$str)).' ';
1123         }
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136         /*************************
1137          *
1138          * Compiling queries
1139          *
1140          *************************/
1141
1142         /**
1143          * Compiles an SQL query from components
1144          *
1145          * @param       array           Array of SQL query components
1146          * @return      string          SQL query
1147          * @see parseSQL()
1148          */
1149         function compileSQL($components)        {
1150                 switch($components['type'])     {
1151                         case 'SELECT':
1152                                 $query = $this->compileSELECT($components);
1153                         break;
1154                         case 'UPDATE':
1155                                 $query = $this->compileUPDATE($components);
1156                         break;
1157                         case 'INSERT':
1158                                 $query = $this->compileINSERT($components);
1159                         break;
1160                         case 'DELETE':
1161                                 $query = $this->compileDELETE($components);
1162                         break;
1163                         case 'EXPLAIN':
1164                                 $query = 'EXPLAIN '.$this->compileSELECT($components);
1165                         break;
1166                         case 'DROPTABLE':
1167                                 $query = 'DROP TABLE'.($components['ifExists']?' IF EXISTS':'').' '.$components['TABLE'];
1168                         break;
1169                         case 'CREATETABLE':
1170                                 $query = $this->compileCREATETABLE($components);
1171                         break;
1172                         case 'ALTERTABLE':
1173                                 $query = $this->compileALTERTABLE($components);
1174                         break;
1175                 }
1176
1177                 return $query;
1178         }
1179
1180         /**
1181          * Compiles a SELECT statement from components array
1182          *
1183          * @param       array           Array of SQL query components
1184          * @return      string          SQL SELECT query
1185          * @see parseSELECT()
1186          */
1187         function compileSELECT($components)     {
1188
1189                         // Initialize:
1190                 $where = $this->compileWhereClause($components['WHERE']);
1191                 $groupBy = $this->compileFieldList($components['GROUPBY']);
1192                 $orderBy = $this->compileFieldList($components['ORDERBY']);
1193                 $limit = $components['LIMIT'];
1194
1195                         // Make query:
1196                 $query = 'SELECT '.($components['STRAIGHT_JOIN'] ? $components['STRAIGHT_JOIN'].'' : '').'
1197                                 '.$this->compileFieldList($components['SELECT']).'
1198                                 FROM '.$this->compileFromTables($components['FROM']).
1199                                         (strlen($where)?'
1200                                 WHERE '.$where : '').
1201                                         (strlen($groupBy)?'
1202                                 GROUP BY '.$groupBy : '').
1203                                         (strlen($orderBy)?'
1204                                 ORDER BY '.$orderBy : '').
1205                                         (strlen($limit)?'
1206                                 LIMIT '.$limit : '');
1207
1208                 return $query;
1209         }
1210
1211         /**
1212          * Compiles an UPDATE statement from components array
1213          *
1214          * @param       array           Array of SQL query components
1215          * @return      string          SQL UPDATE query
1216          * @see parseUPDATE()
1217          */
1218         function compileUPDATE($components)     {
1219
1220                         // Where clause:
1221                 $where = $this->compileWhereClause($components['WHERE']);
1222
1223                         // Fields
1224                 $fields = array();
1225                 foreach($components['FIELDS'] as $fN => $fV)    {
1226                         $fields[]=$fN.'='.$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1227                 }
1228
1229                         // Make query:
1230                 $query = 'UPDATE '.$components['TABLE'].' SET
1231                                 '.implode(',
1232                                 ',$fields).'
1233                                 '.(strlen($where)?'
1234                                 WHERE '.$where : '');
1235
1236                 return $query;
1237         }
1238
1239         /**
1240          * Compiles an INSERT statement from components array
1241          *
1242          * @param       array           Array of SQL query components
1243          * @return      string          SQL INSERT query
1244          * @see parseINSERT()
1245          */
1246         function compileINSERT($components)     {
1247
1248                 if ($components['VALUES_ONLY']) {
1249                                 // Initialize:
1250                         $fields = array();
1251                         foreach($components['VALUES_ONLY'] as $fV)      {
1252                                 $fields[]=$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1253                         }
1254
1255                                 // Make query:
1256                         $query = 'INSERT INTO '.$components['TABLE'].'
1257                                         VALUES
1258                                         ('.implode(',
1259                                         ',$fields).')';
1260                 } else {
1261                                 // Initialize:
1262                         $fields = array();
1263                         foreach($components['FIELDS'] as $fN => $fV)    {
1264                                 $fields[$fN]=$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1265                         }
1266
1267                                 // Make query:
1268                         $query = 'INSERT INTO '.$components['TABLE'].'
1269                                         ('.implode(',
1270                                         ',array_keys($fields)).')
1271                                         VALUES
1272                                         ('.implode(',
1273                                         ',$fields).')';
1274                 }
1275
1276                 return $query;
1277         }
1278
1279         /**
1280          * Compiles an DELETE statement from components array
1281          *
1282          * @param       array           Array of SQL query components
1283          * @return      string          SQL DELETE query
1284          * @see parseDELETE()
1285          */
1286         function compileDELETE($components)     {
1287
1288                         // Where clause:
1289                 $where = $this->compileWhereClause($components['WHERE']);
1290
1291                         // Make query:
1292                 $query = 'DELETE FROM '.$components['TABLE'].
1293                                 (strlen($where)?'
1294                                 WHERE '.$where : '');
1295
1296                 return $query;
1297         }
1298
1299         /**
1300          * Compiles a CREATE TABLE statement from components array
1301          *
1302          * @param       array           Array of SQL query components
1303          * @return      string          SQL CREATE TABLE query
1304          * @see parseCREATETABLE()
1305          */
1306         function compileCREATETABLE($components)        {
1307
1308                         // Create fields and keys:
1309                 $fieldsKeys = array();
1310                 foreach($components['FIELDS'] as $fN => $fCfg)  {
1311                         $fieldsKeys[]=$fN.' '.$this->compileFieldCfg($fCfg['definition']);
1312                 }
1313                 foreach($components['KEYS'] as $kN => $kCfg)    {
1314                         if ($kN == 'PRIMARYKEY')        {
1315                                 $fieldsKeys[]='PRIMARY KEY ('.implode(',', $kCfg).')';
1316                         } else {
1317                                 $fieldsKeys[]='KEY '.$kN.' ('.implode(',', $kCfg).')';
1318                         }
1319                 }
1320
1321                         // Make query:
1322                 $query = 'CREATE TABLE '.$components['TABLE'].' (
1323                         '.implode(',
1324                         ', $fieldsKeys).'
1325                         )'.($components['tableType'] ? ' TYPE='.$components['tableType'] : '');
1326
1327                 return $query;
1328         }
1329
1330         /**
1331          * Compiles an ALTER TABLE statement from components array
1332          *
1333          * @param       array           Array of SQL query components
1334          * @return      string          SQL ALTER TABLE query
1335          * @see parseALTERTABLE()
1336          */
1337         function compileALTERTABLE($components) {
1338
1339                         // Make query:
1340                 $query = 'ALTER TABLE '.$components['TABLE'].' '.$components['action'].' '.($components['FIELD']?$components['FIELD']:$components['KEY']);
1341
1342                         // Based on action, add the final part:
1343                 switch(strtoupper(ereg_replace('[[:space:]]','',$components['action'])))        {
1344                         case 'ADD':
1345                                 $query.=' '.$this->compileFieldCfg($components['definition']);
1346                         break;
1347                         case 'CHANGE':
1348                                 $query.=' '.$components['newField'].' '.$this->compileFieldCfg($components['definition']);
1349                         break;
1350                         case 'DROP':
1351                         case 'DROPKEY':
1352                         break;
1353                         case 'ADDKEY':
1354                         case 'ADDPRIMARYKEY':
1355                                 $query.=' ('.implode(',',$components['fields']).')';
1356                         break;
1357                 }
1358
1359                         // Return query
1360                 return $query;
1361         }
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376         /**************************************
1377          *
1378          * Compiling queries, helper functions for parts of queries
1379          *
1380          **************************************/
1381
1382         /**
1383          * Compiles a "SELECT [output] FROM..:" field list based on input array (made with ->parseFieldList())
1384          * Can also compile field lists for ORDER BY and GROUP BY.
1385          *
1386          * @param       array           Array of select fields, (made with ->parseFieldList())
1387          * @return      string          Select field string
1388          * @see parseFieldList()
1389          */
1390         function compileFieldList($selectFields)        {
1391
1392                         // Prepare buffer variable:
1393                 $outputParts = array();
1394
1395                         // Traverse the selectFields if any:
1396                 if (is_array($selectFields))    {
1397                         foreach($selectFields as $k => $v)      {
1398
1399                                         // Detecting type:
1400                                 switch($v['type'])      {
1401                                         case 'function':
1402                                                 $outputParts[$k] = $v['function'].'('.$v['func_content'].')';
1403                                         break;
1404                                         case 'field':
1405                                                 $outputParts[$k] = ($v['table']?$v['table'].'.':'').$v['field'];
1406                                         break;
1407                                 }
1408
1409                                         // Alias:
1410                                 if ($v['as'])   {
1411                                         $outputParts[$k].= ' '.$v['as_keyword'].' '.$v['as'];
1412                                 }
1413
1414                                         // Specifically for ORDER BY and GROUP BY field lists:
1415                                 if ($v['sortDir'])      {
1416                                         $outputParts[$k].= ' '.$v['sortDir'];
1417                                 }
1418                         }
1419                 }
1420
1421                         // Return imploded buffer:
1422                 return implode(', ',$outputParts);
1423         }
1424
1425         /**
1426          * Compiles a "FROM [output] WHERE..:" table list based on input array (made with ->parseFromTables())
1427          *
1428          * @param       array           Array of table names, (made with ->parseFromTables())
1429          * @return      string          Table name string
1430          * @see parseFromTables()
1431          */
1432         function compileFromTables($tablesArray)        {
1433
1434                         // Prepare buffer variable:
1435                 $outputParts = array();
1436
1437                         // Traverse the table names:
1438                 if (is_array($tablesArray))     {
1439                         foreach($tablesArray as $k => $v)       {
1440
1441                                         // Set table name:
1442                                 $outputParts[$k] = $v['table'];
1443
1444                                         // Add alias AS if there:
1445                                 if ($v['as'])   {
1446                                         $outputParts[$k].= ' '.$v['as_keyword'].' '.$v['as'];
1447                                 }
1448
1449                                 if (is_array($v['JOIN']))       {
1450                                         $outputParts[$k].= ' '.$v['JOIN']['type'].' '.$v['JOIN']['withTable'].' ON '.implode('=',$v['JOIN']['ON']);
1451                                 }
1452
1453                         }
1454                 }
1455
1456                         // Return imploded buffer:
1457                 return implode(', ',$outputParts);
1458         }
1459
1460         /**
1461          * Implodes an array of WHERE clause configuration into a WHERE clause.
1462          * NOTICE: MIGHT BY A TEMPORARY FUNCTION. Use for debugging only!
1463          *
1464          * @param       array           WHERE clause configuration
1465          * @return      string          WHERE clause as string.
1466          * @see explodeWhereClause()
1467          */
1468         function compileWhereClause($clauseArray)       {
1469
1470                         // Prepare buffer variable:
1471                 $output='';
1472
1473                         // Traverse clause array:
1474                 if (is_array($clauseArray))     {
1475                         foreach($clauseArray as $k => $v)       {
1476
1477                                         // Set operator:
1478                                 $output.=$v['operator'] ? ' '.$v['operator'] : '';
1479
1480                                         // Look for sublevel:
1481                                 if (is_array($v['sub']))        {
1482                                         $output.=' ('.trim($this->compileWhereClause($v['sub'])).')';
1483                                 } else {
1484
1485                                                 // Set field/table with modifying prefix if any:
1486                                         $output.=' '.trim($v['modifier'].' '.($v['table']?$v['table'].'.':'').$v['field']);
1487
1488                                                 // Set calculation, if any:
1489                                         if ($v['calc']) {
1490                                                 $output.=$v['calc'].$v['calc_value'][1].$this->compileAddslashes($v['calc_value'][0]).$v['calc_value'][1];
1491                                         }
1492
1493                                                 // Set comparator:
1494                                         if ($v['comparator'])   {
1495                                                 $output.=' '.$v['comparator'];
1496
1497                                                         // Detecting value type; list or plain:
1498                                                 if (t3lib_div::inList('NOTIN,IN',strtoupper(ereg_replace('[[:space:]]','',$v['comparator']))))  {
1499                                                         $valueBuffer = array();
1500                                                         foreach($v['value'] as $realValue)      {
1501                                                                 $valueBuffer[]=$realValue[1].$this->compileAddslashes($realValue[0]).$realValue[1];
1502                                                         }
1503                                                         $output.=' ('.trim(implode(',',$valueBuffer)).')';
1504                                                 } else {
1505                                                         $output.=' '.$v['value'][1].$this->compileAddslashes($v['value'][0]).$v['value'][1];
1506                                                 }
1507                                         }
1508                                 }
1509                         }
1510                 }
1511
1512                         // Return output buffer:
1513                 return $output;
1514         }
1515
1516         /**
1517          * Compile field definition
1518          *
1519          * @param       array           Field definition parts
1520          * @return      string          Field definition string
1521          */
1522         function compileFieldCfg($fieldCfg)     {
1523
1524                         // Set type:
1525                 $cfg = $fieldCfg['fieldType'];
1526
1527                         // Add value, if any:
1528                 if (strlen($fieldCfg['value'])) {
1529                         $cfg.='('.$fieldCfg['value'].')';
1530                 }
1531
1532                         // Add additional features:
1533                 if (is_array($fieldCfg['featureIndex']))        {
1534                         foreach($fieldCfg['featureIndex'] as $featureDef)       {
1535                                 $cfg.=' '.$featureDef['keyword'];
1536
1537                                         // Add value if found:
1538                                 if (is_array($featureDef['value']))     {
1539                                         $cfg.=' '.$featureDef['value'][1].$this->compileAddslashes($featureDef['value'][0]).$featureDef['value'][1];
1540                                 }
1541                         }
1542                 }
1543
1544                         // Return field definition string:
1545                 return $cfg;
1546         }
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558         /*************************
1559          *
1560          * Debugging
1561          *
1562          *************************/
1563
1564         /**
1565          * Check parsability of input SQL part string; Will parse and re-compile after which it is compared
1566          *
1567          * @param       string          Part definition of string; "SELECT" = fieldlist (also ORDER BY and GROUP BY), "FROM" = table list, "WHERE" = Where clause.
1568          * @param       string          SQL string to verify parsability of
1569          * @return      mixed           Returns array with string 1 and 2 if error, otherwise false
1570          */
1571         function debug_parseSQLpart($part,$str) {
1572                 switch($part)   {
1573                         case 'SELECT':
1574                                 return $this->debug_parseSQLpartCompare($str,$this->compileFieldList($this->parseFieldList($str)));
1575                         break;
1576                         case 'FROM':
1577                                 return $this->debug_parseSQLpartCompare($str,$this->compileFromTables($this->parseFromTables($str)));
1578                         break;
1579                         case 'WHERE':
1580                                 return $this->debug_parseSQLpartCompare($str,$this->compileWhereClause($this->parseWhereClause($str)));
1581                         break;
1582                 }
1583         }
1584
1585         /**
1586          * Compare two query strins by stripping away whitespace.
1587          *
1588          * @param       string          SQL String 1
1589          * @param       string          SQL string 2
1590          * @param       boolean         If true, the strings are compared insensitive to case
1591          * @return      mixed           Returns array with string 1 and 2 if error, otherwise false
1592          */
1593         function debug_parseSQLpartCompare($str,$newStr,$caseInsensitive=FALSE) {
1594                 if ($caseInsensitive)   {
1595                         $str1 = strtoupper($str);
1596                         $str2 = strtoupper($newStr);
1597                 } else {
1598                         $str1 = $str;
1599                         $str2 = $newStr;
1600                 }
1601
1602                         // Fixing escaped chars:
1603                 $search = array('\0', '\n', '\r', '\Z');
1604                 $replace = array("\x00", "\x0a", "\x0d", "\x1a");
1605                 $str1 = str_replace($search, $replace, $str1);
1606                 $str2 = str_replace($search, $replace, $str2);
1607
1608                         # Normally, commented out since they are needed only in tricky cases...
1609 #               $str1 = stripslashes($str1);
1610 #               $str2 = stripslashes($str2);
1611
1612                 if (strcmp(ereg_replace('[[:space:]]','',$this->trimSQL($str1)),ereg_replace('[[:space:]]','',$this->trimSQL($str2))))  {
1613                         return array(
1614                                         ereg_replace('[[:space:]]+',' ',$str),
1615                                         ereg_replace('[[:space:]]+',' ',$newStr),
1616                                 );
1617                 }
1618         }
1619
1620         /**
1621          * Performs the ultimate test of the parser: Direct a SQL query in; You will get it back (through the parsed and re-compiled) if no problems, otherwise the script will print the error and exit
1622          *
1623          * @param       string          SQL query
1624          * @return      string          Query if all is well, otherwise exit.
1625          */
1626         function debug_testSQL($SQLquery)       {
1627 #               return $SQLquery;
1628 #debug(array($SQLquery));
1629
1630                         // Getting result array:
1631                 $parseResult = $this->parseSQL($SQLquery);
1632
1633                         // If result array was returned, proceed. Otherwise show error and exit.
1634                 if (is_array($parseResult))     {
1635
1636                                 // Re-compile query:
1637                         $newQuery = $this->compileSQL($parseResult);
1638
1639                                 // TEST the new query:
1640                         $testResult = $this->debug_parseSQLpartCompare($SQLquery, $newQuery);
1641
1642                                 // Return new query if OK, otherwise show error and exit:
1643                         if (!is_array($testResult))     {
1644                                 return $newQuery;
1645                         } else {
1646                                 debug(array('ERROR MESSAGE'=>'Input query did not match the parsed and recompiled query exactly (not observing whitespace)', 'TEST result' => $testResult),'SQL parsing failed:');
1647                                 exit;
1648                         }
1649                 } else {
1650                         debug(array('query' => $SQLquery, 'ERROR MESSAGE'=>$parseResult),'SQL parsing failed:');
1651                         exit;
1652                 }
1653         }
1654 }
1655
1656
1657 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlparser.php']) {
1658         include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlparser.php']);
1659 }
1660 ?>