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