Bug #86151

DatabaseQueryProcessor find_in_set in orderBy Clause fails

Added by Kay Strobach 9 months ago. Updated 9 months ago.

Status:
New
Priority:
Must have
Assignee:
-
Category:
-
Target version:
-
Start date:
2018-09-05
Due date:
% Done:

0%

TYPO3 Version:
8
PHP Version:
7.1
Tags:
Complexity:
Is Regression:
Sprint Focus:

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.

History

#1 Updated by Kay Strobach 9 months ago

btw. find_in_set is mysql specific, so the doctrine behaviour might be right. Maybe a more generic approach to that problem might solve it in the end

Also available in: Atom PDF