search

nextcloud / server

☁️ Nextcloud server, a safe home for all your data
https://nextcloud.com
GNU Affero General Public License v3.0
27.36k stars 4.06k forks source link

Updating calendar objects with long descriptions triggers exceptions SQLSTATE[22001] #26174

Closed yan12125 closed 3 years ago

yan12125 commented 3 years ago

How to use GitHub

Steps to reproduce

  1. Create a task with a very long description in https://some.nextcloud.domain/index.php/apps/tasks/
  2. Update the description and add some characters

Expected behaviour

The task description is updated

Actual behaviour

500 internal server error

I inserted a logger statement in /usr/share/webapps/nextcloud/lib/private/DB/Connection.php, Connection::executeStatement(). The failing SQL statement is:

INSERT INTO "oc_calendarobjects_props" ("calendarid", "calendartype", "objectid", "name", "parameter", "value") VALUES(:dcValue1, :dcValue2, :dcValue3, :name, :parameter, :value)

Where parameters are

{
    "dcValue1":1,
    "dcValue2":0,
    "dcValue3":95470,
    "name":"DESCRIPTION",
    "parameter":null,
    "value":"(some very long text with more than 255 bytes)"
}

That seems quite similar to https://help.nextcloud.com/t/import-ics-db-error-concerning-column-truncation-how-to-report-that-issue/71910. I didn't find a bug report in this repository, hence the report.

Also, this may be related to my previous report https://github.com/nextcloud/server/issues/7876, which also failed in table oc_calendarobjects_props with SQLSTATE 22009. The older issue is no longer reproducible for me, though.

Server configuration

Operating system: Arch Linux

Web server: Apache 2.4.46

Database: PostgreSQL 13.2

PHP version: 8.0.3

Nextcloud version: (see Nextcloud admin page) 21.0.0

Updated from an older Nextcloud/ownCloud or fresh install: Updated from ownCloud several years ago

Where did you install Nextcloud from: Arch Linux official package https://archlinux.org/packages/community/any/nextcloud/

Signing status:

Signing status ``` No errors have been found. ```

List of activated apps:

App list ``` Enabled: - activity: 2.14.3 - admin_audit: 1.11.0 - calendar: 2.1.3 - cloud_federation_api: 1.4.0 - contacts: 3.4.3 - dav: 1.17.1 - federatedfilesharing: 1.11.0 - files: 1.16.0 - logreader: 2.6.0 - lookup_server_connector: 1.9.0 - oauth2: 1.9.0 - provisioning_api: 1.11.0 - settings: 1.3.0 - tasks: 0.13.6 - twofactor_backupcodes: 1.10.0 - viewer: 1.5.0 - workflowengine: 2.3.0 Disabled: - accessibility - comments - contactsinteraction - dashboard - encryption - federation - files_external - files_pdfviewer - files_rightclick - files_sharing - files_trashbin - files_versions - files_videoplayer - firstrunwizard - nextcloud_announcements - notifications - password_policy - photos - privacy - recommendations - serverinfo - sharebymail - support - survey_client - systemtags - text - theming - updatenotification - user_ldap - user_status - weather_status ```

Nextcloud configuration:

Config report ``` { "system": { "version": "21.0.0.18", "installed": true, "instanceid": "***REMOVED SENSITIVE VALUE***", "passwordsalt": "***REMOVED SENSITIVE VALUE***", "secret": "***REMOVED SENSITIVE VALUE***", "trusted_domains": [ "***REMOVED SENSITIVE VALUE***" ], "datadirectory": "***REMOVED SENSITIVE VALUE***", "dbtype": "pgsql", "defaultapp": "calendar", "appstoreenabled": false, "log_type": "syslog", "config_is_read_only": true, "loglevel": 2, "maintenance": false, "overwrite.cli.url": "***REMOVED SENSITIVE VALUE***", "dbname": "***REMOVED SENSITIVE VALUE***", "dbhost": "***REMOVED SENSITIVE VALUE***", "dbuser": "***REMOVED SENSITIVE VALUE***" } } ```

Are you using external storage, if yes which one: local/smb/sftp/... no

Are you using encryption: yes/no no

Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/... no

LDAP configuration (delete this part if not used)

N/A

Client configuration

Browser: Firefox 86.0.1

Operating system: Windows 10 20H2 x64

Logs

Web server error log

Web server error log ``` (IP) - - [17/Mar/2021:20:50:11 +0800] "PUT /remote.php/dav/calendars/username/personal/xxx.ics HTTP/2.0" 500 353 ```

Nextcloud log (data/nextcloud.log)

Nextcloud log ``` {"reqId":"3kbjhkkTmYuHFGr2hiZl","level":4,"time":"2021-03-17T12:41:11+00:00","remoteAddr":"192.168.236.3","user":"yen","app":"webdav","method":"PUT","url":"/remote.php/dav/calendars/yen/personal/4580674220197251593.ics","message":"{\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"Message\":\"An exception occurred while executing a query: SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too long for type character varying(255)\",\"Code\":7,\"Trace\":[{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Connection.php\",\"line\":1728,\"function\":\"convert\",\"class\":\"Doctrine\\\\DBAL\\\\Driver\\\\API\\\\PostgreSQL\\\\ExceptionConverter\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Connection.php\",\"line\":1667,\"function\":\"handleDriverException\",\"class\":\"Doctrine\\\\DBAL\\\\Connection\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Connection.php\",\"line\":1146,\"function\":\"convertExceptionDuringQuery\",\"class\":\"Doctrine\\\\DBAL\\\\Connection\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/lib/private/DB/Connection.php\",\"line\":257,\"function\":\"executeStatement\",\"class\":\"Doctrine\\\\DBAL\\\\Connection\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php\",\"line\":213,\"function\":\"executeStatement\",\"class\":\"OC\\\\DB\\\\Connection\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php\",\"line\":286,\"function\":\"execute\",\"class\":\"Doctrine\\\\DBAL\\\\Query\\\\QueryBuilder\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/CalDAV/CalDavBackend.php\",\"line\":2663,\"function\":\"execute\",\"class\":\"OC\\\\DB\\\\QueryBuilder\\\\QueryBuilder\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/CalDAV/CalDavBackend.php\",\"line\":1215,\"function\":\"updateProperties\",\"class\":\"OCA\\\\DAV\\\\CalDAV\\\\CalDavBackend\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/CalDAV/CalendarObject.php\",\"line\":103,\"function\":\"updateCalendarObject\",\"class\":\"OCA\\\\DAV\\\\CalDAV\\\\CalDavBackend\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":1137,\"function\":\"put\",\"class\":\"Sabre\\\\CalDAV\\\\CalendarObject\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php\",\"line\":492,\"function\":\"updateFile\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php\",\"line\":89,\"function\":\"httpPut\",\"class\":\"Sabre\\\\DAV\\\\CorePlugin\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":472,\"function\":\"emit\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":253,\"function\":\"invokeMethod\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":321,\"function\":\"start\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/Server.php\",\"line\":332,\"function\":\"exec\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/appinfo/v2/remote.php\",\"line\":35,\"function\":\"exec\",\"class\":\"OCA\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/remote.php\",\"line\":167,\"args\":[\"/usr/share/webapps/nextcloud/apps/dav/appinfo/v2/remote.php\"],\"function\":\"require_once\"}],\"File\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php\",\"Line\":83,\"Previous\":{\"Exception\":\"Doctrine\\\\DBAL\\\\Driver\\\\PDO\\\\Exception\",\"Message\":\"SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too long for type character varying(255)\",\"Code\":7,\"Trace\":[{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php\",\"line\":84,\"function\":\"new\",\"class\":\"Doctrine\\\\DBAL\\\\Driver\\\\PDO\\\\Exception\",\"type\":\"::\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Connection.php\",\"line\":1136,\"function\":\"execute\",\"class\":\"Doctrine\\\\DBAL\\\\Driver\\\\PDO\\\\Statement\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/lib/private/DB/Connection.php\",\"line\":257,\"function\":\"executeStatement\",\"class\":\"Doctrine\\\\DBAL\\\\Connection\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php\",\"line\":213,\"function\":\"executeStatement\",\"class\":\"OC\\\\DB\\\\Connection\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php\",\"line\":286,\"function\":\"execute\",\"class\":\"Doctrine\\\\DBAL\\\\Query\\\\QueryBuilder\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/CalDAV/CalDavBackend.php\",\"line\":2663,\"function\":\"execute\",\"class\":\"OC\\\\DB\\\\QueryBuilder\\\\QueryBuilder\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/CalDAV/CalDavBackend.php\",\"line\":1215,\"function\":\"updateProperties\",\"class\":\"OCA\\\\DAV\\\\CalDAV\\\\CalDavBackend\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/CalDAV/CalendarObject.php\",\"line\":103,\"function\":\"updateCalendarObject\",\"class\":\"OCA\\\\DAV\\\\CalDAV\\\\CalDavBackend\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":1137,\"function\":\"put\",\"class\":\"Sabre\\\\CalDAV\\\\CalendarObject\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php\",\"line\":492,\"function\":\"updateFile\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php\",\"line\":89,\"function\":\"httpPut\",\"class\":\"Sabre\\\\DAV\\\\CorePlugin\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":472,\"function\":\"emit\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":253,\"function\":\"invokeMethod\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":321,\"function\":\"start\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/Server.php\",\"line\":332,\"function\":\"exec\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/appinfo/v2/remote.php\",\"line\":35,\"function\":\"exec\",\"class\":\"OCA\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/remote.php\",\"line\":167,\"args\":[\"/usr/share/webapps/nextcloud/apps/dav/appinfo/v2/remote.php\"],\"function\":\"require_once\"}],\"File\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php\",\"Line\":26,\"Previous\":{\"Exception\":\"PDOException\",\"Message\":\"SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too long for type character varying(255)\",\"Code\":\"22001\",\"Trace\":[{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php\",\"line\":82,\"function\":\"execute\",\"class\":\"PDOStatement\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Connection.php\",\"line\":1136,\"function\":\"execute\",\"class\":\"Doctrine\\\\DBAL\\\\Driver\\\\PDO\\\\Statement\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/lib/private/DB/Connection.php\",\"line\":257,\"function\":\"executeStatement\",\"class\":\"Doctrine\\\\DBAL\\\\Connection\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php\",\"line\":213,\"function\":\"executeStatement\",\"class\":\"OC\\\\DB\\\\Connection\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php\",\"line\":286,\"function\":\"execute\",\"class\":\"Doctrine\\\\DBAL\\\\Query\\\\QueryBuilder\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/CalDAV/CalDavBackend.php\",\"line\":2663,\"function\":\"execute\",\"class\":\"OC\\\\DB\\\\QueryBuilder\\\\QueryBuilder\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/CalDAV/CalDavBackend.php\",\"line\":1215,\"function\":\"updateProperties\",\"class\":\"OCA\\\\DAV\\\\CalDAV\\\\CalDavBackend\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/CalDAV/CalendarObject.php\",\"line\":103,\"function\":\"updateCalendarObject\",\"class\":\"OCA\\\\DAV\\\\CalDAV\\\\CalDavBackend\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":1137,\"function\":\"put\",\"class\":\"Sabre\\\\CalDAV\\\\CalendarObject\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php\",\"line\":492,\"function\":\"updateFile\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\",\"args\":[\"*** sensitive parameters replaced ***\"]},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php\",\"line\":89,\"function\":\"httpPut\",\"class\":\"Sabre\\\\DAV\\\\CorePlugin\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":472,\"function\":\"emit\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":253,\"function\":\"invokeMethod\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":321,\"function\":\"start\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/lib/Server.php\",\"line\":332,\"function\":\"exec\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/apps/dav/appinfo/v2/remote.php\",\"line\":35,\"function\":\"exec\",\"class\":\"OCA\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/usr/share/webapps/nextcloud/remote.php\",\"line\":167,\"args\":[\"/usr/share/webapps/nextcloud/apps/dav/appinfo/v2/remote.php\"],\"function\":\"require_once\"}],\"File\":\"/usr/share/webapps/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php\",\"Line\":82}},\"CustomMessage\":\"--\"}","userAgent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:86.0) Gecko/20100101 Firefox/86.0","version":"21.0.0.18"} ```

Browser log

Browser log ``` PUT https://my.nextcloud.domain/remote.php/dav/calendars/username/personal/xxx.ics [HTTP/2 500 Internal Server Error 127ms] ```
yan12125 commented 3 years ago

The issue seems to be that Nextcloud picks first 254 characters for the calendar property value (via mb_substr() [1]), while the PostregreSQL database wants data not longer than 255 bytes. The difference causes issues with long descriptions with multi-byte characters (Chinese in my case). Replacing all mb_substr() calls with mb_strcut() in the file linked above fixes the issue for me.

[1] https://github.com/nextcloud/server/blob/v21.0.0/apps/dav/lib/CalDAV/CalDavBackend.php#L2658