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