TYPO3 Core

} elseif (isset($v['func']) && $v['func']['type'] === 'FIND_IN_SET') {

$output = ' ' . trim($v['modifier']) . ' ';

switch (TRUE) {

case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $functionMapping):

$field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];

$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]

. ')';

break;

default:

$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;

}

switch (TRUE) {

case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('mssql') && $functionMapping):

$output .= ' = 1';

break;

case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('postgres') && $functionMapping):

$output .= ' != 0';

break;

default:

// Nothing more to do

break;

}

} else {

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 overriden from t3lib_db at the moment...

$patternForLike = $this->escapeStrForLike($pattern, $where_clause[$k]['func']['table']);

$where_clause[$k]['func']['str_like'] = $patternForLike;

// BEWARE: no break here to have next statements too

break;

case 'FIND_IN_SET':

-- Add Compatibility operators

--

-- $Id$

-- Aldo on http://mschat.net/blog/index.php?topic=8

-- FIND_IN_SET

CREATE FUNCTION [dbo].FIND_IN_SET(@needle varchar(255), @haystack varchar(255))

RETURNS BIT

AS

BEGIN

DECLARE @found bit;

SET @found = 0;

IF(CHARINDEX(',', @haystack) > 0)

BEGIN

DECLARE @like_1 varchar(255), @like_2 varchar(255), @like_3 varchar(255);

SET @like_1 = @needle + ',%';

SET @like_2 = '%,' + @needle + ',%';

SET @like_3 = '%,' + @needle;

IF(@haystack LIKE @like_1 OR @haystack LIKE @like_2 OR @haystack LIKE @like_3)

SET @found = 1;

END

ELSE

BEGIN

IF(@haystack = @needle)

SET @found = 1;

END;

RETURN @found;

END;

-- FIND_IN_SET

-- FIND_IN_SET()

CREATE OR REPLACE FUNCTION find_in_set(text, text)

RETURNS integer AS $$

DECLARE

list text[];

len integer;

BEGIN

IF $2 = '' THEN

RETURN 0;

END IF;

list := pg_catalog.string_to_array($2, ',');

len := pg_catalog.array_upper(list, 1);

FOR i IN 1..len LOOP

IF list[i] = $1 THEN

RETURN i;

END IF;

END LOOP;

RETURN 0;

END;

$$ STRICT IMMUTABLE LANGUAGE PLPGSQL;

/**

* @test

* @see http://bugs.typo3.org/view.php?id=14985

*/

public function findInSetIsProperlyRemapped() {

$query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(

'*',

'fe_users',

'FIND_IN_SET(10, usergroup)'

));

$expected = 'SELECT * FROM "fe_users" WHERE FIND_IN_SET(10, "usergroup") != 0';

$this->assertEquals($expected, $query);

}

/**

* @test

* @see http://bugs.typo3.org/view.php?id=14985

*/

public function canParseFindInSetStatement() {

$parseString = 'SELECT * FROM fe_users WHERE FIND_IN_SET(10, usergroup)';

$components = $this->fixture->_callRef('parseSELECT', $parseString);

$this->assertTrue(is_array($components), $components);

$selectTable = $this->cleanSql($this->fixture->_callRef('compileSELECT', $components));

$expected = 'SELECT * FROM fe_users WHERE FIND_IN_SET(10, usergroup)';

$this->assertEquals($expected, $selectTable);

}

/**

* @test

* @see http://bugs.typo3.org/view.php?id=14985

*/

public function findInSetIsProperlyRemapped() {

$query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(

'*',

'fe_users',

'FIND_IN_SET(10, usergroup)'

));

$expected = 'SELECT * FROM "fe_users" WHERE ("usergroup" LIKE \'%,10,%\' OR "usergroup" LIKE \'10,%\' OR "usergroup" LIKE \'%,10\' OR "usergroup"= 10)';

$this->assertEquals($expected, $query);

}

/**

* @test

* @see http://bugs.typo3.org/view.php?id=14985

*/

public function findInSetFieldIsProperlyRemapped() {

$selectFields = 'fe_group';

$fromTables = 'tt_news';

$whereClause = 'FIND_IN_SET(10, fe_group)';

$groupBy = '';

$orderBy = '';

$GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);

$query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));

$expected = 'SELECT "usergroup" FROM "ext_tt_news" WHERE'

. ' ("ext_tt_news"."usergroup" LIKE \'%,10,%\''

. ' OR "ext_tt_news"."usergroup" LIKE \'10,%\''

. ' OR "ext_tt_news"."usergroup" LIKE \'%,10\''

. ' OR "ext_tt_news"."usergroup"= 10)';

$this->assertEquals($expected, $query);

}

'uid' => 'news_uid',

'fe_group' => 'usergroup',

/**

* @test

* @see http://bugs.typo3.org/view.php?id=14985

*/

public function findInSetIsProperlyRemapped() {

$query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(

'*',

'fe_users',

'FIND_IN_SET(10, usergroup)'

));

$expected = 'SELECT * FROM "fe_users" WHERE FIND_IN_SET(10, "usergroup") = 1';

$this->assertEquals($expected, $query);

}