Bug #96065
closed
selectTree with ORDER BY using DBAL compatible quoting produces SQL errors
0%
Description
- have a
selectTreeTCA field (sys_categoryfor example, but it happens with other tables as well) - have a
foreign_table_wherecondition containing for exampleORDER BY {#sys_category}.{#title} ASC(or whetever your foreign table/field is), it does not matter if theforeign_table_wherecontains any more conditions or just the order statement alone - open this record in the backend -> tree view is empty, saving or closing the edit panel shows red SQL error bar
Discovered on v10, probably valid on others as well.
Updated by S P almost 3 years ago
The generated SQL looks fine when looking in the configuration module (the order by statetement is quoted as expected). And the initial loading of the record works as well, only the AJAX request that fills the tree View does not work apparently, leaving only an empty tree view.
Updated by S P almost 3 years ago
Apparently the wrong behavior is this:
When using quoting in foreign_table_where of a selectTree, it gets prepended to the original conditions (instead of appended ), leading to something like WHERE ORDER BY (...) AND some_field = 123 which is invalid. But this seems to happen only at runtime, not when looking at the final configuration in the Configuration\TCA backend module.
Not sure where in the core this happens.
All other quotings (in other TCA types, in order statments and regular statements) work perfectly fine. And selectTree withOUT quoting also works fine (also with the same order statements, just without quoting).
Updated by Oliver Bartsch almost 3 years ago
- Status changed from New to Accepted
Just checked this in current master and can confirm. The reason is the regex, which does not match the quoted expression:
I checked with following foreign_table_where: ORDER BY {#pages}.{#sorting}.
After quoting (https://github.com/TYPO3/typo3/blob/master/typo3/sysext/backend/Classes/Form/FormDataProvider/AbstractItemProvider.php#L743), in my case this is ORDER BY `pages`.`sorting` and therefore does not match the regex.
However, even if the regex would be extended for the possible quoting characters, the result would be quoted again, on being added to the query:
This then also results in invalid SQL. In my case:
ORDER BY ```pages```.```sorting``` ASC
Updated by Oliver Bartsch almost 3 years ago
- Status changed from Accepted to Rejected
Hi Stefan,
after checking this again, I think this currently works as intended. Both ORDER BY as well as GROUP BY should NOT be quoted in TCA as quoting is always applied through the API:
- For
ORDER BY: https://github.com/TYPO3/typo3/blob/master/typo3/sysext/backend/Classes/Form/FormDataProvider/AbstractItemProvider.php#L646 - For
GROUP BY: https://github.com/TYPO3/typo3/blob/master/typo3/sysext/backend/Classes/Form/FormDataProvider/AbstractItemProvider.php#L640
Actually, the docs also mention this:
[...] Note that the ORDER BY is NOT quoted since that is parsed out anyway and always receives proper quoting.
I anyways added a PR (https://github.com/TYPO3-Documentation/TYPO3CMS-Reference-TCA/pull/489) for the docs, to make this a bit more clear and will therefore close this issue now.
In case there is still something not working correctly, or you encounter a similar problem, please contact me or create a new issue with reference to this one.