|
TYPO3 API
SVNRelease
|
00001 <?php 00002 /*************************************************************** 00003 * Copyright notice 00004 * 00005 * (c) 2004-2011 Kasper Skårhøj (kasperYYYY@typo3.com) 00006 * All rights reserved 00007 * 00008 * This script is part of the TYPO3 project. The TYPO3 project is 00009 * free software; you can redistribute it and/or modify 00010 * it under the terms of the GNU General Public License as published by 00011 * the Free Software Foundation; either version 2 of the License, or 00012 * (at your option) any later version. 00013 * 00014 * The GNU General Public License can be found at 00015 * http://www.gnu.org/copyleft/gpl.html. 00016 * A copy is found in the textfile GPL.txt and important notices to the license 00017 * from the author is found in LICENSE.txt distributed with these scripts. 00018 * 00019 * 00020 * This script is distributed in the hope that it will be useful, 00021 * but WITHOUT ANY WARRANTY; without even the implied warranty of 00022 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 00023 * GNU General Public License for more details. 00024 * 00025 * This copyright notice MUST APPEAR in all copies of the script! 00026 ***************************************************************/ 00027 /** 00028 * TYPO3 SQL parser 00029 * 00030 * $Id: class.t3lib_sqlparser.php 10121 2011-01-18 20:15:30Z ohader $ 00031 * 00032 * @author Kasper Skårhøj <kasperYYYY@typo3.com> 00033 */ 00034 /** 00035 * [CLASS/FUNCTION INDEX of SCRIPT] 00036 * 00037 * 00038 * 00039 * 107: class t3lib_sqlparser 00040 * 00041 * SECTION: SQL Parsing, full queries 00042 * 129: function parseSQL($parseString) 00043 * 192: function parseSELECT($parseString) 00044 * 261: function parseUPDATE($parseString) 00045 * 315: function parseINSERT($parseString) 00046 * 375: function parseDELETE($parseString) 00047 * 413: function parseEXPLAIN($parseString) 00048 * 435: function parseCREATETABLE($parseString) 00049 * 514: function parseALTERTABLE($parseString) 00050 * 583: function parseDROPTABLE($parseString) 00051 * 616: function parseCREATEDATABASE($parseString) 00052 * 00053 * SECTION: SQL Parsing, helper functions for parts of queries 00054 * 670: function parseFieldList(&$parseString, $stopRegex='') 00055 * 791: function parseFromTables(&$parseString, $stopRegex='') 00056 * 882: function parseWhereClause(&$parseString, $stopRegex='') 00057 * 990: function parseFieldDef(&$parseString, $stopRegex='') 00058 * 00059 * SECTION: Parsing: Helper functions 00060 * 1053: function nextPart(&$parseString,$regex,$trimAll=FALSE) 00061 * 1068: function getValue(&$parseString,$comparator='') 00062 * 1127: function getValueInQuotes(&$parseString,$quote) 00063 * 1153: function parseStripslashes($str) 00064 * 1167: function compileAddslashes($str) 00065 * 1182: function parseError($msg,$restQuery) 00066 * 1196: function trimSQL($str) 00067 * 00068 * SECTION: Compiling queries 00069 * 1225: function compileSQL($components) 00070 * 1263: function compileSELECT($components) 00071 * 1294: function compileUPDATE($components) 00072 * 1322: function compileINSERT($components) 00073 * 1362: function compileDELETE($components) 00074 * 1382: function compileCREATETABLE($components) 00075 * 1415: function compileALTERTABLE($components) 00076 * 00077 * SECTION: Compiling queries, helper functions for parts of queries 00078 * 1468: function compileFieldList($selectFields) 00079 * 1510: function compileFromTables($tablesArray) 00080 * 1551: function compileWhereClause($clauseArray) 00081 * 1605: function compileFieldCfg($fieldCfg) 00082 * 00083 * SECTION: Debugging 00084 * 1654: function debug_parseSQLpart($part,$str) 00085 * 1679: function debug_parseSQLpartCompare($str,$newStr,$caseInsensitive=FALSE) 00086 * 1712: function debug_testSQL($SQLquery) 00087 * 00088 * TOTAL FUNCTIONS: 35 00089 * (This index is automatically created/updated by the extension "extdeveval") 00090 * 00091 */ 00092 00093 00094 /** 00095 * TYPO3 SQL parser class. 00096 * 00097 * @author Kasper Skårhøj <kasperYYYY@typo3.com> 00098 * @package TYPO3 00099 * @subpackage t3lib 00100 */ 00101 class t3lib_sqlparser { 00102 00103 // Parser: 00104 var $parse_error = ''; // Parsing error string 00105 var $lastStopKeyWord = ''; // Last stop keyword used. 00106 00107 00108 /************************************* 00109 * 00110 * SQL Parsing, full queries 00111 * 00112 **************************************/ 00113 00114 /** 00115 * Parses any single SQL query 00116 * 00117 * @param string SQL query 00118 * @return array Result array with all the parts in - or error message string 00119 * @see compileSQL(), debug_testSQL() 00120 */ 00121 public function parseSQL($parseString) { 00122 // Prepare variables: 00123 $parseString = $this->trimSQL($parseString); 00124 $this->parse_error = ''; 00125 $result = array(); 00126 00127 // Finding starting keyword of string: 00128 $_parseString = $parseString; // Protecting original string... 00129 $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|TRUNCATE[[:space:]]+TABLE)[[:space:]]+'); 00130 $keyword = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $keyword)); 00131 00132 switch ($keyword) { 00133 case 'SELECT': 00134 // Parsing SELECT query: 00135 $result = $this->parseSELECT($parseString); 00136 break; 00137 case 'UPDATE': 00138 // Parsing UPDATE query: 00139 $result = $this->parseUPDATE($parseString); 00140 break; 00141 case 'INSERTINTO': 00142 // Parsing INSERT query: 00143 $result = $this->parseINSERT($parseString); 00144 break; 00145 case 'DELETEFROM': 00146 // Parsing DELETE query: 00147 $result = $this->parseDELETE($parseString); 00148 break; 00149 case 'EXPLAIN': 00150 // Parsing EXPLAIN SELECT query: 00151 $result = $this->parseEXPLAIN($parseString); 00152 break; 00153 case 'DROPTABLE': 00154 // Parsing DROP TABLE query: 00155 $result = $this->parseDROPTABLE($parseString); 00156 break; 00157 case 'ALTERTABLE': 00158 // Parsing ALTER TABLE query: 00159 $result = $this->parseALTERTABLE($parseString); 00160 break; 00161 case 'CREATETABLE': 00162 // Parsing CREATE TABLE query: 00163 $result = $this->parseCREATETABLE($parseString); 00164 break; 00165 case 'CREATEDATABASE': 00166 // Parsing CREATE DATABASE query: 00167 $result = $this->parseCREATEDATABASE($parseString); 00168 break; 00169 case 'TRUNCATETABLE': 00170 // Parsing TRUNCATE TABLE query: 00171 $result = $this->parseTRUNCATETABLE($parseString); 00172 break; 00173 default: 00174 $result = $this->parseError('"' . $keyword . '" is not a keyword', $parseString); 00175 break; 00176 } 00177 00178 return $result; 00179 } 00180 00181 /** 00182 * Parsing SELECT query 00183 * 00184 * @param string SQL string with SELECT query to parse 00185 * @param array Array holding references to either named (:name) or question mark (?) parameters found 00186 * @return mixed Returns array with components of SELECT query on success, otherwise an error message string. 00187 * @see compileSELECT() 00188 */ 00189 protected function parseSELECT($parseString, &$parameterReferences = NULL) { 00190 00191 // Removing SELECT: 00192 $parseString = $this->trimSQL($parseString); 00193 $parseString = ltrim(substr($parseString, 6)); 00194 00195 // Init output variable: 00196 $result = array(); 00197 if ($parameterReferences === NULL) { 00198 $result['parameters'] = array(); 00199 $parameterReferences = &$result['parameters']; 00200 } 00201 $result['type'] = 'SELECT'; 00202 00203 // Looking for STRAIGHT_JOIN keyword: 00204 $result['STRAIGHT_JOIN'] = $this->nextPart($parseString, '^(STRAIGHT_JOIN)[[:space:]]+'); 00205 00206 // Select fields: 00207 $result['SELECT'] = $this->parseFieldList($parseString, '^(FROM)[[:space:]]+'); 00208 if ($this->parse_error) { 00209 return $this->parse_error; 00210 } 00211 00212 // Continue if string is not ended: 00213 if ($parseString) { 00214 00215 // Get table list: 00216 $result['FROM'] = $this->parseFromTables($parseString, '^(WHERE)[[:space:]]+'); 00217 if ($this->parse_error) { 00218 return $this->parse_error; 00219 } 00220 00221 // If there are more than just the tables (a WHERE clause that would be...) 00222 if ($parseString) { 00223 00224 // Get WHERE clause: 00225 $result['WHERE'] = $this->parseWhereClause($parseString, '^(GROUP[[:space:]]+BY|ORDER[[:space:]]+BY|LIMIT)[[:space:]]+', $parameterReferences); 00226 if ($this->parse_error) { 00227 return $this->parse_error; 00228 } 00229 00230 // If the WHERE clause parsing was stopped by GROUP BY, ORDER BY or LIMIT, then proceed with parsing: 00231 if ($this->lastStopKeyWord) { 00232 00233 // GROUP BY parsing: 00234 if ($this->lastStopKeyWord == 'GROUPBY') { 00235 $result['GROUPBY'] = $this->parseFieldList($parseString, '^(ORDER[[:space:]]+BY|LIMIT)[[:space:]]+'); 00236 if ($this->parse_error) { 00237 return $this->parse_error; 00238 } 00239 } 00240 00241 // ORDER BY parsing: 00242 if ($this->lastStopKeyWord == 'ORDERBY') { 00243 $result['ORDERBY'] = $this->parseFieldList($parseString, '^(LIMIT)[[:space:]]+'); 00244 if ($this->parse_error) { 00245 return $this->parse_error; 00246 } 00247 } 00248 00249 // LIMIT parsing: 00250 if ($this->lastStopKeyWord == 'LIMIT') { 00251 if (preg_match('/^([0-9]+|[0-9]+[[:space:]]*,[[:space:]]*[0-9]+)$/', trim($parseString))) { 00252 $result['LIMIT'] = $parseString; 00253 } else { 00254 return $this->parseError('No value for limit!', $parseString); 00255 } 00256 } 00257 } 00258 } 00259 } else { 00260 return $this->parseError('No table to select from!', $parseString); 00261 } 00262 00263 // Store current parseString in the result array for possible further processing (e.g., subquery support by DBAL) 00264 $result['parseString'] = $parseString; 00265 00266 // Return result: 00267 return $result; 00268 } 00269 00270 /** 00271 * Parsing UPDATE query 00272 * 00273 * @param string SQL string with UPDATE query to parse 00274 * @return mixed Returns array with components of UPDATE query on success, otherwise an error message string. 00275 * @see compileUPDATE() 00276 */ 00277 protected function parseUPDATE($parseString) { 00278 00279 // Removing UPDATE 00280 $parseString = $this->trimSQL($parseString); 00281 $parseString = ltrim(substr($parseString, 6)); 00282 00283 // Init output variable: 00284 $result = array(); 00285 $result['type'] = 'UPDATE'; 00286 00287 // Get table: 00288 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+'); 00289 00290 // Continue if string is not ended: 00291 if ($result['TABLE']) { 00292 if ($parseString && $this->nextPart($parseString, '^(SET)[[:space:]]+')) { 00293 00294 $comma = TRUE; 00295 00296 // Get field/value pairs: 00297 while ($comma) { 00298 if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*=')) { 00299 $this->nextPart($parseString, '^(=)'); // Strip of "=" sign. 00300 $value = $this->getValue($parseString); 00301 $result['FIELDS'][$fieldName] = $value; 00302 } else { 00303 return $this->parseError('No fieldname found', $parseString); 00304 } 00305 00306 $comma = $this->nextPart($parseString, '^(,)'); 00307 } 00308 00309 // WHERE 00310 if ($this->nextPart($parseString, '^(WHERE)')) { 00311 $result['WHERE'] = $this->parseWhereClause($parseString); 00312 if ($this->parse_error) { 00313 return $this->parse_error; 00314 } 00315 } 00316 } else { 00317 return $this->parseError('Query missing SET...', $parseString); 00318 } 00319 } else { 00320 return $this->parseError('No table found!', $parseString); 00321 } 00322 00323 // Should be no more content now: 00324 if ($parseString) { 00325 return $this->parseError('Still content in clause after parsing!', $parseString); 00326 } 00327 00328 // Return result: 00329 return $result; 00330 } 00331 00332 /** 00333 * Parsing INSERT query 00334 * 00335 * @param string SQL string with INSERT query to parse 00336 * @return mixed Returns array with components of INSERT query on success, otherwise an error message string. 00337 * @see compileINSERT() 00338 */ 00339 protected function parseINSERT($parseString) { 00340 00341 // Removing INSERT 00342 $parseString = $this->trimSQL($parseString); 00343 $parseString = ltrim(substr(ltrim(substr($parseString, 6)), 4)); 00344 00345 // Init output variable: 00346 $result = array(); 00347 $result['type'] = 'INSERT'; 00348 00349 // Get table: 00350 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()'); 00351 00352 if ($result['TABLE']) { 00353 00354 if ($this->nextPart($parseString, '^(VALUES)([[:space:]]+|\()')) { // In this case there are no field names mentioned in the SQL! 00355 // Get values/fieldnames (depending...) 00356 $result['VALUES_ONLY'] = $this->getValue($parseString, 'IN'); 00357 if ($this->parse_error) { 00358 return $this->parse_error; 00359 } 00360 if (preg_match('/^,/', $parseString)) { 00361 $result['VALUES_ONLY'] = array($result['VALUES_ONLY']); 00362 $result['EXTENDED'] = '1'; 00363 while ($this->nextPart($parseString, '^(,)') === ',') { 00364 $result['VALUES_ONLY'][] = $this->getValue($parseString, 'IN'); 00365 if ($this->parse_error) { 00366 return $this->parse_error; 00367 } 00368 } 00369 } 00370 } else { // There are apparently fieldnames listed: 00371 $fieldNames = $this->getValue($parseString, '_LIST'); 00372 if ($this->parse_error) { 00373 return $this->parse_error; 00374 } 00375 00376 if ($this->nextPart($parseString, '^(VALUES)([[:space:]]+|\()')) { // "VALUES" keyword binds the fieldnames to values: 00377 $result['FIELDS'] = array(); 00378 do { 00379 $values = $this->getValue($parseString, 'IN'); // Using the "getValue" function to get the field list... 00380 if ($this->parse_error) { 00381 return $this->parse_error; 00382 } 00383 00384 $insertValues = array(); 00385 foreach ($fieldNames as $k => $fN) { 00386 if (preg_match('/^[[:alnum:]_]+$/', $fN)) { 00387 if (isset($values[$k])) { 00388 if (!isset($insertValues[$fN])) { 00389 $insertValues[$fN] = $values[$k]; 00390 } else { 00391 return $this->parseError('Fieldname ("' . $fN . '") already found in list!', $parseString); 00392 } 00393 } else { 00394 return $this->parseError('No value set!', $parseString); 00395 } 00396 } else { 00397 return $this->parseError('Invalid fieldname ("' . $fN . '")', $parseString); 00398 } 00399 } 00400 if (isset($values[$k + 1])) { 00401 return $this->parseError('Too many values in list!', $parseString); 00402 } 00403 $result['FIELDS'][] = $insertValues; 00404 } while ($this->nextPart($parseString, '^(,)') === ','); 00405 00406 if (count($result['FIELDS']) === 1) { 00407 $result['FIELDS'] = $result['FIELDS'][0]; 00408 } else { 00409 $result['EXTENDED'] = '1'; 00410 } 00411 } else { 00412 return $this->parseError('VALUES keyword expected', $parseString); 00413 } 00414 } 00415 } else { 00416 return $this->parseError('No table found!', $parseString); 00417 } 00418 00419 // Should be no more content now: 00420 if ($parseString) { 00421 return $this->parseError('Still content after parsing!', $parseString); 00422 } 00423 00424 // Return result 00425 return $result; 00426 } 00427 00428 /** 00429 * Parsing DELETE query 00430 * 00431 * @param string SQL string with DELETE query to parse 00432 * @return mixed Returns array with components of DELETE query on success, otherwise an error message string. 00433 * @see compileDELETE() 00434 */ 00435 protected function parseDELETE($parseString) { 00436 00437 // Removing DELETE 00438 $parseString = $this->trimSQL($parseString); 00439 $parseString = ltrim(substr(ltrim(substr($parseString, 6)), 4)); 00440 00441 // Init output variable: 00442 $result = array(); 00443 $result['type'] = 'DELETE'; 00444 00445 // Get table: 00446 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+'); 00447 00448 if ($result['TABLE']) { 00449 00450 // WHERE 00451 if ($this->nextPart($parseString, '^(WHERE)')) { 00452 $result['WHERE'] = $this->parseWhereClause($parseString); 00453 if ($this->parse_error) { 00454 return $this->parse_error; 00455 } 00456 } 00457 } else { 00458 return $this->parseError('No table found!', $parseString); 00459 } 00460 00461 // Should be no more content now: 00462 if ($parseString) { 00463 return $this->parseError('Still content in clause after parsing!', $parseString); 00464 } 00465 00466 // Return result: 00467 return $result; 00468 } 00469 00470 /** 00471 * Parsing EXPLAIN query 00472 * 00473 * @param string SQL string with EXPLAIN query to parse 00474 * @return mixed Returns array with components of EXPLAIN query on success, otherwise an error message string. 00475 * @see parseSELECT() 00476 */ 00477 protected function parseEXPLAIN($parseString) { 00478 00479 // Removing EXPLAIN 00480 $parseString = $this->trimSQL($parseString); 00481 $parseString = ltrim(substr($parseString, 6)); 00482 00483 // Init output variable: 00484 $result = $this->parseSELECT($parseString); 00485 if (is_array($result)) { 00486 $result['type'] = 'EXPLAIN'; 00487 } 00488 00489 return $result; 00490 } 00491 00492 /** 00493 * Parsing CREATE TABLE query 00494 * 00495 * @param string SQL string starting with CREATE TABLE 00496 * @return mixed Returns array with components of CREATE TABLE query on success, otherwise an error message string. 00497 * @see compileCREATETABLE() 00498 */ 00499 protected function parseCREATETABLE($parseString) { 00500 00501 // Removing CREATE TABLE 00502 $parseString = $this->trimSQL($parseString); 00503 $parseString = ltrim(substr(ltrim(substr($parseString, 6)), 5)); 00504 00505 // Init output variable: 00506 $result = array(); 00507 $result['type'] = 'CREATETABLE'; 00508 00509 // Get table: 00510 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*\(', TRUE); 00511 00512 if ($result['TABLE']) { 00513 00514 // While the parseString is not yet empty: 00515 while (strlen($parseString) > 0) { 00516 if ($key = $this->nextPart($parseString, '^(KEY|PRIMARY KEY|UNIQUE KEY|UNIQUE)([[:space:]]+|\()')) { // Getting key 00517 $key = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $key)); 00518 00519 switch ($key) { 00520 case 'PRIMARYKEY': 00521 $result['KEYS']['PRIMARYKEY'] = $this->getValue($parseString, '_LIST'); 00522 if ($this->parse_error) { 00523 return $this->parse_error; 00524 } 00525 break; 00526 case 'UNIQUE': 00527 case 'UNIQUEKEY': 00528 if ($keyName = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()')) { 00529 $result['KEYS']['UNIQUE'] = array($keyName => $this->getValue($parseString, '_LIST')); 00530 if ($this->parse_error) { 00531 return $this->parse_error; 00532 } 00533 } else { 00534 return $this->parseError('No keyname found', $parseString); 00535 } 00536 break; 00537 case 'KEY': 00538 if ($keyName = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()')) { 00539 $result['KEYS'][$keyName] = $this->getValue($parseString, '_LIST', 'INDEX'); 00540 if ($this->parse_error) { 00541 return $this->parse_error; 00542 } 00543 } else { 00544 return $this->parseError('No keyname found', $parseString); 00545 } 00546 break; 00547 } 00548 } elseif ($fieldName = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) { // Getting field: 00549 $result['FIELDS'][$fieldName]['definition'] = $this->parseFieldDef($parseString); 00550 if ($this->parse_error) { 00551 return $this->parse_error; 00552 } 00553 } 00554 00555 // Finding delimiter: 00556 $delim = $this->nextPart($parseString, '^(,|\))'); 00557 if (!$delim) { 00558 return $this->parseError('No delimiter found', $parseString); 00559 } elseif ($delim == ')') { 00560 break; 00561 } 00562 } 00563 00564 // Finding what is after the table definition - table type in MySQL 00565 if ($delim == ')') { 00566 if ($this->nextPart($parseString, '^((ENGINE|TYPE)[[:space:]]*=)')) { 00567 $result['tableType'] = $parseString; 00568 $parseString = ''; 00569 } 00570 } else { 00571 return $this->parseError('No fieldname found!', $parseString); 00572 } 00573 00574 // Getting table type 00575 } else { 00576 return $this->parseError('No table found!', $parseString); 00577 } 00578 00579 // Should be no more content now: 00580 if ($parseString) { 00581 return $this->parseError('Still content in clause after parsing!', $parseString); 00582 } 00583 00584 return $result; 00585 } 00586 00587 /** 00588 * Parsing ALTER TABLE query 00589 * 00590 * @param string SQL string starting with ALTER TABLE 00591 * @return mixed Returns array with components of ALTER TABLE query on success, otherwise an error message string. 00592 * @see compileALTERTABLE() 00593 */ 00594 protected function parseALTERTABLE($parseString) { 00595 00596 // Removing ALTER TABLE 00597 $parseString = $this->trimSQL($parseString); 00598 $parseString = ltrim(substr(ltrim(substr($parseString, 5)), 5)); 00599 00600 // Init output variable: 00601 $result = array(); 00602 $result['type'] = 'ALTERTABLE'; 00603 00604 // Get table: 00605 $hasBackquote = ($this->nextPart($parseString, '^(`)') === '`'); 00606 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)' . ($hasBackquote ? '`' : '') . '[[:space:]]+'); 00607 if ($hasBackquote && $this->nextPart($parseString, '^(`)') !== '`') { 00608 return $this->parseError('No end backquote found!', $parseString); 00609 } 00610 00611 if ($result['TABLE']) { 00612 if ($result['action'] = $this->nextPart($parseString, '^(CHANGE|DROP[[:space:]]+KEY|DROP[[:space:]]+PRIMARY[[:space:]]+KEY|ADD[[:space:]]+KEY|ADD[[:space:]]+PRIMARY[[:space:]]+KEY|ADD[[:space:]]+UNIQUE|DROP|ADD|RENAME|DEFAULT[[:space:]]+CHARACTER[[:space:]]+SET|ENGINE)([[:space:]]+|\(|=)')) { 00613 $actionKey = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $result['action'])); 00614 00615 // Getting field: 00616 if (t3lib_div::inList('ADDPRIMARYKEY,DROPPRIMARYKEY,ENGINE', $actionKey) || $fieldKey = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) { 00617 00618 switch ($actionKey) { 00619 case 'ADD': 00620 $result['FIELD'] = $fieldKey; 00621 $result['definition'] = $this->parseFieldDef($parseString); 00622 if ($this->parse_error) { 00623 return $this->parse_error; 00624 } 00625 break; 00626 case 'DROP': 00627 case 'RENAME': 00628 $result['FIELD'] = $fieldKey; 00629 break; 00630 case 'CHANGE': 00631 $result['FIELD'] = $fieldKey; 00632 if ($result['newField'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) { 00633 $result['definition'] = $this->parseFieldDef($parseString); 00634 if ($this->parse_error) { 00635 return $this->parse_error; 00636 } 00637 } else { 00638 return $this->parseError('No NEW field name found', $parseString); 00639 } 00640 break; 00641 00642 case 'ADDKEY': 00643 case 'ADDPRIMARYKEY': 00644 case 'ADDUNIQUE': 00645 $result['KEY'] = $fieldKey; 00646 $result['fields'] = $this->getValue($parseString, '_LIST', 'INDEX'); 00647 if ($this->parse_error) { 00648 return $this->parse_error; 00649 } 00650 break; 00651 case 'DROPKEY': 00652 $result['KEY'] = $fieldKey; 00653 break; 00654 case 'DROPPRIMARYKEY': 00655 // ??? todo! 00656 break; 00657 case 'DEFAULTCHARACTERSET': 00658 $result['charset'] = $fieldKey; 00659 break; 00660 case 'ENGINE': 00661 $result['engine'] = $this->nextPart($parseString, '^=[[:space:]]*([[:alnum:]]+)[[:space:]]+', TRUE); 00662 break; 00663 } 00664 } else { 00665 return $this->parseError('No field name found', $parseString); 00666 } 00667 } else { 00668 return $this->parseError('No action CHANGE, DROP or ADD found!', $parseString); 00669 } 00670 } else { 00671 return $this->parseError('No table found!', $parseString); 00672 } 00673 00674 // Should be no more content now: 00675 if ($parseString) { 00676 return $this->parseError('Still content in clause after parsing!', $parseString); 00677 } 00678 00679 return $result; 00680 } 00681 00682 /** 00683 * Parsing DROP TABLE query 00684 * 00685 * @param string SQL string starting with DROP TABLE 00686 * @return mixed Returns array with components of DROP TABLE query on success, otherwise an error message string. 00687 */ 00688 protected function parseDROPTABLE($parseString) { 00689 00690 // Removing DROP TABLE 00691 $parseString = $this->trimSQL($parseString); 00692 $parseString = ltrim(substr(ltrim(substr($parseString, 4)), 5)); 00693 00694 // Init output variable: 00695 $result = array(); 00696 $result['type'] = 'DROPTABLE'; 00697 00698 // IF EXISTS 00699 $result['ifExists'] = $this->nextPart($parseString, '^(IF[[:space:]]+EXISTS[[:space:]]+)'); 00700 00701 // Get table: 00702 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+'); 00703 00704 if ($result['TABLE']) { 00705 00706 // Should be no more content now: 00707 if ($parseString) { 00708 return $this->parseError('Still content in clause after parsing!', $parseString); 00709 } 00710 00711 return $result; 00712 } else { 00713 return $this->parseError('No table found!', $parseString); 00714 } 00715 } 00716 00717 /** 00718 * Parsing CREATE DATABASE query 00719 * 00720 * @param string SQL string starting with CREATE DATABASE 00721 * @return mixed Returns array with components of CREATE DATABASE query on success, otherwise an error message string. 00722 */ 00723 protected function parseCREATEDATABASE($parseString) { 00724 00725 // Removing CREATE DATABASE 00726 $parseString = $this->trimSQL($parseString); 00727 $parseString = ltrim(substr(ltrim(substr($parseString, 6)), 8)); 00728 00729 // Init output variable: 00730 $result = array(); 00731 $result['type'] = 'CREATEDATABASE'; 00732 00733 // Get table: 00734 $result['DATABASE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+'); 00735 00736 if ($result['DATABASE']) { 00737 00738 // Should be no more content now: 00739 if ($parseString) { 00740 return $this->parseError('Still content in clause after parsing!', $parseString); 00741 } 00742 00743 return $result; 00744 } else { 00745 return $this->parseError('No database found!', $parseString); 00746 } 00747 } 00748 00749 /** 00750 * Parsing TRUNCATE TABLE query 00751 * 00752 * @param string SQL string starting with TRUNCATE TABLE 00753 * @return mixed Returns array with components of TRUNCATE TABLE query on success, otherwise an error message string. 00754 */ 00755 protected function parseTRUNCATETABLE($parseString) { 00756 00757 // Removing TRUNCATE TABLE 00758 $parseString = $this->trimSQL($parseString); 00759 $parseString = ltrim(substr(ltrim(substr($parseString, 8)), 5)); 00760 00761 // Init output variable: 00762 $result = array(); 00763 $result['type'] = 'TRUNCATETABLE'; 00764 00765 // Get table: 00766 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+'); 00767 00768 if ($result['TABLE']) { 00769 00770 // Should be no more content now: 00771 if ($parseString) { 00772 return $this->parseError('Still content in clause after parsing!', $parseString); 00773 } 00774 00775 return $result; 00776 } else { 00777 return $this->parseError('No table found!', $parseString); 00778 } 00779 } 00780 00781 00782 /************************************** 00783 * 00784 * SQL Parsing, helper functions for parts of queries 00785 * 00786 **************************************/ 00787 00788 /** 00789 * Parsing the fields in the "SELECT [$selectFields] FROM" part of a query into an array. 00790 * The output from this function can be compiled back into a field list with ->compileFieldList() 00791 * 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! 00792 * 00793 * @param string The string with fieldnames, eg. "title, uid AS myUid, max(tstamp), count(*)" etc. NOTICE: passed by reference! 00794 * @param string Regular expressing to STOP parsing, eg. '^(FROM)([[:space:]]*)' 00795 * @return array If successful parsing, returns an array, otherwise an error string. 00796 * @see compileFieldList() 00797 */ 00798 public function parseFieldList(&$parseString, $stopRegex = '') { 00799 00800 $stack = array(); // Contains the parsed content 00801 00802 if (strlen($parseString) == 0) { 00803 return $stack; 00804 } // FIXME - should never happen, why does it? 00805 00806 $pnt = 0; // Pointer to positions in $stack 00807 $level = 0; // Indicates the parenthesis level we are at. 00808 $loopExit = 0; // Recursivity brake. 00809 00810 // Prepare variables: 00811 $parseString = $this->trimSQL($parseString); 00812 $this->lastStopKeyWord = ''; 00813 $this->parse_error = ''; 00814 00815 // Parse any SQL hint / comments 00816 $stack[$pnt]['comments'] = $this->nextPart($parseString, '^(\/\*.*\*\/)'); 00817 00818 // $parseString is continously shortend by the process and we keep parsing it till it is zero: 00819 while (strlen($parseString)) { 00820 00821 // Checking if we are inside / outside parenthesis (in case of a function like count(), max(), min() etc...): 00822 if ($level > 0) { // Inside parenthesis here (does NOT detect if values in quotes are used, the only token is ")" or "("): 00823 00824 // Accumulate function content until next () parenthesis: 00825 $funcContent = $this->nextPart($parseString, '^([^()]*.)'); 00826 $stack[$pnt]['func_content.'][] = array( 00827 'level' => $level, 00828 'func_content' => substr($funcContent, 0, -1) 00829 ); 00830 $stack[$pnt]['func_content'] .= $funcContent; 00831 00832 // Detecting ( or ) 00833 switch (substr($stack[$pnt]['func_content'], -1)) { 00834 case '(': 00835 $level++; 00836 break; 00837 case ')': 00838 $level--; 00839 if (!$level) { // If this was the last parenthesis: 00840 $stack[$pnt]['func_content'] = substr($stack[$pnt]['func_content'], 0, -1); 00841 $parseString = ltrim($parseString); // Remove any whitespace after the parenthesis. 00842 } 00843 break; 00844 } 00845 } else { // Outside parenthesis, looking for next field: 00846 00847 // Looking for a flow-control construct (only known constructs supported) 00848 if (preg_match('/^case([[:space:]][[:alnum:]\*._]+)?[[:space:]]when/i', $parseString)) { 00849 $stack[$pnt]['type'] = 'flow-control'; 00850 $stack[$pnt]['flow-control'] = $this->parseCaseStatement($parseString); 00851 // Looking for "AS" alias: 00852 if ($as = $this->nextPart($parseString, '^(AS)[[:space:]]+')) { 00853 $stack[$pnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)(,|[[:space:]]+)'); 00854 $stack[$pnt]['as_keyword'] = $as; 00855 } 00856 } else { 00857 // Looking for a known function (only known functions supported) 00858 $func = $this->nextPart($parseString, '^(count|max|min|floor|sum|avg)[[:space:]]*\('); 00859 if ($func) { 00860 $parseString = trim(substr($parseString, 1)); // Strip of "(" 00861 $stack[$pnt]['type'] = 'function'; 00862 $stack[$pnt]['function'] = $func; 00863 $level++; // increse parenthesis level counter. 00864 } else { 00865 $stack[$pnt]['distinct'] = $this->nextPart($parseString, '^(distinct[[:space:]]+)'); 00866 // Otherwise, look for regular fieldname: 00867 if (($fieldName = $this->nextPart($parseString, '^([[:alnum:]\*._]+)(,|[[:space:]]+)')) !== '') { 00868 $stack[$pnt]['type'] = 'field'; 00869 00870 // Explode fieldname into field and table: 00871 $tableField = explode('.', $fieldName, 2); 00872 if (count($tableField) == 2) { 00873 $stack[$pnt]['table'] = $tableField[0]; 00874 $stack[$pnt]['field'] = $tableField[1]; 00875 } else { 00876 $stack[$pnt]['table'] = ''; 00877 $stack[$pnt]['field'] = $tableField[0]; 00878 } 00879 } else { 00880 return $this->parseError('No field name found as expected in parseFieldList()', $parseString); 00881 } 00882 } 00883 } 00884 } 00885 00886 // After a function or field we look for "AS" alias and a comma to separate to the next field in the list: 00887 if (!$level) { 00888 00889 // Looking for "AS" alias: 00890 if ($as = $this->nextPart($parseString, '^(AS)[[:space:]]+')) { 00891 $stack[$pnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)(,|[[:space:]]+)'); 00892 $stack[$pnt]['as_keyword'] = $as; 00893 } 00894 00895 // Looking for "ASC" or "DESC" keywords (for ORDER BY) 00896 if ($sDir = $this->nextPart($parseString, '^(ASC|DESC)([[:space:]]+|,)')) { 00897 $stack[$pnt]['sortDir'] = $sDir; 00898 } 00899 00900 // Looking for stop-keywords: 00901 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) { 00902 $this->lastStopKeyWord = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $this->lastStopKeyWord)); 00903 return $stack; 00904 } 00905 00906 // Looking for comma (since the stop-keyword did not trigger a return...) 00907 if (strlen($parseString) && !$this->nextPart($parseString, '^(,)')) { 00908 return $this->parseError('No comma found as expected in parseFieldList()', $parseString); 00909 } 00910 00911 // Increasing pointer: 00912 $pnt++; 00913 } 00914 00915 // Check recursivity brake: 00916 $loopExit++; 00917 if ($loopExit > 500) { 00918 return $this->parseError('More than 500 loops, exiting prematurely in parseFieldList()...', $parseString); 00919 } 00920 } 00921 00922 // Return result array: 00923 return $stack; 00924 } 00925 00926 /** 00927 * Parsing a CASE ... WHEN flow-control construct. 00928 * The output from this function can be compiled back with ->compileCaseStatement() 00929 * 00930 * @param string The string with the CASE ... WHEN construct, eg. "CASE field WHEN 1 THEN 0 ELSE ..." etc. NOTICE: passed by reference! 00931 * @return array If successful parsing, returns an array, otherwise an error string. 00932 * @see compileCaseConstruct() 00933 */ 00934 protected function parseCaseStatement(&$parseString) { 00935 $result = array(); 00936 $result['type'] = $this->nextPart($parseString, '^(case)[[:space:]]+'); 00937 if (!preg_match('/^when[[:space:]]+/i', $parseString)) { 00938 $value = $this->getValue($parseString); 00939 if (!(isset($value[1]) || is_numeric($value[0]))) { 00940 $result['case_field'] = $value[0]; 00941 } else { 00942 $result['case_value'] = $value; 00943 } 00944 } 00945 $result['when'] = array(); 00946 while ($this->nextPart($parseString, '^(when)[[:space:]]')) { 00947 $when = array(); 00948 $when['when_value'] = $this->parseWhereClause($parseString, '^(then)[[:space:]]+'); 00949 $when['then_value'] = $this->getValue($parseString); 00950 $result['when'][] = $when; 00951 } 00952 if ($this->nextPart($parseString, '^(else)[[:space:]]+')) { 00953 $result['else'] = $this->getValue($parseString); 00954 } 00955 if (!$this->nextPart($parseString, '^(end)[[:space:]]+')) { 00956 return $this->parseError('No "end" keyword found as expected in parseCaseStatement()', $parseString); 00957 } 00958 return $result; 00959 } 00960 00961 /** 00962 * Parsing the tablenames in the "FROM [$parseString] WHERE" part of a query into an array. 00963 * The success of this parsing determines if that part of the query is supported by TYPO3. 00964 * 00965 * @param string list of tables, eg. "pages, tt_content" or "pages A, pages B". NOTICE: passed by reference! 00966 * @param string Regular expressing to STOP parsing, eg. '^(WHERE)([[:space:]]*)' 00967 * @return array If successful parsing, returns an array, otherwise an error string. 00968 * @see compileFromTables() 00969 */ 00970 public function parseFromTables(&$parseString, $stopRegex = '') { 00971 00972 // Prepare variables: 00973 $parseString = $this->trimSQL($parseString); 00974 $this->lastStopKeyWord = ''; 00975 $this->parse_error = ''; 00976 00977 $stack = array(); // Contains the parsed content 00978 $pnt = 0; // Pointer to positions in $stack 00979 $loopExit = 0; // Recursivity brake. 00980 00981 // $parseString is continously shortend by the process and we keep parsing it till it is zero: 00982 while (strlen($parseString)) { 00983 // Looking for the table: 00984 if ($stack[$pnt]['table'] = $this->nextPart($parseString, '^([[:alnum:]_]+)(,|[[:space:]]+)')) { 00985 // Looking for stop-keywords before fetching potential table alias: 00986 if ($stopRegex && ($this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))) { 00987 $this->lastStopKeyWord = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $this->lastStopKeyWord)); 00988 return $stack; 00989 } 00990 if (!preg_match('/^(LEFT|RIGHT|JOIN|INNER)[[:space:]]+/i', $parseString)) { 00991 $stack[$pnt]['as_keyword'] = $this->nextPart($parseString, '^(AS[[:space:]]+)'); 00992 $stack[$pnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*'); 00993 } 00994 } else { 00995 return $this->parseError('No table name found as expected in parseFromTables()!', $parseString); 00996 } 00997 00998 // Looking for JOIN 00999 $joinCnt = 0; 01000 while ($join = $this->nextPart($parseString, '^(LEFT[[:space:]]+JOIN|LEFT[[:space:]]+OUTER[[:space:]]+JOIN|RIGHT[[:space:]]+JOIN|RIGHT[[:space:]]+OUTER[[:space:]]+JOIN|INNER[[:space:]]+JOIN|JOIN)[[:space:]]+')) { 01001 $stack[$pnt]['JOIN'][$joinCnt]['type'] = $join; 01002 if ($stack[$pnt]['JOIN'][$joinCnt]['withTable'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+', 1)) { 01003 if (!preg_match('/^ON[[:space:]]+/i', $parseString)) { 01004 $stack[$pnt]['JOIN'][$joinCnt]['as_keyword'] = $this->nextPart($parseString, '^(AS[[:space:]]+)'); 01005 $stack[$pnt]['JOIN'][$joinCnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+'); 01006 } 01007 if (!$this->nextPart($parseString, '^(ON[[:space:]]+)')) { 01008 return $this->parseError('No join condition found in parseFromTables()!', $parseString); 01009 } 01010 $stack[$pnt]['JOIN'][$joinCnt]['ON'] = array(); 01011 $condition = array('operator' => ''); 01012 $parseCondition = TRUE; 01013 while ($parseCondition) { 01014 if (($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)[[:space:]]*(<=|>=|<|>|=|!=)')) !== '') { 01015 // Parse field name into field and table: 01016 $tableField = explode('.', $fieldName, 2); 01017 $condition['left'] = array(); 01018 if (count($tableField) == 2) { 01019 $condition['left']['table'] = $tableField[0]; 01020 $condition['left']['field'] = $tableField[1]; 01021 } else { 01022 $condition['left']['table'] = ''; 01023 $condition['left']['field'] = $tableField[0]; 01024 } 01025 } else { 01026 return $this->parseError('No join field found in parseFromTables()!', $parseString); 01027 } 01028 // Find "comparator": 01029 $condition['comparator'] = $this->nextPart($parseString, '^(<=|>=|<|>|=|!=)'); 01030 if (($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)')) !== '') { 01031 // Parse field name into field and table: 01032 $tableField = explode('.', $fieldName, 2); 01033 $condition['right'] = array(); 01034 if (count($tableField) == 2) { 01035 $condition['right']['table'] = $tableField[0]; 01036 $condition['right']['field'] = $tableField[1]; 01037 } else { 01038 $condition['right']['table'] = ''; 01039 $condition['right']['field'] = $tableField[0]; 01040 } 01041 } else { 01042 return $this->parseError('No join field found in parseFromTables()!', $parseString); 01043 } 01044 $stack[$pnt]['JOIN'][$joinCnt]['ON'][] = $condition; 01045 if (($operator = $this->nextPart($parseString, '^(AND|OR)')) !== '') { 01046 $condition = array('operator' => $operator); 01047 } else { 01048 $parseCondition = FALSE; 01049 } 01050 } 01051 $joinCnt++; 01052 } else { 01053 return $this->parseError('No join table found in parseFromTables()!', $parseString); 01054 } 01055 } 01056 01057 // Looking for stop-keywords: 01058 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) { 01059 $this->lastStopKeyWord = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $this->lastStopKeyWord)); 01060 return $stack; 01061 } 01062 01063 // Looking for comma: 01064 if (strlen($parseString) && !$this->nextPart($parseString, '^(,)')) { 01065 return $this->parseError('No comma found as expected in parseFromTables()', $parseString); 01066 } 01067 01068 // Increasing pointer: 01069 $pnt++; 01070 01071 // Check recursivity brake: 01072 $loopExit++; 01073 if ($loopExit > 500) { 01074 return $this->parseError('More than 500 loops, exiting prematurely in parseFromTables()...', $parseString); 01075 } 01076 } 01077 01078 // Return result array: 01079 return $stack; 01080 } 01081 01082 /** 01083 * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array. 01084 * The success of this parsing determines if that part of the query is supported by TYPO3. 01085 * 01086 * @param string WHERE clause to parse. NOTICE: passed by reference! 01087 * @param string Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)' 01088 * @param array Array holding references to either named (:name) or question mark (?) parameters found 01089 * @return mixed If successful parsing, returns an array, otherwise an error string. 01090 */ 01091 public function parseWhereClause(&$parseString, $stopRegex = '', array &$parameterReferences = array()) { 01092 01093 // Prepare variables: 01094 $parseString = $this->trimSQL($parseString); 01095 $this->lastStopKeyWord = ''; 01096 $this->parse_error = ''; 01097 01098 $stack = array(0 => array()); // Contains the parsed content 01099 $pnt = array(0 => 0); // Pointer to positions in $stack 01100 $level = 0; // Determines parenthesis level 01101 $loopExit = 0; // Recursivity brake. 01102 01103 // $parseString is continously shortend by the process and we keep parsing it till it is zero: 01104 while (strlen($parseString)) { 01105 01106 // Look for next parenthesis level: 01107 $newLevel = $this->nextPart($parseString, '^([(])'); 01108 if ($newLevel == '(') { // If new level is started, manage stack/pointers: 01109 $level++; // Increase level 01110 $pnt[$level] = 0; // Reset pointer for this level 01111 $stack[$level] = array(); // Reset stack for this level 01112 } else { // If no new level is started, just parse the current level: 01113 01114 // Find "modifier", eg. "NOT or !" 01115 $stack[$level][$pnt[$level]]['modifier'] = trim($this->nextPart($parseString, '^(!|NOT[[:space:]]+)')); 01116 01117 // See if condition is EXISTS with a subquery 01118 if (preg_match('/^EXISTS[[:space:]]*[(]/i', $parseString)) { 01119 $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(EXISTS)[[:space:]]*'); 01120 $parseString = trim(substr($parseString, 1)); // Strip of "(" 01121 $stack[$level][$pnt[$level]]['func']['subquery'] = $this->parseSELECT($parseString, $parameterReferences); 01122 // Seek to new position in parseString after parsing of the subquery 01123 $parseString = $stack[$level][$pnt[$level]]['func']['subquery']['parseString']; 01124 unset($stack[$level][$pnt[$level]]['func']['subquery']['parseString']); 01125 if (!$this->nextPart($parseString, '^([)])')) { 01126 return 'No ) parenthesis at end of subquery'; 01127 } 01128 } else { 01129 01130 // See if LOCATE function is found 01131 if (preg_match('/^LOCATE[[:space:]]*[(]/i', $parseString)) { 01132 $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(LOCATE)[[:space:]]*'); 01133 $parseString = trim(substr($parseString, 1)); // Strip of "(" 01134 $stack[$level][$pnt[$level]]['func']['substr'] = $this->getValue($parseString); 01135 if (!$this->nextPart($parseString, '^(,)')) { 01136 return $this->parseError('No comma found as expected in parseWhereClause()', $parseString); 01137 } 01138 if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]\*._]+)[[:space:]]*')) { 01139 01140 // Parse field name into field and table: 01141 $tableField = explode('.', $fieldName, 2); 01142 if (count($tableField) == 2) { 01143 $stack[$level][$pnt[$level]]['func']['table'] = $tableField[0]; 01144 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[1]; 01145 } else { 01146 $stack[$level][$pnt[$level]]['func']['table'] = ''; 01147 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[0]; 01148 } 01149 } else { 01150 return $this->parseError('No field name found as expected in parseWhereClause()', $parseString); 01151 } 01152 if ($this->nextPart($parseString, '^(,)')) { 01153 $stack[$level][$pnt[$level]]['func']['pos'] = $this->getValue($parseString); 01154 } 01155 if (!$this->nextPart($parseString, '^([)])')) { 01156 return $this->parseError('No ) parenthesis at end of function', $parseString); 01157 } 01158 } elseif (preg_match('/^IFNULL[[:space:]]*[(]/i', $parseString)) { 01159 $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(IFNULL)[[:space:]]*'); 01160 $parseString = trim(substr($parseString, 1)); // Strip of "(" 01161 if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]\*._]+)[[:space:]]*')) { 01162 01163 // Parse field name into field and table: 01164 $tableField = explode('.', $fieldName, 2); 01165 if (count($tableField) == 2) { 01166 $stack[$level][$pnt[$level]]['func']['table'] = $tableField[0]; 01167 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[1]; 01168 } else { 01169 $stack[$level][$pnt[$level]]['func']['table'] = ''; 01170 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[0]; 01171 } 01172 } else { 01173 return $this->parseError('No field name found as expected in parseWhereClause()', $parseString); 01174 } 01175 if ($this->nextPart($parseString, '^(,)')) { 01176 $stack[$level][$pnt[$level]]['func']['default'] = $this->getValue($parseString); 01177 } 01178 if (!$this->nextPart($parseString, '^([)])')) { 01179 return $this->parseError('No ) parenthesis at end of function', $parseString); 01180 } 01181 } elseif (preg_match('/^FIND_IN_SET[[:space:]]*[(]/i', $parseString)) { 01182 $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(FIND_IN_SET)[[:space:]]*'); 01183 $parseString = trim(substr($parseString, 1)); // Strip of "(" 01184 if ($str = $this->getValue($parseString)) { 01185 $stack[$level][$pnt[$level]]['func']['str'] = $str; 01186 if ($fieldName = $this->nextPart($parseString, '^,[[:space:]]*([[:alnum:]._]+)[[:space:]]*', TRUE)) { 01187 01188 // Parse field name into field and table: 01189 $tableField = explode('.', $fieldName, 2); 01190 if (count($tableField) == 2) { 01191 $stack[$level][$pnt[$level]]['func']['table'] = $tableField[0]; 01192 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[1]; 01193 } else { 01194 $stack[$level][$pnt[$level]]['func']['table'] = ''; 01195 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[0]; 01196 } 01197 } else { 01198 return $this->parseError('No field name found as expected in parseWhereClause()', $parseString); 01199 } 01200 if (!$this->nextPart($parseString, '^([)])')) { 01201 return $this->parseError('No ) parenthesis at end of function', $parseString); 01202 } 01203 } else { 01204 return $this->parseError('No item to look for found as expected in parseWhereClause()', $parseString); 01205 } 01206 } else { 01207 01208 // Support calculated value only for: 01209 // - "&" (boolean AND) 01210 // - "+" (addition) 01211 // - "-" (substraction) 01212 // - "*" (multiplication) 01213 // - "/" (division) 01214 // - "%" (modulo) 01215 $calcOperators = '&|\+|-|\*|\/|%'; 01216 01217 // Fieldname: 01218 if (($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)([[:space:]]+|' . $calcOperators . '|<=|>=|<|>|=|!=|IS)')) !== '') { 01219 01220 // Parse field name into field and table: 01221 $tableField = explode('.', $fieldName, 2); 01222 if (count($tableField) == 2) { 01223 $stack[$level][$pnt[$level]]['table'] = $tableField[0]; 01224 $stack[$level][$pnt[$level]]['field'] = $tableField[1]; 01225 } else { 01226 $stack[$level][$pnt[$level]]['table'] = ''; 01227 $stack[$level][$pnt[$level]]['field'] = $tableField[0]; 01228 } 01229 } else { 01230 return $this->parseError('No field name found as expected in parseWhereClause()', $parseString); 01231 } 01232 01233 // See if the value is calculated: 01234 $stack[$level][$pnt[$level]]['calc'] = $this->nextPart($parseString, '^(' . $calcOperators . ')'); 01235 if (strlen($stack[$level][$pnt[$level]]['calc'])) { 01236 // Finding value for calculation: 01237 $calc_value = $this->getValue($parseString); 01238 $stack[$level][$pnt[$level]]['calc_value'] = $calc_value; 01239 if (count($calc_value) == 1 && is_string($calc_value[0])) { 01240 // Value is a field, store it to allow DBAL to post-process it (quoting, remapping) 01241 $tableField = explode('.', $calc_value[0], 2); 01242 if (count($tableField) == 2) { 01243 $stack[$level][$pnt[$level]]['calc_table'] = $tableField[0]; 01244 $stack[$level][$pnt[$level]]['calc_field'] = $tableField[1]; 01245 } else { 01246 $stack[$level][$pnt[$level]]['calc_table'] = ''; 01247 $stack[$level][$pnt[$level]]['calc_field'] = $tableField[0]; 01248 } 01249 } 01250 } 01251 } 01252 01253 // Find "comparator": 01254 $comparatorPatterns = array( 01255 '<=', '>=', '<', '>', '=', '!=', 01256 'NOT[[:space:]]+IN', 'IN', 01257 'NOT[[:space:]]+LIKE[[:space:]]+BINARY', 'LIKE[[:space:]]+BINARY', 'NOT[[:space:]]+LIKE', 'LIKE', 01258 'IS[[:space:]]+NOT', 'IS', 01259 'BETWEEN', 'NOT[[:space]]+BETWEEN', 01260 ); 01261 $stack[$level][$pnt[$level]]['comparator'] = $this->nextPart($parseString, '^(' . implode('|', $comparatorPatterns) . ')'); 01262 if (strlen($stack[$level][$pnt[$level]]['comparator'])) { 01263 if (preg_match('/^CONCAT[[:space:]]*\(/', $parseString)) { 01264 $this->nextPart($parseString, '^(CONCAT[[:space:]]?[(])'); 01265 $values = array( 01266 'operator' => 'CONCAT', 01267 'args' => array(), 01268 ); 01269 $cnt = 0; 01270 while ($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)')) { 01271 // Parse field name into field and table: 01272 $tableField = explode('.', $fieldName, 2); 01273 if (count($tableField) == 2) { 01274 $values['args'][$cnt]['table'] = $tableField[0]; 01275 $values['args'][$cnt]['field'] = $tableField[1]; 01276 } else { 01277 $values['args'][$cnt]['table'] = ''; 01278 $values['args'][$cnt]['field'] = $tableField[0]; 01279 } 01280 // Looking for comma: 01281 $this->nextPart($parseString, '^(,)'); 01282 $cnt++; 01283 } 01284 // Look for ending parenthesis: 01285 $this->nextPart($parseString, '([)])'); 01286 $stack[$level][$pnt[$level]]['value'] = $values; 01287 } else { 01288 if (t3lib_div::inList('IN,NOT IN', $stack[$level][$pnt[$level]]['comparator']) && preg_match('/^[(][[:space:]]*SELECT[[:space:]]+/', $parseString)) { 01289 $this->nextPart($parseString, '^([(])'); 01290 $stack[$level][$pnt[$level]]['subquery'] = $this->parseSELECT($parseString, $parameterReferences); 01291 // Seek to new position in parseString after parsing of the subquery 01292 $parseString = $stack[$level][$pnt[$level]]['subquery']['parseString']; 01293 unset($stack[$level][$pnt[$level]]['subquery']['parseString']); 01294 if (!$this->nextPart($parseString, '^([)])')) { 01295 return 'No ) parenthesis at end of subquery'; 01296 } 01297 } else { 01298 if (t3lib_div::inList('BETWEEN,NOT BETWEEN', $stack[$level][$pnt[$level]]['comparator'])) { 01299 $stack[$level][$pnt[$level]]['values'] = array(); 01300 $stack[$level][$pnt[$level]]['values'][0] = $this->getValue($parseString); 01301 if (!$this->nextPart($parseString, '^(AND)')) { 01302 return $this->parseError('No AND operator found as expected in parseWhereClause()', $parseString); 01303 } 01304 $stack[$level][$pnt[$level]]['values'][1] = $this->getValue($parseString); 01305 } else { 01306 // Finding value for comparator: 01307 $stack[$level][$pnt[$level]]['value'] = &$this->getValueOrParameter($parseString, $stack[$level][$pnt[$level]]['comparator'], '', $parameterReferences); 01308 if ($this->parse_error) { 01309 return $this->parse_error; 01310 } 01311 } 01312 } 01313 } 01314 } 01315 } 01316 01317 // Finished, increase pointer: 01318 $pnt[$level]++; 01319 01320 // Checking if we are back to level 0 and we should still decrease level, 01321 // meaning we were probably parsing as subquery and should return here: 01322 if ($level === 0 && preg_match('/^[)]/', $parseString)) { 01323 // Return the stacks lowest level: 01324 return $stack[0]; 01325 } 01326 01327 // Checking if we are back to level 0 and we should still decrease level, 01328 // meaning we were probably parsing a subquery and should return here: 01329 if ($level === 0 && preg_match('/^[)]/', $parseString)) { 01330 // Return the stacks lowest level: 01331 return $stack[0]; 01332 } 01333 01334 // Checking if the current level is ended, in that case do stack management: 01335 while ($this->nextPart($parseString, '^([)])')) { 01336 $level--; // Decrease level: 01337 $stack[$level][$pnt[$level]]['sub'] = $stack[$level + 1]; // Copy stack 01338 $pnt[$level]++; // Increase pointer of the new level 01339 01340 // Make recursivity check: 01341 $loopExit++; 01342 if ($loopExit > 500) { 01343 return $this->parseError('More than 500 loops (in search for exit parenthesis), exiting prematurely in parseWhereClause()...', $parseString); 01344 } 01345 } 01346 01347 // Detecting the operator for the next level: 01348 $op = $this->nextPart($parseString, '^(AND[[:space:]]+NOT|&&[[:space:]]+NOT|OR[[:space:]]+NOT|OR[[:space:]]+NOT|\|\|[[:space:]]+NOT|AND|&&|OR|\|\|)(\(|[[:space:]]+)'); 01349 if ($op) { 01350 // Normalize boolean operator 01351 $op = str_replace(array('&&', '||'), array('AND', 'OR'), $op); 01352 $stack[$level][$pnt[$level]]['operator'] = $op; 01353 } elseif (strlen($parseString)) { 01354 01355 // Looking for stop-keywords: 01356 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) { 01357 $this->lastStopKeyWord = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $this->lastStopKeyWord)); 01358 return $stack[0]; 01359 } else { 01360 return $this->parseError('No operator, but parsing not finished in parseWhereClause().', $parseString); 01361 } 01362 } 01363 } 01364 01365 // Make recursivity check: 01366 $loopExit++; 01367 if ($loopExit > 500) { 01368 return $this->parseError('More than 500 loops, exiting prematurely in parseWhereClause()...', $parseString); 01369 } 01370 } 01371 01372 // Return the stacks lowest level: 01373 return $stack[0]; 01374 } 01375 01376 /** 01377 * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array. 01378 * The success of this parsing determines if that part of the query is supported by TYPO3. 01379 * 01380 * @param string WHERE clause to parse. NOTICE: passed by reference! 01381 * @param string Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)' 01382 * @return mixed If successful parsing, returns an array, otherwise an error string. 01383 */ 01384 public function parseFieldDef(&$parseString, $stopRegex = '') { 01385 // Prepare variables: 01386 $parseString = $this->trimSQL($parseString); 01387 $this->lastStopKeyWord = ''; 01388 $this->parse_error = ''; 01389 01390 $result = array(); 01391 01392 // Field type: 01393 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:],]+|\()')) { 01394 01395 // Looking for value: 01396 if (substr($parseString, 0, 1) == '(') { 01397 $parseString = substr($parseString, 1); 01398 if ($result['value'] = $this->nextPart($parseString, '^([^)]*)')) { 01399 $parseString = ltrim(substr($parseString, 1)); 01400 } else { 01401 return $this->parseError('No end-parenthesis for value found in parseFieldDef()!', $parseString); 01402 } 01403 } 01404 01405 // Looking for keywords 01406 while ($keyword = $this->nextPart($parseString, '^(DEFAULT|NOT[[:space:]]+NULL|AUTO_INCREMENT|UNSIGNED)([[:space:]]+|,|\))')) { 01407 $keywordCmp = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $keyword)); 01408 01409 $result['featureIndex'][$keywordCmp]['keyword'] = $keyword; 01410 01411 switch ($keywordCmp) { 01412 case 'DEFAULT': 01413 $result['featureIndex'][$keywordCmp]['value'] = $this->getValue($parseString); 01414 break; 01415 } 01416 } 01417 } else { 01418 return $this->parseError('Field type unknown in parseFieldDef()!', $parseString); 01419 } 01420 01421 return $result; 01422 } 01423 01424 01425 /************************************ 01426 * 01427 * Parsing: Helper functions 01428 * 01429 ************************************/ 01430 01431 /** 01432 * Strips off a part of the parseString and returns the matching part. 01433 * Helper function for the parsing methods. 01434 * 01435 * @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. 01436 * @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()) 01437 * @param boolean If set the full match of the regex is stripped of the beginning of the string! 01438 * @return string The value of the first parenthesis level of the REGEX. 01439 */ 01440 protected function nextPart(&$parseString, $regex, $trimAll = FALSE) { 01441 $reg = array(); 01442 if (preg_match('/' . $regex . '/i', $parseString . ' ', $reg)) { // Adding space char because [[:space:]]+ is often a requirement in regex's 01443 $parseString = ltrim(substr($parseString, strlen($reg[$trimAll ? 0 : 1]))); 01444 return $reg[1]; 01445 } 01446 // No match found 01447 return ''; 01448 } 01449 01450 /** 01451 * Finds value or either named (:name) or question mark (?) parameter markers at the beginning 01452 * of $parseString, returns result and strips it of parseString. 01453 * This method returns a pointer to the parameter or value that was found. In case of a parameter 01454 * the pointer is a reference to the corresponding item in array $parameterReferences. 01455 * 01456 * @param string $parseString The parseString 01457 * @param string $comparator The comparator used before. 01458 * @param string $mode The mode, e.g., "INDEX" 01459 * @param mixed The value (string/integer) or parameter (:name/?). Otherwise an array with error message in first key (0) 01460 */ 01461 protected function &getValueOrParameter(&$parseString, $comparator = '', $mode = '', array &$parameterReferences = array()) { 01462 $parameter = $this->nextPart($parseString, '^(\\:[[:alnum:]_]+|\\?)'); 01463 if ($parameter === '?') { 01464 if (!isset($parameterReferences['?'])) { 01465 $parameterReferences['?'] = array(); 01466 } 01467 $value = array('?'); 01468 $parameterReferences['?'][] = &$value; 01469 } elseif ($parameter !== '') { // named parameter 01470 if (isset($parameterReferences[$parameter])) { 01471 // Use the same reference as last time we encountered this parameter 01472 $value = &$parameterReferences[$parameter]; 01473 } else { 01474 $value = array($parameter); 01475 $parameterReferences[$parameter] = &$value; 01476 } 01477 } else { 01478 $value = $this->getValue($parseString, $comparator, $mode); 01479 } 01480 return $value; 01481 } 01482 01483 /** 01484 * Finds value in beginning of $parseString, returns result and strips it of parseString 01485 * 01486 * @param string The parseString, eg. "(0,1,2,3) ..." or "('asdf','qwer') ..." or "1234 ..." or "'My string value here' ..." 01487 * @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) 01488 * @param string The mode, eg. "INDEX" 01489 * @return mixed The value (string/integer). Otherwise an array with error message in first key (0) 01490 */ 01491 protected function getValue(&$parseString, $comparator = '', $mode = '') { 01492 $value = ''; 01493 01494 if (t3lib_div::inList('NOTIN,IN,_LIST', strtoupper(str_replace(array(' ', LF, CR, TAB), '', $comparator)))) { // List of values: 01495 if ($this->nextPart($parseString, '^([(])')) { 01496 $listValues = array(); 01497 $comma = ','; 01498 01499 while ($comma == ',') { 01500 $listValues[] = $this->getValue($parseString); 01501 if ($mode === 'INDEX') { 01502 // Remove any length restriction on INDEX definition 01503 $this->nextPart($parseString, '^([(]\d+[)])'); 01504 } 01505 $comma = $this->nextPart($parseString, '^([,])'); 01506 } 01507 01508 $out = $this->nextPart($parseString, '^([)])'); 01509 if ($out) { 01510 if ($comparator == '_LIST') { 01511 $kVals = array(); 01512 foreach ($listValues as $vArr) { 01513 $kVals[] = $vArr[0]; 01514 } 01515 return $kVals; 01516 } else { 01517 return $listValues; 01518 } 01519 } else { 01520 return array($this->parseError('No ) parenthesis in list', $parseString)); 01521 } 01522 } else { 01523 return array($this->parseError('No ( parenthesis starting the list', $parseString)); 01524 } 01525 01526 } else { // Just plain string value, in quotes or not: 01527 01528 // Quote? 01529 $firstChar = substr($parseString, 0, 1); 01530 switch ($firstChar) { 01531 case '"': 01532 $value = array($this->getValueInQuotes($parseString, '"'), '"'); 01533 break; 01534 case "'": 01535 $value = array($this->getValueInQuotes($parseString, "'"), "'"); 01536 break; 01537 default: 01538 $reg = array(); 01539 if (preg_match('/^([[:alnum:]._-]+)/i', $parseString, $reg)) { 01540 $parseString = ltrim(substr($parseString, strlen($reg[0]))); 01541 $value = array($reg[1]); 01542 } 01543 break; 01544 } 01545 } 01546 return $value; 01547 } 01548 01549 /** 01550 * Get value in quotes from $parseString. 01551 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed 01552 * 01553 * @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. 01554 * @param string The quote used; input either " or ' 01555 * @return string The value, passed through stripslashes() ! 01556 */ 01557 protected function getValueInQuotes(&$parseString, $quote) { 01558 01559 $parts = explode($quote, substr($parseString, 1)); 01560 $buffer = ''; 01561 foreach ($parts as $k => $v) { 01562 $buffer .= $v; 01563 01564 $reg = array(); 01565 preg_match('/\\\\$/', $v, $reg); 01566 if ($reg AND strlen($reg[0]) % 2) { 01567 $buffer .= $quote; 01568 } else { 01569 $parseString = ltrim(substr($parseString, strlen($buffer) + 2)); 01570 return $this->parseStripslashes($buffer); 01571 } 01572 } 01573 } 01574 01575 /** 01576 * Strip slashes function used for parsing 01577 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed 01578 * 01579 * @param string Input string 01580 * @return string Output string 01581 */ 01582 protected function parseStripslashes($str) { 01583 $search = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z'); 01584 $replace = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a"); 01585 01586 return str_replace($search, $replace, $str); 01587 } 01588 01589 /** 01590 * Add slashes function used for compiling queries 01591 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed 01592 * 01593 * @param string Input string 01594 * @return string Output string 01595 */ 01596 protected function compileAddslashes($str) { 01597 $search = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a"); 01598 $replace = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z'); 01599 01600 return str_replace($search, $replace, $str); 01601 } 01602 01603 /** 01604 * Setting the internal error message value, $this->parse_error and returns that value. 01605 * 01606 * @param string Input error message 01607 * @param string Remaining query to parse. 01608 * @return string Error message. 01609 */ 01610 protected function parseError($msg, $restQuery) { 01611 $this->parse_error = 'SQL engine parse ERROR: ' . $msg . ': near "' . substr($restQuery, 0, 50) . '"'; 01612 return $this->parse_error; 01613 } 01614 01615 /** 01616 * Trimming SQL as preparation for parsing. 01617 * ";" in the end is stripped of. 01618 * White space is trimmed away around the value 01619 * A single space-char is added in the end 01620 * 01621 * @param string Input string 01622 * @return string Output string 01623 */ 01624 protected function trimSQL($str) { 01625 return trim(rtrim($str, "; \r\n\t")) . ' '; 01626 } 01627 01628 01629 /************************* 01630 * 01631 * Compiling queries 01632 * 01633 *************************/ 01634 01635 /** 01636 * Compiles an SQL query from components 01637 * 01638 * @param array Array of SQL query components 01639 * @return string SQL query 01640 * @see parseSQL() 01641 */ 01642 public function compileSQL($components) { 01643 switch ($components['type']) { 01644 case 'SELECT': 01645 $query = $this->compileSELECT($components); 01646 break; 01647 case 'UPDATE': 01648 $query = $this->compileUPDATE($components); 01649 break; 01650 case 'INSERT': 01651 $query = $this->compileINSERT($components); 01652 break; 01653 case 'DELETE': 01654 $query = $this->compileDELETE($components); 01655 break; 01656 case 'EXPLAIN': 01657 $query = 'EXPLAIN ' . $this->compileSELECT($components); 01658 break; 01659 case 'DROPTABLE': 01660 $query = 'DROP TABLE' . ($components['ifExists'] ? ' IF EXISTS' : '') . ' ' . $components['TABLE']; 01661 break; 01662 case 'CREATETABLE': 01663 $query = $this->compileCREATETABLE($components); 01664 break; 01665 case 'ALTERTABLE': 01666 $query = $this->compileALTERTABLE($components); 01667 break; 01668 case 'TRUNCATETABLE': 01669 $query = $this->compileTRUNCATETABLE($components); 01670 break; 01671 } 01672 01673 return $query; 01674 } 01675 01676 /** 01677 * Compiles a SELECT statement from components array 01678 * 01679 * @param array Array of SQL query components 01680 * @return string SQL SELECT query 01681 * @see parseSELECT() 01682 */ 01683 protected function compileSELECT($components) { 01684 01685 // Initialize: 01686 $where = $this->compileWhereClause($components['WHERE']); 01687 $groupBy = $this->compileFieldList($components['GROUPBY']); 01688 $orderBy = $this->compileFieldList($components['ORDERBY']); 01689 $limit = $components['LIMIT']; 01690 01691 // Make query: 01692 $query = 'SELECT ' . ($components['STRAIGHT_JOIN'] ? $components['STRAIGHT_JOIN'] . '' : '') . ' 01693 ' . $this->compileFieldList($components['SELECT']) . ' 01694 FROM ' . $this->compileFromTables($components['FROM']) . 01695 (strlen($where) ? ' 01696 WHERE ' . $where : '') . 01697 (strlen($groupBy) ? ' 01698 GROUP BY ' . $groupBy : '') . 01699 (strlen($orderBy) ? ' 01700 ORDER BY ' . $orderBy : '') . 01701 (strlen($limit) ? ' 01702 LIMIT ' . $limit : ''); 01703 01704 return $query; 01705 } 01706 01707 /** 01708 * Compiles an UPDATE statement from components array 01709 * 01710 * @param array Array of SQL query components 01711 * @return string SQL UPDATE query 01712 * @see parseUPDATE() 01713 */ 01714 protected function compileUPDATE($components) { 01715 01716 // Where clause: 01717 $where = $this->compileWhereClause($components['WHERE']); 01718 01719 // Fields 01720 $fields = array(); 01721 foreach ($components['FIELDS'] as $fN => $fV) { 01722 $fields[] = $fN . '=' . $fV[1] . $this->compileAddslashes($fV[0]) . $fV[1]; 01723 } 01724 01725 // Make query: 01726 $query = 'UPDATE ' . $components['TABLE'] . ' SET 01727 ' . implode(', 01728 ', $fields) . ' 01729 ' . (strlen($where) ? ' 01730 WHERE ' . $where : ''); 01731 01732 return $query; 01733 } 01734 01735 /** 01736 * Compiles an INSERT statement from components array 01737 * 01738 * @param array Array of SQL query components 01739 * @return string SQL INSERT query 01740 * @see parseINSERT() 01741 */ 01742 protected function compileINSERT($components) { 01743 $values = array(); 01744 01745 if (isset($components['VALUES_ONLY']) && is_array($components['VALUES_ONLY'])) { 01746 $valuesComponents = $components['EXTENDED'] === '1' ? $components['VALUES_ONLY'] : array($components['VALUES_ONLY']); 01747 $tableFields = array(); 01748 } else { 01749 $valuesComponents = $components['EXTENDED'] === '1' ? $components['FIELDS'] : array($components['FIELDS']); 01750 $tableFields = array_keys($valuesComponents[0]); 01751 } 01752 01753 foreach ($valuesComponents as $valuesComponent) { 01754 $fields = array(); 01755 foreach ($valuesComponent as $fV) { 01756 $fields[] = $fV[1] . $this->compileAddslashes($fV[0]) . $fV[1]; 01757 } 01758 $values[] = '(' . implode(', 01759 ', $fields) . ')'; 01760 } 01761 01762 // Make query: 01763 $query = 'INSERT INTO ' . $components['TABLE']; 01764 if (count($tableFields)) { 01765 $query .= ' 01766 (' . implode(', 01767 ', $tableFields) . ')'; 01768 } 01769 $query .= ' 01770 VALUES 01771 ' . implode(', 01772 ', $values); 01773 01774 return $query; 01775 } 01776 01777 /** 01778 * Compiles an DELETE statement from components array 01779 * 01780 * @param array Array of SQL query components 01781 * @return string SQL DELETE query 01782 * @see parseDELETE() 01783 */ 01784 protected function compileDELETE($components) { 01785 01786 // Where clause: 01787 $where = $this->compileWhereClause($components['WHERE']); 01788 01789 // Make query: 01790 $query = 'DELETE FROM ' . $components['TABLE'] . 01791 (strlen($where) ? ' 01792 WHERE ' . $where : ''); 01793 01794 return $query; 01795 } 01796 01797 /** 01798 * Compiles a CREATE TABLE statement from components array 01799 * 01800 * @param array Array of SQL query components 01801 * @return string SQL CREATE TABLE query 01802 * @see parseCREATETABLE() 01803 */ 01804 protected function compileCREATETABLE($components) { 01805 01806 // Create fields and keys: 01807 $fieldsKeys = array(); 01808 foreach ($components['FIELDS'] as $fN => $fCfg) { 01809 $fieldsKeys[] = $fN . ' ' . $this->compileFieldCfg($fCfg['definition']); 01810 } 01811 foreach ($components['KEYS'] as $kN => $kCfg) { 01812 if ($kN === 'PRIMARYKEY') { 01813 $fieldsKeys[] = 'PRIMARY KEY (' . implode(',', $kCfg) . ')'; 01814 } elseif ($kN === 'UNIQUE') { 01815 $key = key($kCfg); 01816 $fields = current($kCfg); 01817 $fieldsKeys[] = 'UNIQUE KEY ' . $key . ' (' . implode(',', $fields) . ')'; 01818 } else { 01819 $fieldsKeys[] = 'KEY ' . $kN . ' (' . implode(',', $kCfg) . ')'; 01820 } 01821 } 01822 01823 // Make query: 01824 $query = 'CREATE TABLE ' . $components['TABLE'] . ' ( 01825 ' . implode(', 01826 ', $fieldsKeys) . ' 01827 )' . ($components['tableType'] ? ' TYPE=' . $components['tableType'] : ''); 01828 01829 return $query; 01830 } 01831 01832 /** 01833 * Compiles an ALTER TABLE statement from components array 01834 * 01835 * @param array Array of SQL query components 01836 * @return string SQL ALTER TABLE query 01837 * @see parseALTERTABLE() 01838 */ 01839 protected function compileALTERTABLE($components) { 01840 01841 // Make query: 01842 $query = 'ALTER TABLE ' . $components['TABLE'] . ' ' . $components['action'] . ' ' . ($components['FIELD'] ? $components['FIELD'] : $components['KEY']); 01843 01844 // Based on action, add the final part: 01845 switch (strtoupper(str_replace(array(' ', TAB, CR, LF), '', $components['action']))) { 01846 case 'ADD': 01847 $query .= ' ' . $this->compileFieldCfg($components['definition']); 01848 break; 01849 case 'CHANGE': 01850 $query .= ' ' . $components['newField'] . ' ' . $this->compileFieldCfg($components['definition']); 01851 break; 01852 case 'DROP': 01853 case 'DROPKEY': 01854 break; 01855 case 'ADDKEY': 01856 case 'ADDPRIMARYKEY': 01857 case 'ADDUNIQUE': 01858 $query .= ' (' . implode(',', $components['fields']) . ')'; 01859 break; 01860 case 'DEFAULTCHARACTERSET': 01861 $query .= $components['charset']; 01862 break; 01863 case 'ENGINE': 01864 $query .= '= ' . $components['engine']; 01865 break; 01866 } 01867 01868 // Return query 01869 return $query; 01870 } 01871 01872 /** 01873 * Compiles a TRUNCATE TABLE statement from components array 01874 * 01875 * @param array Array of SQL query components 01876 * @return string SQL TRUNCATE TABLE query 01877 * @see parseTRUNCATETABLE() 01878 */ 01879 protected function compileTRUNCATETABLE(array $components) { 01880 01881 // Make query: 01882 $query = 'TRUNCATE TABLE ' . $components['TABLE']; 01883 01884 // Return query 01885 return $query; 01886 } 01887 01888 01889 /************************************** 01890 * 01891 * Compiling queries, helper functions for parts of queries 01892 * 01893 **************************************/ 01894 01895 /** 01896 * Compiles a "SELECT [output] FROM..:" field list based on input array (made with ->parseFieldList()) 01897 * Can also compile field lists for ORDER BY and GROUP BY. 01898 * 01899 * @param array Array of select fields, (made with ->parseFieldList()) 01900 * @param boolean Whether comments should be compiled 01901 * @return string Select field string 01902 * @see parseFieldList() 01903 */ 01904 public function compileFieldList($selectFields, $compileComments = TRUE) { 01905 01906 // Prepare buffer variable: 01907 $fields = ''; 01908 01909 // Traverse the selectFields if any: 01910 if (is_array($selectFields)) { 01911 $outputParts = array(); 01912 foreach ($selectFields as $k => $v) { 01913 01914 // Detecting type: 01915 switch ($v['type']) { 01916 case 'function': 01917 $outputParts[$k] = $v['function'] . '(' . $v['func_content'] . ')'; 01918 break; 01919 case 'flow-control': 01920 if ($v['flow-control']['type'] === 'CASE') { 01921 $outputParts[$k] = $this->compileCaseStatement($v['flow-control']); 01922 } 01923 break; 01924 case 'field': 01925 $outputParts[$k] = ($v['distinct'] ? $v['distinct'] : '') . ($v['table'] ? $v['table'] . '.' : '') . $v['field']; 01926 break; 01927 } 01928 01929 // Alias: 01930 if ($v['as']) { 01931 $outputParts[$k] .= ' ' . $v['as_keyword'] . ' ' . $v['as']; 01932 } 01933 01934 // Specifically for ORDER BY and GROUP BY field lists: 01935 if ($v['sortDir']) { 01936 $outputParts[$k] .= ' ' . $v['sortDir']; 01937 } 01938 } 01939 if ($compileComments && $selectFields[0]['comments']) { 01940 $fields = $selectFields[0]['comments'] . ' '; 01941 } 01942 $fields .= implode(', ', $outputParts); 01943 } 01944 01945 return $fields; 01946 } 01947 01948 /** 01949 * Compiles a CASE ... WHEN flow-control construct based on input array (made with ->parseCaseStatement()) 01950 * 01951 * @param array Array of case components, (made with ->parseCaseStatement()) 01952 * @return string case when string 01953 * @see parseCaseStatement() 01954 */ 01955 protected function compileCaseStatement(array $components) { 01956 $statement = 'CASE'; 01957 if (isset($components['case_field'])) { 01958 $statement .= ' ' . $components['case_field']; 01959 } elseif (isset($components['case_value'])) { 01960 $statement .= ' ' . $components['case_value'][1] . $components['case_value'][0] . $components['case_value'][1]; 01961 } 01962 foreach ($components['when'] as $when) { 01963 $statement .= ' WHEN '; 01964 $statement .= $this->compileWhereClause($when['when_value']); 01965 $statement .= ' THEN '; 01966 $statement .= $when['then_value'][1] . $when['then_value'][0] . $when['then_value'][1]; 01967 } 01968 if (isset($components['else'])) { 01969 $statement .= ' ELSE '; 01970 $statement .= $components['else'][1] . $components['else'][0] . $components['else'][1]; 01971 } 01972 $statement .= ' END'; 01973 return $statement; 01974 } 01975 01976 /** 01977 * Compiles a "FROM [output] WHERE..:" table list based on input array (made with ->parseFromTables()) 01978 * 01979 * @param array Array of table names, (made with ->parseFromTables()) 01980 * @return string Table name string 01981 * @see parseFromTables() 01982 */ 01983 public function compileFromTables($tablesArray) { 01984 01985 // Prepare buffer variable: 01986 $outputParts = array(); 01987 01988 // Traverse the table names: 01989 if (is_array($tablesArray)) { 01990 foreach ($tablesArray as $k => $v) { 01991 01992 // Set table name: 01993 $outputParts[$k] = $v['table']; 01994 01995 // Add alias AS if there: 01996 if ($v['as']) { 01997 $outputParts[$k] .= ' ' . $v['as_keyword'] . ' ' . $v['as']; 01998 } 01999 02000 if (is_array($v['JOIN'])) { 02001 foreach ($v['JOIN'] as $join) { 02002 $outputParts[$k] .= ' ' . $join['type'] . ' ' . $join['withTable']; 02003 // Add alias AS if there: 02004 if (isset($join['as']) && $join['as']) { 02005 $outputParts[$k] .= ' ' . $join['as_keyword'] . ' ' . $join['as']; 02006 } 02007 $outputParts[$k] .= ' ON '; 02008 foreach ($join['ON'] as $condition) { 02009 if ($condition['operator'] !== '') { 02010 $outputParts[$k] .= ' ' . $condition['operator'] . ' '; 02011 } 02012 $outputParts[$k] .= ($condition['left']['table']) ? $condition['left']['table'] . '.' : ''; 02013 $outputParts[$k] .= $condition['left']['field']; 02014 $outputParts[$k] .= $condition['comparator']; 02015 $outputParts[$k] .= ($condition['right']['table']) ? $condition['right']['table'] . '.' : ''; 02016 $outputParts[$k] .= $condition['right']['field']; 02017 } 02018 } 02019 } 02020 } 02021 } 02022 02023 // Return imploded buffer: 02024 return implode(', ', $outputParts); 02025 } 02026 02027 /** 02028 * Implodes an array of WHERE clause configuration into a WHERE clause. 02029 * 02030 * @param array WHERE clause configuration 02031 * @return string WHERE clause as string. 02032 * @see explodeWhereClause() 02033 */ 02034 public function compileWhereClause($clauseArray) { 02035 02036 // Prepare buffer variable: 02037 $output = ''; 02038 02039 // Traverse clause array: 02040 if (is_array($clauseArray)) { 02041 foreach ($clauseArray as $k => $v) { 02042 02043 // Set operator: 02044 $output .= $v['operator'] ? ' ' . $v['operator'] : ''; 02045 02046 // Look for sublevel: 02047 if (is_array($v['sub'])) { 02048 $output .= ' (' . trim($this->compileWhereClause($v['sub'])) . ')'; 02049 } elseif (isset($v['func']) && $v['func']['type'] === 'EXISTS') { 02050 $output .= ' ' . trim($v['modifier']) . ' EXISTS (' . $this->compileSELECT($v['func']['subquery']) . ')'; 02051 } else { 02052 02053 if (isset($v['func']) && $v['func']['type'] === 'LOCATE') { 02054 $output .= ' ' . trim($v['modifier']) . ' LOCATE('; 02055 $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1]; 02056 $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; 02057 $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : ''; 02058 $output .= ')'; 02059 } elseif (isset($v['func']) && $v['func']['type'] === 'IFNULL') { 02060 $output = ' ' . trim($v['modifier']) . ' IFNULL('; 02061 $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; 02062 $output .= ', ' . $v['func']['default'][1] . $this->compileAddslashes($v['func']['default'][0]) . $v['func']['default'][1]; 02063 $output .= ')'; 02064 } elseif (isset($v['func']) && $v['func']['type'] === 'FIND_IN_SET') { 02065 $output = ' ' . trim($v['modifier']) . ' FIND_IN_SET('; 02066 $output .= $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1]; 02067 $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; 02068 $output .= ')'; 02069 } else { 02070 02071 // Set field/table with modifying prefix if any: 02072 $output .= ' ' . trim($v['modifier'] . ' ' . ($v['table'] ? $v['table'] . '.' : '') . $v['field']); 02073 02074 // Set calculation, if any: 02075 if ($v['calc']) { 02076 $output .= $v['calc'] . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1]; 02077 } 02078 } 02079 02080 // Set comparator: 02081 if ($v['comparator']) { 02082 $output .= ' ' . $v['comparator']; 02083 02084 // Detecting value type; list or plain: 02085 if (t3lib_div::inList('NOTIN,IN', strtoupper(str_replace(array(' ', TAB, CR, LF), '', $v['comparator'])))) { 02086 if (isset($v['subquery'])) { 02087 $output .= ' (' . $this->compileSELECT($v['subquery']) . ')'; 02088 } else { 02089 $valueBuffer = array(); 02090 foreach ($v['value'] as $realValue) { 02091 $valueBuffer[] = $realValue[1] . $this->compileAddslashes($realValue[0]) . $realValue[1]; 02092 } 02093 $output .= ' (' . trim(implode(',', $valueBuffer)) . ')'; 02094 } 02095 } else { 02096 if (t3lib_div::inList('BETWEEN,NOT BETWEEN', $v['comparator'])) { 02097 $lbound = $v['values'][0]; 02098 $ubound = $v['values'][1]; 02099 $output .= ' ' . $lbound[1] . $this->compileAddslashes($lbound[0]) . $lbound[1]; 02100 $output .= ' AND '; 02101 $output .= $ubound[1] . $this->compileAddslashes($ubound[0]) . $ubound[1]; 02102 } else { 02103 if (isset($v['value']['operator'])) { 02104 $values = array(); 02105 foreach ($v['value']['args'] as $fieldDef) { 02106 $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field']; 02107 } 02108 $output .= ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')'; 02109 } else { 02110 $output .= ' ' . $v['value'][1] . $this->compileAddslashes($v['value'][0]) . $v['value'][1]; 02111 } 02112 } 02113 } 02114 } 02115 } 02116 } 02117 } 02118 02119 // Return output buffer: 02120 return $output; 02121 } 02122 02123 /** 02124 * Compile field definition 02125 * 02126 * @param array Field definition parts 02127 * @return string Field definition string 02128 */ 02129 public function compileFieldCfg($fieldCfg) { 02130 02131 // Set type: 02132 $cfg = $fieldCfg['fieldType']; 02133 02134 // Add value, if any: 02135 if (strlen($fieldCfg['value'])) { 02136 $cfg .= '(' . $fieldCfg['value'] . ')'; 02137 } 02138 02139 // Add additional features: 02140 if (is_array($fieldCfg['featureIndex'])) { 02141 foreach ($fieldCfg['featureIndex'] as $featureDef) { 02142 $cfg .= ' ' . $featureDef['keyword']; 02143 02144 // Add value if found: 02145 if (is_array($featureDef['value'])) { 02146 $cfg .= ' ' . $featureDef['value'][1] . $this->compileAddslashes($featureDef['value'][0]) . $featureDef['value'][1]; 02147 } 02148 } 02149 } 02150 02151 // Return field definition string: 02152 return $cfg; 02153 } 02154 02155 02156 /************************* 02157 * 02158 * Debugging 02159 * 02160 *************************/ 02161 02162 /** 02163 * Check parsability of input SQL part string; Will parse and re-compile after which it is compared 02164 * 02165 * @param string Part definition of string; "SELECT" = fieldlist (also ORDER BY and GROUP BY), "FROM" = table list, "WHERE" = Where clause. 02166 * @param string SQL string to verify parsability of 02167 * @return mixed Returns array with string 1 and 2 if error, otherwise false 02168 */ 02169 public function debug_parseSQLpart($part, $str) { 02170 $retVal = FALSE; 02171 02172 switch ($part) { 02173 case 'SELECT': 02174 $retVal = $this->debug_parseSQLpartCompare($str, $this->compileFieldList($this->parseFieldList($str))); 02175 break; 02176 case 'FROM': 02177 $retVal = $this->debug_parseSQLpartCompare($str, $this->compileFromTables($this->parseFromTables($str))); 02178 break; 02179 case 'WHERE': 02180 $retVal = $this->debug_parseSQLpartCompare($str, $this->compileWhereClause($this->parseWhereClause($str))); 02181 break; 02182 } 02183 return $retVal; 02184 } 02185 02186 /** 02187 * Compare two query strins by stripping away whitespace. 02188 * 02189 * @param string SQL String 1 02190 * @param string SQL string 2 02191 * @param boolean If true, the strings are compared insensitive to case 02192 * @return mixed Returns array with string 1 and 2 if error, otherwise false 02193 */ 02194 public function debug_parseSQLpartCompare($str, $newStr, $caseInsensitive = FALSE) { 02195 if ($caseInsensitive) { 02196 $str1 = strtoupper($str); 02197 $str2 = strtoupper($newStr); 02198 } else { 02199 $str1 = $str; 02200 $str2 = $newStr; 02201 } 02202 02203 // Fixing escaped chars: 02204 $search = array('\0', '\n', '\r', '\Z'); 02205 $replace = array("\x00", "\x0a", "\x0d", "\x1a"); 02206 $str1 = str_replace($search, $replace, $str1); 02207 $str2 = str_replace($search, $replace, $str2); 02208 02209 # Normally, commented out since they are needed only in tricky cases... 02210 # $str1 = stripslashes($str1); 02211 # $str2 = stripslashes($str2); 02212 02213 if (strcmp(str_replace(array(' ', TAB, CR, LF), '', $this->trimSQL($str1)), str_replace(array(' ', TAB, CR, LF), '', $this->trimSQL($str2)))) { 02214 return array( 02215 str_replace(array(' ', TAB, CR, LF), ' ', $str), 02216 str_replace(array(' ', TAB, CR, LF), ' ', $newStr), 02217 ); 02218 } 02219 } 02220 02221 /** 02222 * 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 02223 * 02224 * @param string SQL query 02225 * @return string Query if all is well, otherwise exit. 02226 */ 02227 public function debug_testSQL($SQLquery) { 02228 02229 // Getting result array: 02230 $parseResult = $this->parseSQL($SQLquery); 02231 02232 // If result array was returned, proceed. Otherwise show error and exit. 02233 if (is_array($parseResult)) { 02234 02235 // Re-compile query: 02236 $newQuery = $this->compileSQL($parseResult); 02237 02238 // TEST the new query: 02239 $testResult = $this->debug_parseSQLpartCompare($SQLquery, $newQuery); 02240 02241 // Return new query if OK, otherwise show error and exit: 02242 if (!is_array($testResult)) { 02243 return $newQuery; 02244 } else { 02245 debug(array('ERROR MESSAGE' => 'Input query did not match the parsed and recompiled query exactly (not observing whitespace)', 'TEST result' => $testResult), 'SQL parsing failed:'); 02246 exit; 02247 } 02248 } else { 02249 debug(array('query' => $SQLquery, 'ERROR MESSAGE' => $parseResult), 'SQL parsing failed:'); 02250 exit; 02251 } 02252 } 02253 } 02254 02255 02256 if (defined('TYPO3_MODE') && isset($GLOBALS['TYPO3_CONF_VARS'][TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlparser.php'])) { 02257 include_once($GLOBALS['TYPO3_CONF_VARS'][TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlparser.php']); 02258 } 02259 02260 ?>
1.8.0