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