[Bug]: OC\DB\Exceptions\DbalException: An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;

[AndyXheli]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

OC\DB\Exceptions\DbalException: 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 MariaDB server version for the right syntax to use near ' 49, 49, 49' at line 1

Steps to reproduce

Removed SMB/CIFS External Storage

Expected behavior

OC\DB\Exceptions\DbalException: 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 MariaDB server version for the right syntax to use near ' 49, 49, 49' at line 1

Installation method

Manual installation

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.0

Web server

Apache (supported)

Database engine version

MariaDB

Is this bug present after an update or on a fresh install?

Fresh Nextcloud Server install

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "cloud.next.org"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "23.0.3.2",
        "overwrite.cli.url": "https:\/\/cloud.next.org\/",
        "htaccess.RewriteBase": "\/",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "mysql.utf8mb4": true,
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "memcache.local": "\\OC\\Memcache\\APCu",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6379
        },
        "default_phone_region": "US",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "smtp",
        "mail_sendmailmode": "smtp",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "25",
        "skeletondirectory": "",
        "simpleSignUpLink.shown": false,
        "trashbin_retention_obligation": "auto,30",
        "versions_retention_obligation": "auto,1",
        "allow_local_remote_servers": true,
        "preview_max_memory": 1280,
        "ldapProviderFactory": "OCA\\User_LDAP\\LDAPProviderFactory",
        "twofactor_enforced": "true",
        "twofactor_enforced_groups": [],
        "twofactor_enforced_excluded_groups": [],
        "maintenance": false
    }
}

List of activated Apps

Enabled:
  - accessibility: 1.9.0
  - activity: 2.15.0
  - admin_audit: 1.13.0
  - bruteforcesettings: 2.4.0
  - circles: 23.1.0
  - cloud_federation_api: 1.6.0
  - comments: 1.13.0
  - contactsinteraction: 1.4.0
  - dashboard: 7.3.0
  - dav: 1.21.0
  - federatedfilesharing: 1.13.0
  - federation: 1.13.0
  - files: 1.18.0
  - files_downloadactivity: 1.13.0
  - files_external: 1.15.0
  - files_pdfviewer: 2.4.0
  - files_rightclick: 1.2.0
  - files_sharing: 1.15.0
  - files_trashbin: 1.13.0
  - files_versions: 1.16.0
  - files_videoplayer: 1.12.0
  - firstrunwizard: 2.12.0
  - groupfolders: 11.1.2
  - impersonate: 1.10.0
  - logreader: 2.8.0
  - lookup_server_connector: 1.11.0
  - nextcloud_announcements: 1.12.0
  - notifications: 2.11.1
  - oauth2: 1.11.0
  - officeonline: 1.1.3
  - password_policy: 1.13.0
  - photos: 1.5.0
  - privacy: 1.7.0
  - provisioning_api: 1.13.0
  - recommendations: 1.2.0
  - serverinfo: 1.13.0
  - settings: 1.5.0
  - sharebymail: 1.13.0
  - socialsharing_email: 2.5.0
  - spreed: 13.0.5
  - support: 1.6.0
  - survey_client: 1.11.0
  - suspicious_login: 4.1.0
  - systemtags: 1.13.0
  - text: 3.4.1
  - theming: 1.14.0
  - twofactor_admin: 3.2.0
  - twofactor_backupcodes: 1.12.0
  - twofactor_email: 2.3.0
  - twofactor_totp: 6.2.0
  - updatenotification: 1.13.0
  - user_ldap: 1.13.1
  - user_status: 1.3.1
  - viewer: 1.7.0
  - weather_status: 1.3.0
  - workflowengine: 2.5.0
Disabled:
  - encryption

Nextcloud Signing status

No response

Nextcloud Logs

{"reqId":"xAueb355CjlIu4wUBaFr","level":3,"time":"2022-04-18T20:27:57+00:00","remoteAddr":"10.0.10.99","user":"admin","app":"index","method":"DELETE","url":"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/apps/files_external/globalstorages/8","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 MariaDB server version for the right syntax to use near ' 49, 49, 49' at line 1","userAgent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.88 Safari/537.36","version":"23.0.3.2","exception":{"Exception":"OC\\DB\\Exceptions\\DbalException","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 MariaDB server version for the right syntax to use near ' 49, 49, 49' at line 1","Code":1064,"Trace":[{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":335,"function":"wrap","class":"OC\\DB\\Exceptions\\DbalException","type":"::"},{"file":"/var/www/nextcloud/lib/private/Files/Cache/Storage.php","line":249,"function":"executeStatement","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/apps/files_external/lib/Service/StoragesService.php","line":482,"function":"cleanByMountId","class":"OC\\Files\\Cache\\Storage","type":"::"},{"file":"/var/www/nextcloud/apps/files_external/lib/Controller/StoragesController.php","line":392,"function":"removeStorage","class":"OCA\\Files_External\\Service\\StoragesService","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":217,"function":"destroy","class":"OCA\\Files_External\\Controller\\StoragesController","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":126,"function":"executeController","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/App.php","line":157,"function":"dispatch","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/Route/Router.php","line":302,"function":"main","class":"OC\\AppFramework\\App","type":"::"},{"file":"/var/www/nextcloud/lib/base.php","line":1006,"function":"match","class":"OC\\Route\\Router","type":"->"},{"file":"/var/www/nextcloud/index.php","line":36,"function":"handleRequest","class":"OC","type":"::"}],"File":"/var/www/nextcloud/lib/private/DB/Exceptions/DbalException.php","Line":71,"Previous":{"Exception":"Doctrine\\DBAL\\Exception\\SyntaxErrorException","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 MariaDB server version for the right syntax to use near ' 49, 49, 49' at line 1","Code":1064,"Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":1780,"function":"convert","class":"Doctrine\\DBAL\\Driver\\API\\MySQL\\ExceptionConverter","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":1719,"function":"handleDriverException","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":1173,"function":"convertExceptionDuringQuery","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/Connection.php","line":270,"function":"executeStatement","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php","line":354,"function":"executeStatement","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":287,"function":"execute","class":"Doctrine\\DBAL\\Query\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":333,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Cache/Storage.php","line":249,"function":"executeStatement","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/apps/files_external/lib/Service/StoragesService.php","line":482,"function":"cleanByMountId","class":"OC\\Files\\Cache\\Storage","type":"::"},{"file":"/var/www/nextcloud/apps/files_external/lib/Controller/StoragesController.php","line":392,"function":"removeStorage","class":"OCA\\Files_External\\Service\\StoragesService","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":217,"function":"destroy","class":"OCA\\Files_External\\Controller\\StoragesController","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":126,"function":"executeController","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/App.php","line":157,"function":"dispatch","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/Route/Router.php","line":302,"function":"main","class":"OC\\AppFramework\\App","type":"::"},{"file":"/var/www/nextcloud/lib/base.php","line":1006,"function":"match","class":"OC\\Route\\Router","type":"->"},{"file":"/var/www/nextcloud/index.php","line":36,"function":"handleRequest","class":"OC","type":"::"}],"File":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php","Line":84,"Previous":{"Exception":"Doctrine\\DBAL\\Driver\\PDO\\Exception","Message":"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 ' 49, 49, 49' at line 1","Code":1064,"Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php","line":94,"function":"new","class":"Doctrine\\DBAL\\Driver\\PDO\\Exception","type":"::"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":1163,"function":"execute","class":"Doctrine\\DBAL\\Driver\\PDO\\Statement","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/Connection.php","line":270,"function":"executeStatement","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php","line":354,"function":"executeStatement","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":287,"function":"execute","class":"Doctrine\\DBAL\\Query\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":333,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Cache/Storage.php","line":249,"function":"executeStatement","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/apps/files_external/lib/Service/StoragesService.php","line":482,"function":"cleanByMountId","class":"OC\\Files\\Cache\\Storage","type":"::"},{"file":"/var/www/nextcloud/apps/files_external/lib/Controller/StoragesController.php","line":392,"function":"removeStorage","class":"OCA\\Files_External\\Service\\StoragesService","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":217,"function":"destroy","class":"OCA\\Files_External\\Controller\\StoragesController","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":126,"function":"executeController","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/App.php","line":157,"function":"dispatch","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/Route/Router.php","line":302,"function":"main","class":"OC\\AppFramework\\App","type":"::"},{"file":"/var/www/nextcloud/lib/base.php","line":1006,"function":"match","class":"OC\\Route\\Router","type":"->"},{"file":"/var/www/nextcloud/index.php","line":36,"function":"handleRequest","class":"OC","type":"::"}],"File":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php","Line":26,"Previous":{"Exception":"PDOException","Message":"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 ' 49, 49, 49' at line 1","Code":"42000","Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php","line":92,"function":"execute","class":"PDOStatement","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":1163,"function":"execute","class":"Doctrine\\DBAL\\Driver\\PDO\\Statement","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/Connection.php","line":270,"function":"executeStatement","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php","line":354,"function":"executeStatement","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":287,"function":"execute","class":"Doctrine\\DBAL\\Query\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":333,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/Files/Cache/Storage.php","line":249,"function":"executeStatement","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/apps/files_external/lib/Service/StoragesService.php","line":482,"function":"cleanByMountId","class":"OC\\Files\\Cache\\Storage","type":"::"},{"file":"/var/www/nextcloud/apps/files_external/lib/Controller/StoragesController.php","line":392,"function":"removeStorage","class":"OCA\\Files_External\\Service\\StoragesService","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":217,"function":"destroy","class":"OCA\\Files_External\\Controller\\StoragesController","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":126,"function":"executeController","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/App.php","line":157,"function":"dispatch","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/Route/Router.php","line":302,"function":"main","class":"OC\\AppFramework\\App","type":"::"},{"file":"/var/www/nextcloud/lib/base.php","line":1006,"function":"match","class":"OC\\Route\\Router","type":"->"},{"file":"/var/www/nextcloud/index.php","line":36,"function":"handleRequest","class":"OC","type":"::"}],"File":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php","Line":92}}},"CustomMessage":"--"},"id":"625dc9da192fc"}

Additional info

No response

[alghanim-lab]

I have the same problem

[ChristophWurst]

I believe the fault is at

server/lib/private/Files/Cache/Storage.php

Line 242 in cd7cec5

->where($query->expr()->eq('numeric_id', $query->createNamedParameter($storageIds, IQueryBuilder::PARAM_INT_ARRAY)));

. We use ->eq(...) to compare to an array of ints. It should probably be ->in(...).

[ChristophWurst]

And this is a duplicate of #30560

[AndyXheli]

@ChristophWurst should i make a per request changing from ->eq(...) to ->in(...) ?

[ChristophWurst]

If you can still reproduce it would be awesome to know if it fixes the SQL error. If it does, yes, let's do a PR :)

[AndyXheli]

let me test with and with out and keep you posted.

[ChristophWurst]

The production instance where I saw the issue fails at

server/lib/private/Files/Cache/Storage.php

Line 249 in c674a20

$query->execute();

. So I'm convinced it's the eq expression with an array parameter that generates invalid SQL.