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