[Issue] Correctly handle order clause for reserved columns

[m2-assistant]

This issue is automatically created based on existing pull request: #37387: Correctly handle order clause for reserved columns

---

Description

When using a default admin UI component to render a grid, columns in the data source are handled correctly, even when these are named reserved words (like group), however when sorting by such a column, an exception is thrown. This pull request resolves this by correctly escaping these reserved words when used in an 'order by' clause.

Error logged to var/log/exception.log:

[2023-04-17T19:05:44.755740+00:00] main.CRITICAL: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'group ASC
 LIMIT 20' at line 1 in /var/www/magento2/lib/internal/Magento/Framework/DB/Statement/Pdo/Mysql.php:90
Stack trace:
#0 /var/www/magento2/lib/internal/Magento/Framework/DB/Statement/Pdo/Mysql.php(90): PDOStatement->execute()
#1 /var/www/magento2/lib/internal/Magento/Framework/DB/Statement/Pdo/Mysql.php(106): Magento\Framework\DB\Statement\Pdo\Mysql->Magento\Framework\DB\Statement\Pdo\{closure}()
#2 /var/www/magento2/lib/internal/Magento/Framework/DB/Statement/Pdo/Mysql.php(91): Magento\Framework\DB\Statement\Pdo\Mysql->tryExecute(Object(Closure))
#3 /var/www/magento2/vendor/magento/zend-db/library/Zend/Db/Statement.php(313): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
#4 /var/www/magento2/vendor/magento/zend-db/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#5 /var/www/magento2/vendor/magento/zend-db/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `main_ta...', Array)
#6 /var/www/magento2/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php(564): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `main_ta...', Array)
#7 /var/www/magento2/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php(634): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('SELECT `main_ta...', Array)
#8 /var/www/magento2/generated/code/Magento/Framework/DB/Adapter/Pdo/Mysql/Interceptor.php(95): Magento\Framework\DB\Adapter\Pdo\Mysql->query(Object(Magento\Framework\DB\Select), Array)
#9 /var/www/magento2/vendor/magento/zend-db/library/Zend/Db/Adapter/Abstract.php(737): Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor->query(Object(Magento\Framework\DB\Select), Array)
#10 /var/www/magento2/generated/code/Magento/Framework/DB/Adapter/Pdo/Mysql/Interceptor.php(1085): Zend_Db_Adapter_Abstract->fetchAll(Object(Magento\Framework\DB\Select), Array, 2)
#11 /var/www/magento2/lib/internal/Magento/Framework/Data/Collection/Db/FetchStrategy/Query.php(21): Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor->fetchAll(Object(Magento\Framework\DB\Select), Array)
#12 /var/www/magento2/lib/internal/Magento/Framework/Data/Collection/AbstractDb.php(782): Magento\Framework\Data\Collection\Db\FetchStrategy\Query->fetchAll(Object(Magento\Framework\DB\Select), Array)
#13 /var/www/magento2/lib/internal/Magento/Framework/Data/Collection/AbstractDb.php(677): Magento\Framework\Data\Collection\AbstractDb->_fetchAll(Object(Magento\Framework\DB\Select))
#14 /var/www/magento2/generated/code/Magento/Framework/View/Element/UiComponent/DataProvider/SearchResult/Interceptor.php(383): Magento\Framework\Data\Collection\AbstractDb->getData()
#15 /var/www/magento2/lib/internal/Magento/Framework/Data/Collection/AbstractDb.php(580): Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult\Interceptor->getData()
#16 /var/www/magento2/generated/code/Magento/Framework/View/Element/UiComponent/DataProvider/SearchResult/Interceptor.php(365): Magento\Framework\Data\Collection\AbstractDb->loadWithFilter(false, false)
#17 /var/www/magento2/lib/internal/Magento/Framework/Data/Collection/AbstractDb.php(565): Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult\Interceptor->loadWithFilter(false, false)
#18 /var/www/magento2/generated/code/Magento/Framework/View/Element/UiComponent/DataProvider/SearchResult/Interceptor.php(356): Magento\Framework\Data\Collection\AbstractDb->load(false, false)
#19 /var/www/magento2/lib/internal/Magento/Framework/Data/Collection.php(333): Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult\Interceptor->load()
#20 /var/www/magento2/generated/code/Magento/Framework/View/Element/UiComponent/DataProvider/SearchResult/Interceptor.php(518): Magento\Framework\Data\Collection->getItems()
#21 /var/www/magento2/lib/internal/Magento/Framework/View/Element/UiComponent/DataProvider/DataProvider.php(252): Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult\Interceptor->getItems()
#22 /var/www/magento2/lib/internal/Magento/Framework/View/Element/UiComponent/DataProvider/DataProvider.php(286): Magento\Framework\View\Element\UiComponent\DataProvider\DataProvider->searchResultToOutput(Object(Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult\Interceptor))
#23 /var/www/magento2/app/code/Magento/Ui/Component/Listing.php(38): Magento\Framework\View\Element\UiComponent\DataProvider\DataProvider->getData()
#24 /var/www/magento2/lib/internal/Magento/Framework/View/Element/UiComponent/Context.php(250): Magento\Ui\Component\Listing->getDataSourceData()
#25 /var/www/magento2/lib/internal/Magento/Framework/View/Element/UiComponent/ContentType/Json.php(69): Magento\Framework\View\Element\UiComponent\Context->getDataSourceData(Object(Magento\Ui\Component\Listing))
#26 /var/www/magento2/app/code/Magento/Ui/Component/AbstractComponent.php(158): Magento\Framework\View\Element\UiComponent\ContentType\Json->render(Object(Magento\Ui\Component\Listing), 'templates/listi...')
#27 /var/www/magento2/app/code/Magento/Ui/Controller/Adminhtml/Index/Render.php(91): Magento\Ui\Component\AbstractComponent->render()
#28 /var/www/magento2/lib/internal/Magento/Framework/Interception/Interceptor.php(58): Magento\Ui\Controller\Adminhtml\Index\Render->execute()
#29 /var/www/magento2/lib/internal/Magento/Framework/Interception/Interceptor.php(138): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->___callParent('execute', Array)
#30 /var/www/magento2/lib/internal/Magento/Framework/Interception/Interceptor.php(153): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->Magento\Framework\Interception\{closure}()
#31 /var/www/magento2/generated/code/Magento/Ui/Controller/Adminhtml/Index/Render/Interceptor.php(23): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->___callPlugins('execute', Array, Array)
#32 /var/www/magento2/lib/internal/Magento/Framework/App/Action/Action.php(111): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->execute()
#33 /var/www/magento2/app/code/Magento/Backend/App/AbstractAction.php(151): Magento\Framework\App\Action\Action->dispatch(Object(Magento\Framework\App\Request\Http))
#34 /var/www/magento2/lib/internal/Magento/Framework/Interception/Interceptor.php(58): Magento\Backend\App\AbstractAction->dispatch(Object(Magento\Framework\App\Request\Http))
#35 /var/www/magento2/lib/internal/Magento/Framework/Interception/Interceptor.php(138): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->___callParent('dispatch', Array)
#36 /var/www/magento2/app/code/Magento/Backend/App/Action/Plugin/Authentication.php(145): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#37 /var/www/magento2/lib/internal/Magento/Framework/Interception/Interceptor.php(135): Magento\Backend\App\Action\Plugin\Authentication->aroundDispatch(Object(Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#38 /var/www/magento2/lib/internal/Magento/Framework/Interception/Interceptor.php(153): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#39 /var/www/magento2/generated/code/Magento/Ui/Controller/Adminhtml/Index/Render/Interceptor.php(41): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->___callPlugins('dispatch', Array, Array)
#40 /var/www/magento2/lib/internal/Magento/Framework/App/FrontController.php(245): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#41 /var/www/magento2/lib/internal/Magento/Framework/App/FrontController.php(212): Magento\Framework\App\FrontController->getActionResponse(Object(Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor), Object(Magento\Framework\App\Request\Http))
#42 /var/www/magento2/lib/internal/Magento/Framework/App/FrontController.php(147): Magento\Framework\App\FrontController->processRequest(Object(Magento\Framework\App\Request\Http), Object(Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor))
#43 /var/www/magento2/lib/internal/Magento/Framework/Interception/Interceptor.php(58): Magento\Framework\App\FrontController->dispatch(Object(Magento\Framework\App\Request\Http))
#44 /var/www/magento2/lib/internal/Magento/Framework/Interception/Interceptor.php(138): Magento\Framework\App\FrontController\Interceptor->___callParent('dispatch', Array)
#45 /var/www/magento2/lib/internal/Magento/Framework/Interception/Interceptor.php(153): Magento\Framework\App\FrontController\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#46 /var/www/magento2/generated/code/Magento/Framework/App/FrontController/Interceptor.php(23): Magento\Framework\App\FrontController\Interceptor->___callPlugins('dispatch', Array, Array)
#47 /var/www/magento2/lib/internal/Magento/Framework/App/Http.php(116): Magento\Framework\App\FrontController\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#48 /var/www/magento2/generated/code/Magento/Framework/App/Http/Interceptor.php(23): Magento\Framework\App\Http->launch()
#49 /var/www/magento2/lib/internal/Magento/Framework/App/Bootstrap.php(264): Magento\Framework\App\Http\Interceptor->launch()
#50 /var/www/magento2/pub/index.php(30): Magento\Framework\App\Bootstrap->run(Object(Magento\Framework\App\Http\Interceptor))
#51 {main}

Fixed Issues

1. Fixes Sort by group results in Zend_Db_Statement_Exception Ethan3600/magento2-CronjobManager#196

Manual testing scenarios

1. composer require ethanyehuda/magento2-cronjobmanager
2. bin/magento setup:upgrade
3. Navigate to the admin -> System -> Tools -> Cron Job Manager
4. Sort the grid by each column in turn

• With the changes in this pull request applied, sorting by the "Group" column should work as expected. Without these changes, an exception is thrown and an error message is displayed in the admin.

Questions or comments

I wasn't able to find a suitable list of reserved words in any libraries already listed as dependencies of Magento, nor any easily available that could be trivially included. If there are suggestions for replacing this hard-coded list of reserved words with an externally-maintained list, please let me know.

Contribution checklist

• Pull request has a meaningful description of its purpose
• All commits are accompanied by meaningful commit messages
• All new or changed code is covered with unit/integration tests (if applicable)
• README.md files for modified modules are updated and included in the pull request if any README.md predefined sections require an update
• All automated tests passed successfully (all builds are green)

[engcom-Lima]

Related to Ethan3600/magento2-CronjobManager#196

[github-jira-sync-bot]

✅ Jira issue https://jira.corp.adobe.com/browse/AC-8595 is successfully created for this GitHub issue.

[m2-assistant]

✅ Confirmed by @engcom-Lima. Thank you for verifying the issue.
Issue Available: @engcom-Lima, You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself.