#110329ORDER BY followed by a line break breaks the suggest wizard, because the PCRE regex does not match this linebreak when the wizard is removing the ORDER BY clause from the query
Description
The SuggestWizardController has this:
protected function getWhereClause(array $fieldConfig): string
{
if (!isset($fieldConfig['foreign_table'], $fieldConfig['foreign_table_where'])) {
return '';
}
// strip ORDER BY clause
return trim(preg_replace('/ORDER[[:space:]]+BY.*/i', '', $fieldConfig['foreign_table_where']));
}
However, in PCRE the . does NOT match a linebreak (it will only match a linebreak if the /s modifier is given).
So having a foreign_table_where with something like this (note the linebreak after the ORDER BY!)
AND cond1 AND cond2 ORDER BY
field ASC
Then the wizard will potentially generate invalid SQL:
Uncaught TYPO3 Exception: #1064: An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'ORDER BY table.title ASC) AND (`title` LIKE '%cv%' ESCAP' at line 1 | Doctrine\DBAL\Exception\SyntaxErrorException thrown in file vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php in line 78. Requested URL: redacted/typo3/ajax/wizard/suggest/search?token=redacted
The solution should be to either include the /s modifier or to use [[:space:]] (instead of .) also after the ORDER BY (not just between ORDER and BY).
This is safe, because LIMIT is not allowed here (by TCA means, it is “foreign table where” , after all, and we also have maxitems), so ORDER BY is the last thing in a query anyways.
Discovered on v14, not sure if like this in earlier versions.