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