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