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