Laravel Oracle User Provider


Fix authentication problems because of oracle case sensitive query.

Laravel Oracle User Provider

Fix authentication problems because of oracle case sensitive query.


When using oracle, we may encounter a problem on authentication because oracle queries are case sensitive by default. To avoid user issues when logging in and doing a forgot password failure because of case sensitivity, I need to create a custom user provider to handle this. And with laravel, this is easy as counting 1, 2, 3.

Creating a Custom Oracle User Provider

For a quck reference, see the official documentation for adding a custom user providers.

By default, laravel uses EloquentUserProvider class. Given this, we can begin our custom provider by extending this class. We then just need to override retrieveByCredentials method which laravel uses when authenticating and doing a forgot password task.

public function retrieveByCredentials(array $credentials)
{
    if (empty($credentials)) {
        return;
    }

    // First we will add each credential element to the query as a where clause.
    // Then we can execute the query and, if we found a user, return it in a
    // Eloquent User "model" that will be utilized by the Guard instances.
    $query = $this->createModel()->newQuery();

    foreach ($credentials as $key => $value) {
        if (! Str::contains($key, 'password')) {
            $query->where($key, $value);
        }
    }

    return $query->first();
}

To fix the case sensitivity issue, we just need to set the query statement and change its case to UPPER. Our custom class should look something like below:

namespace App\Auth;

use Illuminate\Auth\EloquentUserProvider;
use Illuminate\Support\Str;

class OracleUserProvider extends EloquentUserProvider
{
    /**
     * Retrieve a user by the given credentials.
     *
     * @param  array $credentials
     * @return \Illuminate\Contracts\Auth\Authenticatable|null
     */
    public function retrieveByCredentials(array $credentials)
    {
        if (empty($credentials)) {
            return;
        }

        // First we will add each credential element to the query as a where clause.
        // Then we can execute the query and, if we found a user, return it in a
        // Eloquent User "model" that will be utilized by the Guard instances.
        $query = $this->createModel()->newQuery();

        foreach ($credentials as $key => $value) {
            if (! Str::contains($key, 'password')) {
                $query->whereRaw("upper({$key}) = upper(?)", [$value]);
            }
        }

        return $query->first();
    }
}

Next step is to register our provider in AuthServiceProvider.

Auth::provider('oracle', function ($app, array $config) {
    return new OracleUserProvider($app['hash'], $config['model']);
});

Finally, to use our custom provider, we just need to update auth.php config and set the driver to oracle

'providers' => [
    'users' => [
        'driver' => 'oracle',
        'model' => App\User::class,
    ],
]

  • Laravel
  • Oracle
  • Oci8