TYPO3 CMS  TYPO3_6-2
SqlParser.php
Go to the documentation of this file.
1 <?php
2 namespace TYPO3\CMS\Dbal\Database;
3 
16 use TYPO3\CMS\Core\Utility\GeneralUtility;
17 
25 class SqlParser extends \TYPO3\CMS\Core\Database\SqlParser {
26 
30  protected $databaseConnection;
31 
35  public function __construct(DatabaseConnection $databaseConnection = NULL) {
36  parent::__construct();
37 
38  $this->databaseConnection = $databaseConnection ?: $GLOBALS['TYPO3_DB'];
39  }
40 
48  protected function getValueInQuotes(&$parseString, $quote) {
49  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
50  case 'adodb':
51  if ($this->databaseConnection->runningADOdbDriver('mssql')) {
52  $value = $this->getValueInQuotesMssql($parseString, $quote);
53  } else {
54  $value = parent::getValueInQuotes($parseString, $quote);
55  }
56  break;
57  default:
58  $value = parent::getValueInQuotes($parseString, $quote);
59  }
60  return $value;
61  }
62 
70  protected function getValueInQuotesMssql(&$parseString, $quote) {
71  $previousIsQuote = FALSE;
72  $inQuote = FALSE;
73  // Go through the whole string
74  for ($c = 0; $c < strlen($parseString); $c++) {
75  // If the parsed string character is the quote string
76  if ($parseString[$c] === $quote) {
77  // If we are already in a quote
78  if ($inQuote) {
79  // Was the previous a quote?
80  if ($previousIsQuote) {
81  // If yes, replace it by a \
82  $parseString[$c - 1] = '\\';
83  }
84  // Invert the state
85  $previousIsQuote = !$previousIsQuote;
86  } else {
87  // So we are in a quote since now
88  $inQuote = TRUE;
89  }
90  } elseif ($inQuote && $previousIsQuote) {
91  $inQuote = FALSE;
92  $previousIsQuote = FALSE;
93  } else {
94  $previousIsQuote = FALSE;
95  }
96  }
97  $parts = explode($quote, substr($parseString, 1));
98  $buffer = '';
99  foreach ($parts as $v) {
100  $buffer .= $v;
101  $reg = array();
102  preg_match('/\\\\$/', $v, $reg);
103  if ($reg && strlen($reg[0]) % 2) {
104  $buffer .= $quote;
105  } else {
106  $parseString = ltrim(substr($parseString, strlen($buffer) + 2));
107  return $this->parseStripslashes($buffer);
108  }
109  }
110  return '';
111  }
112 
123  public function compileFieldList($selectFields, $compileComments = TRUE, $functionMapping = TRUE) {
124  $output = '';
125  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
126  case 'native':
127  $output = parent::compileFieldList($selectFields, $compileComments);
128  break;
129  case 'adodb':
130  // Traverse the selectFields if any:
131  if (is_array($selectFields)) {
132  $outputParts = array();
133  foreach ($selectFields as $k => $v) {
134  // Detecting type:
135  switch ($v['type']) {
136  case 'function':
137  $outputParts[$k] = $v['function'] . '(' . $v['func_content'] . ')';
138  break;
139  case 'flow-control':
140  if ($v['flow-control']['type'] === 'CASE') {
141  $outputParts[$k] = $this->compileCaseStatement($v['flow-control'], $functionMapping);
142  }
143  break;
144  case 'field':
145  $outputParts[$k] = ($v['distinct'] ? $v['distinct'] : '') . ($v['table'] ? $v['table'] . '.' : '') . $v['field'];
146  break;
147  }
148  // Alias:
149  if ($v['as']) {
150  $outputParts[$k] .= ' ' . $v['as_keyword'] . ' ' . $v['as'];
151  }
152  // Specifically for ORDER BY and GROUP BY field lists:
153  if ($v['sortDir']) {
154  $outputParts[$k] .= ' ' . $v['sortDir'];
155  }
156  }
157  // TODO: Handle SQL hints in comments according to current DBMS
158  if (FALSE && $selectFields[0]['comments']) {
159  $output = $selectFields[0]['comments'] . ' ';
160  }
161  $output .= implode(', ', $outputParts);
162  }
163  break;
164  }
165  return $output;
166  }
167 
176  protected function compileCaseStatement(array $components, $functionMapping = TRUE) {
177  $output = '';
178  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
179  case 'native':
180  $output = parent::compileCaseStatement($components);
181  break;
182  case 'adodb':
183  $statement = 'CASE';
184  if (isset($components['case_field'])) {
185  $statement .= ' ' . $components['case_field'];
186  } elseif (isset($components['case_value'])) {
187  $statement .= ' ' . $components['case_value'][1] . $components['case_value'][0] . $components['case_value'][1];
188  }
189  foreach ($components['when'] as $when) {
190  $statement .= ' WHEN ';
191  $statement .= $this->compileWhereClause($when['when_value'], $functionMapping);
192  $statement .= ' THEN ';
193  $statement .= $when['then_value'][1] . $when['then_value'][0] . $when['then_value'][1];
194  }
195  if (isset($components['else'])) {
196  $statement .= ' ELSE ';
197  $statement .= $components['else'][1] . $components['else'][0] . $components['else'][1];
198  }
199  $statement .= ' END';
200  $output = $statement;
201  break;
202  }
203  return $output;
204  }
205 
214  protected function compileAddslashes($str) {
215  // DatabaseConnection::quoteWhereClause() returns an unmodified where clause in native mode,
216  // escaping of special characters needs to be done here.
217  if ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type'] === 'native') {
218  return parent::compileAddslashes($str);
219  }
220 
221  // Return unmodified value, DBMS specific escaping is handled in DatabaseConnection::quoteWhereClause()
222  return $str;
223  }
224 
225  /*************************
226  *
227  * Compiling queries
228  *
229  *************************/
237  protected function compileINSERT($components) {
238  $query = '';
239  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
240  case 'native':
241  $query = parent::compileINSERT($components);
242  break;
243  case 'adodb':
244  $values = array();
245  if (isset($components['VALUES_ONLY']) && is_array($components['VALUES_ONLY'])) {
246  $valuesComponents = $components['EXTENDED'] === '1' ? $components['VALUES_ONLY'] : array($components['VALUES_ONLY']);
247  $tableFields = array_keys($this->databaseConnection->cache_fieldType[$components['TABLE']]);
248  } else {
249  $valuesComponents = $components['EXTENDED'] === '1' ? $components['FIELDS'] : array($components['FIELDS']);
250  $tableFields = array_keys($valuesComponents[0]);
251  }
252  foreach ($valuesComponents as $valuesComponent) {
253  $fields = array();
254  $fc = 0;
255  foreach ($valuesComponent as $fV) {
256  $fields[$tableFields[$fc++]] = $fV[0];
257  }
258  $values[] = $fields;
259  }
260  $query = count($values) === 1 ? $values[0] : $values;
261  break;
262  }
263  return $query;
264  }
265 
273  public function compileCREATETABLE($components) {
274  $query = array();
275  // Execute query (based on handler derived from the TABLE name which we actually know for once!)
276  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->handler_getFromTableList($components['TABLE'])]['type']) {
277  case 'native':
278  $query[] = parent::compileCREATETABLE($components);
279  break;
280  case 'adodb':
281  // Create fields and keys:
282  $fieldsKeys = array();
283  $indexKeys = array();
284  foreach ($components['FIELDS'] as $fN => $fCfg) {
285  $handlerKey = $this->databaseConnection->handler_getFromTableList($components['TABLE']);
286  $fieldsKeys[$fN] = $this->databaseConnection->quoteName($fN, $handlerKey, TRUE) . ' ' . $this->compileFieldCfg($fCfg['definition']);
287  }
288  if (isset($components['KEYS']) && is_array($components['KEYS'])) {
289  foreach ($components['KEYS'] as $kN => $kCfg) {
290  if ($kN === 'PRIMARYKEY') {
291  foreach ($kCfg as $field) {
292  $fieldsKeys[$field] .= ' PRIMARY';
293  }
294  } elseif ($kN === 'UNIQUE') {
295  foreach ($kCfg as $n => $field) {
296  $indexKeys = array_merge($indexKeys, $this->databaseConnection->handlerInstance[$this->databaseConnection->handler_getFromTableList($components['TABLE'])]->DataDictionary->CreateIndexSQL($n, $components['TABLE'], $field, array('UNIQUE')));
297  }
298  } else {
299  $indexKeys = array_merge($indexKeys, $this->databaseConnection->handlerInstance[$this->databaseConnection->handler_getFromTableList($components['TABLE'])]->DataDictionary->CreateIndexSQL($components['TABLE'] . '_' . $kN, $components['TABLE'], $kCfg));
300  }
301  }
302  }
303  // Generally create without OID on PostgreSQL
304  $tableOptions = array('postgres' => 'WITHOUT OIDS');
305  // Fetch table/index generation query:
306  $tableName = $this->databaseConnection->quoteName($components['TABLE'], NULL, TRUE);
307  $query = array_merge($this->databaseConnection->handlerInstance[$this->databaseConnection->lastHandlerKey]->DataDictionary->CreateTableSQL($tableName, implode(',' . chr(10), $fieldsKeys), $tableOptions), $indexKeys);
308  break;
309  }
310  return $query;
311  }
312 
320  public function compileALTERTABLE($components) {
321  $query = '';
322  // Execute query (based on handler derived from the TABLE name which we actually know for once!)
323  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
324  case 'native':
325  $query = parent::compileALTERTABLE($components);
326  break;
327  case 'adodb':
328  $tableName = $this->databaseConnection->quoteName($components['TABLE'], NULL, TRUE);
329  $fieldName = $this->databaseConnection->quoteName($components['FIELD'], NULL, TRUE);
330  switch (strtoupper(str_replace(array(' ', "\n", "\r", "\t"), '', $components['action']))) {
331  case 'ADD':
332  $query = $this->databaseConnection->handlerInstance[$this->databaseConnection->lastHandlerKey]->DataDictionary->AddColumnSQL($tableName, $fieldName . ' ' . $this->compileFieldCfg($components['definition']));
333  break;
334  case 'CHANGE':
335  $query = $this->databaseConnection->handlerInstance[$this->databaseConnection->lastHandlerKey]->DataDictionary->AlterColumnSQL($tableName, $fieldName . ' ' . $this->compileFieldCfg($components['definition']));
336  break;
337  case 'DROP':
338 
339  case 'DROPKEY':
340  break;
341  case 'ADDKEY':
342 
343  case 'ADDPRIMARYKEY':
344 
345  case 'ADDUNIQUE':
346  $query .= ' (' . implode(',', $components['fields']) . ')';
347  break;
348  case 'DEFAULTCHARACTERSET':
349 
350  case 'ENGINE':
351  // ??? todo!
352  break;
353  }
354  break;
355  }
356  return $query;
357  }
358 
365  public function compileFieldCfg($fieldCfg) {
366  $cfg = '';
367  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
368  case 'native':
369  $cfg = parent::compileFieldCfg($fieldCfg);
370  break;
371  case 'adodb':
372  // Set type:
373  $type = $this->databaseConnection->MySQLMetaType($fieldCfg['fieldType']);
374  $cfg = $type;
375  // Add value, if any:
376  if (strlen($fieldCfg['value']) && in_array($type, array('C', 'C2'))) {
377  $cfg .= ' ' . $fieldCfg['value'];
378  } elseif (!isset($fieldCfg['value']) && in_array($type, array('C', 'C2'))) {
379  $cfg .= ' 255';
380  }
381  // Add additional features:
382  $noQuote = TRUE;
383  if (is_array($fieldCfg['featureIndex'])) {
384  // MySQL assigns DEFAULT value automatically if NOT NULL, fake this here
385  // numeric fields get 0 as default, other fields an empty string
386  if (isset($fieldCfg['featureIndex']['NOTNULL']) && !isset($fieldCfg['featureIndex']['DEFAULT']) && !isset($fieldCfg['featureIndex']['AUTO_INCREMENT'])) {
387  switch ($type) {
388  case 'I8':
389 
390  case 'F':
391 
392  case 'N':
393  $fieldCfg['featureIndex']['DEFAULT'] = array('keyword' => 'DEFAULT', 'value' => array('0', ''));
394  break;
395  default:
396  $fieldCfg['featureIndex']['DEFAULT'] = array('keyword' => 'DEFAULT', 'value' => array('', '\''));
397  }
398  }
399  foreach ($fieldCfg['featureIndex'] as $feature => $featureDef) {
400  switch (TRUE) {
401  case $feature === 'UNSIGNED' && !$this->databaseConnection->runningADOdbDriver('mysql'):
402  case $feature === 'NOTNULL' && $this->databaseConnection->runningADOdbDriver('oci8'):
403  continue;
404  case $feature === 'AUTO_INCREMENT':
405  $cfg .= ' AUTOINCREMENT';
406  break;
407  case $feature === 'NOTNULL':
408  $cfg .= ' NOTNULL';
409  break;
410  default:
411  $cfg .= ' ' . $featureDef['keyword'];
412  }
413  // Add value if found:
414  if (is_array($featureDef['value'])) {
415  if ($featureDef['value'][0] === '') {
416  $cfg .= ' "\'\'"';
417  } else {
418  $cfg .= ' ' . $featureDef['value'][1] . $this->compileAddslashes($featureDef['value'][0]) . $featureDef['value'][1];
419  if (!is_numeric($featureDef['value'][0])) {
420  $noQuote = FALSE;
421  }
422  }
423  }
424  }
425  }
426  if ($noQuote) {
427  $cfg .= ' NOQUOTE';
428  }
429  break;
430  }
431  // Return field definition string:
432  return $cfg;
433  }
434 
442  public function checkEmptyDefaultValue($featureIndex) {
443  if (!is_array($featureIndex['DEFAULT']['value'])) {
444  return TRUE;
445  }
446  return !is_numeric($featureIndex['DEFAULT']['value'][0]) && empty($featureIndex['DEFAULT']['value'][0]);
447  }
448 
465  public function compileWhereClause($clauseArray, $functionMapping = TRUE) {
466  $output = '';
467  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
468  case 'native':
469  $output = parent::compileWhereClause($clauseArray);
470  break;
471  case 'adodb':
472  // Prepare buffer variable:
473  $output = '';
474  // Traverse clause array:
475  if (is_array($clauseArray)) {
476  foreach ($clauseArray as $v) {
477  // Set operator:
478  $output .= $v['operator'] ? ' ' . $v['operator'] : '';
479  // Look for sublevel:
480  if (is_array($v['sub'])) {
481  $output .= ' (' . trim($this->compileWhereClause($v['sub'], $functionMapping)) . ')';
482  } elseif (isset($v['func']) && $v['func']['type'] === 'EXISTS') {
483  $output .= ' ' . trim($v['modifier']) . ' EXISTS (' . $this->compileSELECT($v['func']['subquery']) . ')';
484  } else {
485  if (isset($v['func']) && $v['func']['type'] === 'LOCATE') {
486  $output .= ' ' . trim($v['modifier']);
487  switch (TRUE) {
488  case $this->databaseConnection->runningADOdbDriver('mssql') && $functionMapping:
489  $output .= ' CHARINDEX(';
490  $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
491  $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
492  $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
493  $output .= ')';
494  break;
495  case $this->databaseConnection->runningADOdbDriver('oci8') && $functionMapping:
496  $output .= ' INSTR(';
497  $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
498  $output .= ', ' . $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
499  $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
500  $output .= ')';
501  break;
502  default:
503  $output .= ' LOCATE(';
504  $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
505  $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
506  $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
507  $output .= ')';
508  }
509  } elseif (isset($v['func']) && $v['func']['type'] === 'IFNULL') {
510  $output .= ' ' . trim($v['modifier']) . ' ';
511  switch (TRUE) {
512  case $this->databaseConnection->runningADOdbDriver('mssql') && $functionMapping:
513  $output .= 'ISNULL';
514  break;
515  case $this->databaseConnection->runningADOdbDriver('oci8') && $functionMapping:
516  $output .= 'NVL';
517  break;
518  default:
519  $output .= 'IFNULL';
520  }
521  $output .= '(';
522  $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
523  $output .= ', ' . $v['func']['default'][1] . $this->compileAddslashes($v['func']['default'][0]) . $v['func']['default'][1];
524  $output .= ')';
525  } elseif (isset($v['func']) && $v['func']['type'] === 'FIND_IN_SET') {
526  $output .= ' ' . trim($v['modifier']) . ' ';
527  if ($functionMapping) {
528  switch (TRUE) {
529  case $this->databaseConnection->runningADOdbDriver('mssql'):
530  $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
531  if (!isset($v['func']['str_like'])) {
532  $v['func']['str_like'] = $v['func']['str'][0];
533  }
534  $output .= '\',\'+' . $field . '+\',\' LIKE \'%,' . $v['func']['str_like'] . ',%\'';
535  break;
536  case $this->databaseConnection->runningADOdbDriver('oci8'):
537  $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
538  if (!isset($v['func']['str_like'])) {
539  $v['func']['str_like'] = $v['func']['str'][0];
540  }
541  $output .= '\',\'||' . $field . '||\',\' LIKE \'%,' . $v['func']['str_like'] . ',%\'';
542  break;
543  case $this->databaseConnection->runningADOdbDriver('postgres'):
544  $output .= ' FIND_IN_SET(';
545  $output .= $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1];
546  $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
547  $output .= ') != 0';
548  break;
549  default:
550  $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
551  if (!isset($v['func']['str_like'])) {
552  $v['func']['str_like'] = $v['func']['str'][0];
553  }
554  $output .= '(' . $field . ' LIKE \'%,' . $v['func']['str_like'] . ',%\'' . ' OR ' . $field . ' LIKE \'' . $v['func']['str_like'] . ',%\'' . ' OR ' . $field . ' LIKE \'%,' . $v['func']['str_like'] . '\'' . ' OR ' . $field . '= ' . $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1] . ')';
555  }
556  } else {
557  switch (TRUE) {
558  case $this->databaseConnection->runningADOdbDriver('mssql'):
559 
560  case $this->databaseConnection->runningADOdbDriver('oci8'):
561 
562  case $this->databaseConnection->runningADOdbDriver('postgres'):
563  $output .= ' FIND_IN_SET(';
564  $output .= $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1];
565  $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
566  $output .= ')';
567  break;
568  default:
569  $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
570  if (!isset($v['func']['str_like'])) {
571  $v['func']['str_like'] = $v['func']['str'][0];
572  }
573  $output .= '(' . $field . ' LIKE \'%,' . $v['func']['str_like'] . ',%\'' . ' OR ' . $field . ' LIKE \'' . $v['func']['str_like'] . ',%\'' . ' OR ' . $field . ' LIKE \'%,' . $v['func']['str_like'] . '\'' . ' OR ' . $field . '= ' . $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1] . ')';
574  }
575  }
576  } else {
577  // Set field/table with modifying prefix if any:
578  $output .= ' ' . trim($v['modifier']) . ' ';
579  // DBAL-specific: Set calculation, if any:
580  if ($v['calc'] === '&' && $functionMapping) {
581  switch (TRUE) {
582  case $this->databaseConnection->runningADOdbDriver('oci8'):
583  // Oracle only knows BITAND(x,y) - sigh
584  $output .= 'BITAND(' . trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . ',' . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1] . ')';
585  break;
586  default:
587  // MySQL, MS SQL Server, PostgreSQL support the &-syntax
588  $output .= trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . $v['calc'] . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
589  }
590  } elseif ($v['calc']) {
591  $output .= trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . $v['calc'];
592  if (isset($v['calc_table'])) {
593  $output .= trim(($v['calc_table'] ? $v['calc_table'] . '.' : '') . $v['calc_field']);
594  } else {
595  $output .= $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
596  }
597  } elseif (!($this->databaseConnection->runningADOdbDriver('oci8') && preg_match('/(NOT )?LIKE( BINARY)?/', $v['comparator']) && $functionMapping)) {
598  $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']);
599  }
600  }
601  // Set comparator:
602  if ($v['comparator']) {
603  $isLikeOperator = preg_match('/(NOT )?LIKE( BINARY)?/', $v['comparator']);
604  switch (TRUE) {
605  case $this->databaseConnection->runningADOdbDriver('oci8') && $isLikeOperator && $functionMapping:
606  // Oracle cannot handle LIKE on CLOB fields - sigh
607  if (isset($v['value']['operator'])) {
608  $values = array();
609  foreach ($v['value']['args'] as $fieldDef) {
610  $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field'];
611  }
612  $compareValue = ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';
613  } else {
614  $compareValue = $v['value'][1] . $this->compileAddslashes(trim($v['value'][0], '%')) . $v['value'][1];
615  }
616  if (GeneralUtility::isFirstPartOfStr($v['comparator'], 'NOT')) {
617  $output .= 'NOT ';
618  }
619  // To be on the safe side
620  $isLob = TRUE;
621  if ($v['table']) {
622  // Table and field names are quoted:
623  $tableName = substr($v['table'], 1, strlen($v['table']) - 2);
624  $fieldName = substr($v['field'], 1, strlen($v['field']) - 2);
625  $fieldType = $this->databaseConnection->sql_field_metatype($tableName, $fieldName);
626  $isLob = $fieldType === 'B' || $fieldType === 'XL';
627  }
628  if (strtoupper(substr($v['comparator'], -6)) === 'BINARY') {
629  if ($isLob) {
630  $output .= '(dbms_lob.instr(' . trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . ', ' . $compareValue . ',1,1) > 0)';
631  } else {
632  $output .= '(instr(' . trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . ', ' . $compareValue . ',1,1) > 0)';
633  }
634  } else {
635  if ($isLob) {
636  $output .= '(dbms_lob.instr(LOWER(' . trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . '), ' . GeneralUtility::strtolower($compareValue) . ',1,1) > 0)';
637  } else {
638  $output .= '(instr(LOWER(' . trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . '), ' . GeneralUtility::strtolower($compareValue) . ',1,1) > 0)';
639  }
640  }
641  break;
642  default:
643  if ($isLikeOperator && $functionMapping) {
644  if ($this->databaseConnection->runningADOdbDriver('postgres') || $this->databaseConnection->runningADOdbDriver('postgres64') || $this->databaseConnection->runningADOdbDriver('postgres7') || $this->databaseConnection->runningADOdbDriver('postgres8')) {
645  // Remap (NOT)? LIKE to (NOT)? ILIKE
646  // and (NOT)? LIKE BINARY to (NOT)? LIKE
647  switch ($v['comparator']) {
648  case 'LIKE':
649  $v['comparator'] = 'ILIKE';
650  break;
651  case 'NOT LIKE':
652  $v['comparator'] = 'NOT ILIKE';
653  break;
654  default:
655  $v['comparator'] = str_replace(' BINARY', '', $v['comparator']);
656  }
657  } else {
658  // No more BINARY operator
659  $v['comparator'] = str_replace(' BINARY', '', $v['comparator']);
660  }
661  }
662  $output .= ' ' . $v['comparator'];
663  // Detecting value type; list or plain:
664  $comparator = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $v['comparator']));
665  if (GeneralUtility::inList('NOTIN,IN', $comparator)) {
666  if (isset($v['subquery'])) {
667  $output .= ' (' . $this->compileSELECT($v['subquery']) . ')';
668  } else {
669  $valueBuffer = array();
670  foreach ($v['value'] as $realValue) {
671  $valueBuffer[] = $realValue[1] . $this->compileAddslashes($realValue[0]) . $realValue[1];
672  }
673 
674  $dbmsSpecifics = $this->databaseConnection->getSpecifics();
675  if ($dbmsSpecifics === NULL) {
676  $output .= ' (' . trim(implode(',', $valueBuffer)) . ')';
677  } else {
678  $chunkedList = $dbmsSpecifics->splitMaxExpressions($valueBuffer);
679  $chunkCount = count($chunkedList);
680 
681  if ($chunkCount === 1) {
682  $output .= ' (' . trim(implode(',', $valueBuffer)) . ')';
683  } else {
684  $listExpressions = array();
685  $field = trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']);
686 
687  switch ($comparator) {
688  case 'IN':
689  $operator = 'OR';
690  break;
691  case 'NOTIN':
692  $operator = 'AND';
693  break;
694  default:
695  $operator = '';
696  }
697 
698  for ($i = 0; $i < $chunkCount; ++$i) {
699  $listPart = trim(implode(',', $chunkedList[$i]));
700  $listExpressions[] = ' (' . $listPart . ')';
701  }
702 
703  $implodeString = ' ' . $operator . ' ' . $field . ' ' . $v['comparator'];
704 
705  // add opening brace before field
706  $lastFieldPos = strrpos($output, $field);
707  $output = substr_replace($output, '(', $lastFieldPos, 0);
708  $output .= implode($implodeString, $listExpressions) . ')';
709  }
710  }
711  }
712  } elseif (GeneralUtility::inList('BETWEEN,NOT BETWEEN', $v['comparator'])) {
713  $lbound = $v['values'][0];
714  $ubound = $v['values'][1];
715  $output .= ' ' . $lbound[1] . $this->compileAddslashes($lbound[0]) . $lbound[1];
716  $output .= ' AND ';
717  $output .= $ubound[1] . $this->compileAddslashes($ubound[0]) . $ubound[1];
718  } elseif (isset($v['value']['operator'])) {
719  $values = array();
720  foreach ($v['value']['args'] as $fieldDef) {
721  $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field'];
722  }
723  $output .= ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';
724  } else {
725  $output .= ' ' . $v['value'][1] . $this->compileAddslashes($v['value'][0]) . $v['value'][1];
726  }
727  }
728  }
729  }
730  }
731  }
732  break;
733  }
734  return $output;
735  }
736 
743  public function debug_testSQL($SQLquery) {
744  // Getting result array:
745  $parseResult = $this->parseSQL($SQLquery);
746  // If result array was returned, proceed. Otherwise show error and exit.
747  if (is_array($parseResult)) {
748  // Re-compile query:
749  $newQuery = $this->compileSQL($parseResult);
750  // TEST the new query:
751  $testResult = $this->debug_parseSQLpartCompare($SQLquery, $newQuery);
752  // Return new query if OK, otherwise show error and exit:
753  if (!is_array($testResult)) {
754  return $newQuery;
755  } else {
756  debug(array('ERROR MESSAGE' => 'Input query did not match the parsed and recompiled query exactly (not observing whitespace)', 'TEST result' => $testResult), 'SQL parsing failed:');
757  die;
758  }
759  } else {
760  debug(array('query' => $SQLquery, 'ERROR MESSAGE' => $parseResult), 'SQL parsing failed:');
761  die;
762  }
763  }
764 }
TYPO3\CMS\Dbal\Database\SqlParser\checkEmptyDefaultValue
checkEmptyDefaultValue($featureIndex)
Definition: SqlParser.php:442
TYPO3\CMS\Core\Utility\GeneralUtility\isFirstPartOfStr
static isFirstPartOfStr($str, $partStr)
Definition: GeneralUtility.php:956
TYPO3\CMS\Dbal\Database\SqlParser\__construct
__construct(DatabaseConnection $databaseConnection=NULL)
Definition: SqlParser.php:35
TYPO3\CMS\Dbal\Database\SqlParser\getValueInQuotes
getValueInQuotes(&$parseString, $quote)
Definition: SqlParser.php:48
die
die
Definition: index.php:6
TYPO3\CMS\Dbal\Database\SqlParser\getValueInQuotesMssql
getValueInQuotesMssql(&$parseString, $quote)
Definition: SqlParser.php:70
TYPO3\CMS\Dbal\Database\SqlParser\compileFieldList
compileFieldList($selectFields, $compileComments=TRUE, $functionMapping=TRUE)
Definition: SqlParser.php:123
debug
debug($variable='', $name=' *variable *', $line=' *line *', $file=' *file *', $recursiveDepth=3, $debugLevel=E_DEBUG)
Definition: GlobalDebugFunctions.php:13
$GLOBALS
if(!defined('TYPO3_MODE')) $GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_userauth.php']['logoff_pre_processing'][]
Definition: ext_localconf.php:5
TYPO3\CMS\Core\Database\SqlParser\debug_parseSQLpartCompare
debug_parseSQLpartCompare($str, $newStr, $caseInsensitive=FALSE)
Definition: SqlParser.php:1996
TYPO3\CMS\Dbal\Database\SqlParser\compileWhereClause
compileWhereClause($clauseArray, $functionMapping=TRUE)
Definition: SqlParser.php:465
TYPO3\CMS\Dbal\Database\SqlParser\compileCaseStatement
compileCaseStatement(array $components, $functionMapping=TRUE)
Definition: SqlParser.php:176