TYPO3 Core

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.