Bug #87799

Typoscript SELECT: orderBy = ###marker### wont work

Added by Chris W over 2 years ago. Updated 11 months ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
TypoScript
Target version:
-
Start date:
2019-02-26
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
9
PHP Version:
7.2
Tags:
typoscript, select, orderBy, dataProcessing
Complexity:
Is Regression:
Sprint Focus:

Description

I tried to use markers for the TYPOSCRIPT select option "orderBy" as explained in the docs:
https://docs.typo3.org/typo3cms/TyposcriptReference/Functions/Select.html#markers

page.60 = CONTENT
page.60 {
      table = tt_content
      select {
         pidInList = 73
         where = header != ###whatever###
         orderBy = ###sortfield###
         markers {
            whatever.data = GP:first
            sortfield.value = sor
            sortfield.wrap = |ting
         }
      }
}

This works for TYPO3 7 but for 8 or 9 it ends up in an Exception:
Doctrine\DBAL\Exception\InvalidFieldNameException -> .... ORDER BY `'sorting'` ASC': Unknown column ''sorting'' in 'order clause'

If the sorting field is defined without a marker it works as well.
Other TYPOSCRIPT select options like uidInList or max accept markers without any problems.

#1

Updated by Chris W over 2 years ago

  • Description updated (diff)
  • Category set to TypoScript
  • Tags changed from typoscript to typoscript, select, orderBy, dataProcessing
#2

Updated by Benni Mack over 1 year ago

  • Status changed from New to Needs Feedback

Hey Chris,

how about we'll do it like this?

      table = tt_content
      select {
         pidInList = 308
         where = header != '###whatever###'
         orderBy = sor
         orderBy.wrap = |ting
         markers {
            whatever = leer
         }
      }

We should probably update the documentation that markers isn't the way to go.

#3

Updated by Chris W over 1 year ago

'where' accpets values by markers. Only 'orderBy' wont accpet marker values.
Its just the example in the doc which wont work as explained.

page.60 = CONTENT
page.60 {
      table = tt_content
      select {
         pidInList = 73
         where = header != ###whatever###
         orderBy = ###sortfield###
         markers {
            whatever.data = GP:first
            sortfield.value = sor
            sortfield.wrap = |ting
         }
      }
}

Throws:
Doctrine\DBAL\Exception\SyntaxErrorException
ORDER BY `'uid` DESC'': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1

This one without markers works:

page.60 = CONTENT
page.60 {
      table = tt_content
      select {
         pidInList = 73
         where = header != ###whatever###
         orderBy = sorting
         markers {
            whatever.data = GP:first
         }
      }
}

So in case an editor should switch the sorting field or sorting order for something, you can't store his selection to a marker and use this in TS select orderBy.
It's not such necessary but i lost some hours in this cause the docs example.

#4

Updated by Riccardo De Contardi over 1 year ago

  • Status changed from Needs Feedback to New
#5

Updated by Sybille Peters about 1 year ago

Added issue for docs: https://github.com/TYPO3-Documentation/TYPO3CMS-Reference-Typoscript/issues/352


The problem is that the value in the marker is quoted in single quotes which is not correct if a field is to be used in orderBy. (ORDER BY 'sorting').

Quoting in backticks (`sorting`) would be ok.

Connection::quote() is used to quote in single quotes.

Connection::quoteIdentifier() could be used instead.

I don't see an easy way to solve this as the function ContentObjectRenderer::getQueryMarkers() does the quoting in a general way and there is no way to specify how and if the value from the marker should be quoted.

#6

Updated by Jigal van Hemert 11 months ago

The markers concept was introduced to easily prevent SQL injection with external data in queries. In most cases this data is used as values in expressions and not to create table or column names (or even functions).

As table and column names can be quoted according to documentation, it would be possible to apply quoting with {#...}. Currently this would produce incorrect results because of quotes from markers: `'column_name'`.

This problem could be fixed if \TYPO3\CMS\Core\Database\Query\QueryHelper::quoteDatabaseIdentifiers() would trim any quotes that exist around the matched identifier. I haven't tested this yet.
Yes, the construction would be very ugly, but it's not a very common case: orderBy = {####sortfield###}

Also available in: Atom PDF