Passport: SQL when using token with Postgres

[Roemerb]

Hi guys,

Just like #414, I'm experiencing an issue when using a oauth token:

Illuminate \ Database \ QueryException (22P02)
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "" (SQL: select * from "users" where "id" = limit 1)

For some reason, the user ID seems to be empty in this case. The issue is resolved when using MySQL as the database driver. Has anyone solved this or has suggestions on how it could be solved (other than using MySQL)?

Other mentions of this issue

• https://github.com/laravel/framework/pull/18364
• https://github.com/laravel/passport/issues/54
• https://github.com/laravel/passport/issues/311

[zolotov88]

I have this error too.

[webdevog]

same issue

[tepease]

I had this issue as well - it turned out I had accidentally left my route in a group with
'middleware' => ['auth:api'].
Moving it to a group without specified middleware resolved the issue.

[cradoi-ro]

@tepease This means you don't use authorization for that endpoint, I think this isn't the solution.

I have same issue here.

[sukhpalsingh]

Experiencing same issue. From more debugging, it looks like the user_id is not added to the token when the token is getting generating which is causing the empty value for the user_id. Not sure the reason yet why the token record does not have user_id when generating token.

[chewbakartik]

Has there been no traction on this problem? I am experiencing the same issue.

[chewbakartik]

I thought that I would come back and leave a comment here in the event that there are any others in a similar position trying to find answers. My particular reason why it wasn't working is that I was trying to use the client_credentials grant_type for my token, which doesn't use the user_id in the token.

I had not updated my routes middleware, however, to be using the CheckClientCredentials. Here's a good resource for seeing the configuration necessary to sort this out.

https://alfrednutile.info/posts/211

[driesvints]

We really need more info for this. Please post related code: endpoints, http requests, what data you're passing etc for us to know what's going on. Going to close this as this lacks this info but feel free to reply with the above mentioned things and I'll be happy to re-open.

[wederribas]

Hello guys.

@driesvints This comment may help you guys to understand it better. ;-)

I faced this same error when setting up a REST API with Laravel Passport authentication to be used with Client Credentials Grant Tokens. In my use case, I need to expose this API to some batch scripts that run in others servers, with no user interface.

So the client is going to make requests to my _/oauth/token_ in order to get access_token and then make subsequent requests with this token to my protected resources (e.g. _/api/v1/orders/1_).

Then, I followed all the steps in the Laravel Passport documentation page to setup the oauth server. For instance:

$ composer require laravel/passport
$ php artisan migrate
$ php artisan passport:install

Then I added the HasApiTokens trait in the user model, added the Passport::routes(); in the boot() method in the AuthServiceProvider.

Additionally, I have changed the driver option of the api authentication guard to passport. Like this:

'guards' => [
    'web' => [
        'driver' => 'session',
        'provider' => 'users',
    ],

    'api' => [
        'driver' => 'passport',
        'provider' => 'users',
    ],
]

Finally, to enable the Client Credentials Grant, I added the CheckClientCredentials middleware to the $routeMiddleware property in the app/Http/Kernel.php file. It looks like this now:

/**
     * The application's route middleware.
     *
     * These middleware may be assigned to groups or used individually.
     *
     * @var array
     */
    protected $routeMiddleware = [
        'auth' => \App\Http\Middleware\Authenticate::class,
        'auth.basic' => \Illuminate\Auth\Middleware\AuthenticateWithBasicAuth::class,
        'bindings' => \Illuminate\Routing\Middleware\SubstituteBindings::class,
        'cache.headers' => \Illuminate\Http\Middleware\SetCacheHeaders::class,
        'can' => \Illuminate\Auth\Middleware\Authorize::class,
        'guest' => \App\Http\Middleware\RedirectIfAuthenticated::class,
        'signed' => \Illuminate\Routing\Middleware\ValidateSignature::class,
        'throttle' => \Illuminate\Routing\Middleware\ThrottleRequests::class,
        'verified' => \Illuminate\Auth\Middleware\EnsureEmailIsVerified::class,
        'client' => \Laravel\Passport\Http\Middleware\CheckClientCredentials::class
    ];

And this is the route in my routes/api.php file:

Route::group(['prefix'=> 'v1', 'middleware' => 'auth:api'], (function () {

    [...]
    Lots of routes
    [...]

    Route::get('orders/{id}', 'OrdersController@show')->middleware('client');
});

The issue then happened when I tried then to reach this route with the access_token I got with the client_id and client_secret.

For instance, the first request was:

Path: /oauth/token
Header: {
    Accept: application/json
    Content-type: multipart/form-data
}
Form data: {
    grant_type: client_credentials
    client_id: 123123
    client_secret: reallyLongString
    scope: ''
}

Then I got the access_token back, and sent it to the API resource with the token in the Authorization Bearer header:

Path: /api/v1/orders
Header: {
    Accept: application/json
    Authorization: Bearer reallyLongString
}

And then I got a 500 error response code, with the following error output:

SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for integer: \"\" (SQL: select * from \"users\" where \"id\" =  limit 1)

What I found was that is that the route middleware defined in the group route as auth:api was not overridden by the client middleware defined in the _Route::get('orders/{id}', 'OrdersController@show')->middleware('client')_.

My workaround for this was removing the middleware in the group route, and explicitly adding the route middleware to each individual route.

[driesvints]

@wederribas heya. Just had time to check into this. When using the client credentials grant you don't need to use the auth:api middleware on your routes since there's no user involved. Just use the client middleware and you're good.

[wederribas]

@driesvints Yeah, that's the point I was trying to understand (at the time I did not understand the OAuth flow completely). But now I got it. Thank you! 😄