SqlParser.php 74.3 KB
Newer Older
1
2
3
<?php
namespace TYPO3\CMS\Core\Database;

4
/*
5
 * This file is part of the TYPO3 CMS project.
6
 *
7
8
9
 * It is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License, either version 2
 * of the License, or any later version.
10
 *
11
12
 * For the full copyright and license information, please read the
 * LICENSE.txt file that was distributed with this source code.
13
 *
14
15
 * The TYPO3 project - inspiring people to share!
 */
16

17
18
19
20
21
22
23
/**
 * TYPO3 SQL parser class.
 *
 * @author Kasper Skårhøj <kasperYYYY@typo3.com>
 */
class SqlParser {

24
25
26
27
28
	/**
	 * Parsing error string
	 *
	 * @var string
	 */
29
30
	public $parse_error = '';

31
32
33
34
35
	/**
	 * Last stop keyword used.
	 *
	 * @var string
	 */
36
37
	public $lastStopKeyWord = '';

Andreas Fernandez's avatar
Andreas Fernandez committed
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
	/**
	 * Find "comparator"
	 *
	 * @var array
	 */
	protected static $comparatorPatterns = array(
		'<=',
		'>=',
		'<>',
		'<',
		'>',
		'=',
		'!=',
		'NOT[[:space:]]+IN',
		'IN',
		'NOT[[:space:]]+LIKE[[:space:]]+BINARY',
		'LIKE[[:space:]]+BINARY',
		'NOT[[:space:]]+LIKE',
		'LIKE',
		'IS[[:space:]]+NOT',
		'IS',
		'BETWEEN',
		'NOT[[:space]]+BETWEEN'
	);

	/**
	 * Whitespaces in a query
	 *
	 * @var array
	 */
	protected static $interQueryWhitespaces = array(' ', TAB, CR, LF);

70
71
72
	/**
	 * Default constructor
	 */
73
	public function __construct() {}
74

75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
	/*************************************
	 *
	 * SQL Parsing, full queries
	 *
	 **************************************/
	/**
	 * Parses any single SQL query
	 *
	 * @param string $parseString SQL query
	 * @return array Result array with all the parts in - or error message string
	 * @see compileSQL(), debug_testSQL()
	 */
	public function parseSQL($parseString) {
		// Prepare variables:
		$parseString = $this->trimSQL($parseString);
		$this->parse_error = '';
		$result = array();
		// Finding starting keyword of string:
		$_parseString = $parseString;
		// Protecting original string...
Andreas Fernandez's avatar
Andreas Fernandez committed
95
96
		$keyword = $this->nextPart($_parseString, '^(SELECT|UPDATE|INSERT[[:space:]]+INTO|DELETE[[:space:]]+FROM|EXPLAIN|(DROP|CREATE|ALTER|TRUNCATE)[[:space:]]+TABLE|CREATE[[:space:]]+DATABASE)[[:space:]]+');
		$keyword = $this->normalizeKeyword($keyword);
97
		switch ($keyword) {
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
			case 'SELECT':
				// Parsing SELECT query:
				$result = $this->parseSELECT($parseString);
				break;
			case 'UPDATE':
				// Parsing UPDATE query:
				$result = $this->parseUPDATE($parseString);
				break;
			case 'INSERTINTO':
				// Parsing INSERT query:
				$result = $this->parseINSERT($parseString);
				break;
			case 'DELETEFROM':
				// Parsing DELETE query:
				$result = $this->parseDELETE($parseString);
				break;
			case 'EXPLAIN':
				// Parsing EXPLAIN SELECT query:
				$result = $this->parseEXPLAIN($parseString);
				break;
			case 'DROPTABLE':
				// Parsing DROP TABLE query:
				$result = $this->parseDROPTABLE($parseString);
				break;
			case 'ALTERTABLE':
				// Parsing ALTER TABLE query:
				$result = $this->parseALTERTABLE($parseString);
				break;
			case 'CREATETABLE':
				// Parsing CREATE TABLE query:
				$result = $this->parseCREATETABLE($parseString);
				break;
			case 'CREATEDATABASE':
				// Parsing CREATE DATABASE query:
				$result = $this->parseCREATEDATABASE($parseString);
				break;
			case 'TRUNCATETABLE':
				// Parsing TRUNCATE TABLE query:
				$result = $this->parseTRUNCATETABLE($parseString);
				break;
			default:
				$result = $this->parseError('"' . $keyword . '" is not a keyword', $parseString);
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
		}
		return $result;
	}

	/**
	 * Parsing SELECT query
	 *
	 * @param string $parseString SQL string with SELECT query to parse
	 * @param array $parameterReferences Array holding references to either named (:name) or question mark (?) parameters found
	 * @return mixed Returns array with components of SELECT query on success, otherwise an error message string.
	 * @see compileSELECT()
	 */
	protected function parseSELECT($parseString, &$parameterReferences = NULL) {
		// Removing SELECT:
		$parseString = $this->trimSQL($parseString);
		$parseString = ltrim(substr($parseString, 6));
		// Init output variable:
		$result = array();
		if ($parameterReferences === NULL) {
			$result['parameters'] = array();
160
			$parameterReferences = &$result['parameters'];
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
		}
		$result['type'] = 'SELECT';
		// Looking for STRAIGHT_JOIN keyword:
		$result['STRAIGHT_JOIN'] = $this->nextPart($parseString, '^(STRAIGHT_JOIN)[[:space:]]+');
		// Select fields:
		$result['SELECT'] = $this->parseFieldList($parseString, '^(FROM)[[:space:]]+');
		if ($this->parse_error) {
			return $this->parse_error;
		}
		// Continue if string is not ended:
		if ($parseString) {
			// Get table list:
			$result['FROM'] = $this->parseFromTables($parseString, '^(WHERE)[[:space:]]+');
			if ($this->parse_error) {
				return $this->parse_error;
			}
			// If there are more than just the tables (a WHERE clause that would be...)
			if ($parseString) {
				// Get WHERE clause:
Andreas Fernandez's avatar
Andreas Fernandez committed
180
				$result['WHERE'] = $this->parseWhereClause($parseString, '^((GROUP|ORDER)[[:space:]]+BY|LIMIT)[[:space:]]+', $parameterReferences);
181
182
183
184
185
186
				if ($this->parse_error) {
					return $this->parse_error;
				}
				// If the WHERE clause parsing was stopped by GROUP BY, ORDER BY or LIMIT, then proceed with parsing:
				if ($this->lastStopKeyWord) {
					// GROUP BY parsing:
Andreas Fernandez's avatar
Andreas Fernandez committed
187
					if ($this->lastStopKeyWord === 'GROUPBY') {
188
189
190
191
192
193
						$result['GROUPBY'] = $this->parseFieldList($parseString, '^(ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
						if ($this->parse_error) {
							return $this->parse_error;
						}
					}
					// ORDER BY parsing:
Andreas Fernandez's avatar
Andreas Fernandez committed
194
					if ($this->lastStopKeyWord === 'ORDERBY') {
195
196
197
198
199
200
						$result['ORDERBY'] = $this->parseFieldList($parseString, '^(LIMIT)[[:space:]]+');
						if ($this->parse_error) {
							return $this->parse_error;
						}
					}
					// LIMIT parsing:
Andreas Fernandez's avatar
Andreas Fernandez committed
201
					if ($this->lastStopKeyWord === 'LIMIT') {
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
						if (preg_match('/^([0-9]+|[0-9]+[[:space:]]*,[[:space:]]*[0-9]+)$/', trim($parseString))) {
							$result['LIMIT'] = $parseString;
						} else {
							return $this->parseError('No value for limit!', $parseString);
						}
					}
				}
			}
		} else {
			return $this->parseError('No table to select from!', $parseString);
		}
		// Store current parseString in the result array for possible further processing (e.g., subquery support by DBAL)
		$result['parseString'] = $parseString;
		// Return result:
		return $result;
	}

	/**
	 * Parsing UPDATE query
	 *
	 * @param string $parseString SQL string with UPDATE query to parse
	 * @return mixed Returns array with components of UPDATE query on success, otherwise an error message string.
	 * @see compileUPDATE()
	 */
	protected function parseUPDATE($parseString) {
		// Removing UPDATE
		$parseString = $this->trimSQL($parseString);
		$parseString = ltrim(substr($parseString, 6));
		// Init output variable:
		$result = array();
		$result['type'] = 'UPDATE';
		// Get table:
		$result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
		// Continue if string is not ended:
		if ($result['TABLE']) {
			if ($parseString && $this->nextPart($parseString, '^(SET)[[:space:]]+')) {
				$comma = TRUE;
				// Get field/value pairs:
				while ($comma) {
					if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*=')) {
						// Strip of "=" sign.
						$this->nextPart($parseString, '^(=)');
						$value = $this->getValue($parseString);
						$result['FIELDS'][$fieldName] = $value;
					} else {
						return $this->parseError('No fieldname found', $parseString);
					}
					$comma = $this->nextPart($parseString, '^(,)');
				}
				// WHERE
				if ($this->nextPart($parseString, '^(WHERE)')) {
					$result['WHERE'] = $this->parseWhereClause($parseString);
					if ($this->parse_error) {
						return $this->parse_error;
					}
				}
			} else {
				return $this->parseError('Query missing SET...', $parseString);
			}
		} else {
			return $this->parseError('No table found!', $parseString);
		}
		// Should be no more content now:
		if ($parseString) {
			return $this->parseError('Still content in clause after parsing!', $parseString);
		}
		// Return result:
		return $result;
	}

	/**
	 * Parsing INSERT query
	 *
	 * @param string $parseString SQL string with INSERT query to parse
	 * @return mixed Returns array with components of INSERT query on success, otherwise an error message string.
	 * @see compileINSERT()
	 */
	protected function parseINSERT($parseString) {
		// Removing INSERT
		$parseString = $this->trimSQL($parseString);
		$parseString = ltrim(substr(ltrim(substr($parseString, 6)), 4));
		// Init output variable:
		$result = array();
		$result['type'] = 'INSERT';
		// Get table:
		$result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\\()');
		if ($result['TABLE']) {
			// In this case there are no field names mentioned in the SQL!
			if ($this->nextPart($parseString, '^(VALUES)([[:space:]]+|\\()')) {
				// Get values/fieldnames (depending...)
				$result['VALUES_ONLY'] = $this->getValue($parseString, 'IN');
				if ($this->parse_error) {
					return $this->parse_error;
				}
				if (preg_match('/^,/', $parseString)) {
					$result['VALUES_ONLY'] = array($result['VALUES_ONLY']);
					$result['EXTENDED'] = '1';
					while ($this->nextPart($parseString, '^(,)') === ',') {
						$result['VALUES_ONLY'][] = $this->getValue($parseString, 'IN');
						if ($this->parse_error) {
							return $this->parse_error;
						}
					}
				}
			} else {
				// There are apparently fieldnames listed:
				$fieldNames = $this->getValue($parseString, '_LIST');
				if ($this->parse_error) {
					return $this->parse_error;
				}
				// "VALUES" keyword binds the fieldnames to values:
				if ($this->nextPart($parseString, '^(VALUES)([[:space:]]+|\\()')) {
					$result['FIELDS'] = array();
					do {
						// Using the "getValue" function to get the field list...
						$values = $this->getValue($parseString, 'IN');
						if ($this->parse_error) {
							return $this->parse_error;
						}
						$insertValues = array();
						foreach ($fieldNames as $k => $fN) {
							if (preg_match('/^[[:alnum:]_]+$/', $fN)) {
								if (isset($values[$k])) {
									if (!isset($insertValues[$fN])) {
										$insertValues[$fN] = $values[$k];
									} else {
328
										return $this->parseError('Fieldname ("' . $fN . '") already found in list!', $parseString);
329
330
331
332
333
									}
								} else {
									return $this->parseError('No value set!', $parseString);
								}
							} else {
334
								return $this->parseError('Invalid fieldname ("' . $fN . '")', $parseString);
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
							}
						}
						if (isset($values[$k + 1])) {
							return $this->parseError('Too many values in list!', $parseString);
						}
						$result['FIELDS'][] = $insertValues;
					} while ($this->nextPart($parseString, '^(,)') === ',');
					if (count($result['FIELDS']) === 1) {
						$result['FIELDS'] = $result['FIELDS'][0];
					} else {
						$result['EXTENDED'] = '1';
					}
				} else {
					return $this->parseError('VALUES keyword expected', $parseString);
				}
			}
		} else {
			return $this->parseError('No table found!', $parseString);
		}
		// Should be no more content now:
		if ($parseString) {
			return $this->parseError('Still content after parsing!', $parseString);
		}
		// Return result
		return $result;
	}

	/**
	 * Parsing DELETE query
	 *
	 * @param string $parseString SQL string with DELETE query to parse
	 * @return mixed Returns array with components of DELETE query on success, otherwise an error message string.
	 * @see compileDELETE()
	 */
	protected function parseDELETE($parseString) {
		// Removing DELETE
		$parseString = $this->trimSQL($parseString);
		$parseString = ltrim(substr(ltrim(substr($parseString, 6)), 4));
		// Init output variable:
		$result = array();
		$result['type'] = 'DELETE';
		// Get table:
		$result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
		if ($result['TABLE']) {
			// WHERE
			if ($this->nextPart($parseString, '^(WHERE)')) {
				$result['WHERE'] = $this->parseWhereClause($parseString);
				if ($this->parse_error) {
					return $this->parse_error;
				}
			}
		} else {
			return $this->parseError('No table found!', $parseString);
		}
		// Should be no more content now:
		if ($parseString) {
			return $this->parseError('Still content in clause after parsing!', $parseString);
		}
		// Return result:
		return $result;
	}

	/**
	 * Parsing EXPLAIN query
	 *
	 * @param string $parseString SQL string with EXPLAIN query to parse
	 * @return mixed Returns array with components of EXPLAIN query on success, otherwise an error message string.
	 * @see parseSELECT()
	 */
	protected function parseEXPLAIN($parseString) {
		// Removing EXPLAIN
		$parseString = $this->trimSQL($parseString);
		$parseString = ltrim(substr($parseString, 6));
		// Init output variable:
		$result = $this->parseSELECT($parseString);
		if (is_array($result)) {
			$result['type'] = 'EXPLAIN';
		}
		return $result;
	}

	/**
	 * Parsing CREATE TABLE query
	 *
	 * @param string $parseString SQL string starting with CREATE TABLE
	 * @return mixed Returns array with components of CREATE TABLE query on success, otherwise an error message string.
	 * @see compileCREATETABLE()
	 */
	protected function parseCREATETABLE($parseString) {
		// Removing CREATE TABLE
		$parseString = $this->trimSQL($parseString);
		$parseString = ltrim(substr(ltrim(substr($parseString, 6)), 5));
		// Init output variable:
		$result = array();
		$result['type'] = 'CREATETABLE';
		// Get table:
		$result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*\\(', TRUE);
		if ($result['TABLE']) {
			// While the parseString is not yet empty:
434
			while ($parseString !== '') {
435
436
				// Getting key
				if ($key = $this->nextPart($parseString, '^(KEY|PRIMARY KEY|UNIQUE KEY|UNIQUE)([[:space:]]+|\\()')) {
Andreas Fernandez's avatar
Andreas Fernandez committed
437
					$key = $this->normalizeKeyword($key);
438
					switch ($key) {
439
440
						case 'PRIMARYKEY':
							$result['KEYS']['PRIMARYKEY'] = $this->getValue($parseString, '_LIST');
441
442
443
							if ($this->parse_error) {
								return $this->parse_error;
							}
444
445
446
447
448
449
450
451
452
453
454
							break;
						case 'UNIQUE':

						case 'UNIQUEKEY':
							if ($keyName = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\\()')) {
								$result['KEYS']['UNIQUE'] = array($keyName => $this->getValue($parseString, '_LIST'));
								if ($this->parse_error) {
									return $this->parse_error;
								}
							} else {
								return $this->parseError('No keyname found', $parseString);
455
							}
456
457
458
459
460
461
462
463
464
465
466
							break;
						case 'KEY':
							if ($keyName = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\\()')) {
								$result['KEYS'][$keyName] = $this->getValue($parseString, '_LIST', 'INDEX');
								if ($this->parse_error) {
									return $this->parse_error;
								}
							} else {
								return $this->parseError('No keyname found', $parseString);
							}
							break;
467
468
469
470
471
472
473
474
475
476
477
478
					}
				} elseif ($fieldName = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) {
					// Getting field:
					$result['FIELDS'][$fieldName]['definition'] = $this->parseFieldDef($parseString);
					if ($this->parse_error) {
						return $this->parse_error;
					}
				}
				// Finding delimiter:
				$delim = $this->nextPart($parseString, '^(,|\\))');
				if (!$delim) {
					return $this->parseError('No delimiter found', $parseString);
Andreas Fernandez's avatar
Andreas Fernandez committed
479
				} elseif ($delim === ')') {
480
481
482
483
					break;
				}
			}
			// Finding what is after the table definition - table type in MySQL
Andreas Fernandez's avatar
Andreas Fernandez committed
484
			if ($delim === ')') {
485
				if ($this->nextPart($parseString, '^((ENGINE|TYPE)[[:space:]]*=)')) {
486
					$result['engine'] = $parseString;
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
					$parseString = '';
				}
			} else {
				return $this->parseError('No fieldname found!', $parseString);
			}
		} else {
			return $this->parseError('No table found!', $parseString);
		}
		// Should be no more content now:
		if ($parseString) {
			return $this->parseError('Still content in clause after parsing!', $parseString);
		}
		return $result;
	}

	/**
	 * Parsing ALTER TABLE query
	 *
	 * @param string $parseString SQL string starting with ALTER TABLE
	 * @return mixed Returns array with components of ALTER TABLE query on success, otherwise an error message string.
	 * @see compileALTERTABLE()
	 */
	protected function parseALTERTABLE($parseString) {
		// Removing ALTER TABLE
		$parseString = $this->trimSQL($parseString);
		$parseString = ltrim(substr(ltrim(substr($parseString, 5)), 5));
		// Init output variable:
		$result = array();
		$result['type'] = 'ALTERTABLE';
		// Get table:
		$hasBackquote = $this->nextPart($parseString, '^(`)') === '`';
518
		$result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)' . ($hasBackquote ? '`' : '') . '[[:space:]]+');
519
520
521
522
523
		if ($hasBackquote && $this->nextPart($parseString, '^(`)') !== '`') {
			return $this->parseError('No end backquote found!', $parseString);
		}
		if ($result['TABLE']) {
			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:]]+|\\(|=)')) {
Andreas Fernandez's avatar
Andreas Fernandez committed
524
				$actionKey = $this->normalizeKeyword($result['action']);
525
526
527
				// Getting field:
				if (\TYPO3\CMS\Core\Utility\GeneralUtility::inList('ADDPRIMARYKEY,DROPPRIMARYKEY,ENGINE', $actionKey) || ($fieldKey = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+'))) {
					switch ($actionKey) {
528
529
						case 'ADD':
							$result['FIELD'] = $fieldKey;
530
531
532
533
							$result['definition'] = $this->parseFieldDef($parseString);
							if ($this->parse_error) {
								return $this->parse_error;
							}
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
							break;
						case 'DROP':
						case 'RENAME':
							$result['FIELD'] = $fieldKey;
							break;
						case 'CHANGE':
							$result['FIELD'] = $fieldKey;
							if ($result['newField'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) {
								$result['definition'] = $this->parseFieldDef($parseString);
								if ($this->parse_error) {
									return $this->parse_error;
								}
							} else {
								return $this->parseError('No NEW field name found', $parseString);
							}
							break;
						case 'ADDKEY':
						case 'ADDPRIMARYKEY':
						case 'ADDUNIQUE':
							$result['KEY'] = $fieldKey;
							$result['fields'] = $this->getValue($parseString, '_LIST', 'INDEX');
							if ($this->parse_error) {
								return $this->parse_error;
							}
							break;
						case 'DROPKEY':
							$result['KEY'] = $fieldKey;
							break;
						case 'DROPPRIMARYKEY':
563
							// @todo ???
564
565
566
567
568
569
570
							break;
						case 'DEFAULTCHARACTERSET':
							$result['charset'] = $fieldKey;
							break;
						case 'ENGINE':
							$result['engine'] = $this->nextPart($parseString, '^=[[:space:]]*([[:alnum:]]+)[[:space:]]+', TRUE);
							break;
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
					}
				} else {
					return $this->parseError('No field name found', $parseString);
				}
			} else {
				return $this->parseError('No action CHANGE, DROP or ADD found!', $parseString);
			}
		} else {
			return $this->parseError('No table found!', $parseString);
		}
		// Should be no more content now:
		if ($parseString) {
			return $this->parseError('Still content in clause after parsing!', $parseString);
		}
		return $result;
	}

	/**
	 * Parsing DROP TABLE query
	 *
	 * @param string $parseString SQL string starting with DROP TABLE
	 * @return mixed Returns array with components of DROP TABLE query on success, otherwise an error message string.
	 */
	protected function parseDROPTABLE($parseString) {
		// Removing DROP TABLE
		$parseString = $this->trimSQL($parseString);
		$parseString = ltrim(substr(ltrim(substr($parseString, 4)), 5));
		// Init output variable:
		$result = array();
		$result['type'] = 'DROPTABLE';
		// IF EXISTS
		$result['ifExists'] = $this->nextPart($parseString, '^(IF[[:space:]]+EXISTS[[:space:]]+)');
		// Get table:
		$result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
		if ($result['TABLE']) {
			// Should be no more content now:
			if ($parseString) {
				return $this->parseError('Still content in clause after parsing!', $parseString);
			}
			return $result;
		} else {
			return $this->parseError('No table found!', $parseString);
		}
	}

	/**
	 * Parsing CREATE DATABASE query
	 *
	 * @param string $parseString SQL string starting with CREATE DATABASE
	 * @return mixed Returns array with components of CREATE DATABASE query on success, otherwise an error message string.
	 */
	protected function parseCREATEDATABASE($parseString) {
		// Removing CREATE DATABASE
		$parseString = $this->trimSQL($parseString);
		$parseString = ltrim(substr(ltrim(substr($parseString, 6)), 8));
		// Init output variable:
		$result = array();
		$result['type'] = 'CREATEDATABASE';
		// Get table:
		$result['DATABASE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
		if ($result['DATABASE']) {
			// Should be no more content now:
			if ($parseString) {
				return $this->parseError('Still content in clause after parsing!', $parseString);
			}
			return $result;
		} else {
			return $this->parseError('No database found!', $parseString);
		}
	}

	/**
	 * Parsing TRUNCATE TABLE query
	 *
	 * @param string $parseString SQL string starting with TRUNCATE TABLE
	 * @return mixed Returns array with components of TRUNCATE TABLE query on success, otherwise an error message string.
	 */
	protected function parseTRUNCATETABLE($parseString) {
		// Removing TRUNCATE TABLE
		$parseString = $this->trimSQL($parseString);
		$parseString = ltrim(substr(ltrim(substr($parseString, 8)), 5));
		// Init output variable:
		$result = array();
		$result['type'] = 'TRUNCATETABLE';
		// Get table:
		$result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
		if ($result['TABLE']) {
			// Should be no more content now:
			if ($parseString) {
				return $this->parseError('Still content in clause after parsing!', $parseString);
			}
			return $result;
		} else {
			return $this->parseError('No table found!', $parseString);
		}
	}

	/**************************************
	 *
	 * SQL Parsing, helper functions for parts of queries
	 *
	 **************************************/
	/**
	 * Parsing the fields in the "SELECT [$selectFields] FROM" part of a query into an array.
	 * The output from this function can be compiled back into a field list with ->compileFieldList()
	 * 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!
	 *
	 * @param string $parseString The string with fieldnames, eg. "title, uid AS myUid, max(tstamp), count(*)" etc. NOTICE: passed by reference!
	 * @param string $stopRegex Regular expressing to STOP parsing, eg. '^(FROM)([[:space:]]*)'
	 * @return array If successful parsing, returns an array, otherwise an error string.
	 * @see compileFieldList()
	 */
	public function parseFieldList(&$parseString, $stopRegex = '') {
		$stack = array();
		// Contains the parsed content
686
		if ($parseString === '') {
687
688
			return $stack;
		}
689
		// @todo - should never happen, why does it?
690
691
692
693
694
695
696
697
698
699
700
701
702
		// Pointer to positions in $stack
		$pnt = 0;
		// Indicates the parenthesis level we are at.
		$level = 0;
		// Recursivity brake.
		$loopExit = 0;
		// Prepare variables:
		$parseString = $this->trimSQL($parseString);
		$this->lastStopKeyWord = '';
		$this->parse_error = '';
		// Parse any SQL hint / comments
		$stack[$pnt]['comments'] = $this->nextPart($parseString, '^(\\/\\*.*\\*\\/)');
		// $parseString is continuously shortened by the process and we keep parsing it till it is zero:
703
		while ($parseString !== '') {
704
705
706
707
708
709
710
711
712
713
714
715
			// Checking if we are inside / outside parenthesis (in case of a function like count(), max(), min() etc...):
			// Inside parenthesis here (does NOT detect if values in quotes are used, the only token is ")" or "("):
			if ($level > 0) {
				// Accumulate function content until next () parenthesis:
				$funcContent = $this->nextPart($parseString, '^([^()]*.)');
				$stack[$pnt]['func_content.'][] = array(
					'level' => $level,
					'func_content' => substr($funcContent, 0, -1)
				);
				$stack[$pnt]['func_content'] .= $funcContent;
				// Detecting ( or )
				switch (substr($stack[$pnt]['func_content'], -1)) {
716
717
718
719
720
721
722
723
724
725
726
727
					case '(':
						$level++;
						break;
					case ')':
						$level--;
						// If this was the last parenthesis:
						if (!$level) {
							$stack[$pnt]['func_content'] = substr($stack[$pnt]['func_content'], 0, -1);
							// Remove any whitespace after the parenthesis.
							$parseString = ltrim($parseString);
						}
						break;
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
				}
			} else {
				// Outside parenthesis, looking for next field:
				// Looking for a flow-control construct (only known constructs supported)
				if (preg_match('/^case([[:space:]][[:alnum:]\\*._]+)?[[:space:]]when/i', $parseString)) {
					$stack[$pnt]['type'] = 'flow-control';
					$stack[$pnt]['flow-control'] = $this->parseCaseStatement($parseString);
					// Looking for "AS" alias:
					if ($as = $this->nextPart($parseString, '^(AS)[[:space:]]+')) {
						$stack[$pnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)(,|[[:space:]]+)');
						$stack[$pnt]['as_keyword'] = $as;
					}
				} else {
					// Looking for a known function (only known functions supported)
					$func = $this->nextPart($parseString, '^(count|max|min|floor|sum|avg)[[:space:]]*\\(');
					if ($func) {
						// Strip of "("
						$parseString = trim(substr($parseString, 1));
						$stack[$pnt]['type'] = 'function';
						$stack[$pnt]['function'] = $func;
						// increse parenthesis level counter.
						$level++;
					} else {
						$stack[$pnt]['distinct'] = $this->nextPart($parseString, '^(distinct[[:space:]]+)');
						// Otherwise, look for regular fieldname:
						if (($fieldName = $this->nextPart($parseString, '^([[:alnum:]\\*._]+)(,|[[:space:]]+)')) !== '') {
							$stack[$pnt]['type'] = 'field';
							// Explode fieldname into field and table:
							$tableField = explode('.', $fieldName, 2);
Andreas Fernandez's avatar
Andreas Fernandez committed
757
							if (count($tableField) === 2) {
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
								$stack[$pnt]['table'] = $tableField[0];
								$stack[$pnt]['field'] = $tableField[1];
							} else {
								$stack[$pnt]['table'] = '';
								$stack[$pnt]['field'] = $tableField[0];
							}
						} else {
							return $this->parseError('No field name found as expected in parseFieldList()', $parseString);
						}
					}
				}
			}
			// After a function or field we look for "AS" alias and a comma to separate to the next field in the list:
			if (!$level) {
				// Looking for "AS" alias:
				if ($as = $this->nextPart($parseString, '^(AS)[[:space:]]+')) {
					$stack[$pnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)(,|[[:space:]]+)');
					$stack[$pnt]['as_keyword'] = $as;
				}
				// Looking for "ASC" or "DESC" keywords (for ORDER BY)
				if ($sDir = $this->nextPart($parseString, '^(ASC|DESC)([[:space:]]+|,)')) {
					$stack[$pnt]['sortDir'] = $sDir;
				}
				// Looking for stop-keywords:
				if ($stopRegex && ($this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))) {
Andreas Fernandez's avatar
Andreas Fernandez committed
783
					$this->lastStopKeyWord = $this->normalizeKeyword($this->lastStopKeyWord);
784
785
786
					return $stack;
				}
				// Looking for comma (since the stop-keyword did not trigger a return...)
787
				if ($parseString !== '' && !$this->nextPart($parseString, '^(,)')) {
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
					return $this->parseError('No comma found as expected in parseFieldList()', $parseString);
				}
				// Increasing pointer:
				$pnt++;
			}
			// Check recursivity brake:
			$loopExit++;
			if ($loopExit > 500) {
				return $this->parseError('More than 500 loops, exiting prematurely in parseFieldList()...', $parseString);
			}
		}
		// Return result array:
		return $stack;
	}

	/**
	 * Parsing a CASE ... WHEN flow-control construct.
	 * The output from this function can be compiled back with ->compileCaseStatement()
	 *
	 * @param string $parseString The string with the CASE ... WHEN construct, eg. "CASE field WHEN 1 THEN 0 ELSE ..." etc. NOTICE: passed by reference!
	 * @return array If successful parsing, returns an array, otherwise an error string.
	 * @see compileCaseConstruct()
	 */
	protected function parseCaseStatement(&$parseString) {
		$result = array();
		$result['type'] = $this->nextPart($parseString, '^(case)[[:space:]]+');
		if (!preg_match('/^when[[:space:]]+/i', $parseString)) {
			$value = $this->getValue($parseString);
			if (!(isset($value[1]) || is_numeric($value[0]))) {
				$result['case_field'] = $value[0];
			} else {
				$result['case_value'] = $value;
			}
		}
		$result['when'] = array();
		while ($this->nextPart($parseString, '^(when)[[:space:]]')) {
			$when = array();
			$when['when_value'] = $this->parseWhereClause($parseString, '^(then)[[:space:]]+');
			$when['then_value'] = $this->getValue($parseString);
			$result['when'][] = $when;
		}
		if ($this->nextPart($parseString, '^(else)[[:space:]]+')) {
			$result['else'] = $this->getValue($parseString);
		}
		if (!$this->nextPart($parseString, '^(end)[[:space:]]+')) {
			return $this->parseError('No "end" keyword found as expected in parseCaseStatement()', $parseString);
		}
		return $result;
	}

	/**
	 * Parsing the tablenames in the "FROM [$parseString] WHERE" part of a query into an array.
	 * The success of this parsing determines if that part of the query is supported by TYPO3.
	 *
	 * @param string $parseString List of tables, eg. "pages, tt_content" or "pages A, pages B". NOTICE: passed by reference!
	 * @param string $stopRegex Regular expressing to STOP parsing, eg. '^(WHERE)([[:space:]]*)'
	 * @return array If successful parsing, returns an array, otherwise an error string.
	 * @see compileFromTables()
	 */
	public function parseFromTables(&$parseString, $stopRegex = '') {
		// Prepare variables:
		$parseString = $this->trimSQL($parseString);
		$this->lastStopKeyWord = '';
		$this->parse_error = '';
		// Contains the parsed content
		$stack = array();
		// Pointer to positions in $stack
		$pnt = 0;
		// Recursivity brake.
		$loopExit = 0;
		// $parseString is continously shortend by the process and we keep parsing it till it is zero:
859
		while ($parseString !== '') {
860
861
862
863
			// Looking for the table:
			if ($stack[$pnt]['table'] = $this->nextPart($parseString, '^([[:alnum:]_]+)(,|[[:space:]]+)')) {
				// Looking for stop-keywords before fetching potential table alias:
				if ($stopRegex && ($this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))) {
Andreas Fernandez's avatar
Andreas Fernandez committed
864
					$this->lastStopKeyWord = $this->normalizeKeyword($this->lastStopKeyWord);
865
866
867
868
869
870
871
872
873
874
875
					return $stack;
				}
				if (!preg_match('/^(LEFT|RIGHT|JOIN|INNER)[[:space:]]+/i', $parseString)) {
					$stack[$pnt]['as_keyword'] = $this->nextPart($parseString, '^(AS[[:space:]]+)');
					$stack[$pnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*');
				}
			} else {
				return $this->parseError('No table name found as expected in parseFromTables()!', $parseString);
			}
			// Looking for JOIN
			$joinCnt = 0;
Andreas Fernandez's avatar
Andreas Fernandez committed
876
			while ($join = $this->nextPart($parseString, '^(((INNER|(LEFT|RIGHT)([[:space:]]+OUTER)?)[[:space:]]+)?JOIN)[[:space:]]+')) {
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
				$stack[$pnt]['JOIN'][$joinCnt]['type'] = $join;
				if ($stack[$pnt]['JOIN'][$joinCnt]['withTable'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+', 1)) {
					if (!preg_match('/^ON[[:space:]]+/i', $parseString)) {
						$stack[$pnt]['JOIN'][$joinCnt]['as_keyword'] = $this->nextPart($parseString, '^(AS[[:space:]]+)');
						$stack[$pnt]['JOIN'][$joinCnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
					}
					if (!$this->nextPart($parseString, '^(ON[[:space:]]+)')) {
						return $this->parseError('No join condition found in parseFromTables()!', $parseString);
					}
					$stack[$pnt]['JOIN'][$joinCnt]['ON'] = array();
					$condition = array('operator' => '');
					$parseCondition = TRUE;
					while ($parseCondition) {
						if (($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)[[:space:]]*(<=|>=|<|>|=|!=)')) !== '') {
							// Parse field name into field and table:
							$tableField = explode('.', $fieldName, 2);
							$condition['left'] = array();
Andreas Fernandez's avatar
Andreas Fernandez committed
894
							if (count($tableField) === 2) {
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
								$condition['left']['table'] = $tableField[0];
								$condition['left']['field'] = $tableField[1];
							} else {
								$condition['left']['table'] = '';
								$condition['left']['field'] = $tableField[0];
							}
						} else {
							return $this->parseError('No join field found in parseFromTables()!', $parseString);
						}
						// Find "comparator":
						$condition['comparator'] = $this->nextPart($parseString, '^(<=|>=|<|>|=|!=)');
						if (($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)')) !== '') {
							// Parse field name into field and table:
							$tableField = explode('.', $fieldName, 2);
							$condition['right'] = array();
Andreas Fernandez's avatar
Andreas Fernandez committed
910
							if (count($tableField) === 2) {
911
912
913
914
915
916
								$condition['right']['table'] = $tableField[0];
								$condition['right']['field'] = $tableField[1];
							} else {
								$condition['right']['table'] = '';
								$condition['right']['field'] = $tableField[0];
							}
917
918
						} elseif ($value = $this->getValue($parseString)) {
							$condition['right']['value'] = $value;
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
						} else {
							return $this->parseError('No join field found in parseFromTables()!', $parseString);
						}
						$stack[$pnt]['JOIN'][$joinCnt]['ON'][] = $condition;
						if (($operator = $this->nextPart($parseString, '^(AND|OR)')) !== '') {
							$condition = array('operator' => $operator);
						} else {
							$parseCondition = FALSE;
						}
					}
					$joinCnt++;
				} else {
					return $this->parseError('No join table found in parseFromTables()!', $parseString);
				}
			}
			// Looking for stop-keywords:
			if ($stopRegex && ($this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))) {
Andreas Fernandez's avatar
Andreas Fernandez committed
936
				$this->lastStopKeyWord = $this->normalizeKeyword($this->lastStopKeyWord);
937
938
939
				return $stack;
			}
			// Looking for comma:
940
			if ($parseString !== '' && !$this->nextPart($parseString, '^(,)')) {
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
				return $this->parseError('No comma found as expected in parseFromTables()', $parseString);
			}
			// Increasing pointer:
			$pnt++;
			// Check recursivity brake:
			$loopExit++;
			if ($loopExit > 500) {
				return $this->parseError('More than 500 loops, exiting prematurely in parseFromTables()...', $parseString);
			}
		}
		// Return result array:
		return $stack;
	}

	/**
	 * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array.
	 * The success of this parsing determines if that part of the query is supported by TYPO3.
	 *
	 * @param string $parseString WHERE clause to parse. NOTICE: passed by reference!
	 * @param string $stopRegex Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)'
	 * @param array $parameterReferences Array holding references to either named (:name) or question mark (?) parameters found
	 * @return mixed If successful parsing, returns an array, otherwise an error string.
	 */
	public function parseWhereClause(&$parseString, $stopRegex = '', array &$parameterReferences = array()) {
		// Prepare variables:
		$parseString = $this->trimSQL($parseString);
		$this->lastStopKeyWord = '';
		$this->parse_error = '';
		// Contains the parsed content
		$stack = array(0 => array());
		// Pointer to positions in $stack
		$pnt = array(0 => 0);
		// Determines parenthesis level
		$level = 0;
		// Recursivity brake.
		$loopExit = 0;
Markus Klein's avatar
Markus Klein committed
977
		// $parseString is continuously shortened by the process and we keep parsing it till it is zero:
978
		while ($parseString !== '') {
979
980
981
			// Look for next parenthesis level:
			$newLevel = $this->nextPart($parseString, '^([(])');
			// If new level is started, manage stack/pointers:
Andreas Fernandez's avatar
Andreas Fernandez committed
982
			if ($newLevel === '(') {
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
				// Increase level
				$level++;
				// Reset pointer for this level
				$pnt[$level] = 0;
				// Reset stack for this level
				$stack[$level] = array();
			} else {
				// If no new level is started, just parse the current level:
				// Find "modifier", eg. "NOT or !"
				$stack[$level][$pnt[$level]]['modifier'] = trim($this->nextPart($parseString, '^(!|NOT[[:space:]]+)'));
				// See if condition is EXISTS with a subquery
				if (preg_match('/^EXISTS[[:space:]]*[(]/i', $parseString)) {
					$stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(EXISTS)[[:space:]]*');
					// Strip of "("
					$parseString = trim(substr($parseString, 1));
					$stack[$level][$pnt[$level]]['func']['subquery'] = $this->parseSELECT($parseString, $parameterReferences);
					// Seek to new position in parseString after parsing of the subquery
					$parseString = $stack[$level][$pnt[$level]]['func']['subquery']['parseString'];
For faster browsing, not all history is shown. View entire blame