/**
* Gets value in quotes from $parseString.
*
* @param string $parseString String from which to find value in quotes. Notice that $parseString is passed by reference and is shortened by the output of this function.
* @param string $quote The quote used; input either " or '
* @return string The value, passed through parseStripslashes()!
*/
protected function getValueInQuotes(&$parseString, $quote)
{
switch ((string) $this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
case 'adodb':
if ($this->databaseConnection->runningADOdbDriver('mssql')) {
$value = $this->getValueInQuotesMssql($parseString, $quote);
} else {
$value = $this->getValueInQuotesGeneric($parseString, $quote);
}
break;
default:
$value = $this->getValueInQuotesGeneric($parseString, $quote);
}
return $value;
}
/**
* @test
*/
public function multipleExpressiosInWhereClauseAreBracedCorrectly()
{
$listMaxExpressions = 1000;
$mockSpecificsOci8 = $this->getAccessibleMock('TYPO3\\CMS\\Dbal\\Database\\Specifics\\Oci8', array(), array(), '', FALSE);
$mockSpecificsOci8->expects($this->any())->method('getSpecific')->will($this->returnValue($listMaxExpressions));
$INitems = range(0, 1250);
$NOTINItems = range(0, 1001);
$where = 'uid = 1981 AND uid IN(' . implode(',', $INitems) . ') OR uid = 42 AND uid NOT IN(' . implode(',', $NOTINItems) . ')';
$result = $this->subject->SELECTquery('uid, pid', 'tt_content', $where);
$chunks = array_chunk($INitems, $listMaxExpressions);
$INItemsWhereExpr = array();
foreach ($chunks as $chunk) {
$INItemsWhereExpr[] = '"uid" IN (' . implode(',', $chunk) . ')';
}
$chunks = array_chunk($NOTINItems, $listMaxExpressions);
$NOTINItemsWhereExpr = array();
foreach ($chunks as $chunk) {
$NOTINItemsWhereExpr[] = '"uid" NOT IN (' . implode(',', $chunk) . ')';
}
/**
* $expectedWhere:
* "uid" = 1981 AND (
* "uid" IN (1,2,3,4,...,1000)
* OR "uid" IN (1001,1002,...,1250)
* ) OR "uid" = 42 AND (
* "uid" NOT IN (1,2,3,4,...,1000)
* AND "uid" NOT IN (1001)
* )
*/
$expectedWhere = '"uid" = 1981 AND (' . implode(' OR ', $INItemsWhereExpr) . ') OR "uid" = 42 AND (' . implode(' AND ', $NOTINItemsWhereExpr) . ')';
$expectedQuery = 'SELECT "uid", "pid" FROM "tt_content" WHERE ' . $expectedWhere;
$this->assertEquals($expectedQuery, $this->cleanSql($result));
}
/**
* Clears URL cache if it is found in the alias table.
*
* @param string $tableName
* @param int $recordId
* @return void
*/
protected function clearUrlCacheForAliasChanges($tableName, $recordId)
{
if (!preg_match('/^(?:pages|sys_|cf_)/', $tableName)) {
$expirationTime = time() + 30 * 24 * 60 * 60;
// This check would be sufficient for most cases but only when id_field is 'uid' in the configuration
$result = $this->databaseConnection->sql_query('SELECT uid,expire,url_cache_id FROM ' . 'tx_realurl_uniqalias LEFT JOIN tx_realurl_uniqalias_cache_map ON uid=alias_uid ' . 'WHERE tablename=' . $this->databaseConnection->fullQuoteStr($tableName, 'tx_realurl_uniqalias') . ' ' . 'AND value_id=' . $recordId);
while (FALSE !== ($data = $this->databaseConnection->sql_fetch_assoc($result))) {
if ($data['url_cache_id']) {
$this->cache->clearUrlCacheById($data['url_cache_id']);
$this->databaseConnection->exec_DELETEquery('tx_realurl_uniqalias_cache_map', 'uid=' . (int) $data['uid']);
}
if ((int) $data['expire'] === 0) {
$this->databaseConnection->exec_UPDATEquery('tx_realurl_uniqalias', 'uid=' . (int) $data['uid'], array('expire' => $expirationTime));
}
}
$this->databaseConnection->sql_free_result($result);
}
}
/**
* Tries to autodetect the target page UID
*
* @throws \RuntimeException If the page id can not be determined
*/
protected function initializePageUid()
{
if (isset($this->pageUid)) {
return;
}
/** @var PageRepository $pageSelect */
$pageSelect = GeneralUtility::makeInstance('TYPO3\\CMS\\Frontend\\Page\\PageRepository');
$whereStatement = "tt_content.list_type='formhandler_pi1' AND pages.uid=tt_content.pid ";
$whereStatement .= 'AND tt_content.pi_flexform LIKE \'%<field index="predefined">%<value index="vDEF">formhandler_subscription_remove_subscription.</value>%</field>%\'';
$whereStatement .= $pageSelect->enableFields('pages');
$whereStatement .= $pageSelect->enableFields('tt_content');
$contentResult = $this->typo3Db->exec_SELECTquery('pages.uid', 'pages,tt_content', $whereStatement, '', '', '1');
if ($contentResult === FALSE) {
throw new \RuntimeException('Error detecting target PID: ' . $this->typo3Db->sql_error());
}
if (!$this->typo3Db->sql_num_rows($contentResult)) {
throw new \RuntimeException('The target PID could not be detected. No active formhandler plugin content element was found.');
}
$row = $this->typo3Db->sql_fetch_row($contentResult);
$this->pageUid = $row[0];
}
/**
* Magic getter for public properties of \mysqli_stmt access
* by \TYPO3\CMS\Core\Database\PreparedStatement.
*
* @param string $name
* @return mixed
*/
public function __get($name)
{
switch ($name) {
case 'errno':
$output = $this->databaseConnection->sql_errno();
break;
case 'error':
$output = $this->databaseConnection->sql_error();
break;
case 'num_rows':
$output = $this->databaseConnection->sql_num_rows($this->recordSet);
break;
default:
throw new \RuntimeException('Cannot access property ' . $name, 1394631927);
}
return $output;
}
/**
* @test
* @see http://forge.typo3.org/issues/21514
*/
public function notLikeBinaryOperatorIsKept()
{
$result = $this->cleanSql($this->subject->SELECTquery('*', 'tt_content', 'bodytext NOT LIKE BINARY \'test\''));
$expected = 'SELECT * FROM tt_content WHERE bodytext NOT LIKE BINARY \'test\'';
$this->assertEquals($expected, $this->cleanSql($result));
}
/**
* @test
* @see http://forge.typo3.org/issues/32626
*/
public function notEqualAnsiOperatorCanBeParsed()
{
$result = $this->subject->SELECTquery('*', 'pages', 'pid<>3');
$expected = 'SELECT * FROM "pages" WHERE "pid" <> 3';
$this->assertEquals($expected, $this->cleanSql($result));
}
/**
* 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)
{
$output = '';
switch ((string) $this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {
case 'native':
$output = parent::compileWhereClause($clauseArray);
break;
case 'adodb':
// 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'] === '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 = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $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];
}
}
}
}
}
}
break;
}
return $output;
}
/**
* @test
* @param string $indexSQL
* @param string $expected
* @dataProvider equivalentIndexDefinitionDataProvider
*/
public function equivalentIndexDefinitionRemovesLengthInformation($indexSQL, $expected)
{
$result = $this->subject->getEquivalentIndexDefinition($indexSQL);
$this->assertSame($expected, $result);
}
/**
* @test
* @param string $fieldSQL
* @param string $expected
* @dataProvider equivalentFieldTypeDataProvider
* @see http://forge.typo3.org/issues/67301
*/
public function suggestEquivalentFieldDefinitions($fieldSQL, $expected)
{
$actual = $this->subject->getEquivalentFieldDefinition($fieldSQL);
$this->assertSame($expected, $actual);
}
/**
* @test
* @see http://forge.typo3.org/issues/27760
*/
public function singleQuotesAreProperlyEscaped()
{
$result = $this->subject->SELECTquery('ISEC.phash', 'index_section ISEC, index_fulltext IFT', 'IFT.fulltextdata LIKE \'%' . $this->subject->quoteStr("Don't worry", 'index_fulltext') . '%\' AND ISEC.phash = IFT.phash', 'ISEC.phash');
$expected = 'SELECT "ISEC"."phash" FROM "index_section" "ISEC", "index_fulltext" "IFT" WHERE "IFT"."fulltextdata" LIKE \'%Don\'\'t worry%\' AND "ISEC"."phash" = "IFT"."phash" GROUP BY "ISEC"."phash"';
$this->assertEquals($expected, $this->cleanSql($result));
}
/**
* @test
* @see http://forge.typo3.org/issues/21514
*/
public function likeBinaryOperatorIsRemoved()
{
$result = $this->subject->SELECTquery('*', 'tt_content', 'bodytext LIKE BINARY \'test\'');
$expected = 'SELECT * FROM "tt_content" WHERE (dbms_lob.instr("bodytext", \'test\',1,1) > 0)';
$this->assertEquals($expected, $this->cleanSql($result));
}
/**
* @test
* @see http://forge.typo3.org/issues/17552
*/
public function IfNullIsProperlyRemapped()
{
$result = $this->subject->SELECTquery('*', 'tt_news_cat_mm', 'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)');
$expected = 'SELECT * FROM "tt_news_cat_mm" WHERE ISNULL("tt_news_cat_mm"."uid_foreign", 0) IN (21,22)';
$this->assertEquals($expected, $this->cleanSql($result));
}
