5b2b2d5fe05d78bdec21b0675144edd2d6f5e5c9
[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 (strlen($parseString) > 0) {
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 (strlen($parseString) === 0) {
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 (strlen($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 (strlen($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 (strlen($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 } else {
918 return $this->parseError('No join field found in parseFromTables()!', $parseString);
919 }
920 $stack[$pnt]['JOIN'][$joinCnt]['ON'][] = $condition;
921 if (($operator = $this->nextPart($parseString, '^(AND|OR)')) !== '') {
922 $condition = array('operator' => $operator);
923 } else {
924 $parseCondition = FALSE;
925 }
926 }
927 $joinCnt++;
928 } else {
929 return $this->parseError('No join table found in parseFromTables()!', $parseString);
930 }
931 }
932 // Looking for stop-keywords:
933 if ($stopRegex && ($this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))) {
934 $this->lastStopKeyWord = $this->normalizeKeyword($this->lastStopKeyWord);
935 return $stack;
936 }
937 // Looking for comma:
938 if (strlen($parseString) && !$this->nextPart($parseString, '^(,)')) {
939 return $this->parseError('No comma found as expected in parseFromTables()', $parseString);
940 }
941 // Increasing pointer:
942 $pnt++;
943 // Check recursivity brake:
944 $loopExit++;
945 if ($loopExit > 500) {
946 return $this->parseError('More than 500 loops, exiting prematurely in parseFromTables()...', $parseString);
947 }
948 }
949 // Return result array:
950 return $stack;
951 }
952
953 /**
954 * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array.
955 * The success of this parsing determines if that part of the query is supported by TYPO3.
956 *
957 * @param string $parseString WHERE clause to parse. NOTICE: passed by reference!
958 * @param string $stopRegex Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)'
959 * @param array $parameterReferences Array holding references to either named (:name) or question mark (?) parameters found
960 * @return mixed If successful parsing, returns an array, otherwise an error string.
961 */
962 public function parseWhereClause(&$parseString, $stopRegex = '', array &$parameterReferences = array()) {
963 // Prepare variables:
964 $parseString = $this->trimSQL($parseString);
965 $this->lastStopKeyWord = '';
966 $this->parse_error = '';
967 // Contains the parsed content
968 $stack = array(0 => array());
969 // Pointer to positions in $stack
970 $pnt = array(0 => 0);
971 // Determines parenthesis level
972 $level = 0;
973 // Recursivity brake.
974 $loopExit = 0;
975 // $parseString is continuously shortened by the process and we keep parsing it till it is zero:
976 while (strlen($parseString)) {
977 // Look for next parenthesis level:
978 $newLevel = $this->nextPart($parseString, '^([(])');
979 // If new level is started, manage stack/pointers:
980 if ($newLevel === '(') {
981 // Increase level
982 $level++;
983 // Reset pointer for this level
984 $pnt[$level] = 0;
985 // Reset stack for this level
986 $stack[$level] = array();
987 } else {
988 // If no new level is started, just parse the current level:
989 // Find "modifier", eg. "NOT or !"
990 $stack[$level][$pnt[$level]]['modifier'] = trim($this->nextPart($parseString, '^(!|NOT[[:space:]]+)'));
991 // See if condition is EXISTS with a subquery
992 if (preg_match('/^EXISTS[[:space:]]*[(]/i', $parseString)) {
993 $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(EXISTS)[[:space:]]*');
994 // Strip of "("
995 $parseString = trim(substr($parseString, 1));
996 $stack[$level][$pnt[$level]]['func']['subquery'] = $this->parseSELECT($parseString, $parameterReferences);
997 // Seek to new position in parseString after parsing of the subquery
998 $parseString = $stack[$level][$pnt[$level]]['func']['subquery']['parseString'];
999 unset($stack[$level][$pnt[$level]]['func']['subquery']['parseString']);
1000 if (!$this->nextPart($parseString, '^([)])')) {
1001 return 'No ) parenthesis at end of subquery';
1002 }
1003 } else {
1004 // See if LOCATE function is found
1005 if (preg_match('/^LOCATE[[:space:]]*[(]/i', $parseString)) {
1006 $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(LOCATE)[[:space:]]*');
1007 // Strip of "("
1008 $parseString = trim(substr($parseString, 1));
1009 $stack[$level][$pnt[$level]]['func']['substr'] = $this->getValue($parseString);
1010 if (!$this->nextPart($parseString, '^(,)')) {
1011 return $this->parseError('No comma found as expected in parseWhereClause()', $parseString);
1012 }
1013 if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]\\*._]+)[[:space:]]*')) {
1014 // Parse field name into field and table:
1015 $tableField = explode('.', $fieldName, 2);
1016 if (count($tableField) === 2) {
1017 $stack[$level][$pnt[$level]]['func']['table'] = $tableField[0];
1018 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[1];
1019 } else {
1020 $stack[$level][$pnt[$level]]['func']['table'] = '';
1021 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[0];
1022 }
1023 } else {
1024 return $this->parseError('No field name found as expected in parseWhereClause()', $parseString);
1025 }
1026 if ($this->nextPart($parseString, '^(,)')) {
1027 $stack[$level][$pnt[$level]]['func']['pos'] = $this->getValue($parseString);
1028 }
1029 if (!$this->nextPart($parseString, '^([)])')) {
1030 return $this->parseError('No ) parenthesis at end of function', $parseString);
1031 }
1032 } elseif (preg_match('/^IFNULL[[:space:]]*[(]/i', $parseString)) {
1033 $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(IFNULL)[[:space:]]*');
1034 $parseString = trim(substr($parseString, 1));
1035 // Strip of "("
1036 if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]\\*._]+)[[:space:]]*')) {
1037 // Parse field name into field and table:
1038 $tableField = explode('.', $fieldName, 2);
1039 if (count($tableField) === 2) {
1040 $stack[$level][$pnt[$level]]['func']['table'] = $tableField[0];
1041 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[1];
1042 } else {
1043 $stack[$level][$pnt[$level]]['func']['table'] = '';
1044 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[0];
1045 }
1046 } else {
1047 return $this->parseError('No field name found as expected in parseWhereClause()', $parseString);
1048 }
1049 if ($this->nextPart($parseString, '^(,)')) {
1050 $stack[$level][$pnt[$level]]['func']['default'] = $this->getValue($parseString);
1051 }
1052 if (!$this->nextPart($parseString, '^([)])')) {
1053 return $this->parseError('No ) parenthesis at end of function', $parseString);
1054 }
1055 } elseif (preg_match('/^FIND_IN_SET[[:space:]]*[(]/i', $parseString)) {
1056 $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(FIND_IN_SET)[[:space:]]*');
1057 // Strip of "("
1058 $parseString = trim(substr($parseString, 1));
1059 if ($str = $this->getValue($parseString)) {
1060 $stack[$level][$pnt[$level]]['func']['str'] = $str;
1061 if ($fieldName = $this->nextPart($parseString, '^,[[:space:]]*([[:alnum:]._]+)[[:space:]]*', TRUE)) {
1062 // Parse field name into field and table:
1063 $tableField = explode('.', $fieldName, 2);
1064 if (count($tableField) === 2) {
1065 $stack[$level][$pnt[$level]]['func']['table'] = $tableField[0];
1066 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[1];
1067 } else {
1068 $stack[$level][$pnt[$level]]['func']['table'] = '';
1069 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[0];
1070 }
1071 } else {
1072 return $this->parseError('No field name found as expected in parseWhereClause()', $parseString);
1073 }
1074 if (!$this->nextPart($parseString, '^([)])')) {
1075 return $this->parseError('No ) parenthesis at end of function', $parseString);
1076 }
1077 } else {
1078 return $this->parseError('No item to look for found as expected in parseWhereClause()', $parseString);
1079 }
1080 } else {
1081 // Support calculated value only for:
1082 // - "&" (boolean AND)
1083 // - "+" (addition)
1084 // - "-" (substraction)
1085 // - "*" (multiplication)
1086 // - "/" (division)
1087 // - "%" (modulo)
1088 $calcOperators = '&|\\+|-|\\*|\\/|%';
1089 // Fieldname:
1090 if (($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)([[:space:]]+|' . $calcOperators . '|<=|>=|<|>|=|!=|IS)')) !== '') {
1091 // Parse field name into field and table:
1092 $tableField = explode('.', $fieldName, 2);
1093 if (count($tableField) === 2) {
1094 $stack[$level][$pnt[$level]]['table'] = $tableField[0];
1095 $stack[$level][$pnt[$level]]['field'] = $tableField[1];
1096 } else {
1097 $stack[$level][$pnt[$level]]['table'] = '';
1098 $stack[$level][$pnt[$level]]['field'] = $tableField[0];
1099 }
1100 } else {
1101 return $this->parseError('No field name found as expected in parseWhereClause()', $parseString);
1102 }
1103 // See if the value is calculated:
1104 $stack[$level][$pnt[$level]]['calc'] = $this->nextPart($parseString, '^(' . $calcOperators . ')');
1105 if (strlen($stack[$level][$pnt[$level]]['calc'])) {
1106 // Finding value for calculation:
1107 $calc_value = $this->getValue($parseString);
1108 $stack[$level][$pnt[$level]]['calc_value'] = $calc_value;
1109 if (count($calc_value) === 1 && is_string($calc_value[0])) {
1110 // Value is a field, store it to allow DBAL to post-process it (quoting, remapping)
1111 $tableField = explode('.', $calc_value[0], 2);
1112 if (count($tableField) === 2) {
1113 $stack[$level][$pnt[$level]]['calc_table'] = $tableField[0];
1114 $stack[$level][$pnt[$level]]['calc_field'] = $tableField[1];
1115 } else {
1116 $stack[$level][$pnt[$level]]['calc_table'] = '';
1117 $stack[$level][$pnt[$level]]['calc_field'] = $tableField[0];
1118 }
1119 }
1120 }
1121 }
1122 $stack[$level][$pnt[$level]]['comparator'] = $this->nextPart($parseString, '^(' . implode('|', self::$comparatorPatterns) . ')');
1123 if (strlen($stack[$level][$pnt[$level]]['comparator'])) {
1124 if (preg_match('/^CONCAT[[:space:]]*\\(/', $parseString)) {
1125 $this->nextPart($parseString, '^(CONCAT[[:space:]]?[(])');
1126 $values = array(
1127 'operator' => 'CONCAT',
1128 'args' => array()
1129 );
1130 $cnt = 0;
1131 while ($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)')) {
1132 // Parse field name into field and table:
1133 $tableField = explode('.', $fieldName, 2);
1134 if (count($tableField) === 2) {
1135 $values['args'][$cnt]['table'] = $tableField[0];
1136 $values['args'][$cnt]['field'] = $tableField[1];
1137 } else {
1138 $values['args'][$cnt]['table'] = '';
1139 $values['args'][$cnt]['field'] = $tableField[0];
1140 }
1141 // Looking for comma:
1142 $this->nextPart($parseString, '^(,)');
1143 $cnt++;
1144 }
1145 // Look for ending parenthesis:
1146 $this->nextPart($parseString, '([)])');
1147 $stack[$level][$pnt[$level]]['value'] = $values;
1148 } else {
1149 if (\TYPO3\CMS\Core\Utility\GeneralUtility::inList('IN,NOT IN', $stack[$level][$pnt[$level]]['comparator']) && preg_match('/^[(][[:space:]]*SELECT[[:space:]]+/', $parseString)) {
1150 $this->nextPart($parseString, '^([(])');
1151 $stack[$level][$pnt[$level]]['subquery'] = $this->parseSELECT($parseString, $parameterReferences);
1152 // Seek to new position in parseString after parsing of the subquery
1153 $parseString = $stack[$level][$pnt[$level]]['subquery']['parseString'];
1154 unset($stack[$level][$pnt[$level]]['subquery']['parseString']);
1155 if (!$this->nextPart($parseString, '^([)])')) {
1156 return 'No ) parenthesis at end of subquery';
1157 }
1158 } else {
1159 if (\TYPO3\CMS\Core\Utility\GeneralUtility::inList('BETWEEN,NOT BETWEEN', $stack[$level][$pnt[$level]]['comparator'])) {
1160 $stack[$level][$pnt[$level]]['values'] = array();
1161 $stack[$level][$pnt[$level]]['values'][0] = $this->getValue($parseString);
1162 if (!$this->nextPart($parseString, '^(AND)')) {
1163 return $this->parseError('No AND operator found as expected in parseWhereClause()', $parseString);
1164 }
1165 $stack[$level][$pnt[$level]]['values'][1] = $this->getValue($parseString);
1166 } else {
1167 // Finding value for comparator:
1168 $stack[$level][$pnt[$level]]['value'] = &$this->getValueOrParameter($parseString, $stack[$level][$pnt[$level]]['comparator'], '', $parameterReferences);
1169 if ($this->parse_error) {
1170 return $this->parse_error;
1171 }
1172 }
1173 }
1174 }
1175 }
1176 }
1177 // Finished, increase pointer:
1178 $pnt[$level]++;
1179 // Checking if we are back to level 0 and we should still decrease level,
1180 // meaning we were probably parsing as subquery and should return here:
1181 if ($level === 0 && preg_match('/^[)]/', $parseString)) {
1182 // Return the stacks lowest level:
1183 return $stack[0];
1184 }
1185 // Checking if we are back to level 0 and we should still decrease level,
1186 // meaning we were probably parsing a subquery and should return here:
1187 if ($level === 0 && preg_match('/^[)]/', $parseString)) {
1188 // Return the stacks lowest level:
1189 return $stack[0];
1190 }
1191 // Checking if the current level is ended, in that case do stack management:
1192 while ($this->nextPart($parseString, '^([)])')) {
1193 $level--;
1194 // Decrease level:
1195 // Copy stack
1196 $stack[$level][$pnt[$level]]['sub'] = $stack[$level + 1];
1197 // Increase pointer of the new level
1198 $pnt[$level]++;
1199 // Make recursivity check:
1200 $loopExit++;
1201 if ($loopExit > 500) {
1202 return $this->parseError('More than 500 loops (in search for exit parenthesis), exiting prematurely in parseWhereClause()...', $parseString);
1203 }
1204 }
1205 // Detecting the operator for the next level:
1206 $op = $this->nextPart($parseString, '^(AND[[:space:]]+NOT|&&[[:space:]]+NOT|OR[[:space:]]+NOT|OR[[:space:]]+NOT|\\|\\|[[:space:]]+NOT|AND|&&|OR|\\|\\|)(\\(|[[:space:]]+)');
1207 if ($op) {
1208 // Normalize boolean operator
1209 $op = str_replace(array('&&', '||'), array('AND', 'OR'), $op);
1210 $stack[$level][$pnt[$level]]['operator'] = $op;
1211 } elseif (strlen($parseString)) {
1212 // Looking for stop-keywords:
1213 if ($stopRegex && ($this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))) {
1214 $this->lastStopKeyWord = $this->normalizeKeyword($this->lastStopKeyWord);
1215 return $stack[0];
1216 } else {
1217 return $this->parseError('No operator, but parsing not finished in parseWhereClause().', $parseString);
1218 }
1219 }
1220 }
1221 // Make recursivity check:
1222 $loopExit++;
1223 if ($loopExit > 500) {
1224 return $this->parseError('More than 500 loops, exiting prematurely in parseWhereClause()...', $parseString);
1225 }
1226 }
1227 // Return the stacks lowest level:
1228 return $stack[0];
1229 }
1230
1231 /**
1232 * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array.
1233 * The success of this parsing determines if that part of the query is supported by TYPO3.
1234 *
1235 * @param string $parseString WHERE clause to parse. NOTICE: passed by reference!
1236 * @param string $stopRegex Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)'
1237 * @return mixed If successful parsing, returns an array, otherwise an error string.
1238 */
1239 public function parseFieldDef(&$parseString, $stopRegex = '') {
1240 // Prepare variables:
1241 $parseString = $this->trimSQL($parseString);
1242 $this->lastStopKeyWord = '';
1243 $this->parse_error = '';
1244 $result = array();
1245 // Field type:
1246 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:],]+|\\()')) {
1247 // Looking for value:
1248 if ($parseString[0] === '(') {
1249 $parseString = substr($parseString, 1);
1250 if ($result['value'] = $this->nextPart($parseString, '^([^)]*)')) {
1251 $parseString = ltrim(substr($parseString, 1));
1252 } else {
1253 return $this->parseError('No end-parenthesis for value found in parseFieldDef()!', $parseString);
1254 }
1255 }
1256 // Looking for keywords
1257 while ($keyword = $this->nextPart($parseString, '^(DEFAULT|NOT[[:space:]]+NULL|AUTO_INCREMENT|UNSIGNED)([[:space:]]+|,|\\))')) {
1258 $keywordCmp = $this->normalizeKeyword($keyword);
1259 $result['featureIndex'][$keywordCmp]['keyword'] = $keyword;
1260 switch ($keywordCmp) {
1261 case 'DEFAULT':
1262 $result['featureIndex'][$keywordCmp]['value'] = $this->getValue($parseString);
1263 break;
1264 }
1265 }
1266 } else {
1267 return $this->parseError('Field type unknown in parseFieldDef()!', $parseString);
1268 }
1269 return $result;
1270 }
1271
1272 /************************************
1273 *
1274 * Parsing: Helper functions
1275 *
1276 ************************************/
1277 /**
1278 * Strips off a part of the parseString and returns the matching part.
1279 * Helper function for the parsing methods.
1280 *
1281 * @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.
1282 * @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())
1283 * @param bool $trimAll If set the full match of the regex is stripped of the beginning of the string!
1284 * @return string The value of the first parenthesis level of the REGEX.
1285 */
1286 protected function nextPart(&$parseString, $regex, $trimAll = FALSE) {
1287 $reg = array();
1288 // Adding space char because [[:space:]]+ is often a requirement in regex's
1289 if (preg_match('/' . $regex . '/i', $parseString . ' ', $reg)) {
1290 $parseString = ltrim(substr($parseString, strlen($reg[$trimAll ? 0 : 1])));
1291 return $reg[1];
1292 }
1293 // No match found
1294 return '';
1295 }
1296
1297 /**
1298 * Finds value or either named (:name) or question mark (?) parameter markers at the beginning
1299 * of $parseString, returns result and strips it of parseString.
1300 * This method returns a pointer to the parameter or value that was found. In case of a parameter
1301 * the pointer is a reference to the corresponding item in array $parameterReferences.
1302 *
1303 * @param string $parseString The parseString
1304 * @param string $comparator The comparator used before.
1305 * @param string $mode The mode, e.g., "INDEX
1306 * @param mixed The value (string/integer) or parameter (:name/?). Otherwise an array with error message in first key (0)
1307 */
1308 protected function &getValueOrParameter(&$parseString, $comparator = '', $mode = '', array &$parameterReferences = array()) {
1309 $parameter = $this->nextPart($parseString, '^(\\:[[:alnum:]_]+|\\?)');
1310 if ($parameter === '?') {
1311 if (!isset($parameterReferences['?'])) {
1312 $parameterReferences['?'] = array();
1313 }
1314 $value = array('?');
1315 $parameterReferences['?'][] = &$value;
1316 } elseif ($parameter !== '') {
1317 // named parameter
1318 if (isset($parameterReferences[$parameter])) {
1319 // Use the same reference as last time we encountered this parameter
1320 $value = &$parameterReferences[$parameter];
1321 } else {
1322 $value = array($parameter);
1323 $parameterReferences[$parameter] = &$value;
1324 }
1325 } else {
1326 $value = $this->getValue($parseString, $comparator, $mode);
1327 }
1328 return $value;
1329 }
1330
1331 /**
1332 * Finds value in beginning of $parseString, returns result and strips it of parseString
1333 *
1334 * @param string $parseString The parseString, eg. "(0,1,2,3) ..." or "('asdf','qwer') ..." or "1234 ..." or "'My string value here' ...
1335 * @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)
1336 * @param string $mode The mode, eg. "INDEX
1337 * @return mixed The value (string/integer). Otherwise an array with error message in first key (0)
1338 */
1339 protected function getValue(&$parseString, $comparator = '', $mode = '') {
1340 $value = '';
1341 if (\TYPO3\CMS\Core\Utility\GeneralUtility::inList('NOTIN,IN,_LIST', strtoupper(str_replace(array(' ', LF, CR, TAB), '', $comparator)))) {
1342 // List of values:
1343 if ($this->nextPart($parseString, '^([(])')) {
1344 $listValues = array();
1345 $comma = ',';
1346 while ($comma === ',') {
1347 $listValues[] = $this->getValue($parseString);
1348 if ($mode === 'INDEX') {
1349 // Remove any length restriction on INDEX definition
1350 $this->nextPart($parseString, '^([(]\\d+[)])');
1351 }
1352 $comma = $this->nextPart($parseString, '^([,])');
1353 }
1354 $out = $this->nextPart($parseString, '^([)])');
1355 if ($out) {
1356 if ($comparator === '_LIST') {
1357 $kVals = array();
1358 foreach ($listValues as $vArr) {
1359 $kVals[] = $vArr[0];
1360 }
1361 return $kVals;
1362 } else {
1363 return $listValues;
1364 }
1365 } else {
1366 return array($this->parseError('No ) parenthesis in list', $parseString));
1367 }
1368 } else {
1369 return array($this->parseError('No ( parenthesis starting the list', $parseString));
1370 }
1371 } else {
1372 // Just plain string value, in quotes or not:
1373 // Quote?
1374 $firstChar = $parseString[0];
1375 switch ($firstChar) {
1376 case '"':
1377 $value = array($this->getValueInQuotes($parseString, '"'), '"');
1378 break;
1379 case '\'':
1380 $value = array($this->getValueInQuotes($parseString, '\''), '\'');
1381 break;
1382 default:
1383 $reg = array();
1384 if (preg_match('/^([[:alnum:]._-]+)/i', $parseString, $reg)) {
1385 $parseString = ltrim(substr($parseString, strlen($reg[0])));
1386 $value = array($reg[1]);
1387 }
1388 }
1389 }
1390 return $value;
1391 }
1392
1393 /**
1394 * Get value in quotes from $parseString.
1395 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1396 *
1397 * @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.
1398 * @param string $quote The quote used; input either " or '
1399 * @return string The value, passed through stripslashes() !
1400 */
1401 protected function getValueInQuotes(&$parseString, $quote) {
1402 $parts = explode($quote, substr($parseString, 1));
1403 $buffer = '';
1404 foreach ($parts as $k => $v) {
1405 $buffer .= $v;
1406 $reg = array();
1407 preg_match('/\\\\$/', $v, $reg);
1408 if ($reg and strlen($reg[0]) % 2) {
1409 $buffer .= $quote;
1410 } else {
1411 $parseString = ltrim(substr($parseString, strlen($buffer) + 2));
1412 return $this->parseStripslashes($buffer);
1413 }
1414 }
1415 }
1416
1417 /**
1418 * Strip slashes function used for parsing
1419 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1420 *
1421 * @param string $str Input string
1422 * @return string Output string
1423 */
1424 protected function parseStripslashes($str) {
1425 $search = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
1426 $replace = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
1427
1428 return str_replace($search, $replace, $str);
1429 }
1430
1431 /**
1432 * Add slashes function used for compiling queries
1433 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1434 *
1435 * @param string $str Input string
1436 * @return string Output string
1437 */
1438 protected function compileAddslashes($str) {
1439 $search = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
1440 $replace = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
1441
1442 return str_replace($search, $replace, $str);
1443 }
1444
1445 /**
1446 * Setting the internal error message value, $this->parse_error and returns that value.
1447 *
1448 * @param string $msg Input error message
1449 * @param string $restQuery Remaining query to parse.
1450 * @return string Error message.
1451 */
1452 protected function parseError($msg, $restQuery) {
1453 $this->parse_error = 'SQL engine parse ERROR: ' . $msg . ': near "' . substr($restQuery, 0, 50) . '"';
1454 return $this->parse_error;
1455 }
1456
1457 /**
1458 * Trimming SQL as preparation for parsing.
1459 * ";" in the end is stripped off.
1460 * White space is trimmed away around the value
1461 * A single space-char is added in the end
1462 *
1463 * @param string $str Input string
1464 * @return string Output string
1465 */
1466 protected function trimSQL($str) {
1467 return rtrim(rtrim(trim($str), ';')) . ' ';
1468 }
1469
1470 /*************************
1471 *
1472 * Compiling queries
1473 *
1474 *************************/
1475 /**
1476 * Compiles an SQL query from components
1477 *
1478 * @param array $components Array of SQL query components
1479 * @return string SQL query
1480 * @see parseSQL()
1481 */
1482 public function compileSQL($components) {
1483 switch ($components['type']) {
1484 case 'SELECT':
1485 $query = $this->compileSELECT($components);
1486 break;
1487 case 'UPDATE':
1488 $query = $this->compileUPDATE($components);
1489 break;
1490 case 'INSERT':
1491 $query = $this->compileINSERT($components);
1492 break;
1493 case 'DELETE':
1494 $query = $this->compileDELETE($components);
1495 break;
1496 case 'EXPLAIN':
1497 $query = 'EXPLAIN ' . $this->compileSELECT($components);
1498 break;
1499 case 'DROPTABLE':
1500 $query = 'DROP TABLE' . ($components['ifExists'] ? ' IF EXISTS' : '') . ' ' . $components['TABLE'];
1501 break;
1502 case 'CREATETABLE':
1503 $query = $this->compileCREATETABLE($components);
1504 break;
1505 case 'ALTERTABLE':
1506 $query = $this->compileALTERTABLE($components);
1507 break;
1508 case 'TRUNCATETABLE':
1509 $query = $this->compileTRUNCATETABLE($components);
1510 break;
1511 }
1512 return $query;
1513 }
1514
1515 /**
1516 * Compiles a SELECT statement from components array
1517 *
1518 * @param array $components Array of SQL query components
1519 * @return string SQL SELECT query
1520 * @see parseSELECT()
1521 */
1522 protected function compileSELECT($components) {
1523 // Initialize:
1524 $where = $this->compileWhereClause($components['WHERE']);
1525 $groupBy = $this->compileFieldList($components['GROUPBY']);
1526 $orderBy = $this->compileFieldList($components['ORDERBY']);
1527 $limit = $components['LIMIT'];
1528 // Make query:
1529 $query = 'SELECT ' . ($components['STRAIGHT_JOIN'] ?: '') . ' ' .
1530 $this->compileFieldList($components['SELECT']) .
1531 ' FROM ' . $this->compileFromTables($components['FROM']) . (strlen($where) ?
1532 ' WHERE ' . $where : '') . (strlen($groupBy) ?
1533 ' GROUP BY ' . $groupBy : '') . (strlen($orderBy) ?
1534 ' ORDER BY ' . $orderBy : '') . (strlen($limit) ?
1535 ' LIMIT ' . $limit : '');
1536 return $query;
1537 }
1538
1539 /**
1540 * Compiles an UPDATE statement from components array
1541 *
1542 * @param array $components Array of SQL query components
1543 * @return string SQL UPDATE query
1544 * @see parseUPDATE()
1545 */
1546 protected function compileUPDATE($components) {
1547 // Where clause:
1548 $where = $this->compileWhereClause($components['WHERE']);
1549 // Fields
1550 $fields = array();
1551 foreach ($components['FIELDS'] as $fN => $fV) {
1552 $fields[] = $fN . '=' . $fV[1] . $this->compileAddslashes($fV[0]) . $fV[1];
1553 }
1554 // Make query:
1555 $query = 'UPDATE ' . $components['TABLE'] . ' SET ' . implode(',', $fields) .
1556 (strlen($where) ? ' WHERE ' . $where : '');
1557
1558 return $query;
1559 }
1560
1561 /**
1562 * Compiles an INSERT statement from components array
1563 *
1564 * @param array $components Array of SQL query components
1565 * @return string SQL INSERT query
1566 * @see parseINSERT()
1567 */
1568 protected function compileINSERT($components) {
1569 $values = array();
1570 if (isset($components['VALUES_ONLY']) && is_array($components['VALUES_ONLY'])) {
1571 $valuesComponents = $components['EXTENDED'] === '1' ? $components['VALUES_ONLY'] : array($components['VALUES_ONLY']);
1572 $tableFields = array();
1573 } else {
1574 $valuesComponents = $components['EXTENDED'] === '1' ? $components['FIELDS'] : array($components['FIELDS']);
1575 $tableFields = array_keys($valuesComponents[0]);
1576 }
1577 foreach ($valuesComponents as $valuesComponent) {
1578 $fields = array();
1579 foreach ($valuesComponent as $fV) {
1580 $fields[] = $fV[1] . $this->compileAddslashes($fV[0]) . $fV[1];
1581 }
1582 $values[] = '(' . implode(',', $fields) . ')';
1583 }
1584 // Make query:
1585 $query = 'INSERT INTO ' . $components['TABLE'];
1586 if (count($tableFields)) {
1587 $query .= ' (' . implode(',', $tableFields) . ')';
1588 }
1589 $query .= ' VALUES ' . implode(',', $values);
1590
1591 return $query;
1592 }
1593
1594 /**
1595 * Compiles an DELETE statement from components array
1596 *
1597 * @param array $components Array of SQL query components
1598 * @return string SQL DELETE query
1599 * @see parseDELETE()
1600 */
1601 protected function compileDELETE($components) {
1602 // Where clause:
1603 $where = $this->compileWhereClause($components['WHERE']);
1604 // Make query:
1605 $query = 'DELETE FROM ' . $components['TABLE'] . (strlen($where) ? ' WHERE ' . $where : '');
1606
1607 return $query;
1608 }
1609
1610 /**
1611 * Compiles a CREATE TABLE statement from components array
1612 *
1613 * @param array $components Array of SQL query components
1614 * @return string SQL CREATE TABLE query
1615 * @see parseCREATETABLE()
1616 */
1617 protected function compileCREATETABLE($components) {
1618 // Create fields and keys:
1619 $fieldsKeys = array();
1620 foreach ($components['FIELDS'] as $fN => $fCfg) {
1621 $fieldsKeys[] = $fN . ' ' . $this->compileFieldCfg($fCfg['definition']);
1622 }
1623 foreach ($components['KEYS'] as $kN => $kCfg) {
1624 if ($kN === 'PRIMARYKEY') {
1625 $fieldsKeys[] = 'PRIMARY KEY (' . implode(',', $kCfg) . ')';
1626 } elseif ($kN === 'UNIQUE') {
1627 $key = key($kCfg);
1628 $fields = current($kCfg);
1629 $fieldsKeys[] = 'UNIQUE KEY ' . $key . ' (' . implode(',', $fields) . ')';
1630 } else {
1631 $fieldsKeys[] = 'KEY ' . $kN . ' (' . implode(',', $kCfg) . ')';
1632 }
1633 }
1634 // Make query:
1635 $query = 'CREATE TABLE ' . $components['TABLE'] . ' (' .
1636 implode(',', $fieldsKeys) . ')' .
1637 ($components['engine'] ? ' ENGINE=' . $components['engine'] : '');
1638
1639 return $query;
1640 }
1641
1642 /**
1643 * Compiles an ALTER TABLE statement from components array
1644 *
1645 * @param array $components Array of SQL query components
1646 * @return string SQL ALTER TABLE query
1647 * @see parseALTERTABLE()
1648 */
1649 protected function compileALTERTABLE($components) {
1650 // Make query:
1651 $query = 'ALTER TABLE ' . $components['TABLE'] . ' ' . $components['action'] . ' ' . ($components['FIELD'] ?: $components['KEY']);
1652 // Based on action, add the final part:
1653 switch ($this->normalizeKeyword($components['action'])) {
1654 case 'ADD':
1655 $query .= ' ' . $this->compileFieldCfg($components['definition']);
1656 break;
1657 case 'CHANGE':
1658 $query .= ' ' . $components['newField'] . ' ' . $this->compileFieldCfg($components['definition']);
1659 break;
1660 case 'DROP':
1661 case 'DROPKEY':
1662 break;
1663 case 'ADDKEY':
1664 case 'ADDPRIMARYKEY':
1665 case 'ADDUNIQUE':
1666 $query .= ' (' . implode(',', $components['fields']) . ')';
1667 break;
1668 case 'DEFAULTCHARACTERSET':
1669 $query .= $components['charset'];
1670 break;
1671 case 'ENGINE':
1672 $query .= '= ' . $components['engine'];
1673 break;
1674 }
1675 // Return query
1676 return $query;
1677 }
1678
1679 /**
1680 * Compiles a TRUNCATE TABLE statement from components array
1681 *
1682 * @param array $components Array of SQL query components
1683 * @return string SQL TRUNCATE TABLE query
1684 * @see parseTRUNCATETABLE()
1685 */
1686 protected function compileTRUNCATETABLE(array $components) {
1687 // Make query:
1688 $query = 'TRUNCATE TABLE ' . $components['TABLE'];
1689 // Return query
1690 return $query;
1691 }
1692
1693 /**************************************
1694 *
1695 * Compiling queries, helper functions for parts of queries
1696 *
1697 **************************************/
1698 /**
1699 * Compiles a "SELECT [output] FROM..:" field list based on input array (made with ->parseFieldList())
1700 * Can also compile field lists for ORDER BY and GROUP BY.
1701 *
1702 * @param array $selectFields Array of select fields, (made with ->parseFieldList())
1703 * @param bool $compileComments Whether comments should be compiled
1704 * @return string Select field string
1705 * @see parseFieldList()
1706 */
1707 public function compileFieldList($selectFields, $compileComments = TRUE) {
1708 // Prepare buffer variable:
1709 $fields = '';
1710 // Traverse the selectFields if any:
1711 if (is_array($selectFields)) {
1712 $outputParts = array();
1713 foreach ($selectFields as $k => $v) {
1714 // Detecting type:
1715 switch ($v['type']) {
1716 case 'function':
1717 $outputParts[$k] = $v['function'] . '(' . $v['func_content'] . ')';
1718 break;
1719 case 'flow-control':
1720 if ($v['flow-control']['type'] === 'CASE') {
1721 $outputParts[$k] = $this->compileCaseStatement($v['flow-control']);
1722 }
1723 break;
1724 case 'field':
1725 $outputParts[$k] = ($v['distinct'] ? $v['distinct'] : '') . ($v['table'] ? $v['table'] . '.' : '') . $v['field'];
1726 break;
1727 }
1728 // Alias:
1729 if ($v['as']) {
1730 $outputParts[$k] .= ' ' . $v['as_keyword'] . ' ' . $v['as'];
1731 }
1732 // Specifically for ORDER BY and GROUP BY field lists:
1733 if ($v['sortDir']) {
1734 $outputParts[$k] .= ' ' . $v['sortDir'];
1735 }
1736 }
1737 if ($compileComments && $selectFields[0]['comments']) {
1738 $fields = $selectFields[0]['comments'] . ' ';
1739 }
1740 $fields .= implode(', ', $outputParts);
1741 }
1742 return $fields;
1743 }
1744
1745 /**
1746 * Compiles a CASE ... WHEN flow-control construct based on input array (made with ->parseCaseStatement())
1747 *
1748 * @param array $components Array of case components, (made with ->parseCaseStatement())
1749 * @return string Case when string
1750 * @see parseCaseStatement()
1751 */
1752 protected function compileCaseStatement(array $components) {
1753 $statement = 'CASE';
1754 if (isset($components['case_field'])) {
1755 $statement .= ' ' . $components['case_field'];
1756 } elseif (isset($components['case_value'])) {
1757 $statement .= ' ' . $components['case_value'][1] . $components['case_value'][0] . $components['case_value'][1];
1758 }
1759 foreach ($components['when'] as $when) {
1760 $statement .= ' WHEN ';
1761 $statement .= $this->compileWhereClause($when['when_value']);
1762 $statement .= ' THEN ';
1763 $statement .= $when['then_value'][1] . $when['then_value'][0] . $when['then_value'][1];
1764 }
1765 if (isset($components['else'])) {
1766 $statement .= ' ELSE ';
1767 $statement .= $components['else'][1] . $components['else'][0] . $components['else'][1];
1768 }
1769 $statement .= ' END';
1770 return $statement;
1771 }
1772
1773 /**
1774 * Compiles a "FROM [output] WHERE..:" table list based on input array (made with ->parseFromTables())
1775 *
1776 * @param array $tablesArray Array of table names, (made with ->parseFromTables())
1777 * @return string Table name string
1778 * @see parseFromTables()
1779 */
1780 public function compileFromTables($tablesArray) {
1781 // Prepare buffer variable:
1782 $outputParts = array();
1783 // Traverse the table names:
1784 if (is_array($tablesArray)) {
1785 foreach ($tablesArray as $k => $v) {
1786 // Set table name:
1787 $outputParts[$k] = $v['table'];
1788 // Add alias AS if there:
1789 if ($v['as']) {
1790 $outputParts[$k] .= ' ' . $v['as_keyword'] . ' ' . $v['as'];
1791 }
1792 if (is_array($v['JOIN'])) {
1793 foreach ($v['JOIN'] as $join) {
1794 $outputParts[$k] .= ' ' . $join['type'] . ' ' . $join['withTable'];
1795 // Add alias AS if there:
1796 if (isset($join['as']) && $join['as']) {
1797 $outputParts[$k] .= ' ' . $join['as_keyword'] . ' ' . $join['as'];
1798 }
1799 $outputParts[$k] .= ' ON ';
1800 foreach ($join['ON'] as $condition) {
1801 if ($condition['operator'] !== '') {
1802 $outputParts[$k] .= ' ' . $condition['operator'] . ' ';
1803 }
1804 $outputParts[$k] .= $condition['left']['table'] ? $condition['left']['table'] . '.' : '';
1805 $outputParts[$k] .= $condition['left']['field'];
1806 $outputParts[$k] .= $condition['comparator'];
1807 $outputParts[$k] .= $condition['right']['table'] ? $condition['right']['table'] . '.' : '';
1808 $outputParts[$k] .= $condition['right']['field'];
1809 }
1810 }
1811 }
1812 }
1813 }
1814 // Return imploded buffer:
1815 return implode(', ', $outputParts);
1816 }
1817
1818 /**
1819 * Implodes an array of WHERE clause configuration into a WHERE clause.
1820 *
1821 * @param array $clauseArray WHERE clause configuration
1822 * @return string WHERE clause as string.
1823 * @see explodeWhereClause()
1824 */
1825 public function compileWhereClause($clauseArray) {
1826 // Prepare buffer variable:
1827 $output = '';
1828 // Traverse clause array:
1829 if (is_array($clauseArray)) {
1830 foreach ($clauseArray as $k => $v) {
1831 // Set operator:
1832 $output .= $v['operator'] ? ' ' . $v['operator'] : '';
1833 // Look for sublevel:
1834 if (is_array($v['sub'])) {
1835 $output .= ' (' . trim($this->compileWhereClause($v['sub'])) . ')';
1836 } elseif (isset($v['func']) && $v['func']['type'] === 'EXISTS') {
1837 $output .= ' ' . trim($v['modifier']) . ' EXISTS (' . $this->compileSELECT($v['func']['subquery']) . ')';
1838 } else {
1839 if (isset($v['func']) && $v['func']['type'] === 'LOCATE') {
1840 $output .= ' ' . trim($v['modifier']) . ' LOCATE(';
1841 $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
1842 $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
1843 $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
1844 $output .= ')';
1845 } elseif (isset($v['func']) && $v['func']['type'] === 'IFNULL') {
1846 $output .= ' ' . trim($v['modifier']) . ' IFNULL(';
1847 $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
1848 $output .= ', ' . $v['func']['default'][1] . $this->compileAddslashes($v['func']['default'][0]) . $v['func']['default'][1];
1849 $output .= ')';
1850 } elseif (isset($v['func']) && $v['func']['type'] === 'FIND_IN_SET') {
1851 $output .= ' ' . trim($v['modifier']) . ' FIND_IN_SET(';
1852 $output .= $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1];
1853 $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
1854 $output .= ')';
1855 } else {
1856 // Set field/table with modifying prefix if any:
1857 $output .= ' ' . trim(($v['modifier'] . ' ' . ($v['table'] ? $v['table'] . '.' : '') . $v['field']));
1858 // Set calculation, if any:
1859 if ($v['calc']) {
1860 $output .= $v['calc'] . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
1861 }
1862 }
1863 // Set comparator:
1864 if ($v['comparator']) {
1865 $output .= ' ' . $v['comparator'];
1866 // Detecting value type; list or plain:
1867 if (\TYPO3\CMS\Core\Utility\GeneralUtility::inList('NOTIN,IN', $this->normalizeKeyword($v['comparator']))) {
1868 if (isset($v['subquery'])) {
1869 $output .= ' (' . $this->compileSELECT($v['subquery']) . ')';
1870 } else {
1871 $valueBuffer = array();
1872 foreach ($v['value'] as $realValue) {
1873 $valueBuffer[] = $realValue[1] . $this->compileAddslashes($realValue[0]) . $realValue[1];
1874 }
1875 $output .= ' (' . trim(implode(',', $valueBuffer)) . ')';
1876 }
1877 } else {
1878 if (\TYPO3\CMS\Core\Utility\GeneralUtility::inList('BETWEEN,NOT BETWEEN', $v['comparator'])) {
1879 $lbound = $v['values'][0];
1880 $ubound = $v['values'][1];
1881 $output .= ' ' . $lbound[1] . $this->compileAddslashes($lbound[0]) . $lbound[1];
1882 $output .= ' AND ';
1883 $output .= $ubound[1] . $this->compileAddslashes($ubound[0]) . $ubound[1];
1884 } else {
1885 if (isset($v['value']['operator'])) {
1886 $values = array();
1887 foreach ($v['value']['args'] as $fieldDef) {
1888 $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field'];
1889 }
1890 $output .= ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';
1891 } else {
1892 $output .= ' ' . $v['value'][1] . $this->compileAddslashes($v['value'][0]) . $v['value'][1];
1893 }
1894 }
1895 }
1896 }
1897 }
1898 }
1899 }
1900 // Return output buffer:
1901 return $output;
1902 }
1903
1904 /**
1905 * Compile field definition
1906 *
1907 * @param array $fieldCfg Field definition parts
1908 * @return string Field definition string
1909 */
1910 public function compileFieldCfg($fieldCfg) {
1911 // Set type:
1912 $cfg = $fieldCfg['fieldType'];
1913 // Add value, if any:
1914 if (strlen($fieldCfg['value'])) {
1915 $cfg .= '(' . $fieldCfg['value'] . ')';
1916 }
1917 // Add additional features:
1918 if (is_array($fieldCfg['featureIndex'])) {
1919 foreach ($fieldCfg['featureIndex'] as $featureDef) {
1920 $cfg .= ' ' . $featureDef['keyword'];
1921 // Add value if found:
1922 if (is_array($featureDef['value'])) {
1923 $cfg .= ' ' . $featureDef['value'][1] . $this->compileAddslashes($featureDef['value'][0]) . $featureDef['value'][1];
1924 }
1925 }
1926 }
1927 // Return field definition string:
1928 return $cfg;
1929 }
1930
1931 /*************************
1932 *
1933 * Debugging
1934 *
1935 *************************/
1936 /**
1937 * Check parsability of input SQL part string; Will parse and re-compile after which it is compared
1938 *
1939 * @param string $part Part definition of string; "SELECT" = fieldlist (also ORDER BY and GROUP BY), "FROM" = table list, "WHERE" = Where clause.
1940 * @param string $str SQL string to verify parsability of
1941 * @return mixed Returns array with string 1 and 2 if error, otherwise FALSE
1942 */
1943 public function debug_parseSQLpart($part, $str) {
1944 $retVal = FALSE;
1945 switch ($part) {
1946 case 'SELECT':
1947 $retVal = $this->debug_parseSQLpartCompare($str, $this->compileFieldList($this->parseFieldList($str)));
1948 break;
1949 case 'FROM':
1950 $retVal = $this->debug_parseSQLpartCompare($str, $this->compileFromTables($this->parseFromTables($str)));
1951 break;
1952 case 'WHERE':
1953 $retVal = $this->debug_parseSQLpartCompare($str, $this->compileWhereClause($this->parseWhereClause($str)));
1954 break;
1955 }
1956 return $retVal;
1957 }
1958
1959 /**
1960 * Compare two query strings by stripping away whitespace.
1961 *
1962 * @param string $str SQL String 1
1963 * @param string $newStr SQL string 2
1964 * @param bool $caseInsensitive If TRUE, the strings are compared insensitive to case
1965 * @return mixed Returns array with string 1 and 2 if error, otherwise FALSE
1966 */
1967 public function debug_parseSQLpartCompare($str, $newStr, $caseInsensitive = FALSE) {
1968 if ($caseInsensitive) {
1969 $str1 = strtoupper($str);
1970 $str2 = strtoupper($newStr);
1971 } else {
1972 $str1 = $str;
1973 $str2 = $newStr;
1974 }
1975
1976 // Fixing escaped chars:
1977 $search = array(NUL, LF, CR, SUB);
1978 $replace = array("\x00", "\x0a", "\x0d", "\x1a");
1979 $str1 = str_replace($search, $replace, $str1);
1980 $str2 = str_replace($search, $replace, $str2);
1981
1982 $search = self::$interQueryWhitespaces;
1983 if (str_replace($search, '', $this->trimSQL($str1)) !== str_replace($search, '', $this->trimSQL($str2))) {
1984 return array(
1985 str_replace($search, ' ', $str),
1986 str_replace($search, ' ', $newStr),
1987 );
1988 }
1989 }
1990
1991 /**
1992 * Normalizes the keyword by removing any separator and changing to uppercase
1993 *
1994 * @param string $keyword The keyword being normalized
1995 * @return string
1996 */
1997 protected function normalizeKeyword($keyword) {
1998 return strtoupper(str_replace(self::$interQueryWhitespaces, '', $keyword));
1999 }
2000 }