Issue Bug New Orphaned

#86151DatabaseQueryProcessor find_in_set in orderBy Clause fails

Kay Strobach 09/05/18, 11:43 updated 02/25/25, 06:15

Description

Code from current 7.6 project – verified to work properly

# Add content element teaser
tt_content {
    menu =< lib.fluidContent
    menu {
        templateName = Teaser.html
        dataProcessing {
            10 = TYPO3\CMS\Frontend\DataProcessing\DatabaseQueryProcessor
            10 {
                if.isTrue.field = pages
                table = pages
                uidInList.field = pages
                pidInList = 0
                as = pageUids
                orderBy {
                    dataWrap = find_in_set(uid,'|')
                    field = pages
                }

                dataProcessing {
                    10 = TYPO3\CMS\Frontend\DataProcessing\FilesProcessor
                    10 {
                        references.fieldName = media
                        as = files
                    }
                }
            }
            20 < .10
            20 {
                if.isTrue.field = selected_categories
                fieldName = selected_categories
                as = categoryUids
            }
        }
        stdWrap {
            # Setup the edit icon for content element "menu"
            editIcons = tt_content: header [header_layout], menu_type, pages
            editIcons {
                iconTitle.data = LLL:EXT:fluid_styled_content/Resources/Private/Language/FrontendEditing.xlf:editIcon.menu
            }
        }
    }
}

The code utilizes find_in_set to ensure, that the order of the values in the select field is honored for the menu entries.

The problem lays in

\TYPO3\CMS\Frontend\DataProcessing\DatabaseQueryProcessor::process -> L 93
\TYPO3\CMS\Frontend\ContentObject\ContentObjectRenderer::getRecords -> L 7075
\TYPO3\CMS\Frontend\ContentObject\ContentObjectRenderer::exec_getQuery -> L 7056
\TYPO3\CMS\Frontend\ContentObject\ContentObjectRenderer::getQuery -> L 7224
\TYPO3\CMS\Core\Database\Query\QueryBuilder::addOrderBy --> L 811
$this->concreteQueryBuilder->addOrderBy($this->connection->quoteIdentifier($fieldName), $order);
                                           ^-- add escaping sequence, which breaks the query for that special case

I would expect the find_in_set function to work properly.

We have 3 working workarounds, which are both not optimal:

  • remove ordering or order via title
  • use a second php based Dataprocessor to get the ordering right
  • use native queries and bypass doctrine querybuilder

I consider that as a bug, as it breaks functionality which was there for a very long time.
On the other hand escaping the values in the querybuilder might avoid certain security vectors.

I’m excited to get your feedback.

Reviews 0

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

Fix this issue

Start the commit message of your patch with this. Add what the patch does, and the versions it goes to in "Releases:". How to create a patch

Commit message
[BUGFIX] DatabaseQueryProcessor find_in_set in orderBy Clause fails Resolves: #86151 Releases: main