Bug #87799
open
Typoscript SELECT: orderBy = ###marker### wont work
0%
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.
Updated by Chris W over 5 years ago
- Description updated (diff)
- Category set to TypoScript
- Tags changed from typoscript to typoscript, select, orderBy, dataProcessing
Updated by Benni Mack almost 5 years 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.
Updated by Chris W almost 5 years 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.
Updated by Riccardo De Contardi almost 5 years ago
- Status changed from Needs Feedback to New
Updated by Sybille Peters about 4 years 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.
Updated by Jigal van Hemert about 4 years 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###}