Issue Bug Closed

#95841AbstractItemProvider::processForeignTableClause() messes up ORDER BY / GROUP BY clauses with backtick-quoted identifiers.

Leonie Philine 11/01/21, 16:39 updated 02/23/25, 07:22

Description

This bug is about \TYPO3\CMS\Backend\Form\FormDataProvider\AbstractItemProvider::processForeignTableClause()

Regular expressions are used to match ORDER BY and GROUP BY clauses:

https://github.com/TYPO3/typo3/blob/master/typo3/sysext/backend/Classes/Form/FormDataProvider/AbstractItemProvider.php#L866-L877

        // Find ORDER BY
        $reg = [];
        if (preg_match('/^(.*)[[:space:]]+ORDER[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._()"]+)$/is', $foreignTableClause, $reg)) {
            $foreignTableClauseArray['ORDERBY'] = QueryHelper::parseOrderBy(trim($reg[2]));
            $foreignTableClause = $reg[1];
        }
        // Find GROUP BY
        $reg = [];
        if (preg_match('/^(.*)[[:space:]]+GROUP[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._()"]+)$/is', $foreignTableClause, $reg)) {
            $foreignTableClauseArray['GROUPBY'] = QueryHelper::parseGroupBy(trim($reg[2]));
            $foreignTableClause = $reg[1];
        }

These regular expressions exclude use of the backtick character ` which is used in most database systems used with TYPO3 for quoting field names.

To fix this bug, the character class within the ([[:alnum:][:space:],._()"]+) regex match group must be extended by the backtick as possible character.

Otherwise, e.g. 'foreign_table_where' clauses like 'AND `fe_groups`.`pid` = 3169 ORDER BY `fe_groups`.`title`' result in syntax errors, as TYPO3 fails to extract and remove the ORDER BY part, leaving it in the middle of the WHERE clause.

This bug affects at least TYPO3 10, 11 and the main development branch.

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.

History 2

  1. Simon Schaufelberger commented 03/26/22, 21:46

  2. Georg Ringer commented and changed status New Closed 02/23/25, 07:22

    to be compatible with DBAL the quoting is done a bit different, see https://docs.typo3.org/m/typo3/reference-tca/main/en-us/ColumnsConfig/Type/Select/SingleBox/Index.html#confval-select-singlebox-foreign-table-where

    'foreign_table_where' => 'AND {#tx_styleguide_staticdata}.{#value_1} LIKE \'%foo%\' ORDER BY uid',

    therefore closing the issue