projects / Packages / TYPO3.CMS.git / blob
? search:


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