Wrong column types for user_id and client_id

[kschroeer]

I used the script at /data/oauth2.sql to create the database model. Here I find the following table definition:

CREATE TABLE `oauth_access_tokens` (
  ...
  `user_id` int(10) DEFAULT NULL,
  `client_id` int(10) NOT NULL,
  ...
);

The column types are therefore integers, which also makes sense if you want to refer to the IDs of the tables oauth_clients and oauth_users.
But then the following array is assembled in Mezzio\Authentication\OAuth2\Repository\Pdo\AccessTokenRepository:

$params = [
    ':id'         => $accessTokenEntity->getIdentifier(),
    ':user_id'    => $accessTokenEntity->getUserIdentifier(),
    ':client_id'  => $accessTokenEntity->getClient()->getIdentifier(),
    ':scopes'     => $this->scopesToString($accessTokenEntity->getScopes()),
    ':revoked'    => 0,
    ':expires_at' => date(
        'Y-m-d H:i:s',
        $accessTokenEntity->getExpiryDateTime()->getTimestamp()
    ),
];

And here user_id and client_id are returned as a string, which is why the database INSERT subsequently fails and the UniqueTokenIdentifierConstraintViolationException is thrown.

[vitaha85]

I have got the same issue. like @kschroeer
Incorrect integer value: 'user_test' for column mezzio_db_local.oauth_access_tokens.user_id at row 1
my $params:

Array
(
    [:id] => 5537fa43d5e5ca969f77eab2ab594cd0cbafd24460e61cc46149c559c36295da26c0c51a068e43d6
    [:user_id] => user_test
    [:client_id] => client_test
    [:scopes] => test
    [:revoked] => 0
    [:expires_at] => 2020-08-12 09:38:46
)

how to modify UserRepository/ClientRepository? or we have to change integer type in DB table to varchar?

POST /oauth2/token HTTP/1.1
Host: mezzio:8080
Content-Type: application/x-www-form-urlencoded

grant_type=password&client_id=client_test&client_secret=test&scope=test&username=user_test&password=test

[RodriAndreotti]

I decided to change the type of the field to varchar, but I believe it is not the best solution.
In my opinion the ideal is that the customer's auto incremental ID should be saved, but I believe that the problem is not in the class wich @kschroeer mentioned, but in:

Mezzio\Authentication\OAuth2\Repository\Pdo\ClientRepository

This is the class that generates the client entity, therefore it is the one that populates the client's identifier for Mezzio\Authentication\OAuth2\Repository\Pdo\AccessTokenRepository.

This only forwards the received identifier to the entity instead of searching for the ID in the table:

/**
  * {@inheritDoc}
  */
 public function getClientEntity($clientIdentifier) : ?ClientEntityInterface
 {
     $clientData = $this->getClientData($clientIdentifier);

     if (empty($clientData)) {
         return null;
     }

     return new ClientEntity(
         $clientIdentifier,
         $clientData['name'] ?? '',
         $clientData['redirect'] ?? '',
         (bool) ($clientData['is_confidential'] ?? null)
     );
 }

Now I'm running out of time, but I'll try to take a look at this in the next few days, in case nobody solves it before ... heheeh

[ceadreak]

Same issue in V2.1.0
Still don't know if the user_id should be the id field in user table, or the username.

UserRepository create the user with the username as Identifier
return new UserEntity($username);

[mhsdev]

I have got the same issue
when send user info to TokenEndpointHandler for request token,
( grant_type = password)

reterun this error:

{
“error”: “access_token_duplicate”,
“error_description”: “Could not create unique access token identifier”,
“message”: “Could not create unique access token identifier”
}

and, i found this error from here:
./vendor/mezzio/mezzio-authentication-oauth2/src/Repository/Pdo/AccessTokenRepository.php

Method persistNewAccessToken() sets values to table oauth_access_tokens, fields user_id and client_id set values as strings, but columns type is integer

[charithar]

I have the same issue. Is there any fix for it?

Edit: Seems like the version that support PHP 7.3 still has this bug. May be it's discontinued.

[Ocramius]

Send patches, if you have a reproducible test case.

[charithar]

@Ocramius I will give you the steps I followed which lead to this issue with version 2.41

1. Installed the mezzio skeleton app from https://docs.mezzio.dev/mezzio/ without any template renders
2. Installed this package from https://docs.mezzio.dev/mezzio-authentication-oauth2/
3. Followed the instructions from https://docs.mezzio.dev/mezzio-authentication-oauth2/v1/intro/ to setup
4. Created mysql db structure using the two scripts provided in https://docs.mezzio.dev/mezzio-authentication-oauth2/v1/intro/#oauth2-database
5. Tried to authenticate using the test credentials created above with "password" grant type.

I managed to face issues with both user_id and client_id field. The db is created with integer fields but the library tries to insert string values (username and client name) to those fields. I hope this helps. Am I using wrong scripts to create the db?

[rbroen]

I can confirm this. When the parameters below are set in persistNewAccessToken() from class Mezzio\Authentication\OAuth2\Repository\Pdo\AccessTokenRepository, both variables will be strings, but the table 'oauth_access_tokens' is expecting integers.

':user_id'    => $accessTokenEntity->getUserIdentifier(),
':client_id'  => $accessTokenEntity->getClient()->getIdentifier(),

[Ocramius]

At the risk of sounding like a broken record again: send patches with automated tests, please.

[matthewpallotta]

Looks like between 1.2.0 and 1.2.1 oauth2.sql changed from VARCHAR to INT for oauth_access_tokens.
Looking at Laminas API they used VARCHAR and DotKernel doctrine setup also uses VARCHAR. Would updating the oauth2.sql included in the data folder fix this?

[rbroen]

@matthewpallotta Nice to see someone interested in solving this.
I believe it is not only the sql (in this repository), but also some code and the how-to in the documentation. It looks like the documentation is in this repository, that would make things easier...

Please send me a mail if you want to collaborate on this (or just want me to do a PR test or a proof read).

[matthewpallotta]

I am looking into it. I may have a PR ready over the New Year.