[Bug]: Search result fails when user is not admin

[orjansandell]

Expected behavior

When I as a non admin user executes a search I get a search result.

Actual behavior

I get an error message saying:
Timestamp: Wed Oct 11 2023 15:03:59 GMT+0200 (centraleuropeisk sommartid) Status: 500 | URL: /admin/bundle/advanced-object-search/admin/grid-proxy?classId=product&xaction=read&_dc=1697029439413 Method: POST Message: 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 'key),cpath)=1 ORDER BY LENGTH(cpath) DESC LIMIT 1)=1 ' at line 2 Trace: in /var/www/pimcore/vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php:86 #0 /var/www/pimcore/vendor/doctrine/dbal/src/Connection.php(1935): Doctrine\DBAL\Driver\API\MySQL\ExceptionConverter->convert(Object(Doctrine\DBAL\Driver\PDO\Exception), Object(Doctrine\DBAL\Query)) #1 /var/www/pimcore/vendor/doctrine/dbal/src/Connection.php(1878): Doctrine\DBAL\Connection->handleDriverException(Object(Doctrine\DBAL\Driver\PDO\Exception), Object(Doctrine\DBAL\Query)) #2 /var/www/pimcore/vendor/doctrine/dbal/src/Connection.php(1103): Doctrine\DBAL\Connection->convertExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDO\Exception), 'SELECT object_l...', Array, Array) #3 /var/www/pimcore/vendor/doctrine/dbal/src/Connection.php(950): Doctrine\DBAL\Connection->executeQuery('SELECT object_l...', Array, Array) #4 /var/www/pimcore/vendor/pimcore/pimcore/models/DataObject/Listing/Dao.php(108): Doctrine\DBAL\Connection->fetchFirstColumn('SELECT object_l...', Array, Array) #5 /var/www/pimcore/vendor/pimcore/pimcore/models/DataObject/Listing/Concrete/Dao.php(45): Pimcore\Model\DataObject\Listing\Dao->loadIdList() #6 /var/www/pimcore/vendor/pimcore/pimcore/models/DataObject/Listing/Dao.php(65): Pimcore\Model\DataObject\Listing\Concrete\Dao->loadIdList() #7 [internal function]: Pimcore\Model\DataObject\Listing\Dao->load() #8 /var/www/pimcore/vendor/pimcore/pimcore/lib/Model/AbstractModel.php(222): call_user_func_array(Array, Array) #9 /var/www/pimcore/vendor/pimcore/advanced-object-search/src/Controller/AdminController.php(172): Pimcore\Model\AbstractModel->__call('load', Array) #10 /var/www/pimcore/vendor/symfony/http-kernel/HttpKernel.php(182): AdvancedObjectSearchBundle\Controller\AdminController->gridProxyAction(Object(Symfony\Component\HttpFoundation\Request), Object(AdvancedObjectSearchBundle\Service), Object(Symfony\Component\HttpKernel\Debug\TraceableEventDispatcher)) #11 /var/www/pimcore/vendor/symfony/http-kernel/HttpKernel.php(76): Symfony\Component\HttpKernel\HttpKernel->handleRaw(Object(Symfony\Component\HttpFoundation\Request), 1) #12 /var/www/pimcore/vendor/symfony/http-kernel/Kernel.php(197): Symfony\Component\HttpKernel\HttpKernel->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true) #13 /var/www/pimcore/vendor/symfony/runtime/Runner/Symfony/HttpKernelRunner.php(35): Symfony\Component\HttpKernel\Kernel->handle(Object(Symfony\Component\HttpFoundation\Request)) #14 /var/www/pimcore/vendor/autoload_runtime.php(29): Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run() #15 /var/www/pimcore/public/index.php(19): require_once('/var/www/pimcor...') #16 {main}

Steps to reproduce

Log in as a user that does not have admin privileges (Pimcore 11). Create a New search and click on the Results tab.

While debugging I found that this query fails:
SELECT object_localized_product_nb_NO.id as id, object_localized_product_nb_NO.type as type FROM object_localized_product_nb_NO WHERE ( (select list from users_workspaces_object where userId in (7,12) and LOCATE(CONCAT(path, key),cpath)=1 ORDER BY LENGTH(cpath) DESC LIMIT 1)=1 OR (select list from users_workspaces_object where userId in (7,12) and LOCATE(cpath,CONCAT(path, key))=1 ORDER BY LENGTH(cpath) DESC LIMIT 1)=1 ) AND 1=2 AND object_localized_product_nb_NO.type IN ('object','folder','variant');

It is the CONCAT(path, key) that fails, since path and key are not columns in the users_workspaces_object table. I tried to execute the query by changing to CONCAT(object_localized_product_nb_NO.path, object_localized_product_nb_NO.key), then the query is ok. When running with an admin account the parts looking in the users_workspaces_object table is not present and everything works fine.

[dvesh3]

Fixed by #252

[Xirdion]

Hello everyone,

I noticed that with version 6, the changes related to this error have disappeared. However, I have been able to observe exactly the problems described above.

Were the changes perhaps accidentally lost?