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