Cleanup #12440: Copy DBAL-only SQL parser methods from t3lib_sqlparser to ux_t3lib_sq...
[Packages/TYPO3.CMS.git] / t3lib / class.t3lib_sqlparser.php
1 <?php
2 /***************************************************************
3 * Copyright notice
4 *
5 * (c) 2004-2009 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)[[:space:]]+');
138 $keyword = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$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 default:
178 $result = $this->parseError('"'.$keyword.'" is not a keyword',$parseString);
179 break;
180 }
181
182 return $result;
183 }
184
185 /**
186 * Parsing SELECT query
187 *
188 * @param string SQL string with SELECT query to parse
189 * @return mixed Returns array with components of SELECT query on success, otherwise an error message string.
190 * @see compileSELECT()
191 */
192 protected function parseSELECT($parseString) {
193
194 // Removing SELECT:
195 $parseString = $this->trimSQL($parseString);
196 $parseString = ltrim(substr($parseString,6));
197
198 // Init output variable:
199 $result = array();
200 $result['type'] = 'SELECT';
201
202 // Looking for STRAIGHT_JOIN keyword:
203 $result['STRAIGHT_JOIN'] = $this->nextPart($parseString, '^(STRAIGHT_JOIN)[[:space:]]+');
204
205 // Select fields:
206 $result['SELECT'] = $this->parseFieldList($parseString, '^(FROM)[[:space:]]+');
207 if ($this->parse_error) { return $this->parse_error; }
208
209 // Continue if string is not ended:
210 if ($parseString) {
211
212 // Get table list:
213 $result['FROM'] = $this->parseFromTables($parseString, '^(WHERE)[[:space:]]+');
214 if ($this->parse_error) { return $this->parse_error; }
215
216 // If there are more than just the tables (a WHERE clause that would be...)
217 if ($parseString) {
218
219 // Get WHERE clause:
220 $result['WHERE'] = $this->parseWhereClause($parseString, '^(GROUP[[:space:]]+BY|ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
221 if ($this->parse_error) { return $this->parse_error; }
222
223 // If the WHERE clause parsing was stopped by GROUP BY, ORDER BY or LIMIT, then proceed with parsing:
224 if ($this->lastStopKeyWord) {
225
226 // GROUP BY parsing:
227 if ($this->lastStopKeyWord == 'GROUPBY') {
228 $result['GROUPBY'] = $this->parseFieldList($parseString, '^(ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
229 if ($this->parse_error) { return $this->parse_error; }
230 }
231
232 // ORDER BY parsing:
233 if ($this->lastStopKeyWord == 'ORDERBY') {
234 $result['ORDERBY'] = $this->parseFieldList($parseString, '^(LIMIT)[[:space:]]+');
235 if ($this->parse_error) { return $this->parse_error; }
236 }
237
238 // LIMIT parsing:
239 if ($this->lastStopKeyWord == 'LIMIT') {
240 if (preg_match('/^([0-9]+|[0-9]+[[:space:]]*,[[:space:]]*[0-9]+)$/',trim($parseString))) {
241 $result['LIMIT'] = $parseString;
242 } else {
243 return $this->parseError('No value for limit!',$parseString);
244 }
245 }
246 }
247 }
248 } else return $this->parseError('No table to select from!',$parseString);
249
250 // Return result:
251 return $result;
252 }
253
254 /**
255 * Parsing UPDATE query
256 *
257 * @param string SQL string with UPDATE query to parse
258 * @return mixed Returns array with components of UPDATE query on success, otherwise an error message string.
259 * @see compileUPDATE()
260 */
261 protected function parseUPDATE($parseString) {
262
263 // Removing UPDATE
264 $parseString = $this->trimSQL($parseString);
265 $parseString = ltrim(substr($parseString,6));
266
267 // Init output variable:
268 $result = array();
269 $result['type'] = 'UPDATE';
270
271 // Get table:
272 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
273
274 // Continue if string is not ended:
275 if ($result['TABLE']) {
276 if ($parseString && $this->nextPart($parseString, '^(SET)[[:space:]]+')) {
277
278 $comma = TRUE;
279
280 // Get field/value pairs:
281 while($comma) {
282 if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]*=')) {
283 $this->nextPart($parseString,'^(=)'); // Strip of "=" sign.
284 $value = $this->getValue($parseString);
285 $result['FIELDS'][$fieldName] = $value;
286 } else return $this->parseError('No fieldname found',$parseString);
287
288 $comma = $this->nextPart($parseString,'^(,)');
289 }
290
291 // WHERE
292 if ($this->nextPart($parseString,'^(WHERE)')) {
293 $result['WHERE'] = $this->parseWhereClause($parseString);
294 if ($this->parse_error) { return $this->parse_error; }
295 }
296 } else return $this->parseError('Query missing SET...',$parseString);
297 } else return $this->parseError('No table found!',$parseString);
298
299 // Should be no more content now:
300 if ($parseString) {
301 return $this->parseError('Still content in clause after parsing!',$parseString);
302 }
303
304 // Return result:
305 return $result;
306 }
307
308 /**
309 * Parsing INSERT query
310 *
311 * @param string SQL string with INSERT query to parse
312 * @return mixed Returns array with components of INSERT query on success, otherwise an error message string.
313 * @see compileINSERT()
314 */
315 protected function parseINSERT($parseString) {
316
317 // Removing INSERT
318 $parseString = $this->trimSQL($parseString);
319 $parseString = ltrim(substr(ltrim(substr($parseString,6)),4));
320
321 // Init output variable:
322 $result = array();
323 $result['type'] = 'INSERT';
324
325 // Get table:
326 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()');
327
328 if ($result['TABLE']) {
329
330 if ($this->nextPart($parseString,'^(VALUES)[[:space:]]+')) { // In this case there are no field names mentioned in the SQL!
331 // Get values/fieldnames (depending...)
332 $result['VALUES_ONLY'] = $this->getValue($parseString,'IN');
333 if ($this->parse_error) { return $this->parse_error; }
334 } else { // There are apparently fieldnames listed:
335 $fieldNames = $this->getValue($parseString,'_LIST');
336 if ($this->parse_error) { return $this->parse_error; }
337
338 if ($this->nextPart($parseString,'^(VALUES)[[:space:]]+')) { // "VALUES" keyword binds the fieldnames to values:
339
340 $values = $this->getValue($parseString,'IN'); // Using the "getValue" function to get the field list...
341 if ($this->parse_error) { return $this->parse_error; }
342
343 foreach($fieldNames as $k => $fN) {
344 if (preg_match('/^[[:alnum:]_]+$/',$fN)) {
345 if (isset($values[$k])) {
346 if (!isset($result['FIELDS'][$fN])) {
347 $result['FIELDS'][$fN] = $values[$k];
348 } else return $this->parseError('Fieldname ("'.$fN.'") already found in list!',$parseString);
349 } else return $this->parseError('No value set!',$parseString);
350 } else return $this->parseError('Invalid fieldname ("'.$fN.'")',$parseString);
351 }
352 if (isset($values[$k+1])) {
353 return $this->parseError('Too many values in list!',$parseString);
354 }
355 } else return $this->parseError('VALUES keyword expected',$parseString);
356 }
357 } else return $this->parseError('No table found!',$parseString);
358
359 // Should be no more content now:
360 if ($parseString) {
361 return $this->parseError('Still content after parsing!',$parseString);
362 }
363
364 // Return result
365 return $result;
366 }
367
368 /**
369 * Parsing DELETE query
370 *
371 * @param string SQL string with DELETE query to parse
372 * @return mixed Returns array with components of DELETE query on success, otherwise an error message string.
373 * @see compileDELETE()
374 */
375 protected function parseDELETE($parseString) {
376
377 // Removing DELETE
378 $parseString = $this->trimSQL($parseString);
379 $parseString = ltrim(substr(ltrim(substr($parseString,6)),4));
380
381 // Init output variable:
382 $result = array();
383 $result['type'] = 'DELETE';
384
385 // Get table:
386 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
387
388 if ($result['TABLE']) {
389
390 // WHERE
391 if ($this->nextPart($parseString,'^(WHERE)')) {
392 $result['WHERE'] = $this->parseWhereClause($parseString);
393 if ($this->parse_error) { return $this->parse_error; }
394 }
395 } else return $this->parseError('No table found!',$parseString);
396
397 // Should be no more content now:
398 if ($parseString) {
399 return $this->parseError('Still content in clause after parsing!',$parseString);
400 }
401
402 // Return result:
403 return $result;
404 }
405
406 /**
407 * Parsing EXPLAIN query
408 *
409 * @param string SQL string with EXPLAIN query to parse
410 * @return mixed Returns array with components of EXPLAIN query on success, otherwise an error message string.
411 * @see parseSELECT()
412 */
413 protected function parseEXPLAIN($parseString) {
414
415 // Removing EXPLAIN
416 $parseString = $this->trimSQL($parseString);
417 $parseString = ltrim(substr($parseString,6));
418
419 // Init output variable:
420 $result = $this->parseSELECT($parseString);
421 if (is_array($result)) {
422 $result['type'] = 'EXPLAIN';
423 }
424
425 return $result;
426 }
427
428 /**
429 * Parsing CREATE TABLE query
430 *
431 * @param string SQL string starting with CREATE TABLE
432 * @return mixed Returns array with components of CREATE TABLE query on success, otherwise an error message string.
433 * @see compileCREATETABLE()
434 */
435 protected function parseCREATETABLE($parseString) {
436
437 // Removing CREATE TABLE
438 $parseString = $this->trimSQL($parseString);
439 $parseString = ltrim(substr(ltrim(substr($parseString,6)),5));
440
441 // Init output variable:
442 $result = array();
443 $result['type'] = 'CREATETABLE';
444
445 // Get table:
446 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*\(',TRUE);
447
448 if ($result['TABLE']) {
449
450 // While the parseString is not yet empty:
451 while(strlen($parseString)>0) {
452 if ($key = $this->nextPart($parseString, '^(KEY|PRIMARY KEY|UNIQUE KEY|UNIQUE)([[:space:]]+|\()')) { // Getting key
453 $key = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$key));
454
455 switch($key) {
456 case 'PRIMARYKEY':
457 $result['KEYS']['PRIMARYKEY'] = $this->getValue($parseString,'_LIST');
458 if ($this->parse_error) { return $this->parse_error; }
459 break;
460 case 'UNIQUE':
461 case 'UNIQUEKEY':
462 if ($keyName = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()')) {
463 $result['KEYS']['UNIQUE'] = array($keyName => $this->getValue($parseString,'_LIST'));
464 if ($this->parse_error) { return $this->parse_error; }
465 } else return $this->parseError('No keyname found',$parseString);
466 break;
467 case 'KEY':
468 if ($keyName = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()')) {
469 $result['KEYS'][$keyName] = $this->getValue($parseString,'_LIST');
470 if ($this->parse_error) { return $this->parse_error; }
471 } else return $this->parseError('No keyname found',$parseString);
472 break;
473 }
474 } elseif ($fieldName = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) { // Getting field:
475 $result['FIELDS'][$fieldName]['definition'] = $this->parseFieldDef($parseString);
476 if ($this->parse_error) { return $this->parse_error; }
477 }
478
479 // Finding delimiter:
480 $delim = $this->nextPart($parseString, '^(,|\))');
481 if (!$delim) {
482 return $this->parseError('No delimiter found',$parseString);
483 } elseif ($delim==')') {
484 break;
485 }
486 }
487
488 // Finding what is after the table definition - table type in MySQL
489 if ($delim==')') {
490 if ($this->nextPart($parseString, '^((ENGINE|TYPE)[[:space:]]*=)')) {
491 $result['tableType'] = $parseString;
492 $parseString = '';
493 }
494 } else return $this->parseError('No fieldname found!',$parseString);
495
496 // Getting table type
497 } else return $this->parseError('No table found!',$parseString);
498
499 // Should be no more content now:
500 if ($parseString) {
501 return $this->parseError('Still content in clause after parsing!',$parseString);
502 }
503
504 return $result;
505 }
506
507 /**
508 * Parsing ALTER TABLE query
509 *
510 * @param string SQL string starting with ALTER TABLE
511 * @return mixed Returns array with components of ALTER TABLE query on success, otherwise an error message string.
512 * @see compileALTERTABLE()
513 */
514 protected function parseALTERTABLE($parseString) {
515
516 // Removing ALTER TABLE
517 $parseString = $this->trimSQL($parseString);
518 $parseString = ltrim(substr(ltrim(substr($parseString,5)),5));
519
520 // Init output variable:
521 $result = array();
522 $result['type'] = 'ALTERTABLE';
523
524 // Get table:
525 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
526
527 if ($result['TABLE']) {
528 if ($result['action'] = $this->nextPart($parseString, '^(CHANGE|DROP[[:space:]]+KEY|DROP[[:space:]]+PRIMARY[[:space:]]+KEY|ADD[[:space:]]+KEY|ADD[[:space:]]+PRIMARY[[:space:]]+KEY|DROP|ADD|RENAME)([[:space:]]+|\()')) {
529 $actionKey = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$result['action']));
530
531 // Getting field:
532 if (t3lib_div::inList('ADDPRIMARYKEY,DROPPRIMARYKEY',$actionKey) || $fieldKey = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) {
533
534 switch($actionKey) {
535 case 'ADD':
536 $result['FIELD'] = $fieldKey;
537 $result['definition'] = $this->parseFieldDef($parseString);
538 if ($this->parse_error) { return $this->parse_error; }
539 break;
540 case 'DROP':
541 case 'RENAME':
542 $result['FIELD'] = $fieldKey;
543 break;
544 case 'CHANGE':
545 $result['FIELD'] = $fieldKey;
546 if ($result['newField'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) {
547 $result['definition'] = $this->parseFieldDef($parseString);
548 if ($this->parse_error) { return $this->parse_error; }
549 } else return $this->parseError('No NEW field name found',$parseString);
550 break;
551
552 case 'ADDKEY':
553 case 'ADDPRIMARYKEY':
554 $result['KEY'] = $fieldKey;
555 $result['fields'] = $this->getValue($parseString,'_LIST');
556 if ($this->parse_error) { return $this->parse_error; }
557 break;
558 case 'DROPKEY':
559 $result['KEY'] = $fieldKey;
560 break;
561 case 'DROPPRIMARYKEY':
562 // ??? todo!
563 break;
564 }
565 } else return $this->parseError('No field name found',$parseString);
566 } else return $this->parseError('No action CHANGE, DROP or ADD found!',$parseString);
567 } else return $this->parseError('No table found!',$parseString);
568
569 // Should be no more content now:
570 if ($parseString) {
571 return $this->parseError('Still content in clause after parsing!',$parseString);
572 }
573
574 return $result;
575 }
576
577 /**
578 * Parsing DROP TABLE query
579 *
580 * @param string SQL string starting with DROP TABLE
581 * @return mixed Returns array with components of DROP TABLE query on success, otherwise an error message string.
582 */
583 protected function parseDROPTABLE($parseString) {
584
585 // Removing DROP TABLE
586 $parseString = $this->trimSQL($parseString);
587 $parseString = ltrim(substr(ltrim(substr($parseString,4)),5));
588
589 // Init output variable:
590 $result = array();
591 $result['type'] = 'DROPTABLE';
592
593 // IF EXISTS
594 $result['ifExists'] = $this->nextPart($parseString, '^(IF[[:space:]]+EXISTS[[:space:]]+)');
595
596 // Get table:
597 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
598
599 if ($result['TABLE']) {
600
601 // Should be no more content now:
602 if ($parseString) {
603 return $this->parseError('Still content in clause after parsing!',$parseString);
604 }
605
606 return $result;
607 } else return $this->parseError('No table found!',$parseString);
608 }
609
610 /**
611 * Parsing CREATE DATABASE query
612 *
613 * @param string SQL string starting with CREATE DATABASE
614 * @return mixed Returns array with components of CREATE DATABASE query on success, otherwise an error message string.
615 */
616 protected function parseCREATEDATABASE($parseString) {
617
618 // Removing CREATE DATABASE
619 $parseString = $this->trimSQL($parseString);
620 $parseString = ltrim(substr(ltrim(substr($parseString,6)),8));
621
622 // Init output variable:
623 $result = array();
624 $result['type'] = 'CREATEDATABASE';
625
626 // Get table:
627 $result['DATABASE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
628
629 if ($result['DATABASE']) {
630
631 // Should be no more content now:
632 if ($parseString) {
633 return $this->parseError('Still content in clause after parsing!',$parseString);
634 }
635
636 return $result;
637 } else return $this->parseError('No database found!',$parseString);
638 }
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654 /**************************************
655 *
656 * SQL Parsing, helper functions for parts of queries
657 *
658 **************************************/
659
660 /**
661 * Parsing the fields in the "SELECT [$selectFields] FROM" part of a query into an array.
662 * The output from this function can be compiled back into a field list with ->compileFieldList()
663 * 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!
664 *
665 * @param string The string with fieldnames, eg. "title, uid AS myUid, max(tstamp), count(*)" etc. NOTICE: passed by reference!
666 * @param string Regular expressing to STOP parsing, eg. '^(FROM)([[:space:]]*)'
667 * @return array If successful parsing, returns an array, otherwise an error string.
668 * @see compileFieldList()
669 */
670 public function parseFieldList(&$parseString, $stopRegex = '') {
671
672 $stack = array(); // Contains the parsed content
673
674 if(strlen($parseString)==0) return $stack; // FIXME - should never happen, why does it?
675
676 $pnt = 0; // Pointer to positions in $stack
677 $level = 0; // Indicates the parenthesis level we are at.
678 $loopExit = 0; // Recursivity brake.
679
680 // Prepare variables:
681 $parseString = $this->trimSQL($parseString);
682 $this->lastStopKeyWord = '';
683 $this->parse_error = '';
684
685 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
686 while (strlen($parseString)) {
687
688 // Checking if we are inside / outside parenthesis (in case of a function like count(), max(), min() etc...):
689 if ($level>0) { // Inside parenthesis here (does NOT detect if values in quotes are used, the only token is ")" or "("):
690
691 // Accumulate function content until next () parenthesis:
692 $funcContent = $this->nextPart($parseString,'^([^()]*.)');
693 $stack[$pnt]['func_content.'][] = array(
694 'level' => $level,
695 'func_content' => substr($funcContent,0,-1)
696 );
697 $stack[$pnt]['func_content'].= $funcContent;
698
699 // Detecting ( or )
700 switch(substr($stack[$pnt]['func_content'],-1)) {
701 case '(':
702 $level++;
703 break;
704 case ')':
705 $level--;
706 if (!$level) { // If this was the last parenthesis:
707 $stack[$pnt]['func_content'] = substr($stack[$pnt]['func_content'],0,-1);
708 $parseString = ltrim($parseString); // Remove any whitespace after the parenthesis.
709 }
710 break;
711 }
712 } else { // Outside parenthesis, looking for next field:
713
714 // Looking for a known function (only known functions supported)
715 $func = $this->nextPart($parseString,'^(count|max|min|floor|sum|avg)[[:space:]]*\(');
716 if ($func) {
717 $parseString = trim(substr($parseString,1)); // Strip of "("
718 $stack[$pnt]['type'] = 'function';
719 $stack[$pnt]['function'] = $func;
720 $level++; // increse parenthesis level counter.
721 } else {
722 $stack[$pnt]['distinct'] = $this->nextPart($parseString,'^(distinct[[:space:]]+)');
723 // Otherwise, look for regular fieldname:
724 if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]\*._]+)(,|[[:space:]]+)')) {
725 $stack[$pnt]['type'] = 'field';
726
727 // Explode fieldname into field and table:
728 $tableField = explode('.',$fieldName,2);
729 if (count($tableField)==2) {
730 $stack[$pnt]['table'] = $tableField[0];
731 $stack[$pnt]['field'] = $tableField[1];
732 } else {
733 $stack[$pnt]['table'] = '';
734 $stack[$pnt]['field'] = $tableField[0];
735 }
736 } else {
737 return $this->parseError('No field name found as expected in parseFieldList()',$parseString);
738 }
739 }
740 }
741
742 // After a function or field we look for "AS" alias and a comma to separate to the next field in the list:
743 if (!$level) {
744
745 // Looking for "AS" alias:
746 if ($as = $this->nextPart($parseString,'^(AS)[[:space:]]+')) {
747 $stack[$pnt]['as'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)');
748 $stack[$pnt]['as_keyword'] = $as;
749 }
750
751 // Looking for "ASC" or "DESC" keywords (for ORDER BY)
752 if ($sDir = $this->nextPart($parseString,'^(ASC|DESC)([[:space:]]+|,)')) {
753 $stack[$pnt]['sortDir'] = $sDir;
754 }
755
756 // Looking for stop-keywords:
757 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
758 $this->lastStopKeyWord = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$this->lastStopKeyWord));
759 return $stack;
760 }
761
762 // Looking for comma (since the stop-keyword did not trigger a return...)
763 if (strlen($parseString) && !$this->nextPart($parseString,'^(,)')) {
764 return $this->parseError('No comma found as expected in parseFieldList()',$parseString);
765 }
766
767 // Increasing pointer:
768 $pnt++;
769 }
770
771 // Check recursivity brake:
772 $loopExit++;
773 if ($loopExit>500) {
774 return $this->parseError('More than 500 loops, exiting prematurely in parseFieldList()...',$parseString);
775 }
776 }
777
778 // Return result array:
779 return $stack;
780 }
781
782 /**
783 * Parsing the tablenames in the "FROM [$parseString] WHERE" part of a query into an array.
784 * The success of this parsing determines if that part of the query is supported by TYPO3.
785 *
786 * @param string list of tables, eg. "pages, tt_content" or "pages A, pages B". NOTICE: passed by reference!
787 * @param string Regular expressing to STOP parsing, eg. '^(WHERE)([[:space:]]*)'
788 * @return array If successful parsing, returns an array, otherwise an error string.
789 * @see compileFromTables()
790 * @deprecated since TYPO3 4.3, this function will be removed in TYPO3 4.5, this is a DBAL-only method that was moved to ux_t3lib_sqlparser.
791 */
792 public function parseFromTables(&$parseString, $stopRegex = '') {
793 t3lib_div::logDeprecatedFunction();
794
795 // Prepare variables:
796 $parseString = $this->trimSQL($parseString);
797 $this->lastStopKeyWord = '';
798 $this->parse_error = '';
799
800 $stack = array(); // Contains the parsed content
801 $pnt = 0; // Pointer to positions in $stack
802 $loopExit = 0; // Recursivity brake.
803
804 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
805 while (strlen($parseString)) {
806 // Looking for the table:
807 if ($stack[$pnt]['table'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)')) {
808 // Looking for stop-keywords before fetching potential table alias:
809 if ($stopRegex && ($this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))) {
810 $this->lastStopKeyWord = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$this->lastStopKeyWord));
811 return $stack;
812 }
813 if(!preg_match('/^(LEFT|JOIN)[[:space:]]+/i',$parseString)) {
814 $stack[$pnt]['as_keyword'] = $this->nextPart($parseString,'^(AS[[:space:]]+)');
815 $stack[$pnt]['as'] = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]*');
816 }
817 } else return $this->parseError('No table name found as expected in parseFromTables()!',$parseString);
818
819 // Looking for JOIN
820 if ($join = $this->nextPart($parseString,'^(LEFT[[:space:]]+JOIN|LEFT[[:space:]]+OUTER[[:space:]]+JOIN|JOIN)[[:space:]]+')) {
821 $stack[$pnt]['JOIN']['type'] = $join;
822 if ($stack[$pnt]['JOIN']['withTable'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+', 1)) {
823 if (!preg_match('/^ON[[:space:]]+/i', $parseString)) {
824 $stack[$pnt]['JOIN']['as_keyword'] = $this->nextPart($parseString, '^(AS[[:space:]]+)');
825 $stack[$pnt]['JOIN']['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
826 }
827 if (!$this->nextPart($parseString, '^(ON[[:space:]]+)')) {
828 return $this->parseError('No join condition found in parseFromTables()!', $parseString);
829 }
830 $field1 = $this->nextPart($parseString,'^([[:alnum:]_.]+)[[:space:]]*=[[:space:]]*',1);
831 $field2 = $this->nextPart($parseString,'^([[:alnum:]_.]+)[[:space:]]+');
832 if ($field1 && $field2) {
833
834 // Explode fields into field and table:
835 $tableField = explode('.',$field1,2);
836 $field1 = array();
837 if (count($tableField)!=2) {
838 $field1['table'] = '';
839 $field1['field'] = $tableField[0];
840 } else {
841 $field1['table'] = $tableField[0];
842 $field1['field'] = $tableField[1];
843 }
844 $tableField = explode('.',$field2,2);
845 $field2 = array();
846 if (count($tableField)!=2) {
847 $field2['table'] = '';
848 $field2['field'] = $tableField[0];
849 } else {
850 $field2['table'] = $tableField[0];
851 $field2['field'] = $tableField[1];
852 }
853 $stack[$pnt]['JOIN']['ON'] = array($field1,$field2);
854 } else return $this->parseError('No join fields found in parseFromTables()!',$parseString);
855 } else return $this->parseError('No join table found in parseFromTables()!',$parseString);
856 }
857
858 // Looking for stop-keywords:
859 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
860 $this->lastStopKeyWord = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$this->lastStopKeyWord));
861 return $stack;
862 }
863
864 // Looking for comma:
865 if (strlen($parseString) && !$this->nextPart($parseString,'^(,)')) {
866 return $this->parseError('No comma found as expected in parseFromTables()',$parseString);
867 }
868
869 // Increasing pointer:
870 $pnt++;
871
872 // Check recursivity brake:
873 $loopExit++;
874 if ($loopExit>500) {
875 return $this->parseError('More than 500 loops, exiting prematurely in parseFromTables()...',$parseString);
876 }
877 }
878
879 // Return result array:
880 return $stack;
881 }
882
883 /**
884 * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array.
885 * The success of this parsing determines if that part of the query is supported by TYPO3.
886 *
887 * @param string WHERE clause to parse. NOTICE: passed by reference!
888 * @param string Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)'
889 * @return mixed If successful parsing, returns an array, otherwise an error string.
890 * @deprecated since TYPO3 4.3, this function will be removed in TYPO3 4.5, this is a DBAL-only method that was moved to ux_t3lib_sqlparser.
891 */
892 public function parseWhereClause(&$parseString, $stopRegex = '') {
893 t3lib_div::logDeprecatedFunction();
894
895 // Prepare variables:
896 $parseString = $this->trimSQL($parseString);
897 $this->lastStopKeyWord = '';
898 $this->parse_error = '';
899
900 $stack = array(0 => array()); // Contains the parsed content
901 $pnt = array(0 => 0); // Pointer to positions in $stack
902 $level = 0; // Determines parenthesis level
903 $loopExit = 0; // Recursivity brake.
904
905 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
906 while (strlen($parseString)) {
907
908 // Look for next parenthesis level:
909 $newLevel = $this->nextPart($parseString,'^([(])');
910 if ($newLevel=='(') { // If new level is started, manage stack/pointers:
911 $level++; // Increase level
912 $pnt[$level] = 0; // Reset pointer for this level
913 $stack[$level] = array(); // Reset stack for this level
914 } else { // If no new level is started, just parse the current level:
915
916 // Find "modifyer", eg. "NOT or !"
917 $stack[$level][$pnt[$level]]['modifier'] = trim($this->nextPart($parseString,'^(!|NOT[[:space:]]+)'));
918
919 // Support calculated value only for:
920 // - "&" (boolean AND)
921 // - "+" (addition)
922 // - "-" (substraction)
923 // - "*" (multiplication)
924 // - "/" (division)
925 // - "%" (modulo)
926 $calcOperators = '&|\+|-|\*|\/|%';
927
928 // Fieldname:
929 if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)([[:space:]]+|' . $calcOperators . '|<=|>=|<|>|=|!=|IS)')) {
930
931 // Parse field name into field and table:
932 $tableField = explode('.',$fieldName,2);
933 if (count($tableField)==2) {
934 $stack[$level][$pnt[$level]]['table'] = $tableField[0];
935 $stack[$level][$pnt[$level]]['field'] = $tableField[1];
936 } else {
937 $stack[$level][$pnt[$level]]['table'] = '';
938 $stack[$level][$pnt[$level]]['field'] = $tableField[0];
939 }
940 } else {
941 return $this->parseError('No field name found as expected in parseWhereClause()',$parseString);
942 }
943
944 // See if the value is calculated:
945 $stack[$level][$pnt[$level]]['calc'] = $this->nextPart($parseString, '^(' . $calcOperators . ')');
946 if (strlen($stack[$level][$pnt[$level]]['calc'])) {
947 // Finding value for calculation:
948 $stack[$level][$pnt[$level]]['calc_value'] = $this->getValue($parseString);
949 }
950
951 // Find "comparator":
952 $stack[$level][$pnt[$level]]['comparator'] = $this->nextPart($parseString,'^(<=|>=|<|>|=|!=|NOT[[:space:]]+IN|IN|NOT[[:space:]]+LIKE|LIKE|IS[[:space:]]+NOT|IS)');
953 if (strlen($stack[$level][$pnt[$level]]['comparator'])) {
954 // Finding value for comparator:
955 $stack[$level][$pnt[$level]]['value'] = $this->getValue($parseString,$stack[$level][$pnt[$level]]['comparator']);
956 if ($this->parse_error) { return $this->parse_error; }
957 }
958
959 // Finished, increase pointer:
960 $pnt[$level]++;
961
962 // Checking if the current level is ended, in that case do stack management:
963 while ($this->nextPart($parseString,'^([)])')) {
964 $level--; // Decrease level:
965 $stack[$level][$pnt[$level]]['sub'] = $stack[$level+1]; // Copy stack
966 $pnt[$level]++; // Increase pointer of the new level
967
968 // Make recursivity check:
969 $loopExit++;
970 if ($loopExit>500) {
971 return $this->parseError('More than 500 loops (in search for exit parenthesis), exiting prematurely in parseWhereClause()...',$parseString);
972 }
973 }
974
975 // Detecting the operator for the next level:
976 $op = $this->nextPart($parseString, '^(AND[[:space:]]+NOT|&&[[:space:]]+NOT|OR[[:space:]]+NOT|OR[[:space:]]+NOT|\|\|[[:space:]]+NOT|AND|&&|OR|\|\|)(\(|[[:space:]]+)');
977 if ($op) {
978 // Normalize boolean operator
979 $op = str_replace(array('&&', '||'), array('AND', 'OR'), $op);
980 $stack[$level][$pnt[$level]]['operator'] = $op;
981 } elseif (strlen($parseString)) {
982
983 // Looking for stop-keywords:
984 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
985 $this->lastStopKeyWord = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$this->lastStopKeyWord));
986 return $stack[0];
987 } else {
988 return $this->parseError('No operator, but parsing not finished in parseWhereClause().',$parseString);
989 }
990 }
991 }
992
993 // Make recursivity check:
994 $loopExit++;
995 if ($loopExit>500) {
996 return $this->parseError('More than 500 loops, exiting prematurely in parseWhereClause()...',$parseString);
997 }
998 }
999
1000 // Return the stacks lowest level:
1001 return $stack[0];
1002 }
1003
1004 /**
1005 * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array.
1006 * The success of this parsing determines if that part of the query is supported by TYPO3.
1007 *
1008 * @param string WHERE clause to parse. NOTICE: passed by reference!
1009 * @param string Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)'
1010 * @return mixed If successful parsing, returns an array, otherwise an error string.
1011 */
1012 public function parseFieldDef(&$parseString, $stopRegex = '') {
1013 // Prepare variables:
1014 $parseString = $this->trimSQL($parseString);
1015 $this->lastStopKeyWord = '';
1016 $this->parse_error = '';
1017
1018 $result = array();
1019
1020 // Field type:
1021 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:],]+|\()')) {
1022
1023 // Looking for value:
1024 if (substr($parseString,0,1)=='(') {
1025 $parseString = substr($parseString,1);
1026 if ($result['value'] = $this->nextPart($parseString,'^([^)]*)')) {
1027 $parseString = ltrim(substr($parseString,1));
1028 } else return $this->parseError('No end-parenthesis for value found in parseFieldDef()!',$parseString);
1029 }
1030
1031 // Looking for keywords
1032 while($keyword = $this->nextPart($parseString,'^(DEFAULT|NOT[[:space:]]+NULL|AUTO_INCREMENT|UNSIGNED)([[:space:]]+|,|\))')) {
1033 $keywordCmp = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$keyword));
1034
1035 $result['featureIndex'][$keywordCmp]['keyword'] = $keyword;
1036
1037 switch($keywordCmp) {
1038 case 'DEFAULT':
1039 $result['featureIndex'][$keywordCmp]['value'] = $this->getValue($parseString);
1040 break;
1041 }
1042 }
1043 } else {
1044 return $this->parseError('Field type unknown in parseFieldDef()!',$parseString);
1045 }
1046
1047 return $result;
1048 }
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060 /************************************
1061 *
1062 * Parsing: Helper functions
1063 *
1064 ************************************/
1065
1066 /**
1067 * Strips off a part of the parseString and returns the matching part.
1068 * Helper function for the parsing methods.
1069 *
1070 * @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.
1071 * @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())
1072 * @param boolean If set the full match of the regex is stripped of the beginning of the string!
1073 * @return string The value of the first parenthesis level of the REGEX.
1074 */
1075 protected function nextPart(&$parseString, $regex, $trimAll = FALSE) {
1076 $reg = array();
1077 if (preg_match('/'.$regex.'/i',$parseString.' ', $reg)) { // Adding space char because [[:space:]]+ is often a requirement in regex's
1078 $parseString = ltrim(substr($parseString,strlen($reg[$trimAll?0:1])));
1079 return $reg[1];
1080 }
1081 }
1082
1083 /**
1084 * Finds value in beginning of $parseString, returns result and strips it of parseString
1085 *
1086 * @param string The parseString, eg. "(0,1,2,3) ..." or "('asdf','qwer') ..." or "1234 ..." or "'My string value here' ..."
1087 * @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)
1088 * @return mixed The value (string/integer). Otherwise an array with error message in first key (0)
1089 */
1090 protected function getValue(&$parseString, $comparator = '') {
1091 $value = '';
1092
1093 if (t3lib_div::inList('NOTIN,IN,_LIST',strtoupper(str_replace(array(' ',"\n","\r","\t"),'',$comparator)))) { // List of values:
1094 if ($this->nextPart($parseString,'^([(])')) {
1095 $listValues = array();
1096 $comma=',';
1097
1098 while($comma==',') {
1099 $listValues[] = $this->getValue($parseString);
1100 $comma = $this->nextPart($parseString,'^([,])');
1101 }
1102
1103 $out = $this->nextPart($parseString,'^([)])');
1104 if ($out) {
1105 if ($comparator=='_LIST') {
1106 $kVals = array();
1107 foreach ($listValues as $vArr) {
1108 $kVals[] = $vArr[0];
1109 }
1110 return $kVals;
1111 } else {
1112 return $listValues;
1113 }
1114 } else return array($this->parseError('No ) parenthesis in list',$parseString));
1115 } else return array($this->parseError('No ( parenthesis starting the list',$parseString));
1116
1117 } else { // Just plain string value, in quotes or not:
1118
1119 // Quote?
1120 $firstChar = substr($parseString,0,1);
1121 switch($firstChar) {
1122 case '"':
1123 $value = array($this->getValueInQuotes($parseString,'"'),'"');
1124 break;
1125 case "'":
1126 $value = array($this->getValueInQuotes($parseString,"'"),"'");
1127 break;
1128 default:
1129 $reg = array();
1130 if (preg_match('/^([[:alnum:]._-]+)/i',$parseString, $reg)) {
1131 $parseString = ltrim(substr($parseString,strlen($reg[0])));
1132 $value = array($reg[1]);
1133 }
1134 break;
1135 }
1136 }
1137 return $value;
1138 }
1139
1140 /**
1141 * Get value in quotes from $parseString.
1142 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1143 *
1144 * @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.
1145 * @param string The quote used; input either " or '
1146 * @return string The value, passed through stripslashes() !
1147 */
1148 protected function getValueInQuotes(&$parseString, $quote) {
1149
1150 $parts = explode($quote,substr($parseString,1));
1151 $buffer = '';
1152 foreach($parts as $k => $v) {
1153 $buffer.=$v;
1154
1155 $reg = array();
1156 preg_match('/\\*$/',$v,$reg);
1157 if ($reg AND strlen($reg[0])%2) {
1158 $buffer.=$quote;
1159 } else {
1160 $parseString = ltrim(substr($parseString,strlen($buffer)+2));
1161 return $this->parseStripslashes($buffer);
1162 }
1163 }
1164 }
1165
1166 /**
1167 * Strip slashes function used for parsing
1168 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1169 *
1170 * @param string Input string
1171 * @return string Output string
1172 */
1173 protected function parseStripslashes($str) {
1174 $search = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
1175 $replace = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
1176
1177 return str_replace($search, $replace, $str);
1178 }
1179
1180 /**
1181 * Add slashes function used for compiling queries
1182 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1183 *
1184 * @param string Input string
1185 * @return string Output string
1186 */
1187 protected function compileAddslashes($str) {
1188 return $str;
1189 $search = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
1190 $replace = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
1191
1192 return str_replace($search, $replace, $str);
1193 }
1194
1195 /**
1196 * Setting the internal error message value, $this->parse_error and returns that value.
1197 *
1198 * @param string Input error message
1199 * @param string Remaining query to parse.
1200 * @return string Error message.
1201 */
1202 protected function parseError($msg, $restQuery) {
1203 $this->parse_error = 'SQL engine parse ERROR: '.$msg.': near "'.substr($restQuery,0,50).'"';
1204 return $this->parse_error;
1205 }
1206
1207 /**
1208 * Trimming SQL as preparation for parsing.
1209 * ";" in the end is stripped of.
1210 * White space is trimmed away around the value
1211 * A single space-char is added in the end
1212 *
1213 * @param string Input string
1214 * @return string Output string
1215 */
1216 protected function trimSQL($str) {
1217 return trim(rtrim($str, "; \r\n\t")).' ';
1218 }
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231 /*************************
1232 *
1233 * Compiling queries
1234 *
1235 *************************/
1236
1237 /**
1238 * Compiles an SQL query from components
1239 *
1240 * @param array Array of SQL query components
1241 * @return string SQL query
1242 * @see parseSQL()
1243 */
1244 public function compileSQL($components) {
1245 switch($components['type']) {
1246 case 'SELECT':
1247 $query = $this->compileSELECT($components);
1248 break;
1249 case 'UPDATE':
1250 $query = $this->compileUPDATE($components);
1251 break;
1252 case 'INSERT':
1253 $query = $this->compileINSERT($components);
1254 break;
1255 case 'DELETE':
1256 $query = $this->compileDELETE($components);
1257 break;
1258 case 'EXPLAIN':
1259 $query = 'EXPLAIN '.$this->compileSELECT($components);
1260 break;
1261 case 'DROPTABLE':
1262 $query = 'DROP TABLE'.($components['ifExists']?' IF EXISTS':'').' '.$components['TABLE'];
1263 break;
1264 case 'CREATETABLE':
1265 $query = $this->compileCREATETABLE($components);
1266 break;
1267 case 'ALTERTABLE':
1268 $query = $this->compileALTERTABLE($components);
1269 break;
1270 }
1271
1272 return $query;
1273 }
1274
1275 /**
1276 * Compiles a SELECT statement from components array
1277 *
1278 * @param array Array of SQL query components
1279 * @return string SQL SELECT query
1280 * @see parseSELECT()
1281 */
1282 protected function compileSELECT($components) {
1283
1284 // Initialize:
1285 $where = $this->compileWhereClause($components['WHERE']);
1286 $groupBy = $this->compileFieldList($components['GROUPBY']);
1287 $orderBy = $this->compileFieldList($components['ORDERBY']);
1288 $limit = $components['LIMIT'];
1289
1290 // Make query:
1291 $query = 'SELECT '.($components['STRAIGHT_JOIN'] ? $components['STRAIGHT_JOIN'].'' : '').'
1292 '.$this->compileFieldList($components['SELECT']).'
1293 FROM '.$this->compileFromTables($components['FROM']).
1294 (strlen($where)?'
1295 WHERE '.$where : '').
1296 (strlen($groupBy)?'
1297 GROUP BY '.$groupBy : '').
1298 (strlen($orderBy)?'
1299 ORDER BY '.$orderBy : '').
1300 (strlen($limit)?'
1301 LIMIT '.$limit : '');
1302
1303 return $query;
1304 }
1305
1306 /**
1307 * Compiles an UPDATE statement from components array
1308 *
1309 * @param array Array of SQL query components
1310 * @return string SQL UPDATE query
1311 * @see parseUPDATE()
1312 */
1313 protected function compileUPDATE($components) {
1314
1315 // Where clause:
1316 $where = $this->compileWhereClause($components['WHERE']);
1317
1318 // Fields
1319 $fields = array();
1320 foreach($components['FIELDS'] as $fN => $fV) {
1321 $fields[]=$fN.'='.$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1322 }
1323
1324 // Make query:
1325 $query = 'UPDATE '.$components['TABLE'].' SET
1326 '.implode(',
1327 ',$fields).'
1328 '.(strlen($where)?'
1329 WHERE '.$where : '');
1330
1331 return $query;
1332 }
1333
1334 /**
1335 * Compiles an INSERT statement from components array
1336 *
1337 * @param array Array of SQL query components
1338 * @return string SQL INSERT query
1339 * @see parseINSERT()
1340 */
1341 protected function compileINSERT($components) {
1342
1343 if ($components['VALUES_ONLY']) {
1344 // Initialize:
1345 $fields = array();
1346 foreach($components['VALUES_ONLY'] as $fV) {
1347 $fields[]=$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1348 }
1349
1350 // Make query:
1351 $query = 'INSERT INTO '.$components['TABLE'].'
1352 VALUES
1353 ('.implode(',
1354 ',$fields).')';
1355 } else {
1356 // Initialize:
1357 $fields = array();
1358 foreach($components['FIELDS'] as $fN => $fV) {
1359 $fields[$fN]=$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1360 }
1361
1362 // Make query:
1363 $query = 'INSERT INTO '.$components['TABLE'].'
1364 ('.implode(',
1365 ',array_keys($fields)).')
1366 VALUES
1367 ('.implode(',
1368 ',$fields).')';
1369 }
1370
1371 return $query;
1372 }
1373
1374 /**
1375 * Compiles an DELETE statement from components array
1376 *
1377 * @param array Array of SQL query components
1378 * @return string SQL DELETE query
1379 * @see parseDELETE()
1380 */
1381 protected function compileDELETE($components) {
1382
1383 // Where clause:
1384 $where = $this->compileWhereClause($components['WHERE']);
1385
1386 // Make query:
1387 $query = 'DELETE FROM '.$components['TABLE'].
1388 (strlen($where)?'
1389 WHERE '.$where : '');
1390
1391 return $query;
1392 }
1393
1394 /**
1395 * Compiles a CREATE TABLE statement from components array
1396 *
1397 * @param array Array of SQL query components
1398 * @return string SQL CREATE TABLE query
1399 * @see parseCREATETABLE()
1400 */
1401 protected function compileCREATETABLE($components) {
1402
1403 // Create fields and keys:
1404 $fieldsKeys = array();
1405 foreach($components['FIELDS'] as $fN => $fCfg) {
1406 $fieldsKeys[]=$fN.' '.$this->compileFieldCfg($fCfg['definition']);
1407 }
1408 foreach($components['KEYS'] as $kN => $kCfg) {
1409 if ($kN == 'PRIMARYKEY') {
1410 $fieldsKeys[]='PRIMARY KEY ('.implode(',', $kCfg).')';
1411 } elseif ($kN == 'UNIQUE') {
1412 $fieldsKeys[]='UNIQUE '.$kN.' ('.implode(',', $kCfg).')';
1413 } else {
1414 $fieldsKeys[]='KEY '.$kN.' ('.implode(',', $kCfg).')';
1415 }
1416 }
1417
1418 // Make query:
1419 $query = 'CREATE TABLE '.$components['TABLE'].' (
1420 '.implode(',
1421 ', $fieldsKeys).'
1422 )'.($components['tableType'] ? ' TYPE='.$components['tableType'] : '');
1423
1424 return $query;
1425 }
1426
1427 /**
1428 * Compiles an ALTER TABLE statement from components array
1429 *
1430 * @param array Array of SQL query components
1431 * @return string SQL ALTER TABLE query
1432 * @see parseALTERTABLE()
1433 */
1434 protected function compileALTERTABLE($components) {
1435
1436 // Make query:
1437 $query = 'ALTER TABLE '.$components['TABLE'].' '.$components['action'].' '.($components['FIELD']?$components['FIELD']:$components['KEY']);
1438
1439 // Based on action, add the final part:
1440 switch(strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$components['action']))) {
1441 case 'ADD':
1442 $query.=' '.$this->compileFieldCfg($components['definition']);
1443 break;
1444 case 'CHANGE':
1445 $query.=' '.$components['newField'].' '.$this->compileFieldCfg($components['definition']);
1446 break;
1447 case 'DROP':
1448 case 'DROPKEY':
1449 break;
1450 case 'ADDKEY':
1451 case 'ADDPRIMARYKEY':
1452 $query.=' ('.implode(',',$components['fields']).')';
1453 break;
1454 }
1455
1456 // Return query
1457 return $query;
1458 }
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473 /**************************************
1474 *
1475 * Compiling queries, helper functions for parts of queries
1476 *
1477 **************************************/
1478
1479 /**
1480 * Compiles a "SELECT [output] FROM..:" field list based on input array (made with ->parseFieldList())
1481 * Can also compile field lists for ORDER BY and GROUP BY.
1482 *
1483 * @param array Array of select fields, (made with ->parseFieldList())
1484 * @return string Select field string
1485 * @see parseFieldList()
1486 */
1487 public function compileFieldList($selectFields) {
1488
1489 // Prepare buffer variable:
1490 $outputParts = array();
1491
1492 // Traverse the selectFields if any:
1493 if (is_array($selectFields)) {
1494 foreach($selectFields as $k => $v) {
1495
1496 // Detecting type:
1497 switch($v['type']) {
1498 case 'function':
1499 $outputParts[$k] = $v['function'].'('.$v['func_content'].')';
1500 break;
1501 case 'field':
1502 $outputParts[$k] = ($v['distinct']?$v['distinct']:'').($v['table']?$v['table'].'.':'').$v['field'];
1503 break;
1504 }
1505
1506 // Alias:
1507 if ($v['as']) {
1508 $outputParts[$k].= ' '.$v['as_keyword'].' '.$v['as'];
1509 }
1510
1511 // Specifically for ORDER BY and GROUP BY field lists:
1512 if ($v['sortDir']) {
1513 $outputParts[$k].= ' '.$v['sortDir'];
1514 }
1515 }
1516 }
1517
1518 // Return imploded buffer:
1519 return implode(', ',$outputParts);
1520 }
1521
1522 /**
1523 * Compiles a "FROM [output] WHERE..:" table list based on input array (made with ->parseFromTables())
1524 *
1525 * @param array Array of table names, (made with ->parseFromTables())
1526 * @return string Table name string
1527 * @see parseFromTables()
1528 * @deprecated since TYPO3 4.3, this function will be removed in TYPO3 4.5, this is a DBAL-only method that was moved to ux_t3lib_sqlparser.
1529 */
1530 public function compileFromTables($tablesArray) {
1531 t3lib_div::logDeprecatedFunction();
1532
1533 // Prepare buffer variable:
1534 $outputParts = array();
1535
1536 // Traverse the table names:
1537 if (is_array($tablesArray)) {
1538 foreach($tablesArray as $k => $v) {
1539
1540 // Set table name:
1541 $outputParts[$k] = $v['table'];
1542
1543 // Add alias AS if there:
1544 if ($v['as']) {
1545 $outputParts[$k].= ' '.$v['as_keyword'].' '.$v['as'];
1546 }
1547
1548 if (is_array($v['JOIN'])) {
1549 $outputParts[$k] .= ' ' . $v['JOIN']['type'] . ' ' . $v['JOIN']['withTable'];
1550 // Add alias AS if there:
1551 if (isset($v['JOIN']['as']) && $v['JOIN']['as']) {
1552 $outputParts[$k] .= ' ' . $v['JOIN']['as_keyword'] . ' ' . $v['JOIN']['as'];
1553 }
1554 $outputParts[$k] .= ' ON ';
1555 $outputParts[$k] .= ($v['JOIN']['ON'][0]['table']) ? $v['JOIN']['ON'][0]['table'].'.' : '';
1556 $outputParts[$k] .= $v['JOIN']['ON'][0]['field'];
1557 $outputParts[$k] .= '=';
1558 $outputParts[$k] .= ($v['JOIN']['ON'][1]['table']) ? $v['JOIN']['ON'][1]['table'].'.' : '';
1559 $outputParts[$k] .= $v['JOIN']['ON'][1]['field'];
1560 }
1561 }
1562 }
1563
1564 // Return imploded buffer:
1565 return implode(', ',$outputParts);
1566 }
1567
1568 /**
1569 * Implodes an array of WHERE clause configuration into a WHERE clause.
1570 * NOTICE: MIGHT BY A TEMPORARY FUNCTION. Use for debugging only!
1571 * BUT IT IS NEEDED FOR DBAL - MAKE IT PERMANENT?!?!
1572 *
1573 * @param array WHERE clause configuration
1574 * @return string WHERE clause as string.
1575 * @see explodeWhereClause()
1576 * @deprecated since TYPO3 4.3, this function will be removed in TYPO3 4.5, this is a DBAL-only method that was moved to ux_t3lib_sqlparser.
1577 */
1578 public function compileWhereClause($clauseArray) {
1579 t3lib_div::logDeprecatedFunction();
1580
1581 // Prepare buffer variable:
1582 $output='';
1583
1584 // Traverse clause array:
1585 if (is_array($clauseArray)) {
1586 foreach($clauseArray as $k => $v) {
1587
1588 // Set operator:
1589 $output.=$v['operator'] ? ' '.$v['operator'] : '';
1590
1591 // Look for sublevel:
1592 if (is_array($v['sub'])) {
1593 $output.=' ('.trim($this->compileWhereClause($v['sub'])).')';
1594 } else {
1595
1596 // Set field/table with modifying prefix if any:
1597 $output.=' '.trim($v['modifier'].' '.($v['table']?$v['table'].'.':'').$v['field']);
1598
1599 // Set calculation, if any:
1600 if ($v['calc']) {
1601 $output.=$v['calc'].$v['calc_value'][1].$this->compileAddslashes($v['calc_value'][0]).$v['calc_value'][1];
1602 }
1603
1604 // Set comparator:
1605 if ($v['comparator']) {
1606 $output.=' '.$v['comparator'];
1607
1608 // Detecting value type; list or plain:
1609 if (t3lib_div::inList('NOTIN,IN',strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$v['comparator'])))) {
1610 $valueBuffer = array();
1611 foreach($v['value'] as $realValue) {
1612 $valueBuffer[]=$realValue[1].$this->compileAddslashes($realValue[0]).$realValue[1];
1613 }
1614 $output.=' ('.trim(implode(',',$valueBuffer)).')';
1615 } else {
1616 $output.=' '.$v['value'][1].$this->compileAddslashes($v['value'][0]).$v['value'][1];
1617 }
1618 }
1619 }
1620 }
1621 }
1622
1623 // Return output buffer:
1624 return $output;
1625 }
1626
1627 /**
1628 * Compile field definition
1629 *
1630 * @param array Field definition parts
1631 * @return string Field definition string
1632 */
1633 protected function compileFieldCfg($fieldCfg) {
1634
1635 // Set type:
1636 $cfg = $fieldCfg['fieldType'];
1637
1638 // Add value, if any:
1639 if (strlen($fieldCfg['value'])) {
1640 $cfg.='('.$fieldCfg['value'].')';
1641 }
1642
1643 // Add additional features:
1644 if (is_array($fieldCfg['featureIndex'])) {
1645 foreach($fieldCfg['featureIndex'] as $featureDef) {
1646 $cfg.=' '.$featureDef['keyword'];
1647
1648 // Add value if found:
1649 if (is_array($featureDef['value'])) {
1650 $cfg.=' '.$featureDef['value'][1].$this->compileAddslashes($featureDef['value'][0]).$featureDef['value'][1];
1651 }
1652 }
1653 }
1654
1655 // Return field definition string:
1656 return $cfg;
1657 }
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669 /*************************
1670 *
1671 * Debugging
1672 *
1673 *************************/
1674
1675 /**
1676 * Check parsability of input SQL part string; Will parse and re-compile after which it is compared
1677 *
1678 * @param string Part definition of string; "SELECT" = fieldlist (also ORDER BY and GROUP BY), "FROM" = table list, "WHERE" = Where clause.
1679 * @param string SQL string to verify parsability of
1680 * @return mixed Returns array with string 1 and 2 if error, otherwise false
1681 */
1682 public function debug_parseSQLpart($part, $str) {
1683 $retVal = false;
1684
1685 switch($part) {
1686 case 'SELECT':
1687 $retVal = $this->debug_parseSQLpartCompare($str,$this->compileFieldList($this->parseFieldList($str)));
1688 break;
1689 case 'FROM':
1690 $retVal = $this->debug_parseSQLpartCompare($str,$this->compileFromTables($this->parseFromTables($str)));
1691 break;
1692 case 'WHERE':
1693 $retVal = $this->debug_parseSQLpartCompare($str,$this->compileWhereClause($this->parseWhereClause($str)));
1694 break;
1695 }
1696 return $retVal;
1697 }
1698
1699 /**
1700 * Compare two query strins by stripping away whitespace.
1701 *
1702 * @param string SQL String 1
1703 * @param string SQL string 2
1704 * @param boolean If true, the strings are compared insensitive to case
1705 * @return mixed Returns array with string 1 and 2 if error, otherwise false
1706 */
1707 public function debug_parseSQLpartCompare($str, $newStr, $caseInsensitive = FALSE) {
1708 if ($caseInsensitive) {
1709 $str1 = strtoupper($str);
1710 $str2 = strtoupper($newStr);
1711 } else {
1712 $str1 = $str;
1713 $str2 = $newStr;
1714 }
1715
1716 // Fixing escaped chars:
1717 $search = array('\0', '\n', '\r', '\Z');
1718 $replace = array("\x00", "\x0a", "\x0d", "\x1a");
1719 $str1 = str_replace($search, $replace, $str1);
1720 $str2 = str_replace($search, $replace, $str2);
1721
1722 # Normally, commented out since they are needed only in tricky cases...
1723 # $str1 = stripslashes($str1);
1724 # $str2 = stripslashes($str2);
1725
1726 if (strcmp(str_replace(array(' ',"\t","\r","\n"),'',$this->trimSQL($str1)),str_replace(array(' ',"\t","\r","\n"),'',$this->trimSQL($str2)))) {
1727 return array(
1728 str_replace(array(' ',"\t","\r","\n"),' ',$str),
1729 str_replace(array(' ',"\t","\r","\n"),' ',$newStr),
1730 );
1731 }
1732 }
1733
1734 /**
1735 * 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
1736 *
1737 * @param string SQL query
1738 * @return string Query if all is well, otherwise exit.
1739 */
1740 public function debug_testSQL($SQLquery) {
1741
1742 // Getting result array:
1743 $parseResult = $this->parseSQL($SQLquery);
1744
1745 // If result array was returned, proceed. Otherwise show error and exit.
1746 if (is_array($parseResult)) {
1747
1748 // Re-compile query:
1749 $newQuery = $this->compileSQL($parseResult);
1750
1751 // TEST the new query:
1752 $testResult = $this->debug_parseSQLpartCompare($SQLquery, $newQuery);
1753
1754 // Return new query if OK, otherwise show error and exit:
1755 if (!is_array($testResult)) {
1756 return $newQuery;
1757 } else {
1758 debug(array('ERROR MESSAGE'=>'Input query did not match the parsed and recompiled query exactly (not observing whitespace)', 'TEST result' => $testResult),'SQL parsing failed:');
1759 exit;
1760 }
1761 } else {
1762 debug(array('query' => $SQLquery, 'ERROR MESSAGE'=>$parseResult),'SQL parsing failed:');
1763 exit;
1764 }
1765 }
1766 }
1767
1768
1769 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlparser.php']) {
1770 include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlparser.php']);
1771 }
1772
1773 ?>