#87799Typoscript SELECT: orderBy = ###marker### wont work
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
-
Chris W changed description category Tags
typoscripttyposcript, select, orderBy, dataProcessing <span data-processor="relativetime" data-value="2019-02-26T18:29:15+00:00" > 02/26/19, 18:29 </span> -
Benni Mack commented and changed status
NewNeeds Feedback <span data-processor="relativetime" data-value="2020-01-16T15:29:04+00:00" > 01/16/20, 15:29 </span> -
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 1This 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. -
Riccardo De Contardi changed status
Needs FeedbackNew <span data-processor="relativetime" data-value="2020-01-20T09:15:25+00:00" > 01/20/20, 09:15 </span> -
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.
-
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###} -
Georg Ringer commented and changed status
NewClosed <span data-processor="relativetime" data-value="2026-08-13T18:11:35+00:00" > 08/13/26, 18:11 </span>The problem is not that
orderBycannot be quoted — it is that markers apply value*
quoting whileorderByneeds *identifier quoting.ContentObjectRenderer::getQueryMarkers()quotes every non-numeric marker value with
@Connection::quote()@, sosortingbecomes'sorting'. That value is substituted into
@orderBy@,QueryHelper::parseOrderBy()splits it into field name and direction, and
@QueryBuilder::addOrderBy()@ then unconditionally appliesquoteIdentifier()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 inorderBy(needs identifier quoting) within one
@select@.Handling this generically is not possible either:
orderByis not a single identifier.sorting DESC,sorting DESC, uid ASC,
FIND_IN_SET(uid, '1,2,3')andRAND()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 ingetQuery(), andorderByis not among the properties it is applied to
(onlylanguageField,selectFields,join,leftjoin,rightjoinandwhere). So
@{####sortfield###}@ never worked, and even iforderBywere added to that list the marker
would still expand to'sorting'afterwards.The supported way to make
orderBydynamic is stdWrap, whichorderBysupports 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”.
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.