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