Bug #23103 » 15001_dbal_v4.diff
| class.ux_t3lib_sqlparser.php (working copy) | ||
|---|---|---|
|
// Set comparator:
|
||
|
if ($v['comparator']) {
|
||
|
switch (TRUE) {
|
||
|
case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $v['comparator'] === 'LIKE' && $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 ($v['comparator'] === 'LIKE') {
|
||
|
if ($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $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'];
|
||
|
}
|
||
|
// 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 = $GLOBALS['TYPO3_DB']->sql_field_metatype($tableName, $fieldName);
|
||
|
$isLob = ($fieldType === 'B' || $fieldType === 'XL');
|
||
|
$compareValue = ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';
|
||
|
} else {
|
||
|
// Remove %'s at the beginning and at the end
|
||
|
$value = trim($v['value'][0], '%');
|
||
|
// But keep it within the search string if it is escaped => literal
|
||
|
if (substr($value, -1) === '\\') {
|
||
|
$value .= '%';
|
||
|
}
|
||
|
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)';
|
||
|
}
|
||
|
break;
|
||
|
default:
|
||
|
$output .= ' ' . $v['comparator'];
|
||
|
// Detecting value type; list or plain:
|
||
|
if (t3lib_div::inList('NOTIN,IN', strtoupper(str_replace(array(' ', "\t", "\r", "\n"), '', $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 (isset($v['value']['operator'])) {
|
||
|
// As we are going to use the instr method, wildcards should not be escaped anymore
|
||
|
$value = preg_replace('/\\\\([%_])/', '$1', $value);
|
||
|
$compareValue = $v['value'][1] . $this->compileAddslashes($value) . $v['value'][1];
|
||
|
}
|
||
|
// 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 = $GLOBALS['TYPO3_DB']->sql_field_metatype($tableName, $fieldName);
|
||
|
$isLob = ($fieldType === 'B' || $fieldType === 'XL');
|
||
|
}
|
||
|
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 {
|
||
|
$output .= ' LIKE ';
|
||
|
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) . ')';
|
||
|
$output .= $v['value']['operator'] . '(' . implode(',', $values) . ')';
|
||
|
} else {
|
||
|
$output .= ' ' . $v['value'][1] . $this->compileAddslashes($v['value'][0]) . $v['value'][1];
|
||
|
$output .= $v['value'][1] . $this->compileAddslashes($v['value'][0]) . $v['value'][1];
|
||
|
}
|
||
|
break;
|
||
|
if ($GLOBALS['TYPO3_DB']->runningADOdbDriver('mssql') && $functionMapping && preg_match('/\\\\[%_]/', $v['value'][0])) {
|
||
|
// Escaped wilcards need special handling with MSSQL
|
||
|
$output .= ' ESCAPE \'\\\'';
|
||
|
}
|
||
|
}
|
||
|
} else /* $v['comparator'] !== 'LIKE' */ {
|
||
|
$output .= ' ' . $v['comparator'];
|
||
|
// Detecting value type; list or plain:
|
||
|
if (t3lib_div::inList('NOTIN,IN', strtoupper(str_replace(array(' ', "\t", "\r", "\n"), '', $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 (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];
|
||
|
}
|
||
|
}
|
||
|
}
|
||
|
}
|
||
| class.ux_t3lib_db.php (working copy) | ||
|---|---|---|
|
$table = $queryParts['TABLE'];
|
||
|
$values = array();
|
||
|
if (isset($queryParts['VALUES_ONLY']) && is_array($queryParts['VALUES_ONLY'])) {
|
||
|
$fields = $GLOBALS['TYPO3_DB']->cache_fieldType[$table];
|
||
|
$fields = $this->cache_fieldType[$table];
|
||
|
$fc = 0;
|
||
|
foreach ($fields as $fn => $fd) {
|
||
|
$values[$fn] = $queryParts['VALUES_ONLY'][$fc++][0];
|
||
| ... | ... | |
|
return $query;
|
||
|
}
|
||
|
/**
|
||
|
* Returns a WHERE clause which will make an AND search for the words in the $searchWords array
|
||
|
* in any of the fields in array $fields.
|
||
|
*
|
||
|
* @param array Array of search words
|
||
|
* @param array Array of fields
|
||
|
* @param string Table in which we are searching (for DBAL detection of quoteStr() method)
|
||
|
* @return string WHERE clause for search
|
||
|
* @see removeUnsupportedFieldsForLike()
|
||
|
*/
|
||
|
public function searchQuery($searchWords, $fields, $table) {
|
||
|
$queryPart = $this->removeUnsupportedFieldsForLike($searchWords, $fields, $table);
|
||
|
if (strlen($queryPart) > 0) {
|
||
|
$queryPart .= ' OR ';
|
||
|
}
|
||
|
if (count($fields) > 0) {
|
||
|
$queryPart .= parent::searchQuery($searchWords, $fields, $table);
|
||
|
}
|
||
|
return $queryPart;
|
||
|
}
|
||
|
/**************************************
|
||
|
*
|
||
|
* Functions for quoting table/field names
|
||
| ... | ... | |
|
}
|
||
|
}
|
||
|
/**
|
||
|
* Returns a where clause part or an empty string, deletes all unsupported fields from referenced fields-array.
|
||
|
*
|
||
|
* DBAL specific: Oracle: no LIKE for numeric fields, use exact search instead
|
||
|
* Oracle: no LIKE for BLOB fields
|
||
|
*
|
||
|
* @param array Array of search words
|
||
|
* @param array Array of fields, passed by reference!
|
||
|
* @param string Table in which we are searching (for DBAL detection of quoteStr() method)
|
||
|
* @return string WHERE clause for search
|
||
|
*/
|
||
|
protected function removeUnsupportedFieldsForLike($searchWords, &$fields, $table) {
|
||
|
$queryPart = '';
|
||
|
if ($this->runningADOdbDriver('oci8')) {
|
||
|
$numericQueryParts = array();
|
||
|
$numericSearchWords = array_filter($searchWords, 'is_numeric');
|
||
|
foreach ($fields as $key => $fieldName) {
|
||
|
switch ($this->cache_fieldType[$table][$fieldName]['metaType']) {
|
||
|
case 'I':
|
||
|
case 'I1':
|
||
|
case 'I2':
|
||
|
case 'I4':
|
||
|
case 'I8':
|
||
|
case 'F':
|
||
|
// Looking within numeric fields with an exact search
|
||
|
foreach ($numericSearchWords as $word) {
|
||
|
$numericQueryParts[] = $fields[$key] . '=' . intval($word);
|
||
|
}
|
||
|
unset($fields[$key]);
|
||
|
break;
|
||
|
case 'B':
|
||
|
// Remove all BLOB columns, as LIKE is not supported on them...
|
||
|
unset($fields[$key]);
|
||
|
break;
|
||
|
default:
|
||
|
// Do nothing
|
||
|
}
|
||
|
}
|
||
|
reset($fields);
|
||
|
$queryPart .= implode(' OR ', $numericQueryParts);
|
||
|
}
|
||
|
return $queryPart;
|
||
|
}
|
||
|
/**************************************
|
||
|
*
|
||
|
* SQL wrapper functions (Overriding parent methods)
|
||
| tests/dbOracleTest.php (working copy) | ||
|---|---|---|
|
$this->assertEquals($expected, $select);
|
||
|
}
|
||
|
/**
|
||
|
* @test
|
||
|
* @see http://bugs.typo3.org/view.php?id=15001
|
||
|
*/
|
||
|
public function wildcardCharacterCanBeUsedWithLike() {
|
||
|
$select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
|
||
|
'*',
|
||
|
'pages',
|
||
|
'title LIKE \'%\\%%\''
|
||
|
));
|
||
|
$expected = 'SELECT * FROM "pages" WHERE (dbms_lob.instr("title", \'%\',1,1) > 0)';
|
||
|
$this->assertEquals($expected, $select);
|
||
|
$select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
|
||
|
'*',
|
||
|
'pages',
|
||
|
'title LIKE \'%Some\\%thing\\_Else\\_%\''
|
||
|
));
|
||
|
$expected = 'SELECT * FROM "pages" WHERE (dbms_lob.instr("title", \'Some%thing_Else_\',1,1) > 0)';
|
||
|
$this->assertEquals($expected, $select);
|
||
|
}
|
||
|
///////////////////////////////////////
|
||
|
// Tests concerning DB management
|
||
|
///////////////////////////////////////
|
||
| tests/dbMssqlTest.php (working copy) | ||
|---|---|---|
|
}
|
||
|
///////////////////////////////////////
|
||
|
// Tests concerning remapping
|
||
|
///////////////////////////////////////
|
||
|
/**
|
||
|
* @test
|
||
|
* @see http://bugs.typo3.org/view.php?id=15001
|
||
|
*/
|
||
|
public function wildcardCharacterCanBeUsedWithLike() {
|
||
|
$select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
|
||
|
'*',
|
||
|
'pages',
|
||
|
'title LIKE \'%\\%%\''
|
||
|
));
|
||
|
$expected = 'SELECT * FROM "pages" WHERE "title" LIKE \'%\\%%\' ESCAPE \'\\\'';
|
||
|
$this->assertEquals($expected, $select);
|
||
|
$select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
|
||
|
'*',
|
||
|
'pages',
|
||
|
'title LIKE \'%Some\\%thing\\_Else\\_%\''
|
||
|
));
|
||
|
$expected = 'SELECT * FROM "pages" WHERE "title" LIKE \'%Some\\%thing\\_Else\\_%\' ESCAPE \'\\\'';
|
||
|
$this->assertEquals($expected, $select);
|
||
|
}
|
||
|
///////////////////////////////////////
|
||
|
// Tests concerning advanced operators
|
||
|
///////////////////////////////////////
|
||
| ext_localconf.php (working copy) | ||
|---|---|---|
|
$TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_db.php'] = t3lib_extMgm::extPath('dbal') . 'class.ux_t3lib_db.php';
|
||
|
$TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlparser.php'] = t3lib_extMgm::extPath('dbal') . 'class.ux_t3lib_sqlparser.php';
|
||
|
$TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['typo3/class.db_list_extra.inc'] = t3lib_extMgm::extPath('dbal') . 'class.ux_db_list_extra.php';
|
||
|
// Register a hook for the installer
|
||
|
$GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['ext/install/mod/class.tx_install.php']['stepOutput'][] = 'EXT:dbal/class.tx_dbal_installtool.php:tx_dbal_installtool';
|
||