user_saml icon indicating copy to clipboard operation
user_saml copied to clipboard
verified publisher icon nextcloud

length of "uid" column for SAML authentication is too short (table oc_user_saml_users)

Open martinhaase opened this issue 7 years ago • 3 comments

Steps to reproduce

1.Connect SSO/SAML app to an Identity Provider that issues the eduGAIN-wide standard eduPersonUniqueID as User ID 2. associate this epUID (name="urn:oid:1.3.6.1.4.1.5923.1.1.1.13") to the UserID in the SAML app's preferences 3. Login with that IdP

Expected behaviour

Login succeeding

Actual behaviour

Login failing; The epUID f7980c823d8168f9a9e750ec8f2239a87eff717759d1cb811c7c61d7420462a9@dariah.eu (fixed-length 64byte Hash + '@' + variable-length institution's scope) is not being accepted:

Error | index | Doctrine\DBAL\Exception\DriverException: An  exception occurred while executing 'INSERT INTO `oc_user_saml_users`  (`uid`) VALUES(?)' with params  ["f7980c823d8168f9a9e750ec8f2239a87eff717759d1cb811c7c61d7420462a9@dariah.eu"]:  SQLSTATE[22001]: String data, right truncated: 1406 Data too long for  column 'uid' at row 1/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php - line 128: Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException("An exceptio ... 1", Doctrine\DBA ... ]})/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php - line 1015: Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Doctrine\DBA ... {}, Doctrine\DBA ... ]}, "INSERT INTO ... )", { 1: "f7980c ... "})/var/www/nextcloud/lib/private/DB/Connection.php - line 216: Doctrine\DBAL\Connection->executeUpdate("INSERT INTO ... )", [ "f7980c823 ... "], [ 2])/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php - line 208: OC\DB\Connection->executeUpdate("INSERT INTO ... )", { dcValue1:  ... "}, { dcValue1: 2})/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php - line 214: Doctrine\DBAL\Query\QueryBuilder->execute()/var/www/nextcloud/apps/user_saml/lib/UserBackend.php - line 124: OC\DB\QueryBuilder\QueryBuilder->execute()/var/www/nextcloud/apps/user_saml/lib/Controller/SAMLController.php - line 142: OCA\User_SAML\UserBackend->createUserIfNotExists("f7980c823d8 ... u")/var/www/nextcloud/apps/user_saml/lib/Controller/SAMLController.php - line 256: OCA\User_SAML\Controller\SAMLController->autoprovisionIfPossible({ urn:oid:1. ... ]})/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 166: OCA\User_SAML\Controller\SAMLController->assertionConsumerService()/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 99: OC\AppFramework\Http\Dispatcher->executeController(OCA\User_SAM ... {}, "assertionConsumerService")/var/www/nextcloud/lib/private/AppFramework/App.php - line 118: OC\AppFramework\Http\Dispatcher->dispatch(OCA\User_SAM ... {}, "assertionConsumerService")/var/www/nextcloud/lib/private/AppFramework/Routing/RouteActionHandler.php - line 47: OC\AppFramework\App::main("OCA\\User_S ... r", "assertionConsumerService", OC\AppFramew ... {}, { _route: "u ... "})OC\AppFramework\Routing\RouteActionHandler->__invoke({ _route: "u ... "})/var/www/nextcloud/lib/private/Route/Router.php - line 297: call_user_func(OC\AppFramew ... {}, { _route: "u ... "})/var/www/nextcloud/lib/base.php - line 987: OC\Route\Router->match("/apps/user_saml/saml/acs")/var/www/nextcloud/index.php - line 42: OC::handleRequest()Eingefasst vonDoctrine\DBAL\Driver\PDOException: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'uid' at row 1/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php - line 1006: Doctrine\DBAL\Driver\PDOStatement->execute()/var/www/nextcloud/lib/private/DB/Connection.php - line 216: Doctrine\DBAL\Connection->executeUpdate("INSERT INTO ... )", [ "f7980c823 ... "], [ 2])/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php - line 208: OC\DB\Connection->executeUpdate("INSERT INTO ... )", { dcValue1:  ... "}, { dcValue1: 2})/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php - line 214: Doctrine\DBAL\Query\QueryBuilder->execute()/var/www/nextcloud/apps/user_saml/lib/UserBackend.php - line 124: OC\DB\QueryBuilder\QueryBuilder->execute()/var/www/nextcloud/apps/user_saml/lib/Controller/SAMLController.php - line 142: OCA\User_SAML\UserBackend->createUserIfNotExists("f7980c823d8 ... u")/var/www/nextcloud/apps/user_saml/lib/Controller/SAMLController.php - line 256: OCA\User_SAML\Controller\SAMLController->autoprovisionIfPossible({ urn:oid:1. ... ]})/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 166: OCA\User_SAML\Controller\SAMLController->assertionConsumerService()/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 99: OC\AppFramework\Http\Dispatcher->executeController(OCA\User_SAM ... {}, "assertionConsumerService")/var/www/nextcloud/lib/private/AppFramework/App.php - line 118: OC\AppFramework\Http\Dispatcher->dispatch(OCA\User_SAM ... {}, "assertionConsumerService")/var/www/nextcloud/lib/private/AppFramework/Routing/RouteActionHandler.php - line 47: OC\AppFramework\App::main("OCA\\User_S ... r", "assertionConsumerService", OC\AppFramew ... {}, { _route: "u ... "})OC\AppFramework\Routing\RouteActionHandler->__invoke({ _route: "u ... "})/var/www/nextcloud/lib/private/Route/Router.php - line 297: call_user_func(OC\AppFramew ... {}, { _route: "u ... "})/var/www/nextcloud/lib/base.php - line 987: OC\Route\Router->match("/apps/user_saml/saml/acs")/var/www/nextcloud/index.php - line 42: OC::handleRequest()Eingefasst vonPDOException: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'uid' at row 1/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php - line 105: PDOStatement->execute(null)/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php - line 1006: Doctrine\DBAL\Driver\PDOStatement->execute()/var/www/nextcloud/lib/private/DB/Connection.php - line 216: Doctrine\DBAL\Connection->executeUpdate("INSERT INTO ... )", [ "f7980c823 ... "], [ 2])/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php - line 208: OC\DB\Connection->executeUpdate("INSERT INTO ... )", { dcValue1:  ... "}, { dcValue1: 2})/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php - line 214: Doctrine\DBAL\Query\QueryBuilder->execute()/var/www/nextcloud/apps/user_saml/lib/UserBackend.php - line 124: OC\DB\QueryBuilder\QueryBuilder->execute()/var/www/nextcloud/apps/user_saml/lib/Controller/SAMLController.php - line 142: OCA\User_SAML\UserBackend->createUserIfNotExists("f7980c823d8 ... u")/var/www/nextcloud/apps/user_saml/lib/Controller/SAMLController.php - line 256: OCA\User_SAML\Controller\SAMLController->autoprovisionIfPossible({ urn:oid:1. ... ]})/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 166: OCA\User_SAML\Controller\SAMLController->assertionConsumerService()/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 99: OC\AppFramework\Http\Dispatcher->executeController(OCA\User_SAM ... {}, "assertionConsumerService")/var/www/nextcloud/lib/private/AppFramework/App.php - line 118: OC\AppFramework\Http\Dispatcher->dispatch(OCA\User_SAM ... {}, "assertionConsumerService")/var/www/nextcloud/lib/private/AppFramework/Routing/RouteActionHandler.php - line 47: OC\AppFramework\App::main("OCA\\User_S ... r", "assertionConsumerService", OC\AppFramew ... {}, { _route: "u ... "})OC\AppFramework\Routing\RouteActionHandler->__invoke({ _route: "u ... "})/var/www/nextcloud/lib/private/Route/Router.php - line 297: call_user_func(OC\AppFramew ... {}, { _route: "u ... "})/var/www/nextcloud/lib/base.php - line 987: OC\Route\Router->match("/apps/user_saml/saml/acs")/var/www/nextcloud/index.php - line 42: OC::handleRequest()
-- | -- | --

Server configuration

Operating system: CENTOS7

Web server: Apache2.4

Database: MariaDB 10.3.10

PHP version: 7.2

Nextcloud version: (see Nextcloud admin page) 14

Updated from an older Nextcloud/ownCloud or fresh install: fresh

Where did you install Nextcloud from: (community edition from official website as ZIP file)

Signing status:

No errors have been found.

Cf. https://github.com/nextcloud/server/issues/12015

martinhaase avatar Nov 02 '18 08:11 martinhaase

I just had this problem too. connected the SSO/SAML app to Azure ID. First test to create a new user went well. Second test with existing user was successful. Third test to create a new user threw this error.

[index] Error: Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'INSERT INTO `oc_user_saml_users` (`uid`) VALUES(?)' with params ["xxxxxxxxxxx.x.xxxxxxx_gmail.com#EXT#@UtahFoundation.onmicrosoft.com"]:

SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'uid' at row 1 at <<closure>>

 0. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 169
    Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException("An exception oc ... 1", Doctrine\DBAL\Dr ... ]})
 1. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 149
    Doctrine\DBAL\DBALException::wrapException(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "An exception oc ... 1")
 2. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 1071
    Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "INSERT INTO `oc ... )", {1: "xxxxxxxxxxx... "})
 3. /var/www/html/lib/private/DB/Connection.php line 214
    Doctrine\DBAL\Connection->executeUpdate("INSERT INTO `oc ... )", ["xxxxxxxxxxx.x. ... "], [2])
 4. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php line 209
    OC\DB\Connection->executeUpdate("INSERT INTO `oc ... )", {dcValue1: "chri ... "}, {dcValue1: 2})
 5. /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php line 217
    Doctrine\DBAL\Query\QueryBuilder->execute()
 6. /var/www/html/custom_apps/user_saml/lib/UserBackend.php line 147
    OC\DB\QueryBuilder\QueryBuilder->execute()
 7. /var/www/html/custom_apps/user_saml/lib/Controller/SAMLController.php line 162
    OCA\User_SAML\UserBackend->createUserIfNotExists("xxxxxxxxxxx.x.x ... m", {http://schemas. ... ]})
 8. /var/www/html/custom_apps/user_saml/lib/Controller/SAMLController.php line 343
    OCA\User_SAML\Controller\SAMLController->autoprovisionIfPossible({http://schemas. ... ]})
 9. /var/www/html/lib/private/AppFramework/Http/Dispatcher.php line 169
    OCA\User_SAML\Controller\SAMLController->assertionConsumerService()
10. /var/www/html/lib/private/AppFramework/Http/Dispatcher.php line 100
    OC\AppFramework\Http\Dispatcher->executeController(OCA\User_SAML\Co ... {}, "assertionConsumerService")
11. /var/www/html/lib/private/AppFramework/App.php line 152
    OC\AppFramework\Http\Dispatcher->dispatch(OCA\User_SAML\Co ... {}, "assertionConsumerService")
12. /var/www/html/lib/private/Route/Router.php line 308
    OC\AppFramework\App::main("OCA\\User_SAML\ ... r", "assertionConsumerService", OC\AppFramework\ ... {}, {action: null,_r ... "})
13. /var/www/html/lib/base.php line 1009
    OC\Route\Router->match("/apps/user_saml/saml/acs")
14. /var/www/html/index.php line 37
    OC::handleRequest()

POST /apps/user_saml/saml/acs
from 10.0.1.9 at 2021-01-06T23:34:30+00:00

Running Nextcloud 20.0.0 on Ubuntu 20.04 via Docker

xplreitr avatar Jan 06 '21 23:01 xplreitr

Ok, so for those interested in a workaround:

WARNING I have little experience and probably don't know what I am breaking by making these changes.

Nextcloud databases apparently only allow uid/logins up to 64 characters. If you go longer than that it starts throwing errors. I had to lengthen VARCHAR for the following tables and columns. I lengthened them to 255.

ALTER TABLE oc_user_saml_users MODIFY COLUMN uid VARCHAR(255);
ALTER TABLE oc_mounts MODIFY COLUMN user_id VARCHAR(255);
ALTER TABLE oc_preferences MODIFY COLUMN userid VARCHAR(255);
ALTER TABLE oc_authtoken MODIFY COLUMN uid VARCHAR(255);
ALTER TABLE oc_authtoken MODIFY COLUMN login_name VARCHAR(255);
ALTER TABLE oc_twofactor_providers MODIFY COLUMN uid VARCHAR(255);
ALTER TABLE oc_text_sessions MODIFY COLUMN user_idVARCHAR(255);
ALTER TABLE oc_comments MODIFY COLUMN actor_idVARCHAR(255);
ALTER TABLE oc_accounts MODIFY COLUMN uid VARCHAR(255);

Seems to be working just fine at this point. But changes that would allow user ids longer than 64 characters would have to be made not only to the SSO/SAML app, but to the nextcloud core databases as well

xplreitr avatar Jan 07 '21 05:01 xplreitr

@xplreitr The next problem that you will run into will be that user ids are immutable in Nextcloud and the next time the email addresses change the user will either not be able to auth successfully into Nextcloud or get an empty account. Use a unique identifier instead, and you won't have problems with lengths either.

On the original problem with eduPersonUniqueID I fail to understand why another uniquied id "standard" is necessary while we have UUIDs standardized in RFC 4122. But it is good that it can contain emojis ;)

The only way to address longer ids, if necessary, is to have some sort of translation/mapping of and replacing it with a Nextcloud compatible value. We're not going to change uid field lengths for various reasons, I am pretty sure it would break key lengths in many cases as well.

blizzz avatar Jan 07 '21 10:01 blizzz