2 /***************************************************************
5 * (c) 2004-2006 Kasper Skaarhoj (kasperYYYY@typo3.com)
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.
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.
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.
25 * This copyright notice MUST APPEAR in all copies of the script!
26 ***************************************************************/
32 * @author Kasper Skaarhoj <kasperYYYY@typo3.com>
35 * [CLASS/FUNCTION INDEX of SCRIPT]
39 * 107: class t3lib_sqlparser
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)
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='')
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)
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)
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)
84 * 1654: function debug_parseSQLpart($part,$str)
85 * 1679: function debug_parseSQLpartCompare($str,$newStr,$caseInsensitive=FALSE)
86 * 1712: function debug_testSQL($SQLquery)
89 * (This index is automatically created/updated by the extension "extdeveval")
101 * TYPO3 SQL parser class.
103 * @author Kasper Skaarhoj <kasperYYYY@typo3.com>
107 class t3lib_sqlparser
{
110 var $parse_error = ''; // Parsing error string
111 var $lastStopKeyWord = ''; // Last stop keyword used.
116 /*************************************
118 * SQL Parsing, full queries
120 **************************************/
123 * Parses any single SQL query
125 * @param string SQL query
126 * @return array Result array with all the parts in - or error message string
127 * @see compileSQL(), debug_testSQL()
129 function parseSQL($parseString) {
130 // Prepare variables:
131 $parseString = $this->trimSQL($parseString);
132 $this->parse_error
= '';
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));
142 // Parsing SELECT query:
143 $result = $this->parseSELECT($parseString);
146 // Parsing UPDATE query:
147 $result = $this->parseUPDATE($parseString);
150 // Parsing INSERT query:
151 $result = $this->parseINSERT($parseString);
154 // Parsing DELETE query:
155 $result = $this->parseDELETE($parseString);
158 // Parsing EXPLAIN SELECT query:
159 $result = $this->parseEXPLAIN($parseString);
162 // Parsing DROP TABLE query:
163 $result = $this->parseDROPTABLE($parseString);
166 // Parsing ALTER TABLE query:
167 $result = $this->parseALTERTABLE($parseString);
170 // Parsing CREATE TABLE query:
171 $result = $this->parseCREATETABLE($parseString);
173 case 'CREATEDATABASE':
174 // Parsing CREATE DATABASE query:
175 $result = $this->parseCREATEDATABASE($parseString);
178 $result = $this->parseError('"'.$keyword.'" is not a keyword',$parseString);
186 * Parsing SELECT query
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()
192 function parseSELECT($parseString) {
195 $parseString = $this->trimSQL($parseString);
196 $parseString = ltrim(substr($parseString,6)); // REMOVE eregi_replace('^SELECT[[:space:]]+','',$parseString);
198 // Init output variable:
200 $result['type'] = 'SELECT';
202 // Looking for STRAIGHT_JOIN keyword:
203 $result['STRAIGHT_JOIN'] = $this->nextPart($parseString, '^(STRAIGHT_JOIN)[[:space:]]+');
206 $result['SELECT'] = $this->parseFieldList($parseString, '^(FROM)[[:space:]]+');
207 if ($this->parse_error
) { return $this->parse_error
; }
209 // Continue if string is not ended:
213 $result['FROM'] = $this->parseFromTables($parseString, '^(WHERE)[[:space:]]+');
214 if ($this->parse_error
) { return $this->parse_error
; }
216 // If there are more than just the tables (a WHERE clause that would be...)
220 $result['WHERE'] = $this->parseWhereClause($parseString, '^(GROUP[[:space:]]+BY|ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
221 if ($this->parse_error
) { return $this->parse_error
; }
223 // If the WHERE clause parsing was stopped by GROUP BY, ORDER BY or LIMIT, then proceed with parsing:
224 if ($this->lastStopKeyWord
) {
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
; }
233 if ($this->lastStopKeyWord
== 'ORDERBY') {
234 $result['ORDERBY'] = $this->parseFieldList($parseString, '^(LIMIT)[[:space:]]+');
235 if ($this->parse_error
) { return $this->parse_error
; }
239 if ($this->lastStopKeyWord
== 'LIMIT') {
240 if (preg_match('/^([0-9]+|[0-9]+[[:space:]]*,[[:space:]]*[0-9]+)$/',trim($parseString))) {
241 $result['LIMIT'] = $parseString;
243 return $this->parseError('No value for limit!',$parseString);
248 } else return $this->parseError('No table to select from!',$parseString);
255 * Parsing UPDATE query
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()
261 function parseUPDATE($parseString) {
264 $parseString = $this->trimSQL($parseString);
265 $parseString = ltrim(substr($parseString,6)); // REMOVE eregi_replace('^UPDATE[[:space:]]+','',$parseString);
267 // Init output variable:
269 $result['type'] = 'UPDATE';
272 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
274 // Continue if string is not ended:
275 if ($result['TABLE']) {
276 if ($parseString && $this->nextPart($parseString, '^(SET)[[:space:]]+')) {
280 // Get field/value pairs:
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);
288 $comma = $this->nextPart($parseString,'^(,)');
292 if ($this->nextPart($parseString,'^(WHERE)')) {
293 $result['WHERE'] = $this->parseWhereClause($parseString);
294 if ($this->parse_error
) { return $this->parse_error
; }
296 } else return $this->parseError('Query missing SET...',$parseString);
297 } else return $this->parseError('No table found!',$parseString);
299 // Should be no more content now:
301 return $this->parseError('Still content in clause after parsing!',$parseString);
309 * Parsing INSERT query
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()
315 function parseINSERT($parseString) {
318 $parseString = $this->trimSQL($parseString);
319 $parseString = ltrim(substr(ltrim(substr($parseString,6)),4)); // REMOVE eregi_replace('^INSERT[[:space:]]+INTO[[:space:]]+','',$parseString);
321 // Init output variable:
323 $result['type'] = 'INSERT';
326 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()');
328 if ($result['TABLE']) {
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
; }
338 if ($this->nextPart($parseString,'^(VALUES)[[:space:]]+')) { // "VALUES" keyword binds the fieldnames to values:
340 $values = $this->getValue($parseString,'IN'); // Using the "getValue" function to get the field list...
341 if ($this->parse_error
) { return $this->parse_error
; }
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);
352 if (isset($values[$k+
1])) {
353 return $this->parseError('Too many values in list!',$parseString);
355 } else return $this->parseError('VALUES keyword expected',$parseString);
357 } else return $this->parseError('No table found!',$parseString);
359 // Should be no more content now:
361 return $this->parseError('Still content after parsing!',$parseString);
369 * Parsing DELETE query
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()
375 function parseDELETE($parseString) {
378 $parseString = $this->trimSQL($parseString);
379 $parseString = ltrim(substr(ltrim(substr($parseString,6)),4)); // REMOVE eregi_replace('^DELETE[[:space:]]+FROM[[:space:]]+','',$parseString);
381 // Init output variable:
383 $result['type'] = 'DELETE';
386 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
388 if ($result['TABLE']) {
391 if ($this->nextPart($parseString,'^(WHERE)')) {
392 $result['WHERE'] = $this->parseWhereClause($parseString);
393 if ($this->parse_error
) { return $this->parse_error
; }
395 } else return $this->parseError('No table found!',$parseString);
397 // Should be no more content now:
399 return $this->parseError('Still content in clause after parsing!',$parseString);
407 * Parsing EXPLAIN query
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.
413 function parseEXPLAIN($parseString) {
416 $parseString = $this->trimSQL($parseString);
417 $parseString = ltrim(substr($parseString,6)); // REMOVE eregi_replace('^EXPLAIN[[:space:]]+','',$parseString);
419 // Init output variable:
420 $result = $this->parseSELECT($parseString);
421 if (is_array($result)) {
422 $result['type'] = 'EXPLAIN';
429 * Parsing CREATE TABLE query
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()
435 function parseCREATETABLE($parseString) {
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);
441 // Init output variable:
443 $result['type'] = 'CREATETABLE';
446 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*\(',TRUE);
448 if ($result['TABLE']) {
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));
457 $result['KEYS']['PRIMARYKEY'] = $this->getValue($parseString,'_LIST');
458 if ($this->parse_error
) { return $this->parse_error
; }
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);
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);
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
; }
479 // Finding delimiter:
480 $delim = $this->nextPart($parseString, '^(,|\))');
482 return $this->parseError('No delimiter found',$parseString);
483 } elseif ($delim==')') {
488 // Finding what is after the table definition - table type in MySQL
490 if ($this->nextPart($parseString, '^(TYPE[[:space:]]*=)')) {
491 $result['tableType'] = $parseString;
494 } else return $this->parseError('No fieldname found!',$parseString);
496 // Getting table type
497 } else return $this->parseError('No table found!',$parseString);
499 // Should be no more content now:
501 return $this->parseError('Still content in clause after parsing!',$parseString);
508 * Parsing ALTER TABLE query
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()
514 function parseALTERTABLE($parseString) {
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);
520 // Init output variable:
522 $result['type'] = 'ALTERTABLE';
525 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
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']));
532 if (t3lib_div
::inList('ADDPRIMARYKEY,DROPPRIMARYKEY',$actionKey) ||
$fieldKey = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) {
536 $result['FIELD'] = $fieldKey;
537 $result['definition'] = $this->parseFieldDef($parseString);
538 if ($this->parse_error
) { return $this->parse_error
; }
542 $result['FIELD'] = $fieldKey;
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);
553 case 'ADDPRIMARYKEY':
554 $result['KEY'] = $fieldKey;
555 $result['fields'] = $this->getValue($parseString,'_LIST');
556 if ($this->parse_error
) { return $this->parse_error
; }
559 $result['KEY'] = $fieldKey;
561 case 'DROPPRIMARYKEY':
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);
569 // Should be no more content now:
571 return $this->parseError('Still content in clause after parsing!',$parseString);
578 * Parsing DROP TABLE query
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.
583 function parseDROPTABLE($parseString) {
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);
589 // Init output variable:
591 $result['type'] = 'DROPTABLE';
594 $result['ifExists'] = $this->nextPart($parseString, '^(IF[[:space:]]+EXISTS[[:space:]]+)');
597 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
599 if ($result['TABLE']) {
601 // Should be no more content now:
603 return $this->parseError('Still content in clause after parsing!',$parseString);
607 } else return $this->parseError('No table found!',$parseString);
611 * Parsing CREATE DATABASE query
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.
616 function parseCREATEDATABASE($parseString) {
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);
622 // Init output variable:
624 $result['type'] = 'CREATEDATABASE';
627 $result['DATABASE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
629 if ($result['DATABASE']) {
631 // Should be no more content now:
633 return $this->parseError('Still content in clause after parsing!',$parseString);
637 } else return $this->parseError('No database found!',$parseString);
654 /**************************************
656 * SQL Parsing, helper functions for parts of queries
658 **************************************/
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!
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()
670 function parseFieldList(&$parseString, $stopRegex='') {
672 $stack = array(); // Contains the parsed content
674 if(strlen($parseString)==0) return $stack; // FIXME - should never happen, why does it?
676 $pnt = 0; // Pointer to positions in $stack
677 $level = 0; // Indicates the parenthesis level we are at.
678 $loopExit = 0; // Recursivity brake.
680 // Prepare variables:
681 $parseString = $this->trimSQL($parseString);
682 $this->lastStopKeyWord
= '';
683 $this->parse_error
= '';
685 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
686 while (strlen($parseString)) {
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 "("):
691 // Accumulate function content until next () parenthesis:
692 $funcContent = $this->nextPart($parseString,'^([^()]*.)');
693 $stack[$pnt]['func_content.'][] = array(
695 'func_content' => substr($funcContent,0,-1)
697 $stack[$pnt]['func_content'].= $funcContent;
700 switch(substr($stack[$pnt]['func_content'],-1)) {
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.
712 } else { // Outside parenthesis, looking for next field:
714 // Looking for a known function (only known functions supported)
715 $func = $this->nextPart($parseString,'^(count|max|min|floor|sum|avg)[[:space:]]*\(');
717 $parseString = trim(substr($parseString,1)); // Strip of "("
718 $stack[$pnt]['type'] = 'function';
719 $stack[$pnt]['function'] = $func;
720 $level++
; // increse parenthesis level counter.
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';
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];
733 $stack[$pnt]['table'] = '';
734 $stack[$pnt]['field'] = $tableField[0];
737 return $this->parseError('No field name found as expected in parseFieldList()',$parseString);
742 // After a function or field we look for "AS" alias and a comma to separate to the next field in the list:
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;
751 // Looking for "ASC" or "DESC" keywords (for ORDER BY)
752 if ($sDir = $this->nextPart($parseString,'^(ASC|DESC)([[:space:]]+|,)')) {
753 $stack[$pnt]['sortDir'] = $sDir;
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
));
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);
767 // Increasing pointer:
771 // Check recursivity brake:
774 return $this->parseError('More than 500 loops, exiting prematurely in parseFieldList()...',$parseString);
778 // Return result array:
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.
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()
791 function parseFromTables(&$parseString, $stopRegex='') {
793 // Prepare variables:
794 $parseString = $this->trimSQL($parseString);
795 $this->lastStopKeyWord
= '';
796 $this->parse_error
= '';
798 $stack = array(); // Contains the parsed content
799 $pnt = 0; // Pointer to positions in $stack
800 $loopExit = 0; // Recursivity brake.
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
));
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:]]*');
815 } else return $this->parseError('No table name found as expected in parseFromTables()!',$parseString);
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) {
825 // Explode fields into field and table:
826 $tableField = explode('.',$field1,2);
828 if (count($tableField)!=2) {
829 $field1['table'] = '';
830 $field1['field'] = $tableField[0];
832 $field1['table'] = $tableField[0];
833 $field1['field'] = $tableField[1];
835 $tableField = explode('.',$field2,2);
837 if (count($tableField)!=2) {
838 $field2['table'] = '';
839 $field2['field'] = $tableField[0];
841 $field2['table'] = $tableField[0];
842 $field2['field'] = $tableField[1];
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);
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
));
855 // Looking for comma:
856 if (strlen($parseString) && !$this->nextPart($parseString,'^(,)')) {
857 return $this->parseError('No comma found as expected in parseFromTables()',$parseString);
860 // Increasing pointer:
863 // Check recursivity brake:
866 return $this->parseError('More than 500 loops, exiting prematurely in parseFromTables()...',$parseString);
870 // Return result array:
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.
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.
882 function parseWhereClause(&$parseString, $stopRegex='') {
884 // Prepare variables:
885 $parseString = $this->trimSQL($parseString);
886 $this->lastStopKeyWord
= '';
887 $this->parse_error
= '';
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.
894 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
895 while (strlen($parseString)) {
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:
905 // Find "modifyer", eg. "NOT or !"
906 $stack[$level][$pnt[$level]]['modifier'] = trim($this->nextPart($parseString,'^(!|NOT[[:space:]]+)'));
909 if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]._]+)([[:space:]]+|&|<=|>=|<|>|=|!=|IS)')) {
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];
917 $stack[$level][$pnt[$level]]['table'] = '';
918 $stack[$level][$pnt[$level]]['field'] = $tableField[0];
921 return $this->parseError('No field name found as expected in parseWhereClause()',$parseString);
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);
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
; }
939 // Finished, increase pointer:
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
948 // Make recursivity check:
951 return $this->parseError('More than 500 loops (in search for exit parenthesis), exiting prematurely in parseWhereClause()...',$parseString);
955 // Detecting the operator for the next level:
956 $op = $this->nextPart($parseString,'^(AND[[:space:]]+NOT|OR[[:space:]]+NOT|AND|OR)(\(|[[:space:]]+)');
958 $stack[$level][$pnt[$level]]['operator'] = $op;
959 } elseif (strlen($parseString)) {
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
));
966 return $this->parseError('No operator, but parsing not finished in parseWhereClause().',$parseString);
971 // Make recursivity check:
974 return $this->parseError('More than 500 loops, exiting prematurely in parseWhereClause()...',$parseString);
978 // Return the stacks lowest level:
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.
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.
990 function parseFieldDef(&$parseString, $stopRegex='') {
991 // Prepare variables:
992 $parseString = $this->trimSQL($parseString);
993 $this->lastStopKeyWord
= '';
994 $this->parse_error
= '';
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:],]+|\()')) {
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);
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));
1013 $result['featureIndex'][$keywordCmp]['keyword'] = $keyword;
1015 switch($keywordCmp) {
1017 $result['featureIndex'][$keywordCmp]['value'] = $this->getValue($parseString);
1022 return $this->parseError('Field type unknown in parseFieldDef()!',$parseString);
1038 /************************************
1040 * Parsing: Helper functions
1042 ************************************/
1045 * Strips off a part of the parseString and returns the matching part.
1046 * Helper function for the parsing methods.
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.
1053 function nextPart(&$parseString,$regex,$trimAll=FALSE) {
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])));
1062 * Finds value in beginning of $parseString, returns result and strips it of parseString
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)
1068 function getValue(&$parseString,$comparator='') {
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();
1076 while($comma==',') {
1077 $listValues[] = $this->getValue($parseString);
1078 $comma = $this->nextPart($parseString,'^([,])');
1081 $out = $this->nextPart($parseString,'^([)])');
1083 if ($comparator=='_LIST') {
1085 foreach ($listValues as $vArr) {
1086 $kVals[] = $vArr[0];
1092 } else return array($this->parseError('No ) parenthesis in list',$parseString));
1093 } else return array($this->parseError('No ( parenthesis starting the list',$parseString));
1095 } else { // Just plain string value, in quotes or not:
1098 $firstChar = substr($parseString,0,1);
1099 switch($firstChar) {
1101 $value = array($this->getValueInQuotes($parseString,'"'),'"');
1104 $value = array($this->getValueInQuotes($parseString,"'"),"'");
1108 if (preg_match('/^([[:alnum:]._-]+)/i',$parseString, $reg)) {
1109 $parseString = ltrim(substr($parseString,strlen($reg[0])));
1110 $value = array($reg[1]);
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
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() !
1126 function getValueInQuotes(&$parseString,$quote) {
1128 $parts = explode($quote,substr($parseString,1));
1130 foreach($parts as $k => $v) {
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
) {
1139 $parseString = ltrim(substr($parseString,strlen($buffer)+
2));
1140 return $this->parseStripslashes($buffer);
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
1149 * @param string Input string
1150 * @return string Output string
1152 function parseStripslashes($str) {
1153 $search = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
1154 $replace = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
1156 return str_replace($search, $replace, $str);
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
1163 * @param string Input string
1164 * @return string Output string
1166 function compileAddslashes($str) {
1168 $search = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
1169 $replace = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
1171 return str_replace($search, $replace, $str);
1175 * Setting the internal error message value, $this->parse_error and returns that value.
1177 * @param string Input error message
1178 * @param string Remaining query to parse.
1179 * @return string Error message.
1181 function parseError($msg,$restQuery) {
1182 $this->parse_error
= 'SQL engine parse ERROR: '.$msg.': near "'.substr($restQuery,0,50).'"';
1183 return $this->parse_error
;
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
1192 * @param string Input string
1193 * @return string Output string
1195 function trimSQL($str) {
1196 return trim(rtrim($str, "; \r\n\t")).' ';
1197 //return trim(ereg_replace('[[:space:];]*$','',$str)).' ';
1211 /*************************
1215 *************************/
1218 * Compiles an SQL query from components
1220 * @param array Array of SQL query components
1221 * @return string SQL query
1224 function compileSQL($components) {
1225 switch($components['type']) {
1227 $query = $this->compileSELECT($components);
1230 $query = $this->compileUPDATE($components);
1233 $query = $this->compileINSERT($components);
1236 $query = $this->compileDELETE($components);
1239 $query = 'EXPLAIN '.$this->compileSELECT($components);
1242 $query = 'DROP TABLE'.($components['ifExists']?
' IF EXISTS':'').' '.$components['TABLE'];
1245 $query = $this->compileCREATETABLE($components);
1248 $query = $this->compileALTERTABLE($components);
1256 * Compiles a SELECT statement from components array
1258 * @param array Array of SQL query components
1259 * @return string SQL SELECT query
1260 * @see parseSELECT()
1262 function compileSELECT($components) {
1265 $where = $this->compileWhereClause($components['WHERE']);
1266 $groupBy = $this->compileFieldList($components['GROUPBY']);
1267 $orderBy = $this->compileFieldList($components['ORDERBY']);
1268 $limit = $components['LIMIT'];
1271 $query = 'SELECT '.($components['STRAIGHT_JOIN'] ?
$components['STRAIGHT_JOIN'].'' : '').'
1272 '.$this->compileFieldList($components['SELECT']).'
1273 FROM '.$this->compileFromTables($components['FROM']).
1275 WHERE '.$where : '').
1277 GROUP BY '.$groupBy : '').
1279 ORDER BY '.$orderBy : '').
1281 LIMIT '.$limit : '');
1287 * Compiles an UPDATE statement from components array
1289 * @param array Array of SQL query components
1290 * @return string SQL UPDATE query
1291 * @see parseUPDATE()
1293 function compileUPDATE($components) {
1296 $where = $this->compileWhereClause($components['WHERE']);
1300 foreach($components['FIELDS'] as $fN => $fV) {
1301 $fields[]=$fN.'='.$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1305 $query = 'UPDATE '.$components['TABLE'].' SET
1309 WHERE '.$where : '');
1315 * Compiles an INSERT statement from components array
1317 * @param array Array of SQL query components
1318 * @return string SQL INSERT query
1319 * @see parseINSERT()
1321 function compileINSERT($components) {
1323 if ($components['VALUES_ONLY']) {
1326 foreach($components['VALUES_ONLY'] as $fV) {
1327 $fields[]=$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1331 $query = 'INSERT INTO '.$components['TABLE'].'
1338 foreach($components['FIELDS'] as $fN => $fV) {
1339 $fields[$fN]=$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1343 $query = 'INSERT INTO '.$components['TABLE'].'
1345 ',array_keys($fields)).')
1355 * Compiles an DELETE statement from components array
1357 * @param array Array of SQL query components
1358 * @return string SQL DELETE query
1359 * @see parseDELETE()
1361 function compileDELETE($components) {
1364 $where = $this->compileWhereClause($components['WHERE']);
1367 $query = 'DELETE FROM '.$components['TABLE'].
1369 WHERE '.$where : '');
1375 * Compiles a CREATE TABLE statement from components array
1377 * @param array Array of SQL query components
1378 * @return string SQL CREATE TABLE query
1379 * @see parseCREATETABLE()
1381 function compileCREATETABLE($components) {
1383 // Create fields and keys:
1384 $fieldsKeys = array();
1385 foreach($components['FIELDS'] as $fN => $fCfg) {
1386 $fieldsKeys[]=$fN.' '.$this->compileFieldCfg($fCfg['definition']);
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).')';
1394 $fieldsKeys[]='KEY '.$kN.' ('.implode(',', $kCfg).')';
1399 $query = 'CREATE TABLE '.$components['TABLE'].' (
1402 )'.($components['tableType'] ?
' TYPE='.$components['tableType'] : '');
1408 * Compiles an ALTER TABLE statement from components array
1410 * @param array Array of SQL query components
1411 * @return string SQL ALTER TABLE query
1412 * @see parseALTERTABLE()
1414 function compileALTERTABLE($components) {
1417 $query = 'ALTER TABLE '.$components['TABLE'].' '.$components['action'].' '.($components['FIELD']?
$components['FIELD']:$components['KEY']);
1419 // Based on action, add the final part:
1420 switch(strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$components['action']))) {
1422 $query.=' '.$this->compileFieldCfg($components['definition']);
1425 $query.=' '.$components['newField'].' '.$this->compileFieldCfg($components['definition']);
1431 case 'ADDPRIMARYKEY':
1432 $query.=' ('.implode(',',$components['fields']).')';
1453 /**************************************
1455 * Compiling queries, helper functions for parts of queries
1457 **************************************/
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.
1463 * @param array Array of select fields, (made with ->parseFieldList())
1464 * @return string Select field string
1465 * @see parseFieldList()
1467 function compileFieldList($selectFields) {
1469 // Prepare buffer variable:
1470 $outputParts = array();
1472 // Traverse the selectFields if any:
1473 if (is_array($selectFields)) {
1474 foreach($selectFields as $k => $v) {
1477 switch($v['type']) {
1479 $outputParts[$k] = $v['function'].'('.$v['func_content'].')';
1482 $outputParts[$k] = ($v['distinct']?
$v['distinct']:'').($v['table']?
$v['table'].'.':'').$v['field'];
1488 $outputParts[$k].= ' '.$v['as_keyword'].' '.$v['as'];
1491 // Specifically for ORDER BY and GROUP BY field lists:
1492 if ($v['sortDir']) {
1493 $outputParts[$k].= ' '.$v['sortDir'];
1498 // Return imploded buffer:
1499 return implode(', ',$outputParts);
1503 * Compiles a "FROM [output] WHERE..:" table list based on input array (made with ->parseFromTables())
1505 * @param array Array of table names, (made with ->parseFromTables())
1506 * @return string Table name string
1507 * @see parseFromTables()
1509 function compileFromTables($tablesArray) {
1511 // Prepare buffer variable:
1512 $outputParts = array();
1514 // Traverse the table names:
1515 if (is_array($tablesArray)) {
1516 foreach($tablesArray as $k => $v) {
1519 $outputParts[$k] = $v['table'];
1521 // Add alias AS if there:
1523 $outputParts[$k].= ' '.$v['as_keyword'].' '.$v['as'];
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'];
1537 // Return imploded buffer:
1538 return implode(', ',$outputParts);
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?!?!
1546 * @param array WHERE clause configuration
1547 * @return string WHERE clause as string.
1548 * @see explodeWhereClause()
1550 function compileWhereClause($clauseArray) {
1552 // Prepare buffer variable:
1555 // Traverse clause array:
1556 if (is_array($clauseArray)) {
1557 foreach($clauseArray as $k => $v) {
1560 $output.=$v['operator'] ?
' '.$v['operator'] : '';
1562 // Look for sublevel:
1563 if (is_array($v['sub'])) {
1564 $output.=' ('.trim($this->compileWhereClause($v['sub'])).')';
1567 // Set field/table with modifying prefix if any:
1568 $output.=' '.trim($v['modifier'].' '.($v['table']?
$v['table'].'.':'').$v['field']);
1570 // Set calculation, if any:
1572 $output.=$v['calc'].$v['calc_value'][1].$this->compileAddslashes($v['calc_value'][0]).$v['calc_value'][1];
1576 if ($v['comparator']) {
1577 $output.=' '.$v['comparator'];
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];
1585 $output.=' ('.trim(implode(',',$valueBuffer)).')';
1587 $output.=' '.$v['value'][1].$this->compileAddslashes($v['value'][0]).$v['value'][1];
1594 // Return output buffer:
1599 * Compile field definition
1601 * @param array Field definition parts
1602 * @return string Field definition string
1604 function compileFieldCfg($fieldCfg) {
1607 $cfg = $fieldCfg['fieldType'];
1609 // Add value, if any:
1610 if (strlen($fieldCfg['value'])) {
1611 $cfg.='('.$fieldCfg['value'].')';
1614 // Add additional features:
1615 if (is_array($fieldCfg['featureIndex'])) {
1616 foreach($fieldCfg['featureIndex'] as $featureDef) {
1617 $cfg.=' '.$featureDef['keyword'];
1619 // Add value if found:
1620 if (is_array($featureDef['value'])) {
1621 $cfg.=' '.$featureDef['value'][1].$this->compileAddslashes($featureDef['value'][0]).$featureDef['value'][1];
1626 // Return field definition string:
1640 /*************************
1644 *************************/
1647 * Check parsability of input SQL part string; Will parse and re-compile after which it is compared
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
1653 function debug_parseSQLpart($part,$str) {
1658 $retVal = $this->debug_parseSQLpartCompare($str,$this->compileFieldList($this->parseFieldList($str)));
1661 $retVal = $this->debug_parseSQLpartCompare($str,$this->compileFromTables($this->parseFromTables($str)));
1664 $retVal = $this->debug_parseSQLpartCompare($str,$this->compileWhereClause($this->parseWhereClause($str)));
1671 * Compare two query strins by stripping away whitespace.
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
1678 function debug_parseSQLpartCompare($str,$newStr,$caseInsensitive=FALSE) {
1679 if ($caseInsensitive) {
1680 $str1 = strtoupper($str);
1681 $str2 = strtoupper($newStr);
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);
1693 # Normally, commented out since they are needed only in tricky cases...
1694 # $str1 = stripslashes($str1);
1695 # $str2 = stripslashes($str2);
1697 if (strcmp(str_replace(array(' ',"\t","\r","\n"),'',$this->trimSQL($str1)),str_replace(array(' ',"\t","\r","\n"),'',$this->trimSQL($str2)))) {
1699 str_replace(array(' ',"\t","\r","\n"),' ',$str),
1700 str_replace(array(' ',"\t","\r","\n"),' ',$newStr),
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
1708 * @param string SQL query
1709 * @return string Query if all is well, otherwise exit.
1711 function debug_testSQL($SQLquery) {
1713 // Getting result array:
1714 $parseResult = $this->parseSQL($SQLquery);
1716 // If result array was returned, proceed. Otherwise show error and exit.
1717 if (is_array($parseResult)) {
1719 // Re-compile query:
1720 $newQuery = $this->compileSQL($parseResult);
1722 // TEST the new query:
1723 $testResult = $this->debug_parseSQLpartCompare($SQLquery, $newQuery);
1725 // Return new query if OK, otherwise show error and exit:
1726 if (!is_array($testResult)) {
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:');
1733 debug(array('query' => $SQLquery, 'ERROR MESSAGE'=>$parseResult),'SQL parsing failed:');
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']);