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