/** * Implodes an array of WHERE clause configuration into a WHERE clause. * * @param array $clauseArray WHERE clause configuration * @param bool $functionMapping * @return string WHERE clause as string. * @see explodeWhereClause() */ public function compileWhereClause($clauseArray, $functionMapping = true) { // Prepare buffer variable: $output = ''; // Traverse clause array: if (is_array($clauseArray)) { foreach ($clauseArray as $k => $v) { // Set operator: $output .= $v['operator'] ? ' ' . $v['operator'] : ''; // Look for sublevel: if (is_array($v['sub'])) { $output .= ' (' . trim($this->compileWhereClause($v['sub'])) . ')'; } elseif (isset($v['func']) && $v['func']['type'] === 'EXISTS') { $output .= ' ' . trim($v['modifier']) . ' EXISTS (' . $this->compileSELECT($v['func']['subquery']) . ')'; } else { if (isset($v['func']) && $v['func']['type'] === 'LOCATE') { $output .= ' ' . trim($v['modifier']) . ' LOCATE('; $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1]; $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : ''; $output .= ')'; } elseif (isset($v['func']) && $v['func']['type'] === 'IFNULL') { $output .= ' ' . trim($v['modifier']) . ' IFNULL('; $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; $output .= ', ' . $v['func']['default'][1] . $this->compileAddslashes($v['func']['default'][0]) . $v['func']['default'][1]; $output .= ')'; } elseif (isset($v['func']) && $v['func']['type'] === 'CAST') { $output .= ' ' . trim($v['modifier']) . ' CAST('; $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; $output .= ' AS ' . $v['func']['datatype'][0]; $output .= ')'; } elseif (isset($v['func']) && $v['func']['type'] === 'FIND_IN_SET') { $output .= ' ' . trim($v['modifier']) . ' FIND_IN_SET('; $output .= $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1]; $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; $output .= ')'; } else { // Set field/table with modifying prefix if any: $output .= ' ' . trim($v['modifier'] . ' ' . ($v['table'] ? $v['table'] . '.' : '') . $v['field']); // Set calculation, if any: if ($v['calc']) { $output .= $v['calc'] . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1]; } } // Set comparator: if ($v['comparator']) { $output .= ' ' . $v['comparator']; // Detecting value type; list or plain: if (GeneralUtility::inList('NOTIN,IN', SqlParser::normalizeKeyword($v['comparator']))) { if (isset($v['subquery'])) { $output .= ' (' . $this->compileSELECT($v['subquery']) . ')'; } else { $valueBuffer = array(); foreach ($v['value'] as $realValue) { $valueBuffer[] = $realValue[1] . $this->compileAddslashes($realValue[0]) . $realValue[1]; } $output .= ' (' . trim(implode(',', $valueBuffer)) . ')'; } } else { if (GeneralUtility::inList('BETWEEN,NOT BETWEEN', $v['comparator'])) { $lbound = $v['values'][0]; $ubound = $v['values'][1]; $output .= ' ' . $lbound[1] . $this->compileAddslashes($lbound[0]) . $lbound[1]; $output .= ' AND '; $output .= $ubound[1] . $this->compileAddslashes($ubound[0]) . $ubound[1]; } else { if (isset($v['value']['operator'])) { $values = array(); foreach ($v['value']['args'] as $fieldDef) { $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field']; } $output .= ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')'; } else { $output .= ' ' . $v['value'][1] . $this->compileAddslashes($v['value'][0]) . $v['value'][1]; } } } } } } } // Return output buffer: return $output; }
/** * Implodes an array of WHERE clause configuration into a WHERE clause. * * DBAL-specific: The only(!) handled "calc" operators supported by parseWhereClause() are: * - the bitwise logical and (&) * - the addition (+) * - the substraction (-) * - the multiplication (*) * - the division (/) * - the modulo (%) * * @param array $clauseArray * @param bool $functionMapping * @return string WHERE clause as string. * @see \TYPO3\CMS\Core\Database\SqlParser::parseWhereClause() */ public function compileWhereClause($clauseArray, $functionMapping = true) { // Prepare buffer variable: $output = ''; // Traverse clause array: if (is_array($clauseArray)) { foreach ($clauseArray as $v) { // Set operator: $output .= $v['operator'] ? ' ' . $v['operator'] : ''; // Look for sublevel: if (is_array($v['sub'])) { $output .= ' (' . trim($this->compileWhereClause($v['sub'], $functionMapping)) . ')'; } elseif (isset($v['func']) && $v['func']['type'] === 'EXISTS') { $output .= ' ' . trim($v['modifier']) . ' EXISTS (' . $this->compileSELECT($v['func']['subquery']) . ')'; } else { if (isset($v['func']) && $v['func']['type'] === 'CAST') { $output .= ' ' . trim($v['modifier']); $output .= ' CAST('; $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; $output .= ' AS ' . $v['func']['datatype'][0] . ')'; } elseif (isset($v['func']) && $v['func']['type'] === 'LOCATE') { $output .= ' ' . trim($v['modifier']); switch (true) { case $this->databaseConnection->runningADOdbDriver('mssql') && $functionMapping: $output .= ' CHARINDEX('; $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1]; $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : ''; $output .= ')'; break; case $this->databaseConnection->runningADOdbDriver('oci8') && $functionMapping: $output .= ' INSTR('; $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; $output .= ', ' . $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1]; $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : ''; $output .= ')'; break; default: $output .= ' LOCATE('; $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1]; $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : ''; $output .= ')'; } } elseif (isset($v['func']) && $v['func']['type'] === 'IFNULL') { $output .= ' ' . trim($v['modifier']) . ' '; switch (true) { case $this->databaseConnection->runningADOdbDriver('mssql') && $functionMapping: $output .= 'ISNULL'; break; case $this->databaseConnection->runningADOdbDriver('oci8') && $functionMapping: $output .= 'NVL'; break; default: $output .= 'IFNULL'; } $output .= '('; $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; $output .= ', ' . $v['func']['default'][1] . $this->compileAddslashes($v['func']['default'][0]) . $v['func']['default'][1]; $output .= ')'; } elseif (isset($v['func']) && $v['func']['type'] === 'FIND_IN_SET') { $output .= ' ' . trim($v['modifier']) . ' '; if ($functionMapping) { switch (true) { case $this->databaseConnection->runningADOdbDriver('mssql'): $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; if (!isset($v['func']['str_like'])) { $v['func']['str_like'] = $v['func']['str'][0]; } $output .= '\',\'+' . $field . '+\',\' LIKE \'%,' . $v['func']['str_like'] . ',%\''; break; case $this->databaseConnection->runningADOdbDriver('oci8'): $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; if (!isset($v['func']['str_like'])) { $v['func']['str_like'] = $v['func']['str'][0]; } $output .= '\',\'||' . $field . '||\',\' LIKE \'%,' . $v['func']['str_like'] . ',%\''; break; case $this->databaseConnection->runningADOdbDriver('postgres'): $output .= ' FIND_IN_SET('; $output .= $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1]; $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; $output .= ') != 0'; break; default: $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; if (!isset($v['func']['str_like'])) { $v['func']['str_like'] = $v['func']['str'][0]; } $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] . ')'; } } else { switch (true) { case $this->databaseConnection->runningADOdbDriver('mssql'): case $this->databaseConnection->runningADOdbDriver('oci8'): case $this->databaseConnection->runningADOdbDriver('postgres'): $output .= ' FIND_IN_SET('; $output .= $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1]; $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; $output .= ')'; break; default: $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field']; if (!isset($v['func']['str_like'])) { $v['func']['str_like'] = $v['func']['str'][0]; } $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] . ')'; } } } else { // Set field/table with modifying prefix if any: $output .= ' ' . trim($v['modifier']) . ' '; // DBAL-specific: Set calculation, if any: if ($v['calc'] === '&' && $functionMapping) { switch (true) { case $this->databaseConnection->runningADOdbDriver('oci8'): // Oracle only knows BITAND(x,y) - sigh $output .= 'BITAND(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ',' . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1] . ')'; break; default: // MySQL, MS SQL Server, PostgreSQL support the &-syntax $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . $v['calc'] . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1]; } } elseif ($v['calc']) { $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . $v['calc']; if (isset($v['calc_table'])) { $output .= trim(($v['calc_table'] ? $v['calc_table'] . '.' : '') . $v['calc_field']); } else { $output .= $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1]; } } elseif (!($this->databaseConnection->runningADOdbDriver('oci8') && preg_match('/(NOT )?LIKE( BINARY)?/', $v['comparator']) && $functionMapping)) { $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']); } } // Set comparator: if ($v['comparator']) { $isLikeOperator = preg_match('/(NOT )?LIKE( BINARY)?/', $v['comparator']); switch (true) { case $this->databaseConnection->runningADOdbDriver('oci8') && $isLikeOperator && $functionMapping: // Oracle cannot handle LIKE on CLOB fields - sigh if (isset($v['value']['operator'])) { $values = array(); foreach ($v['value']['args'] as $fieldDef) { $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field']; } $compareValue = ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')'; } else { $compareValue = $v['value'][1] . $this->compileAddslashes(trim($v['value'][0], '%')) . $v['value'][1]; } if (GeneralUtility::isFirstPartOfStr($v['comparator'], 'NOT')) { $output .= 'NOT '; } // To be on the safe side $isLob = true; if ($v['table']) { // Table and field names are quoted: $tableName = substr($v['table'], 1, strlen($v['table']) - 2); $fieldName = substr($v['field'], 1, strlen($v['field']) - 2); $fieldType = $this->databaseConnection->sql_field_metatype($tableName, $fieldName); $isLob = $fieldType === 'B' || $fieldType === 'XL'; } if (strtoupper(substr($v['comparator'], -6)) === 'BINARY') { if ($isLob) { $output .= '(dbms_lob.instr(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ', ' . $compareValue . ',1,1) > 0)'; } else { $output .= '(instr(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ', ' . $compareValue . ',1,1) > 0)'; } } else { if ($isLob) { $output .= '(dbms_lob.instr(LOWER(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . '), ' . GeneralUtility::strtolower($compareValue) . ',1,1) > 0)'; } else { $output .= '(instr(LOWER(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . '), ' . GeneralUtility::strtolower($compareValue) . ',1,1) > 0)'; } } break; default: if ($isLikeOperator && $functionMapping) { if ($this->databaseConnection->runningADOdbDriver('postgres') || $this->databaseConnection->runningADOdbDriver('postgres64') || $this->databaseConnection->runningADOdbDriver('postgres7') || $this->databaseConnection->runningADOdbDriver('postgres8')) { // Remap (NOT)? LIKE to (NOT)? ILIKE // and (NOT)? LIKE BINARY to (NOT)? LIKE switch ($v['comparator']) { case 'LIKE': $v['comparator'] = 'ILIKE'; break; case 'NOT LIKE': $v['comparator'] = 'NOT ILIKE'; break; default: $v['comparator'] = str_replace(' BINARY', '', $v['comparator']); } } else { // No more BINARY operator $v['comparator'] = str_replace(' BINARY', '', $v['comparator']); } } $output .= ' ' . $v['comparator']; // Detecting value type; list or plain: $comparator = SqlParser::normalizeKeyword($v['comparator']); if (GeneralUtility::inList('NOTIN,IN', $comparator)) { if (isset($v['subquery'])) { $output .= ' (' . $this->compileSELECT($v['subquery']) . ')'; } else { $valueBuffer = array(); foreach ($v['value'] as $realValue) { $valueBuffer[] = $realValue[1] . $this->compileAddslashes($realValue[0]) . $realValue[1]; } $dbmsSpecifics = $this->databaseConnection->getSpecifics(); if ($dbmsSpecifics === null) { $output .= ' (' . trim(implode(',', $valueBuffer)) . ')'; } else { $chunkedList = $dbmsSpecifics->splitMaxExpressions($valueBuffer); $chunkCount = count($chunkedList); if ($chunkCount === 1) { $output .= ' (' . trim(implode(',', $valueBuffer)) . ')'; } else { $listExpressions = array(); $field = trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']); switch ($comparator) { case 'IN': $operator = 'OR'; break; case 'NOTIN': $operator = 'AND'; break; default: $operator = ''; } for ($i = 0; $i < $chunkCount; ++$i) { $listPart = trim(implode(',', $chunkedList[$i])); $listExpressions[] = ' (' . $listPart . ')'; } $implodeString = ' ' . $operator . ' ' . $field . ' ' . $v['comparator']; // add opening brace before field $lastFieldPos = strrpos($output, $field); $output = substr_replace($output, '(', $lastFieldPos, 0); $output .= implode($implodeString, $listExpressions) . ')'; } } } } elseif (GeneralUtility::inList('BETWEEN,NOT BETWEEN', $v['comparator'])) { $lbound = $v['values'][0]; $ubound = $v['values'][1]; $output .= ' ' . $lbound[1] . $this->compileAddslashes($lbound[0]) . $lbound[1]; $output .= ' AND '; $output .= $ubound[1] . $this->compileAddslashes($ubound[0]) . $ubound[1]; } elseif (isset($v['value']['operator'])) { $values = array(); foreach ($v['value']['args'] as $fieldDef) { $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field']; } $output .= ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')'; } else { $output .= ' ' . $v['value'][1] . $this->compileAddslashes($v['value'][0]) . $v['value'][1]; } } } } } } return $output; }
/** * Quotes field names in a SQL WHERE clause according to DB rules * * @param array $where_clause The parsed WHERE clause to quote * @return array * @see quoteWhereClause() */ protected function _quoteWhereClause(array $where_clause) { foreach ($where_clause as $k => $v) { // Look for sublevel: if (is_array($where_clause[$k]['sub'])) { $where_clause[$k]['sub'] = $this->_quoteWhereClause($where_clause[$k]['sub']); } elseif (isset($v['func'])) { switch ($where_clause[$k]['func']['type']) { case 'EXISTS': $where_clause[$k]['func']['subquery'] = $this->quoteSELECTsubquery($v['func']['subquery']); break; case 'FIND_IN_SET': // quoteStr that will be used for Oracle $pattern = str_replace($where_clause[$k]['func']['str'][1], '\\' . $where_clause[$k]['func']['str'][1], $where_clause[$k]['func']['str'][0]); // table is not really needed and may in fact be empty in real statements // but it's not overridden from \TYPO3\CMS\Core\Database\DatabaseConnection at the moment... $patternForLike = $this->escapeStrForLike($pattern, $where_clause[$k]['func']['table']); $where_clause[$k]['func']['str_like'] = $patternForLike; if ($where_clause[$k]['func']['table'] !== '') { if ($this->dbmsSpecifics->getSpecific(Specifics\AbstractSpecifics::CAST_FIND_IN_SET)) { $where_clause[$k]['func']['table'] = 'CAST(' . $this->quoteName($v['func']['table']); } else { $where_clause[$k]['func']['table'] = $this->quoteName($v['func']['table']); } } if ($where_clause[$k]['func']['field'] !== '') { if ($this->dbmsSpecifics->getSpecific(Specifics\AbstractSpecifics::CAST_FIND_IN_SET)) { if ($where_clause[$k]['func']['table'] !== '') { $where_clause[$k]['func']['field'] = $this->quoteName($v['func']['field']) . ' AS CHAR)'; } else { $where_clause[$k]['func']['field'] = 'CAST(' . $this->quoteName($v['func']['field']) . ' AS CHAR)'; } } else { $where_clause[$k]['func']['field'] = $this->quoteName($v['func']['field']); } } break; case 'CAST': // Intentional fallthrough // Intentional fallthrough case 'IFNULL': // Intentional fallthrough // Intentional fallthrough case 'LOCATE': if ($where_clause[$k]['func']['table'] != '') { $where_clause[$k]['func']['table'] = $this->quoteName($v['func']['table']); } if ($where_clause[$k]['func']['field'] != '') { $where_clause[$k]['func']['field'] = $this->quoteName($v['func']['field']); } break; } } else { if ($where_clause[$k]['table'] != '') { $where_clause[$k]['table'] = $this->quoteName($where_clause[$k]['table']); } if (!is_numeric($where_clause[$k]['field'])) { $where_clause[$k]['field'] = $this->quoteName($where_clause[$k]['field']); } if (isset($where_clause[$k]['calc_table'])) { if ($where_clause[$k]['calc_table'] != '') { $where_clause[$k]['calc_table'] = $this->quoteName($where_clause[$k]['calc_table']); } if ($where_clause[$k]['calc_field'] != '') { $where_clause[$k]['calc_field'] = $this->quoteName($where_clause[$k]['calc_field']); } } } if ($where_clause[$k]['comparator']) { if (isset($v['value']['operator'])) { foreach ($where_clause[$k]['value']['args'] as $argK => $fieldDef) { $where_clause[$k]['value']['args'][$argK]['table'] = $this->quoteName($fieldDef['table']); $where_clause[$k]['value']['args'][$argK]['field'] = $this->quoteName($fieldDef['field']); } } else { // Detecting value type; list or plain: $comparator = $this->SQLparser->normalizeKeyword($where_clause[$k]['comparator']); if ($comparator === 'NOTIN' || $comparator === 'IN') { if (isset($v['subquery'])) { $where_clause[$k]['subquery'] = $this->quoteSELECTsubquery($v['subquery']); } } else { if ((!isset($where_clause[$k]['value'][1]) || $where_clause[$k]['value'][1] == '') && is_string($where_clause[$k]['value'][0]) && strstr($where_clause[$k]['value'][0], '.')) { $where_clause[$k]['value'][0] = $this->quoteFieldNames($where_clause[$k]['value'][0]); } elseif ($this->runningADOdbDriver('mssql')) { $where_clause[$k]['value'][0] = substr($this->handlerInstance[$this->lastHandlerKey]->qstr($where_clause[$k]['value'][0]), 1, -1); } } } } } return $where_clause; }