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