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