Issue Bug Closed

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

Chris W 02/26/19, 18:26 updated 08/13/26, 18:11

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.

Reviews 0

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

History 7

  1. Chris W changed description category Tags typoscript typoscript, select, orderBy, dataProcessing <span data-processor="relativetime" data-value="2019-02-26T18:29:15+00:00" > 02/26/19, 18:29 </span>

  2. Benni Mack commented and changed status New Needs Feedback <span data-processor="relativetime" data-value="2020-01-16T15:29:04+00:00" > 01/16/20, 15:29 </span>

    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. Chris W commented <span data-processor="relativetime" data-value="2020-01-17T18:25:01+00:00" > 01/17/20, 18:25 </span>

    ‘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. Sybille Peters commented <span data-processor="relativetime" data-value="2020-08-31T11:04:38+00:00" > 08/31/20, 11:04 </span>

    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.

  5. Jigal van Hemert commented <span data-processor="relativetime" data-value="2020-11-05T13:50:27+00:00" > 11/05/20, 13:50 </span>

    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###}

  6. Georg Ringer commented and changed status New Closed <span data-processor="relativetime" data-value="2026-08-13T18:11:35+00:00" > 08/13/26, 18:11 </span>

    The problem is not that orderBy cannot be quoted — it is that markers apply value*
    quoting while orderBy needs *identifier
    quoting.

    ContentObjectRenderer::getQueryMarkers() quotes every non-numeric marker value with
    @Connection::quote()@, so sorting becomes 'sorting'. That value is substituted into
    @orderBy@, QueryHelper::parseOrderBy() splits it into field name and direction, and
    @QueryBuilder::addOrderBy()@ then unconditionally applies quoteIdentifier() to the field
    name. The result is `'sorting'` — hence the “Unknown column” error.

    A marker cannot know which of the two it should be: the very same marker may be used in
    @where@ (needs value quoting) and in orderBy (needs identifier quoting) within one
    @select@.

    Handling this generically is not possible either:

    • orderBy is not a single identifier. sorting DESC, sorting DESC, uid ASC,
      FIND_IN_SET(uid, '1,2,3') and RAND() are all valid values. Applying
      quoteIdentifier() to the marker value would break every one of them.
    • The purpose of the marker mechanism is SQL injection safety for values. There is no
      escaping that makes arbitrary user input safe inside an ORDER BY clause — only an allow
      list of permitted fields does. A “safe” orderBy marker would therefore be a false promise.

    Regarding #note-6: QueryHelper::quoteDatabaseIdentifiers() runs before the marker
    substitution in getQuery(), and orderBy is not among the properties it is applied to
    (only languageField, selectFields, join, leftjoin, rightjoin and where). So
    @{####sortfield###}@ never worked, and even if orderBy were added to that list the marker
    would still expand to 'sorting' afterwards.

    The supported way to make orderBy dynamic is stdWrap, which orderBy supports directly:

    page.60 = CONTENT
    page.60 {
        table = tt_content
        select {
            pidInList = 73
            orderBy.cObject = CASE
            orderBy.cObject {
                key.data = GP:sort
                default = TEXT
                default.value = sorting
                title = TEXT
                title.value = title
                date = TEXT
                date.value = crdate DESC
            }
        }
    }

    Mapping the request parameter onto a fixed set of allowed fields, as above, is not merely a
    workaround for this bug — it is the only safe way to build an ORDER BY clause from request
    data in the first place.

    The documentation has been corrected in the meantime: the misleading
    @orderBy = ###sortfield###@ example is gone and replaced by a warning that references this
    issue. As that was the actual damage reported here, and the underlying behaviour cannot be
    fixed in a meaningful way, closing this as “Won’t fix”.