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