/** * 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)); }