unescaped column identifier in join breaks oracle #22835

Closed
butonic opened this Issue Mar 3, 2016 · 5 comments

Projects

None yet

4 participants

@butonic
Member
butonic commented Mar 3, 2016

https://github.com/owncloud/core/blob/master/apps/files_external/service/dbconfigservice.php#L95 and https://github.com/owncloud/core/blob/master/apps/files_external/service/dbconfigservice.php#L151
produce an unescaped join on column identifier, eg

    SELECT m."mount_id", "mount_point", "storage_backend", "auth_backend", "priority", m."type"
      FROM "oc_external_mounts" m
INNER JOIN "oc_external_applicable" a
        ON m.mount_id = a.mount_id -- <-- these need to be escaped
     WHERE (a."type" = ?)
       AND (a."value" = ?)
       AND (m."type" = 1)

This is in the log

Exception: {"Exception":"Doctrine\DBAL\Exception\InvalidFieldNameException","Message":"An exception occurred while executing 'SELECT m."mount_id", "mount_point", "storage_backend", "auth_backend", "priority", m."type" FROM "oc_external_mounts" m INNER JOIN "oc_external_applicable" a ON m.mount_id = a.mount_id WHERE (a."type" = ?) AND (a."value" = ?) AND (m."type" = 1)' with params [3, "admin"]:

ORA-00904: "A"."MOUNT_ID": invalid identifier","Code":0,"Trace":"#0 /var/www/owncloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(116): Doctrine\DBAL\Driver\AbstractOracleDriver->convertException('An exception oc...', Object(Doctrine\DBAL\Driver\OCI8\OCI8Exception))
#1 /var/www/owncloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(836): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(Doctrine\DBAL\Driver\OCI8\Driver), Object(Doctrine\DBAL\Driver\OCI8\OCI8Exception), 'SELECT m."mount...', Array)
#2 /var/www/owncloud/lib/private/db/connection.php(184): Doctrine\DBAL\Connection->executeQuery('SELECT m."mount...', Array, Array, NULL)
#3 /var/www/owncloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(206): OC\DB\Connection->executeQuery('SELECT m.`mount...', Array, Array)
#4 /var/www/owncloud/lib/private/db/querybuilder/querybuilder.php(141): Doctrine\DBAL\Query\QueryBuilder->execute()
#5 /var/www/owncloud/apps/files_external/service/dbconfigservice.php(323): OC\DB\QueryBuilder\QueryBuilder->execute()
#6 /var/www/owncloud/apps/files_external/service/dbconfigservice.php(133): OCA\Files_External\Service\DBConfigService->getMountsFromQuery(Object(OC\DB\QueryBuilder\QueryBuilder))
#7 /var/www/owncloud/apps/files_external/service/userglobalstoragesservice.php(77): OCA\Files_External\Service\DBConfigService->getAdminMountsFor(3, 'admin')
#8 /var/www/owncloud/apps/files_external/service/storagesservice.php(122): OCA\Files_External\Service\UserGlobalStoragesService->readDBConfig()
#9 /var/www/owncloud/apps/files_external/service/storagesservice.php(172): OCA\Files_external\Service\StoragesService->readConfig()
#10 /var/www/owncloud/apps/files_external/service/storagesservice.php(181): OCA\Files_external\Service\StoragesService->getAllStorages()
#11 /var/www/owncloud/apps/files_external/service/userglobalstoragesservice.php(110): OCA\Files_external\Service\StoragesService->getStorages()
#12 /var/www/owncloud/apps/files_external/lib/config/configadapter.php(124): OCA\Files_External\Service\UserGlobalStoragesService->getUniqueStorages()
#13 /var/www/owncloud/lib/private/files/config/mountprovidercollection.php(70): OCA\Files_External\Config\ConfigAdapter->getMountsForUser(Object(OC\User\User), Object(OC\Files\Storage\StorageFactory))
#14 [internal function]: OC\Files\Config\MountProviderCollection->OC\Files\Config\{closure}(Object(OCA\Files_External\Config\ConfigAdapter))
#15 /var/www/owncloud/lib/private/files/config/mountprovidercollection.php(71): array_map(Object(Closure), Array)
#16 /var/www/owncloud/lib/private/files/filesystem.php(428): OC\Files\Config\MountProviderCollection->getMountsForUser(Object(OC\User\User))
#17 /var/www/owncloud/lib/private/files/filesystem.php(352): OC\Files\Filesystem::initMountPoints('admin')
#18 /var/www/owncloud/lib/private/util.php(218): OC\Files\Filesystem::init('admin', '/admin/files')
#19 /var/www/owncloud/lib/base.php(872): OC_Util::setupFS()
#20 /var/www/owncloud/index.php(39): OC::handleRequest()
#21 {main}","File":"/var/www/owncloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractOracleDriver.php","Line":48

I tried with

            ->innerJoin('m', 'external_applicable', 'a', 'm.`mount_id` = a.`mount_id`')

and did not get the exception, but I guess a proper test is in order and maybe the query builder can be used in a different way ...

@butonic butonic added this to the 9.0.1-next-maintenance milestone Mar 3, 2016
@DeepDiver1975
Member

looks like this statement is not properly unit tested

@PVince81
Collaborator
PVince81 commented Mar 4, 2016
@PVince81
Collaborator
PVince81 commented Mar 9, 2016

@nickvergessen mind taking care of this ?

@nickvergessen
Contributor

Will do

@nickvergessen
Contributor

Master in #23074
Backport in #23075

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment