TYPO3 CMS  TYPO3_7-6
SqlParser.php
Go to the documentation of this file.
1 <?php
2 namespace TYPO3\CMS\Dbal\Database;
3 
4 /*
5  * This file is part of the TYPO3 CMS project.
6  *
7  * It is free software; you can redistribute it and/or modify it under
8  * the terms of the GNU General Public License, either version 2
9  * of the License, or any later version.
10  *
11  * For the full copyright and license information, please read the
12  * LICENSE.txt file that was distributed with this source code.
13  *
14  * The TYPO3 project - inspiring people to share!
15  */
16 
17 use TYPO3\CMS\Core\Utility\GeneralUtility;
18 
22 class SqlParser
23 {
29  public $parse_error = '';
30 
36  public $lastStopKeyWord = '';
37 
43  protected static $comparatorPatterns = [
44  '<=',
45  '>=',
46  '<>',
47  '<',
48  '>',
49  '=',
50  '!=',
51  'NOT[[:space:]]+IN',
52  'IN',
53  'NOT[[:space:]]+LIKE[[:space:]]+BINARY',
54  'LIKE[[:space:]]+BINARY',
55  'NOT[[:space:]]+LIKE',
56  'LIKE',
57  'IS[[:space:]]+NOT',
58  'IS',
59  'BETWEEN',
60  'NOT[[:space]]+BETWEEN'
61  ];
62 
68  protected static $interQueryWhitespaces = [' ', TAB, CR, LF];
69 
73  protected $databaseConnection;
74 
78  protected $nativeSqlCompiler;
79 
83  protected $sqlCompiler;
84 
88  public function __construct(DatabaseConnection $databaseConnection = null)
89  {
90  $this->databaseConnection = $databaseConnection ?: $GLOBALS['TYPO3_DB'];
91  $this->sqlCompiler = GeneralUtility::makeInstance(SqlCompilers\Adodb::class, $this->databaseConnection);
92  $this->nativeSqlCompiler = GeneralUtility::makeInstance(SqlCompilers\Mysql::class, $this->databaseConnection);
93  }
94 
102  protected function getValueInQuotes(&$parseString, $quote)
103  {
104  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
105  case 'adodb':
106  if ($this->databaseConnection->runningADOdbDriver('mssql')) {
107  $value = $this->getValueInQuotesMssql($parseString, $quote);
108  } else {
109  $value = $this->getValueInQuotesGeneric($parseString, $quote);
110  }
111  break;
112  default:
113  $value = $this->getValueInQuotesGeneric($parseString, $quote);
114  }
115  return $value;
116  }
117 
126  protected function getValueInQuotesGeneric(&$parseString, $quote)
127  {
128  $parts = explode($quote, substr($parseString, 1));
129  $buffer = '';
130  foreach ($parts as $k => $v) {
131  $buffer .= $v;
132  $reg = [];
133  preg_match('/\\\\$/', $v, $reg);
134  if ($reg && strlen($reg[0]) % 2) {
135  $buffer .= $quote;
136  } else {
137  $parseString = ltrim(substr($parseString, strlen($buffer) + 2));
138  return $this->parseStripslashes($buffer);
139  }
140  }
141  }
142 
150  protected function getValueInQuotesMssql(&$parseString, $quote)
151  {
152  $previousIsQuote = false;
153  $inQuote = false;
154  // Go through the whole string
155  for ($c = 0; $c < strlen($parseString); $c++) {
156  // If the parsed string character is the quote string
157  if ($parseString[$c] === $quote) {
158  // If we are already in a quote
159  if ($inQuote) {
160  // Was the previous a quote?
161  if ($previousIsQuote) {
162  // If yes, replace it by a \
163  $parseString[$c - 1] = '\\';
164  }
165  // Invert the state
166  $previousIsQuote = !$previousIsQuote;
167  } else {
168  // So we are in a quote since now
169  $inQuote = true;
170  }
171  } elseif ($inQuote && $previousIsQuote) {
172  $inQuote = false;
173  $previousIsQuote = false;
174  } else {
175  $previousIsQuote = false;
176  }
177  }
178  $parts = explode($quote, substr($parseString, 1));
179  $buffer = '';
180  foreach ($parts as $v) {
181  $buffer .= $v;
182  $reg = [];
183  preg_match('/\\\\$/', $v, $reg);
184  if ($reg && strlen($reg[0]) % 2) {
185  $buffer .= $quote;
186  } else {
187  $parseString = ltrim(substr($parseString, strlen($buffer) + 2));
188  return $this->parseStripslashes($buffer);
189  }
190  }
191  return '';
192  }
193 
194  /*************************************
195  *
196  * SQL Parsing, full queries
197  *
198  **************************************/
206  public function parseSQL($parseString)
207  {
208  // Prepare variables:
209  $parseString = $this->trimSQL($parseString);
210  $this->parse_error = '';
211  $result = [];
212  // Finding starting keyword of string:
213  $_parseString = $parseString;
214  // Protecting original string...
215  $keyword = $this->nextPart($_parseString, '^(SELECT|UPDATE|INSERT[[:space:]]+INTO|DELETE[[:space:]]+FROM|EXPLAIN|(DROP|CREATE|ALTER|TRUNCATE)[[:space:]]+TABLE|CREATE[[:space:]]+DATABASE)[[:space:]]+');
216  $keyword = $this->normalizeKeyword($keyword);
217  switch ($keyword) {
218  case 'SELECT':
219  // Parsing SELECT query:
220  $result = $this->parseSELECT($parseString);
221  break;
222  case 'UPDATE':
223  // Parsing UPDATE query:
224  $result = $this->parseUPDATE($parseString);
225  break;
226  case 'INSERTINTO':
227  // Parsing INSERT query:
228  $result = $this->parseINSERT($parseString);
229  break;
230  case 'DELETEFROM':
231  // Parsing DELETE query:
232  $result = $this->parseDELETE($parseString);
233  break;
234  case 'EXPLAIN':
235  // Parsing EXPLAIN SELECT query:
236  $result = $this->parseEXPLAIN($parseString);
237  break;
238  case 'DROPTABLE':
239  // Parsing DROP TABLE query:
240  $result = $this->parseDROPTABLE($parseString);
241  break;
242  case 'ALTERTABLE':
243  // Parsing ALTER TABLE query:
244  $result = $this->parseALTERTABLE($parseString);
245  break;
246  case 'CREATETABLE':
247  // Parsing CREATE TABLE query:
248  $result = $this->parseCREATETABLE($parseString);
249  break;
250  case 'CREATEDATABASE':
251  // Parsing CREATE DATABASE query:
252  $result = $this->parseCREATEDATABASE($parseString);
253  break;
254  case 'TRUNCATETABLE':
255  // Parsing TRUNCATE TABLE query:
256  $result = $this->parseTRUNCATETABLE($parseString);
257  break;
258  default:
259  $result = $this->parseError('"' . $keyword . '" is not a keyword', $parseString);
260  }
261  return $result;
262  }
263 
272  protected function parseSELECT($parseString, &$parameterReferences = null)
273  {
274  // Removing SELECT:
275  $parseString = $this->trimSQL($parseString);
276  $parseString = ltrim(substr($parseString, 6));
277  // Init output variable:
278  $result = [];
279  if ($parameterReferences === null) {
280  $result['parameters'] = [];
281  $parameterReferences = &$result['parameters'];
282  }
283  $result['type'] = 'SELECT';
284  // Looking for STRAIGHT_JOIN keyword:
285  $result['STRAIGHT_JOIN'] = $this->nextPart($parseString, '^(STRAIGHT_JOIN)[[:space:]]+');
286  // Select fields:
287  $result['SELECT'] = $this->parseFieldList($parseString, '^(FROM)[[:space:]]+');
288  if ($this->parse_error) {
289  return $this->parse_error;
290  }
291  // Continue if string is not ended:
292  if ($parseString) {
293  // Get table list:
294  $result['FROM'] = $this->parseFromTables($parseString, '^(WHERE)[[:space:]]+');
295  if ($this->parse_error) {
296  return $this->parse_error;
297  }
298  // If there are more than just the tables (a WHERE clause that would be...)
299  if ($parseString) {
300  // Get WHERE clause:
301  $result['WHERE'] = $this->parseWhereClause($parseString, '^((GROUP|ORDER)[[:space:]]+BY|LIMIT)[[:space:]]+', $parameterReferences);
302  if ($this->parse_error) {
303  return $this->parse_error;
304  }
305  // If the WHERE clause parsing was stopped by GROUP BY, ORDER BY or LIMIT, then proceed with parsing:
306  if ($this->lastStopKeyWord) {
307  // GROUP BY parsing:
308  if ($this->lastStopKeyWord === 'GROUPBY') {
309  $result['GROUPBY'] = $this->parseFieldList($parseString, '^(ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
310  if ($this->parse_error) {
311  return $this->parse_error;
312  }
313  }
314  // ORDER BY parsing:
315  if ($this->lastStopKeyWord === 'ORDERBY') {
316  $result['ORDERBY'] = $this->parseFieldList($parseString, '^(LIMIT)[[:space:]]+');
317  if ($this->parse_error) {
318  return $this->parse_error;
319  }
320  }
321  // LIMIT parsing:
322  if ($this->lastStopKeyWord === 'LIMIT') {
323  if (preg_match('/^([0-9]+|[0-9]+[[:space:]]*,[[:space:]]*[0-9]+)$/', trim($parseString))) {
324  $result['LIMIT'] = $parseString;
325  } else {
326  return $this->parseError('No value for limit!', $parseString);
327  }
328  }
329  }
330  }
331  } else {
332  return $this->parseError('No table to select from!', $parseString);
333  }
334  // Store current parseString in the result array for possible further processing (e.g., subquery support by DBAL)
335  $result['parseString'] = $parseString;
336  // Return result:
337  return $result;
338  }
339 
347  protected function parseUPDATE($parseString)
348  {
349  // Removing UPDATE
350  $parseString = $this->trimSQL($parseString);
351  $parseString = ltrim(substr($parseString, 6));
352  // Init output variable:
353  $result = [];
354  $result['type'] = 'UPDATE';
355  // Get table:
356  $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
357  // Continue if string is not ended:
358  if ($result['TABLE']) {
359  if ($parseString && $this->nextPart($parseString, '^(SET)[[:space:]]+')) {
360  $comma = true;
361  // Get field/value pairs:
362  while ($comma) {
363  if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*=')) {
364  // Strip off "=" sign.
365  $this->nextPart($parseString, '^(=)');
366  $value = $this->getValue($parseString);
367  $result['FIELDS'][$fieldName] = $value;
368  } else {
369  return $this->parseError('No fieldname found', $parseString);
370  }
371  $comma = $this->nextPart($parseString, '^(,)');
372  }
373  // WHERE
374  if ($this->nextPart($parseString, '^(WHERE)')) {
375  $result['WHERE'] = $this->parseWhereClause($parseString);
376  if ($this->parse_error) {
377  return $this->parse_error;
378  }
379  }
380  } else {
381  return $this->parseError('Query missing SET...', $parseString);
382  }
383  } else {
384  return $this->parseError('No table found!', $parseString);
385  }
386  // Should be no more content now:
387  if ($parseString) {
388  return $this->parseError('Still content in clause after parsing!', $parseString);
389  }
390  // Return result:
391  return $result;
392  }
393 
401  protected function parseINSERT($parseString)
402  {
403  // Removing INSERT
404  $parseString = $this->trimSQL($parseString);
405  $parseString = ltrim(substr(ltrim(substr($parseString, 6)), 4));
406  // Init output variable:
407  $result = [];
408  $result['type'] = 'INSERT';
409  // Get table:
410  $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\\()');
411  if ($result['TABLE']) {
412  // In this case there are no field names mentioned in the SQL!
413  if ($this->nextPart($parseString, '^(VALUES)([[:space:]]+|\\()')) {
414  // Get values/fieldnames (depending...)
415  $result['VALUES_ONLY'] = $this->getValue($parseString, 'IN');
416  if ($this->parse_error) {
417  return $this->parse_error;
418  }
419  if (preg_match('/^,/', $parseString)) {
420  $result['VALUES_ONLY'] = [$result['VALUES_ONLY']];
421  $result['EXTENDED'] = '1';
422  while ($this->nextPart($parseString, '^(,)') === ',') {
423  $result['VALUES_ONLY'][] = $this->getValue($parseString, 'IN');
424  if ($this->parse_error) {
425  return $this->parse_error;
426  }
427  }
428  }
429  } else {
430  // There are apparently fieldnames listed:
431  $fieldNames = $this->getValue($parseString, '_LIST');
432  if ($this->parse_error) {
433  return $this->parse_error;
434  }
435  // "VALUES" keyword binds the fieldnames to values:
436  if ($this->nextPart($parseString, '^(VALUES)([[:space:]]+|\\()')) {
437  $result['FIELDS'] = [];
438  do {
439  // Using the "getValue" function to get the field list...
440  $values = $this->getValue($parseString, 'IN');
441  if ($this->parse_error) {
442  return $this->parse_error;
443  }
444  $insertValues = [];
445  foreach ($fieldNames as $k => $fN) {
446  if (preg_match('/^[[:alnum:]_]+$/', $fN)) {
447  if (isset($values[$k])) {
448  if (!isset($insertValues[$fN])) {
449  $insertValues[$fN] = $values[$k];
450  } else {
451  return $this->parseError('Fieldname ("' . $fN . '") already found in list!', $parseString);
452  }
453  } else {
454  return $this->parseError('No value set!', $parseString);
455  }
456  } else {
457  return $this->parseError('Invalid fieldname ("' . $fN . '")', $parseString);
458  }
459  }
460  if (isset($values[$k + 1])) {
461  return $this->parseError('Too many values in list!', $parseString);
462  }
463  $result['FIELDS'][] = $insertValues;
464  } while ($this->nextPart($parseString, '^(,)') === ',');
465  if (count($result['FIELDS']) === 1) {
466  $result['FIELDS'] = $result['FIELDS'][0];
467  } else {
468  $result['EXTENDED'] = '1';
469  }
470  } else {
471  return $this->parseError('VALUES keyword expected', $parseString);
472  }
473  }
474  } else {
475  return $this->parseError('No table found!', $parseString);
476  }
477  // Should be no more content now:
478  if ($parseString) {
479  return $this->parseError('Still content after parsing!', $parseString);
480  }
481  // Return result
482  return $result;
483  }
484 
492  protected function parseDELETE($parseString)
493  {
494  // Removing DELETE
495  $parseString = $this->trimSQL($parseString);
496  $parseString = ltrim(substr(ltrim(substr($parseString, 6)), 4));
497  // Init output variable:
498  $result = [];
499  $result['type'] = 'DELETE';
500  // Get table:
501  $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
502  if ($result['TABLE']) {
503  // WHERE
504  if ($this->nextPart($parseString, '^(WHERE)')) {
505  $result['WHERE'] = $this->parseWhereClause($parseString);
506  if ($this->parse_error) {
507  return $this->parse_error;
508  }
509  }
510  } else {
511  return $this->parseError('No table found!', $parseString);
512  }
513  // Should be no more content now:
514  if ($parseString) {
515  return $this->parseError('Still content in clause after parsing!', $parseString);
516  }
517  // Return result:
518  return $result;
519  }
520 
528  protected function parseEXPLAIN($parseString)
529  {
530  // Removing EXPLAIN
531  $parseString = $this->trimSQL($parseString);
532  $parseString = ltrim(substr($parseString, 6));
533  // Init output variable:
534  $result = $this->parseSELECT($parseString);
535  if (is_array($result)) {
536  $result['type'] = 'EXPLAIN';
537  }
538  return $result;
539  }
540 
548  protected function parseCREATETABLE($parseString)
549  {
550  // Removing CREATE TABLE
551  $parseString = $this->trimSQL($parseString);
552  $parseString = ltrim(substr(ltrim(substr($parseString, 6)), 5));
553  // Init output variable:
554  $result = [];
555  $result['type'] = 'CREATETABLE';
556  // Get table:
557  $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*\\(', true);
558  if ($result['TABLE']) {
559  // While the parseString is not yet empty:
560  while ($parseString !== '') {
561  // Getting key
562  if ($key = $this->nextPart($parseString, '^(KEY|PRIMARY KEY|UNIQUE KEY|UNIQUE)([[:space:]]+|\\()')) {
563  $key = $this->normalizeKeyword($key);
564  switch ($key) {
565  case 'PRIMARYKEY':
566  $result['KEYS']['PRIMARYKEY'] = $this->getValue($parseString, '_LIST');
567  if ($this->parse_error) {
568  return $this->parse_error;
569  }
570  break;
571  case 'UNIQUE':
572 
573  case 'UNIQUEKEY':
574  if ($keyName = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\\()')) {
575  $result['KEYS']['UNIQUE'] = [$keyName => $this->getValue($parseString, '_LIST')];
576  if ($this->parse_error) {
577  return $this->parse_error;
578  }
579  } else {
580  return $this->parseError('No keyname found', $parseString);
581  }
582  break;
583  case 'KEY':
584  if ($keyName = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\\()')) {
585  $result['KEYS'][$keyName] = $this->getValue($parseString, '_LIST', 'INDEX');
586  if ($this->parse_error) {
587  return $this->parse_error;
588  }
589  } else {
590  return $this->parseError('No keyname found', $parseString);
591  }
592  break;
593  }
594  } elseif ($fieldName = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) {
595  // Getting field:
596  $result['FIELDS'][$fieldName]['definition'] = $this->parseFieldDef($parseString);
597  if ($this->parse_error) {
598  return $this->parse_error;
599  }
600  }
601  // Finding delimiter:
602  $delim = $this->nextPart($parseString, '^(,|\\))');
603  if (!$delim) {
604  return $this->parseError('No delimiter found', $parseString);
605  } elseif ($delim === ')') {
606  break;
607  }
608  }
609  // Finding what is after the table definition - table type in MySQL
610  if ($delim === ')') {
611  if ($this->nextPart($parseString, '^((ENGINE|TYPE)[[:space:]]*=)')) {
612  $result['engine'] = $parseString;
613  $parseString = '';
614  }
615  } else {
616  return $this->parseError('No fieldname found!', $parseString);
617  }
618  } else {
619  return $this->parseError('No table found!', $parseString);
620  }
621  // Should be no more content now:
622  if ($parseString) {
623  return $this->parseError('Still content in clause after parsing!', $parseString);
624  }
625  return $result;
626  }
627 
635  protected function parseALTERTABLE($parseString)
636  {
637  // Removing ALTER TABLE
638  $parseString = $this->trimSQL($parseString);
639  $parseString = ltrim(substr(ltrim(substr($parseString, 5)), 5));
640  // Init output variable:
641  $result = [];
642  $result['type'] = 'ALTERTABLE';
643  // Get table:
644  $hasBackquote = $this->nextPart($parseString, '^(`)') === '`';
645  $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)' . ($hasBackquote ? '`' : '') . '[[:space:]]+');
646  if ($hasBackquote && $this->nextPart($parseString, '^(`)') !== '`') {
647  return $this->parseError('No end backquote found!', $parseString);
648  }
649  if ($result['TABLE']) {
650  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:]]+|\\(|=)')) {
651  $actionKey = $this->normalizeKeyword($result['action']);
652  // Getting field:
653  if ($actionKey === 'ADDPRIMARYKEY' || $actionKey === 'DROPPRIMARYKEY' || $actionKey === 'ENGINE' || ($fieldKey = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+'))) {
654  switch ($actionKey) {
655  case 'ADD':
656  $result['FIELD'] = $fieldKey;
657  $result['definition'] = $this->parseFieldDef($parseString);
658  if ($this->parse_error) {
659  return $this->parse_error;
660  }
661  break;
662  case 'DROP':
663  case 'RENAME':
664  $result['FIELD'] = $fieldKey;
665  break;
666  case 'CHANGE':
667  $result['FIELD'] = $fieldKey;
668  if ($result['newField'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) {
669  $result['definition'] = $this->parseFieldDef($parseString);
670  if ($this->parse_error) {
671  return $this->parse_error;
672  }
673  } else {
674  return $this->parseError('No NEW field name found', $parseString);
675  }
676  break;
677  case 'ADDKEY':
678  case 'ADDPRIMARYKEY':
679  case 'ADDUNIQUE':
680  $result['KEY'] = $fieldKey;
681  $result['fields'] = $this->getValue($parseString, '_LIST', 'INDEX');
682  if ($this->parse_error) {
683  return $this->parse_error;
684  }
685  break;
686  case 'DROPKEY':
687  $result['KEY'] = $fieldKey;
688  break;
689  case 'DROPPRIMARYKEY':
690  // @todo ???
691  break;
692  case 'DEFAULTCHARACTERSET':
693  $result['charset'] = $fieldKey;
694  break;
695  case 'ENGINE':
696  $result['engine'] = $this->nextPart($parseString, '^=[[:space:]]*([[:alnum:]]+)[[:space:]]+', true);
697  break;
698  }
699  } else {
700  return $this->parseError('No field name found', $parseString);
701  }
702  } else {
703  return $this->parseError('No action CHANGE, DROP or ADD found!', $parseString);
704  }
705  } else {
706  return $this->parseError('No table found!', $parseString);
707  }
708  // Should be no more content now:
709  if ($parseString) {
710  return $this->parseError('Still content in clause after parsing!', $parseString);
711  }
712  return $result;
713  }
714 
721  protected function parseDROPTABLE($parseString)
722  {
723  // Removing DROP TABLE
724  $parseString = $this->trimSQL($parseString);
725  $parseString = ltrim(substr(ltrim(substr($parseString, 4)), 5));
726  // Init output variable:
727  $result = [];
728  $result['type'] = 'DROPTABLE';
729  // IF EXISTS
730  $result['ifExists'] = $this->nextPart($parseString, '^(IF[[:space:]]+EXISTS[[:space:]]+)');
731  // Get table:
732  $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
733  if ($result['TABLE']) {
734  // Should be no more content now:
735  if ($parseString) {
736  return $this->parseError('Still content in clause after parsing!', $parseString);
737  }
738  return $result;
739  } else {
740  return $this->parseError('No table found!', $parseString);
741  }
742  }
743 
750  protected function parseCREATEDATABASE($parseString)
751  {
752  // Removing CREATE DATABASE
753  $parseString = $this->trimSQL($parseString);
754  $parseString = ltrim(substr(ltrim(substr($parseString, 6)), 8));
755  // Init output variable:
756  $result = [];
757  $result['type'] = 'CREATEDATABASE';
758  // Get table:
759  $result['DATABASE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
760  if ($result['DATABASE']) {
761  // Should be no more content now:
762  if ($parseString) {
763  return $this->parseError('Still content in clause after parsing!', $parseString);
764  }
765  return $result;
766  } else {
767  return $this->parseError('No database found!', $parseString);
768  }
769  }
770 
777  protected function parseTRUNCATETABLE($parseString)
778  {
779  // Removing TRUNCATE TABLE
780  $parseString = $this->trimSQL($parseString);
781  $parseString = ltrim(substr(ltrim(substr($parseString, 8)), 5));
782  // Init output variable:
783  $result = [];
784  $result['type'] = 'TRUNCATETABLE';
785  // Get table:
786  $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
787  if ($result['TABLE']) {
788  // Should be no more content now:
789  if ($parseString) {
790  return $this->parseError('Still content in clause after parsing!', $parseString);
791  }
792  return $result;
793  } else {
794  return $this->parseError('No table found!', $parseString);
795  }
796  }
797 
798  /**************************************
799  *
800  * SQL Parsing, helper functions for parts of queries
801  *
802  **************************************/
813  public function parseFieldList(&$parseString, $stopRegex = '')
814  {
815  $stack = [];
816  // Contains the parsed content
817  if ($parseString === '') {
818  return $stack;
819  }
820  // @todo - should never happen, why does it?
821  // Pointer to positions in $stack
822  $pnt = 0;
823  // Indicates the parenthesis level we are at.
824  $level = 0;
825  // Recursivity brake.
826  $loopExit = 0;
827  // Prepare variables:
828  $parseString = $this->trimSQL($parseString);
829  $this->lastStopKeyWord = '';
830  $this->parse_error = '';
831  // Parse any SQL hint / comments
832  $stack[$pnt]['comments'] = $this->nextPart($parseString, '^(\\/\\*.*\\*\\/)');
833  // $parseString is continuously shortened by the process and we keep parsing it till it is zero:
834  while ($parseString !== '') {
835  // Checking if we are inside / outside parenthesis (in case of a function like count(), max(), min() etc...):
836  // Inside parenthesis here (does NOT detect if values in quotes are used, the only token is ")" or "("):
837  if ($level > 0) {
838  // Accumulate function content until next () parenthesis:
839  $funcContent = $this->nextPart($parseString, '^([^()]*.)');
840  $stack[$pnt]['func_content.'][] = [
841  'level' => $level,
842  'func_content' => substr($funcContent, 0, -1)
843  ];
844  $stack[$pnt]['func_content'] .= $funcContent;
845  // Detecting ( or )
846  switch (substr($stack[$pnt]['func_content'], -1)) {
847  case '(':
848  $level++;
849  break;
850  case ')':
851  $level--;
852  // If this was the last parenthesis:
853  if (!$level) {
854  $stack[$pnt]['func_content'] = substr($stack[$pnt]['func_content'], 0, -1);
855  // Remove any whitespace after the parenthesis.
856  $parseString = ltrim($parseString);
857  }
858  break;
859  }
860  } else {
861  // Outside parenthesis, looking for next field:
862  // Looking for a flow-control construct (only known constructs supported)
863  if (preg_match('/^case([[:space:]][[:alnum:]\\*._]+)?[[:space:]]when/i', $parseString)) {
864  $stack[$pnt]['type'] = 'flow-control';
865  $stack[$pnt]['flow-control'] = $this->parseCaseStatement($parseString);
866  // Looking for "AS" alias:
867  if ($as = $this->nextPart($parseString, '^(AS)[[:space:]]+')) {
868  $stack[$pnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)(,|[[:space:]]+)');
869  $stack[$pnt]['as_keyword'] = $as;
870  }
871  } else {
872  // Looking for a known function (only known functions supported)
873  $func = $this->nextPart($parseString, '^(count|max|min|floor|sum|avg)[[:space:]]*\\(');
874  if ($func) {
875  // Strip off "("
876  $parseString = trim(substr($parseString, 1));
877  $stack[$pnt]['type'] = 'function';
878  $stack[$pnt]['function'] = $func;
879  // increse parenthesis level counter.
880  $level++;
881  } else {
882  $stack[$pnt]['distinct'] = $this->nextPart($parseString, '^(distinct[[:space:]]+)');
883  // Otherwise, look for regular fieldname:
884  if (($fieldName = $this->nextPart($parseString, '^([[:alnum:]\\*._]+)(,|[[:space:]]+)')) !== '') {
885  $stack[$pnt]['type'] = 'field';
886  // Explode fieldname into field and table:
887  $tableField = explode('.', $fieldName, 2);
888  if (count($tableField) === 2) {
889  $stack[$pnt]['table'] = $tableField[0];
890  $stack[$pnt]['field'] = $tableField[1];
891  } else {
892  $stack[$pnt]['table'] = '';
893  $stack[$pnt]['field'] = $tableField[0];
894  }
895  } else {
896  return $this->parseError('No field name found as expected in parseFieldList()', $parseString);
897  }
898  }
899  }
900  }
901  // After a function or field we look for "AS" alias and a comma to separate to the next field in the list:
902  if (!$level) {
903  // Looking for "AS" alias:
904  if ($as = $this->nextPart($parseString, '^(AS)[[:space:]]+')) {
905  $stack[$pnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)(,|[[:space:]]+)');
906  $stack[$pnt]['as_keyword'] = $as;
907  }
908  // Looking for "ASC" or "DESC" keywords (for ORDER BY)
909  if ($sDir = $this->nextPart($parseString, '^(ASC|DESC)([[:space:]]+|,)')) {
910  $stack[$pnt]['sortDir'] = $sDir;
911  }
912  // Looking for stop-keywords:
913  if ($stopRegex && ($this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))) {
914  $this->lastStopKeyWord = $this->normalizeKeyword($this->lastStopKeyWord);
915  return $stack;
916  }
917  // Looking for comma (since the stop-keyword did not trigger a return...)
918  if ($parseString !== '' && !$this->nextPart($parseString, '^(,)')) {
919  return $this->parseError('No comma found as expected in parseFieldList()', $parseString);
920  }
921  // Increasing pointer:
922  $pnt++;
923  }
924  // Check recursivity brake:
925  $loopExit++;
926  if ($loopExit > 500) {
927  return $this->parseError('More than 500 loops, exiting prematurely in parseFieldList()...', $parseString);
928  }
929  }
930  // Return result array:
931  return $stack;
932  }
933 
942  protected function parseCaseStatement(&$parseString)
943  {
944  $result = [];
945  $result['type'] = $this->nextPart($parseString, '^(case)[[:space:]]+');
946  if (!preg_match('/^when[[:space:]]+/i', $parseString)) {
947  $value = $this->getValue($parseString);
948  if (!(isset($value[1]) || is_numeric($value[0]))) {
949  $result['case_field'] = $value[0];
950  } else {
951  $result['case_value'] = $value;
952  }
953  }
954  $result['when'] = [];
955  while ($this->nextPart($parseString, '^(when)[[:space:]]')) {
956  $when = [];
957  $when['when_value'] = $this->parseWhereClause($parseString, '^(then)[[:space:]]+');
958  $when['then_value'] = $this->getValue($parseString);
959  $result['when'][] = $when;
960  }
961  if ($this->nextPart($parseString, '^(else)[[:space:]]+')) {
962  $result['else'] = $this->getValue($parseString);
963  }
964  if (!$this->nextPart($parseString, '^(end)[[:space:]]+')) {
965  return $this->parseError('No "end" keyword found as expected in parseCaseStatement()', $parseString);
966  }
967  return $result;
968  }
969 
977  protected function parseCastStatement(&$parseString)
978  {
979  $this->nextPart($parseString, '^(CAST)[[:space:]]*');
980  $parseString = trim(substr($parseString, 1));
981  $castDefinition = ['type' => 'cast'];
982  // Strip off "("
983  if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]\\*._]+)[[:space:]]*')) {
984  // Parse field name into field and table:
985  $tableField = explode('.', $fieldName, 2);
986  if (count($tableField) === 2) {
987  $castDefinition['table'] = $tableField[0];
988  $castDefinition['field'] = $tableField[1];
989  } else {
990  $castDefinition['table'] = '';
991  $castDefinition['field'] = $tableField[0];
992  }
993  } else {
994  return $this->parseError('No casted join field found in parseCastStatement()!', $parseString);
995  }
996  if ($this->nextPart($parseString, '^([[:space:]]*AS[[:space:]]*)')) {
997  $castDefinition['datatype'] = $this->getValue($parseString);
998  }
999  if (!$this->nextPart($parseString, '^([)])')) {
1000  return $this->parseError('No end parenthesis at end of CAST function', $parseString);
1001  }
1002  return $castDefinition;
1003  }
1004 
1014  public function parseFromTables(&$parseString, $stopRegex = '')
1015  {
1016  // Prepare variables:
1017  $parseString = $this->trimSQL($parseString);
1018  $this->lastStopKeyWord = '';
1019  $this->parse_error = '';
1020  // Contains the parsed content
1021  $stack = [];
1022  // Pointer to positions in $stack
1023  $pnt = 0;
1024  // Recursivity brake.
1025  $loopExit = 0;
1026  // $parseString is continously shortend by the process and we keep parsing it till it is zero:
1027  while ($parseString !== '') {
1028  // Looking for the table:
1029  if ($stack[$pnt]['table'] = $this->nextPart($parseString, '^([[:alnum:]_]+)(,|[[:space:]]+)')) {
1030  // Looking for stop-keywords before fetching potential table alias:
1031  if ($stopRegex && ($this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))) {
1032  $this->lastStopKeyWord = $this->normalizeKeyword($this->lastStopKeyWord);
1033  return $stack;
1034  }
1035  if (!preg_match('/^(LEFT|RIGHT|JOIN|INNER)[[:space:]]+/i', $parseString)) {
1036  $stack[$pnt]['as_keyword'] = $this->nextPart($parseString, '^(AS[[:space:]]+)');
1037  $stack[$pnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*');
1038  }
1039  } else {
1040  return $this->parseError('No table name found as expected in parseFromTables()!', $parseString);
1041  }
1042  // Looking for JOIN
1043  $joinCnt = 0;
1044  while ($join = $this->nextPart($parseString, '^(((INNER|(LEFT|RIGHT)([[:space:]]+OUTER)?)[[:space:]]+)?JOIN)[[:space:]]+')) {
1045  $stack[$pnt]['JOIN'][$joinCnt]['type'] = $join;
1046  if ($stack[$pnt]['JOIN'][$joinCnt]['withTable'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+', 1)) {
1047  if (!preg_match('/^ON[[:space:]]+/i', $parseString)) {
1048  $stack[$pnt]['JOIN'][$joinCnt]['as_keyword'] = $this->nextPart($parseString, '^(AS[[:space:]]+)');
1049  $stack[$pnt]['JOIN'][$joinCnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
1050  }
1051  if (!$this->nextPart($parseString, '^(ON[[:space:]]+)')) {
1052  return $this->parseError('No join condition found in parseFromTables()!', $parseString);
1053  }
1054  $stack[$pnt]['JOIN'][$joinCnt]['ON'] = [];
1055  $condition = ['operator' => ''];
1056  $parseCondition = true;
1057  while ($parseCondition) {
1058  if (($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)[[:space:]]*(<=|>=|<|>|=|!=)')) !== '') {
1059  // Parse field name into field and table:
1060  $tableField = explode('.', $fieldName, 2);
1061  $condition['left'] = [];
1062  if (count($tableField) === 2) {
1063  $condition['left']['table'] = $tableField[0];
1064  $condition['left']['field'] = $tableField[1];
1065  } else {
1066  $condition['left']['table'] = '';
1067  $condition['left']['field'] = $tableField[0];
1068  }
1069  } elseif (preg_match('/^CAST[[:space:]]*[(]/i', $parseString)) {
1070  $condition['left'] = $this->parseCastStatement($parseString);
1071  // Return the parse error
1072  if (!is_array($condition['left'])) {
1073  return $condition['left'];
1074  }
1075  } else {
1076  return $this->parseError('No join field found in parseFromTables()!', $parseString);
1077  }
1078  // Find "comparator":
1079  $condition['comparator'] = $this->nextPart($parseString, '^(<=|>=|<|>|=|!=)');
1080  if (preg_match('/^CAST[[:space:]]*[(]/i', $parseString)) {
1081  $condition['right'] = $this->parseCastStatement($parseString);
1082  // Return the parse error
1083  if (!is_array($condition['right'])) {
1084  return $condition['right'];
1085  }
1086  } elseif (($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)')) !== '') {
1087  // Parse field name into field and table:
1088  $tableField = explode('.', $fieldName, 2);
1089  $condition['right'] = [];
1090  if (count($tableField) === 2) {
1091  $condition['right']['table'] = $tableField[0];
1092  $condition['right']['field'] = $tableField[1];
1093  } else {
1094  $condition['right']['table'] = '';
1095  $condition['right']['field'] = $tableField[0];
1096  }
1097  } elseif ($value = $this->getValue($parseString)) {
1098  $condition['right']['value'] = $value;
1099  } else {
1100  return $this->parseError('No join field found in parseFromTables()!', $parseString);
1101  }
1102  $stack[$pnt]['JOIN'][$joinCnt]['ON'][] = $condition;
1103  if (($operator = $this->nextPart($parseString, '^(AND|OR)')) !== '') {
1104  $condition = ['operator' => $operator];
1105  } else {
1106  $parseCondition = false;
1107  }
1108  }
1109  $joinCnt++;
1110  } else {
1111  return $this->parseError('No join table found in parseFromTables()!', $parseString);
1112  }
1113  }
1114  // Looking for stop-keywords:
1115  if ($stopRegex && ($this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))) {
1116  $this->lastStopKeyWord = $this->normalizeKeyword($this->lastStopKeyWord);
1117  return $stack;
1118  }
1119  // Looking for comma:
1120  if ($parseString !== '' && !$this->nextPart($parseString, '^(,)')) {
1121  return $this->parseError('No comma found as expected in parseFromTables()', $parseString);
1122  }
1123  // Increasing pointer:
1124  $pnt++;
1125  // Check recursivity brake:
1126  $loopExit++;
1127  if ($loopExit > 500) {
1128  return $this->parseError('More than 500 loops, exiting prematurely in parseFromTables()...', $parseString);
1129  }
1130  }
1131  // Return result array:
1132  return $stack;
1133  }
1134 
1144  public function parseWhereClause(&$parseString, $stopRegex = '', array &$parameterReferences = [])
1145  {
1146  // Prepare variables:
1147  $parseString = $this->trimSQL($parseString);
1148  $this->lastStopKeyWord = '';
1149  $this->parse_error = '';
1150  // Contains the parsed content
1151  $stack = [0 => []];
1152  // Pointer to positions in $stack
1153  $pnt = [0 => 0];
1154  // Determines parenthesis level
1155  $level = 0;
1156  // Recursivity brake.
1157  $loopExit = 0;
1158  // $parseString is continuously shortened by the process and we keep parsing it till it is zero:
1159  while ($parseString !== '') {
1160  // Look for next parenthesis level:
1161  $newLevel = $this->nextPart($parseString, '^([(])');
1162  // If new level is started, manage stack/pointers:
1163  if ($newLevel === '(') {
1164  // Increase level
1165  $level++;
1166  // Reset pointer for this level
1167  $pnt[$level] = 0;
1168  // Reset stack for this level
1169  $stack[$level] = [];
1170  } else {
1171  // If no new level is started, just parse the current level:
1172  // Find "modifier", eg. "NOT or !"
1173  $stack[$level][$pnt[$level]]['modifier'] = trim($this->nextPart($parseString, '^(!|NOT[[:space:]]+)'));
1174  // See if condition is EXISTS with a subquery
1175  if (preg_match('/^EXISTS[[:space:]]*[(]/i', $parseString)) {
1176  $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(EXISTS)[[:space:]]*');
1177  // Strip off "("
1178  $parseString = trim(substr($parseString, 1));
1179  $stack[$level][$pnt[$level]]['func']['subquery'] = $this->parseSELECT($parseString, $parameterReferences);
1180  // Seek to new position in parseString after parsing of the subquery
1181  $parseString = $stack[$level][$pnt[$level]]['func']['subquery']['parseString'];
1182  unset($stack[$level][$pnt[$level]]['func']['subquery']['parseString']);
1183  if (!$this->nextPart($parseString, '^([)])')) {
1184  return 'No ) parenthesis at end of subquery';
1185  }
1186  } else {
1187  // See if LOCATE function is found
1188  if (preg_match('/^LOCATE[[:space:]]*[(]/i', $parseString)) {
1189  $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(LOCATE)[[:space:]]*');
1190  // Strip off "("
1191  $parseString = trim(substr($parseString, 1));
1192  $stack[$level][$pnt[$level]]['func']['substr'] = $this->getValue($parseString);
1193  if (!$this->nextPart($parseString, '^(,)')) {
1194  return $this->parseError('No comma found as expected in parseWhereClause()', $parseString);
1195  }
1196  if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]\\*._]+)[[:space:]]*')) {
1197  // Parse field name into field and table:
1198  $tableField = explode('.', $fieldName, 2);
1199  if (count($tableField) === 2) {
1200  $stack[$level][$pnt[$level]]['func']['table'] = $tableField[0];
1201  $stack[$level][$pnt[$level]]['func']['field'] = $tableField[1];
1202  } else {
1203  $stack[$level][$pnt[$level]]['func']['table'] = '';
1204  $stack[$level][$pnt[$level]]['func']['field'] = $tableField[0];
1205  }
1206  } else {
1207  return $this->parseError('No field name found as expected in parseWhereClause()', $parseString);
1208  }
1209  if ($this->nextPart($parseString, '^(,)')) {
1210  $stack[$level][$pnt[$level]]['func']['pos'] = $this->getValue($parseString);
1211  }
1212  if (!$this->nextPart($parseString, '^([)])')) {
1213  return $this->parseError('No ) parenthesis at end of function', $parseString);
1214  }
1215  } elseif (preg_match('/^IFNULL[[:space:]]*[(]/i', $parseString)) {
1216  $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(IFNULL)[[:space:]]*');
1217  $parseString = trim(substr($parseString, 1));
1218  // Strip off "("
1219  if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]\\*._]+)[[:space:]]*')) {
1220  // Parse field name into field and table:
1221  $tableField = explode('.', $fieldName, 2);
1222  if (count($tableField) === 2) {
1223  $stack[$level][$pnt[$level]]['func']['table'] = $tableField[0];
1224  $stack[$level][$pnt[$level]]['func']['field'] = $tableField[1];
1225  } else {
1226  $stack[$level][$pnt[$level]]['func']['table'] = '';
1227  $stack[$level][$pnt[$level]]['func']['field'] = $tableField[0];
1228  }
1229  } else {
1230  return $this->parseError('No field name found as expected in parseWhereClause()', $parseString);
1231  }
1232  if ($this->nextPart($parseString, '^(,)')) {
1233  $stack[$level][$pnt[$level]]['func']['default'] = $this->getValue($parseString);
1234  }
1235  if (!$this->nextPart($parseString, '^([)])')) {
1236  return $this->parseError('No ) parenthesis at end of function', $parseString);
1237  }
1238  } elseif (preg_match('/^CAST[[:space:]]*[(]/i', $parseString)) {
1239  $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(CAST)[[:space:]]*');
1240  $parseString = trim(substr($parseString, 1));
1241  // Strip off "("
1242  if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]\\*._]+)[[:space:]]*')) {
1243  // Parse field name into field and table:
1244  $tableField = explode('.', $fieldName, 2);
1245  if (count($tableField) === 2) {
1246  $stack[$level][$pnt[$level]]['func']['table'] = $tableField[0];
1247  $stack[$level][$pnt[$level]]['func']['field'] = $tableField[1];
1248  } else {
1249  $stack[$level][$pnt[$level]]['func']['table'] = '';
1250  $stack[$level][$pnt[$level]]['func']['field'] = $tableField[0];
1251  }
1252  } else {
1253  return $this->parseError('No field name found as expected in parseWhereClause()', $parseString);
1254  }
1255  if ($this->nextPart($parseString, '^([[:space:]]*AS[[:space:]]*)')) {
1256  $stack[$level][$pnt[$level]]['func']['datatype'] = $this->getValue($parseString);
1257  }
1258  if (!$this->nextPart($parseString, '^([)])')) {
1259  return $this->parseError('No ) parenthesis at end of function', $parseString);
1260  }
1261  } elseif (preg_match('/^FIND_IN_SET[[:space:]]*[(]/i', $parseString)) {
1262  $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(FIND_IN_SET)[[:space:]]*');
1263  // Strip off "("
1264  $parseString = trim(substr($parseString, 1));
1265  if ($str = $this->getValue($parseString)) {
1266  $stack[$level][$pnt[$level]]['func']['str'] = $str;
1267  if ($fieldName = $this->nextPart($parseString, '^,[[:space:]]*([[:alnum:]._]+)[[:space:]]*', true)) {
1268  // Parse field name into field and table:
1269  $tableField = explode('.', $fieldName, 2);
1270  if (count($tableField) === 2) {
1271  $stack[$level][$pnt[$level]]['func']['table'] = $tableField[0];
1272  $stack[$level][$pnt[$level]]['func']['field'] = $tableField[1];
1273  } else {
1274  $stack[$level][$pnt[$level]]['func']['table'] = '';
1275  $stack[$level][$pnt[$level]]['func']['field'] = $tableField[0];
1276  }
1277  } else {
1278  return $this->parseError('No field name found as expected in parseWhereClause()', $parseString);
1279  }
1280  if (!$this->nextPart($parseString, '^([)])')) {
1281  return $this->parseError('No ) parenthesis at end of function', $parseString);
1282  }
1283  } else {
1284  return $this->parseError('No item to look for found as expected in parseWhereClause()', $parseString);
1285  }
1286  } else {
1287  // Support calculated value only for:
1288  // - "&" (boolean AND)
1289  // - "+" (addition)
1290  // - "-" (substraction)
1291  // - "*" (multiplication)
1292  // - "/" (division)
1293  // - "%" (modulo)
1294  $calcOperators = '&|\\+|-|\\*|\\/|%';
1295  // Fieldname:
1296  if (($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)([[:space:]]+|' . $calcOperators . '|<=|>=|<|>|=|!=|IS)')) !== '') {
1297  // Parse field name into field and table:
1298  $tableField = explode('.', $fieldName, 2);
1299  if (count($tableField) === 2) {
1300  $stack[$level][$pnt[$level]]['table'] = $tableField[0];
1301  $stack[$level][$pnt[$level]]['field'] = $tableField[1];
1302  } else {
1303  $stack[$level][$pnt[$level]]['table'] = '';
1304  $stack[$level][$pnt[$level]]['field'] = $tableField[0];
1305  }
1306  } else {
1307  return $this->parseError('No field name found as expected in parseWhereClause()', $parseString);
1308  }
1309  // See if the value is calculated:
1310  $stack[$level][$pnt[$level]]['calc'] = $this->nextPart($parseString, '^(' . $calcOperators . ')');
1311  if ((string)$stack[$level][$pnt[$level]]['calc'] !== '') {
1312  // Finding value for calculation:
1313  $calc_value = $this->getValue($parseString);
1314  $stack[$level][$pnt[$level]]['calc_value'] = $calc_value;
1315  if (count($calc_value) === 1 && is_string($calc_value[0])) {
1316  // Value is a field, store it to allow DBAL to post-process it (quoting, remapping)
1317  $tableField = explode('.', $calc_value[0], 2);
1318  if (count($tableField) === 2) {
1319  $stack[$level][$pnt[$level]]['calc_table'] = $tableField[0];
1320  $stack[$level][$pnt[$level]]['calc_field'] = $tableField[1];
1321  } else {
1322  $stack[$level][$pnt[$level]]['calc_table'] = '';
1323  $stack[$level][$pnt[$level]]['calc_field'] = $tableField[0];
1324  }
1325  }
1326  }
1327  }
1328  $stack[$level][$pnt[$level]]['comparator'] = $this->nextPart($parseString, '^(' . implode('|', self::$comparatorPatterns) . ')');
1329  if ($stack[$level][$pnt[$level]]['comparator'] !== '') {
1330  if (preg_match('/^CONCAT[[:space:]]*\\(/', $parseString)) {
1331  $this->nextPart($parseString, '^(CONCAT[[:space:]]?[(])');
1332  $values = [
1333  'operator' => 'CONCAT',
1334  'args' => []
1335  ];
1336  $cnt = 0;
1337  while ($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)')) {
1338  // Parse field name into field and table:
1339  $tableField = explode('.', $fieldName, 2);
1340  if (count($tableField) === 2) {
1341  $values['args'][$cnt]['table'] = $tableField[0];
1342  $values['args'][$cnt]['field'] = $tableField[1];
1343  } else {
1344  $values['args'][$cnt]['table'] = '';
1345  $values['args'][$cnt]['field'] = $tableField[0];
1346  }
1347  // Looking for comma:
1348  $this->nextPart($parseString, '^(,)');
1349  $cnt++;
1350  }
1351  // Look for ending parenthesis:
1352  $this->nextPart($parseString, '([)])');
1353  $stack[$level][$pnt[$level]]['value'] = $values;
1354  } else {
1355  $comparator = $this->normalizeKeyword($stack[$level][$pnt[$level]]['comparator']);
1356  if (($comparator === 'IN' || $comparator == 'NOT IN') && preg_match('/^[(][[:space:]]*SELECT[[:space:]]+/', $parseString)) {
1357  $this->nextPart($parseString, '^([(])');
1358  $stack[$level][$pnt[$level]]['subquery'] = $this->parseSELECT($parseString, $parameterReferences);
1359  // Seek to new position in parseString after parsing of the subquery
1360  if (!empty($stack[$level][$pnt[$level]]['subquery']['parseString'])) {
1361  $parseString = $stack[$level][$pnt[$level]]['subquery']['parseString'];
1362  unset($stack[$level][$pnt[$level]]['subquery']['parseString']);
1363  }
1364  if (!$this->nextPart($parseString, '^([)])')) {
1365  return 'No ) parenthesis at end of subquery';
1366  }
1367  } elseif ($comparator === 'BETWEEN' || $comparator === 'NOT BETWEEN') {
1368  $stack[$level][$pnt[$level]]['values'] = [];
1369  $stack[$level][$pnt[$level]]['values'][0] = $this->getValue($parseString);
1370  if (!$this->nextPart($parseString, '^(AND)')) {
1371  return $this->parseError('No AND operator found as expected in parseWhereClause()', $parseString);
1372  }
1373  $stack[$level][$pnt[$level]]['values'][1] = $this->getValue($parseString);
1374  } else {
1375  // Finding value for comparator:
1376  $stack[$level][$pnt[$level]]['value'] = &$this->getValueOrParameter($parseString, $stack[$level][$pnt[$level]]['comparator'], '', $parameterReferences);
1377  if ($this->parse_error) {
1378  return $this->parse_error;
1379  }
1380  }
1381  }
1382  }
1383  }
1384  // Finished, increase pointer:
1385  $pnt[$level]++;
1386  // Checking if we are back to level 0 and we should still decrease level,
1387  // meaning we were probably parsing as subquery and should return here:
1388  if ($level === 0 && preg_match('/^[)]/', $parseString)) {
1389  // Return the stacks lowest level:
1390  return $stack[0];
1391  }
1392  // Checking if we are back to level 0 and we should still decrease level,
1393  // meaning we were probably parsing a subquery and should return here:
1394  if ($level === 0 && preg_match('/^[)]/', $parseString)) {
1395  // Return the stacks lowest level:
1396  return $stack[0];
1397  }
1398  // Checking if the current level is ended, in that case do stack management:
1399  while ($this->nextPart($parseString, '^([)])')) {
1400  $level--;
1401  // Decrease level:
1402  // Copy stack
1403  $stack[$level][$pnt[$level]]['sub'] = $stack[$level + 1];
1404  // Increase pointer of the new level
1405  $pnt[$level]++;
1406  // Make recursivity check:
1407  $loopExit++;
1408  if ($loopExit > 500) {
1409  return $this->parseError('More than 500 loops (in search for exit parenthesis), exiting prematurely in parseWhereClause()...', $parseString);
1410  }
1411  }
1412  // Detecting the operator for the next level:
1413  $op = $this->nextPart($parseString, '^(AND[[:space:]]+NOT|&&[[:space:]]+NOT|OR[[:space:]]+NOT|OR[[:space:]]+NOT|\\|\\|[[:space:]]+NOT|AND|&&|OR|\\|\\|)(\\(|[[:space:]]+)');
1414  if ($op) {
1415  // Normalize boolean operator
1416  $op = str_replace(['&&', '||'], ['AND', 'OR'], $op);
1417  $stack[$level][$pnt[$level]]['operator'] = $op;
1418  } elseif ($parseString !== '') {
1419  // Looking for stop-keywords:
1420  if ($stopRegex && ($this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))) {
1421  $this->lastStopKeyWord = $this->normalizeKeyword($this->lastStopKeyWord);
1422  return $stack[0];
1423  } else {
1424  return $this->parseError('No operator, but parsing not finished in parseWhereClause().', $parseString);
1425  }
1426  }
1427  }
1428  // Make recursivity check:
1429  $loopExit++;
1430  if ($loopExit > 500) {
1431  return $this->parseError('More than 500 loops, exiting prematurely in parseWhereClause()...', $parseString);
1432  }
1433  }
1434  // Return the stacks lowest level:
1435  return $stack[0];
1436  }
1437 
1446  public function parseFieldDef(&$parseString, $stopRegex = '')
1447  {
1448  // Prepare variables:
1449  $parseString = $this->trimSQL($parseString);
1450  $this->lastStopKeyWord = '';
1451  $this->parse_error = '';
1452  $result = [];
1453  // Field type:
1454  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|date|datetime|time|year|timestamp)([[:space:],]+|\\()')) {
1455  // Looking for value:
1456  if ($parseString[0] === '(') {
1457  $parseString = substr($parseString, 1);
1458  if ($result['value'] = $this->nextPart($parseString, '^([^)]*)')) {
1459  $parseString = ltrim(substr($parseString, 1));
1460  } else {
1461  return $this->parseError('No end-parenthesis for value found in parseFieldDef()!', $parseString);
1462  }
1463  }
1464  // Looking for keywords
1465  while ($keyword = $this->nextPart($parseString, '^(DEFAULT|NOT[[:space:]]+NULL|AUTO_INCREMENT|UNSIGNED)([[:space:]]+|,|\\))')) {
1466  $keywordCmp = $this->normalizeKeyword($keyword);
1467  $result['featureIndex'][$keywordCmp]['keyword'] = $keyword;
1468  switch ($keywordCmp) {
1469  case 'DEFAULT':
1470  $result['featureIndex'][$keywordCmp]['value'] = $this->getValue($parseString);
1471  break;
1472  }
1473  }
1474  } else {
1475  return $this->parseError('Field type unknown in parseFieldDef()!', $parseString);
1476  }
1477  return $result;
1478  }
1479 
1487  public function checkEmptyDefaultValue($featureIndex)
1488  {
1489  if (!is_array($featureIndex['DEFAULT']['value'])) {
1490  return true;
1491  }
1492  return !is_numeric($featureIndex['DEFAULT']['value'][0]) && empty($featureIndex['DEFAULT']['value'][0]);
1493  }
1494 
1495  /************************************
1496  *
1497  * Parsing: Helper functions
1498  *
1499  ************************************/
1509  protected function nextPart(&$parseString, $regex, $trimAll = false)
1510  {
1511  $reg = [];
1512  // Adding space char because [[:space:]]+ is often a requirement in regex's
1513  if (preg_match('/' . $regex . '/i', $parseString . ' ', $reg)) {
1514  $parseString = ltrim(substr($parseString, strlen($reg[$trimAll ? 0 : 1])));
1515  return $reg[1];
1516  }
1517  // No match found
1518  return '';
1519  }
1520 
1527  public static function normalizeKeyword($keyword)
1528  {
1529  return strtoupper(str_replace(self::$interQueryWhitespaces, '', $keyword));
1530  }
1531 
1544  protected function &getValueOrParameter(&$parseString, $comparator = '', $mode = '', array &$parameterReferences = [])
1545  {
1546  $parameter = $this->nextPart($parseString, '^(\\:[[:alnum:]_]+|\\?)');
1547  if ($parameter === '?') {
1548  if (!isset($parameterReferences['?'])) {
1549  $parameterReferences['?'] = [];
1550  }
1551  $value = ['?'];
1552  $parameterReferences['?'][] = &$value;
1553  } elseif ($parameter !== '') {
1554  // named parameter
1555  if (isset($parameterReferences[$parameter])) {
1556  // Use the same reference as last time we encountered this parameter
1557  $value = &$parameterReferences[$parameter];
1558  } else {
1559  $value = [$parameter];
1560  $parameterReferences[$parameter] = &$value;
1561  }
1562  } else {
1563  $value = $this->getValue($parseString, $comparator, $mode);
1564  }
1565  return $value;
1566  }
1567 
1576  protected function getValue(&$parseString, $comparator = '', $mode = '')
1577  {
1578  $value = '';
1579  $comparator = $this->normalizeKeyword($comparator);
1580  if ($comparator === 'NOTIN' || $comparator === 'IN' || $comparator === '_LIST') {
1581  // List of values:
1582  if ($this->nextPart($parseString, '^([(])')) {
1583  $listValues = [];
1584  $comma = ',';
1585  while ($comma === ',') {
1586  $listValues[] = $this->getValue($parseString);
1587  if ($mode === 'INDEX') {
1588  // Remove any length restriction on INDEX definition
1589  $this->nextPart($parseString, '^([(]\\d+[)])');
1590  }
1591  $comma = $this->nextPart($parseString, '^([,])');
1592  }
1593  $out = $this->nextPart($parseString, '^([)])');
1594  if ($out) {
1595  if ($comparator === '_LIST') {
1596  $kVals = [];
1597  foreach ($listValues as $vArr) {
1598  $kVals[] = $vArr[0];
1599  }
1600  return $kVals;
1601  } else {
1602  return $listValues;
1603  }
1604  } else {
1605  return [$this->parseError('No ) parenthesis in list', $parseString)];
1606  }
1607  } else {
1608  return [$this->parseError('No ( parenthesis starting the list', $parseString)];
1609  }
1610  } else {
1611  // Just plain string value, in quotes or not:
1612  // Quote?
1613  $firstChar = $parseString[0];
1614  switch ($firstChar) {
1615  case '"':
1616  $value = [$this->getValueInQuotes($parseString, '"'), '"'];
1617  break;
1618  case '\'':
1619  $value = [$this->getValueInQuotes($parseString, '\''), '\''];
1620  break;
1621  default:
1622  $reg = [];
1623  if (preg_match('/^([[:alnum:]._-]+(?:\\([0-9]+\\))?)/i', $parseString, $reg)) {
1624  $parseString = ltrim(substr($parseString, strlen($reg[0])));
1625  $value = [$reg[1]];
1626  }
1627  }
1628  }
1629  return $value;
1630  }
1631 
1639  protected function parseStripslashes($str)
1640  {
1641  $search = ['\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z'];
1642  $replace = ['\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a"];
1643 
1644  return str_replace($search, $replace, $str);
1645  }
1646 
1654  protected function parseError($msg, $restQuery)
1655  {
1656  $this->parse_error = 'SQL engine parse ERROR: ' . $msg . ': near "' . substr($restQuery, 0, 50) . '"';
1657  return $this->parse_error;
1658  }
1659 
1669  protected function trimSQL($str)
1670  {
1671  return rtrim(rtrim(trim($str), ';')) . ' ';
1672  }
1673 
1674  /*************************
1675  *
1676  * Compiling queries
1677  *
1678  *************************/
1679 
1687  public function compileSQL($components)
1688  {
1689  return $this->getSqlCompiler()->compileSQL($components);
1690  }
1691 
1701  public function compileFieldList($selectFields, $compileComments = true)
1702  {
1703  return $this->getSqlCompiler()->compileFieldList($selectFields, $compileComments);
1704  }
1705 
1713  public function compileFromTables($tablesArray)
1714  {
1715  return $this->getSqlCompiler()->compileFromTables($tablesArray);
1716  }
1717 
1724  public function compileFieldCfg($fieldCfg)
1725  {
1726  return $this->getSqlCompiler()->compileFieldCfg($fieldCfg);
1727  }
1728 
1745  public function compileWhereClause($clauseArray, $functionMapping = true)
1746  {
1747  return $this->getSqlCompiler()->compileWhereClause($clauseArray, $functionMapping);
1748  }
1749 
1753  protected function getSqlCompiler()
1754  {
1755  if ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type'] === 'native') {
1756  return $this->nativeSqlCompiler;
1757  } else {
1758  return $this->sqlCompiler;
1759  }
1760  }
1761 
1762  /*************************
1763  *
1764  * Debugging
1765  *
1766  *************************/
1773  public function debug_testSQL($SQLquery)
1774  {
1775  // Getting result array:
1776  $parseResult = $this->parseSQL($SQLquery);
1777  // If result array was returned, proceed. Otherwise show error and exit.
1778  if (is_array($parseResult)) {
1779  // Re-compile query:
1780  $newQuery = $this->compileSQL($parseResult);
1781  // TEST the new query:
1782  $testResult = $this->debug_parseSQLpartCompare($SQLquery, $newQuery);
1783  // Return new query if OK, otherwise show error and exit:
1784  if (!is_array($testResult)) {
1785  return $newQuery;
1786  } else {
1787  debug(['ERROR MESSAGE' => 'Input query did not match the parsed and recompiled query exactly (not observing whitespace)', 'TEST result' => $testResult], 'SQL parsing failed:');
1788  die;
1789  }
1790  } else {
1791  debug(['query' => $SQLquery, 'ERROR MESSAGE' => $parseResult], 'SQL parsing failed:');
1792  die;
1793  }
1794  }
1795 
1803  public function debug_parseSQLpart($part, $str)
1804  {
1805  $retVal = false;
1806  switch ($part) {
1807  case 'SELECT':
1808  $retVal = $this->debug_parseSQLpartCompare($str, $this->compileFieldList($this->parseFieldList($str)));
1809  break;
1810  case 'FROM':
1811  $retVal = $this->debug_parseSQLpartCompare($str, $this->getSqlCompiler()->compileFromTables($this->parseFromTables($str)));
1812  break;
1813  case 'WHERE':
1814  $retVal = $this->debug_parseSQLpartCompare($str, $this->getSqlCompiler()->compileWhereClause($this->parseWhereClause($str)));
1815  break;
1816  }
1817  return $retVal;
1818  }
1819 
1828  public function debug_parseSQLpartCompare($str, $newStr, $caseInsensitive = false)
1829  {
1830  if ($caseInsensitive) {
1831  $str1 = strtoupper($str);
1832  $str2 = strtoupper($newStr);
1833  } else {
1834  $str1 = $str;
1835  $str2 = $newStr;
1836  }
1837 
1838  // Fixing escaped chars:
1839  $search = [NUL, LF, CR, SUB];
1840  $replace = ["\x00", "\x0a", "\x0d", "\x1a"];
1841  $str1 = str_replace($search, $replace, $str1);
1842  $str2 = str_replace($search, $replace, $str2);
1843 
1844  $search = self::$interQueryWhitespaces;
1845  if (str_replace($search, '', $this->trimSQL($str1)) !== str_replace($search, '', $this->trimSQL($str2))) {
1846  return [
1847  str_replace($search, ' ', $str),
1848  str_replace($search, ' ', $newStr),
1849  ];
1850  }
1851  }
1852 }
TYPO3\CMS\Dbal\Database\SqlParser\nextPart
nextPart(&$parseString, $regex, $trimAll=false)
Definition: SqlParser.php:1509
TYPO3\CMS\Dbal\Database\SqlParser\__construct
__construct(DatabaseConnection $databaseConnection=null)
Definition: SqlParser.php:88
TYPO3\CMS\Dbal\Database\SqlParser\compileFieldList
compileFieldList($selectFields, $compileComments=true)
Definition: SqlParser.php:1701
debug
debug($variable='', $name=' *variable *', $line=' *line *', $file=' *file *', $recursiveDepth=3, $debugLevel='E_DEBUG')
Definition: GlobalDebugFunctions.php:14
TYPO3\CMS\Dbal\Database\SqlParser\parseSELECT
parseSELECT($parseString, &$parameterReferences=null)
Definition: SqlParser.php:272
TYPO3\CMS\Dbal\Database\SqlParser\parseFieldDef
parseFieldDef(&$parseString, $stopRegex='')
Definition: SqlParser.php:1446
TYPO3\CMS\Dbal\Database\SqlParser\getValueInQuotes
getValueInQuotes(&$parseString, $quote)
Definition: SqlParser.php:102
TYPO3\CMS\Dbal\Database\SqlParser\getValue
getValue(&$parseString, $comparator='', $mode='')
Definition: SqlParser.php:1576
TYPO3\CMS\Dbal\Database\SqlParser\getValueOrParameter
& getValueOrParameter(&$parseString, $comparator='', $mode='', array &$parameterReferences=[])
Definition: SqlParser.php:1544
TYPO3\CMS\Dbal\Database\SqlParser\getValueInQuotesMssql
getValueInQuotesMssql(&$parseString, $quote)
Definition: SqlParser.php:150
TYPO3\CMS\Dbal\Database\SqlParser\getValueInQuotesGeneric
getValueInQuotesGeneric(&$parseString, $quote)
Definition: SqlParser.php:126
TYPO3\CMS\Dbal\Database\SqlParser\compileWhereClause
compileWhereClause($clauseArray, $functionMapping=true)
Definition: SqlParser.php:1745
TYPO3\CMS\Dbal\Database\SqlParser\parseFromTables
parseFromTables(&$parseString, $stopRegex='')
Definition: SqlParser.php:1014
TYPO3\CMS\Dbal\Database\SqlParser\parseWhereClause
parseWhereClause(&$parseString, $stopRegex='', array &$parameterReferences=[])
Definition: SqlParser.php:1144
$GLOBALS
if(TYPO3_MODE==='BE') $GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_tsfebeuserauth.php']['frontendEditingController']['default']
Definition: ext_localconf.php:31
TYPO3\CMS\Dbal\Database\SqlParser\parseFieldList
parseFieldList(&$parseString, $stopRegex='')
Definition: SqlParser.php:813
TYPO3\CMS\Dbal\Database\SqlParser\debug_parseSQLpartCompare
debug_parseSQLpartCompare($str, $newStr, $caseInsensitive=false)
Definition: SqlParser.php:1828