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