Framework: Case-sensitive "unique" Validator

[lamoni]

Is there a way to get the "unique" validator option to be case insensitive? It seems right now, Laravel assumes you're using a case-insensitive database.

[ArjanSchouten]

(homestead:)

• Mysql is case-insensitive
• Postgresql is case-sensitive
• Sqlite and MSSQL not tested yet

This is different behavior and that's not why I use Laravel. I want to be able to switch database and get the same behavior.

The best solution I think is adding a new option iunique (like ILIKE). It's actually a bit difficult to add this behavior. I think we can't add it in a simple way. Tried code similar to this and it worked.

$attribute = DB::raw('LOWER('.$attribute.')');
$this->validateUnique($attribute, $value, $parameters);

However this violates the idea of an database independent validator. Therefore we've to change the DatabasePresenceVerifier. But telling the DatabasePresenceVerifier which case to choose is impossible. Therefore we've to change the PresenceVerifierInterface.

This means we've to wait for Laravel 5.2 if Taylor decides to add this functionality or accept a PR for this. This because we change the interface which means that mentioning in an upgrade guide is required.

My suggestion is to change the getCount method to:
public function getCount($collection, $column, $value, $excludeId = null, $idColumn = null, array $extra = [], $ignoreCase = true);. However the code becomes more unreadable right now. There are too many parameters. (Probably we can do the same with getMultiCount).

When decided to add this functionality we've another problem. Making the database handling queries case-(in)sensitive is a bit difficult. In postgresql for example we can use ILIKE which means that we've to escape characters used in likes. Furthermore this is slow.

We can use LOWER(column)=LOWER(value) but this can't use indexes.

Conclusion: it's not impossible but is difficult, needs database specific code and can violate performance. We've to add functionality to the Grammers for this.

[lamoni]

@ArjanSchouten Excellent write-up, thanks.

I'd even prefer to make database drivers have to implement some sort of "getIsUnique" type interface, which the unique validator could then reference. I say this because I'm using Neo4j as my database, which I highly doubt would get implemented by default due to the lack of popular use of graph DBs.

[GrahamCampbell]

Ping @taylorotwell.

[fisharebest]

We can use LOWER(column)=LOWER(value)

Not in MySQL. If value is a case-insensitive string, then LOWER(value) is also a case insensitive string. Instead, you need to specify a binary collation

mysql> SELECT 'a' = 'A' COLLATE utf8_bin;
+----------------------------+
| 'a' = 'A' COLLATE utf8_bin |
+----------------------------+
|                          0 |
+----------------------------+

but this can't use indexes.

Yes it can?

mysql> CREATE TABLE t (col VARCHAR(32), INDEX(col));
mysql> INSERT INTO t (col) VALUES ('a'), ('b'), ('A');
mysql> SELECT * FROM t WHERE col = 'A';
+------+
| col  |
+------+
| a    |
| A    |
+------+
mysql> SELECT * FROM t WHERE col = 'A' COLLATE utf8_bin;
+------+
| col  |
+------+
| A    |
+------+
mysql> EXPLAIN SELECT * FROM t WHERE col = 'A' COLLATE utf8_bin;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t     | index | col           | col  | 99      | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

[ArjanSchouten]

@fisharebest its some time ago when I investigated this. I think it's due to the fact that mysql is by default case-insensitive that mysql is using the index. However if Im right, mysql can be configured to be case sensitive. I wonder if mysql is going to use the index if it's configured to handle queries case sensitive.

Note: mysql isn't the only database which is supported by Laravel. So we've to do some research on sqlite and mssql too!

[sangar82]

@GrahamCampbell Reopen please!

[nshontz]

I second the reopen! Is there a suitable workaround?

[lamoni]

My (crappy) workaround has to been to just have another column specifically for holding the lower-case values, and doing the validation on those columns when needed. For obvious reasons, this is not ideal, but luckily my data set allows me to do this.

[fisharebest]

Is there a suitable workaround?

Create the database/column with case-sensitive collation?

$table->string('col')->collate('utf8_bin');

[nshontz]

@fisharebest Thanks for your reply, I'm using Postgres, collation is en_US.UTF-8, I've also used $table->string('email')->collate('utf8_bin')->change(); but i'm still having the same issue where my unique rule ('email' => 'required|email|unique:user') is case sensitive and allowing multiple.

If this is a me problem or if there's something I can do to fix it, I'm all about doing that, but this behavior seems to be counter intuitive and should be looked into further either.

[fisharebest]

I'm using Postgres

IIRC, Laravel's ->collate() function is only implemented for the MySQL driver. You'll need to use raw SQL.

I wrote a PR for this (#3666) some time ago. It was rejected at the time, although the MySQL parts were subsequently implemented.

If you really don't want to use raw SQL, it is possible to extend the DB drivers.

[tortuetorche]

With PostgreSQL, we can do:

CREATE UNIQUE INDEX users_email_unique on users (LOWER(email));

But the command below, doesn't work:

ALTER TABLE users
  ADD CONSTRAINT users_email_unique UNIQUE (LOWER(email));

Source: http://shuber.io/case-insensitive-unique-constraints-in-postgres

So in my user migration file, I'm doing this:

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('email')/*->unique()*/;
            $table->string('password', 64);
            $table->string('remember_token', 100)->nullable();
            $table->timestamps();
        });
        DB::statement('CREATE UNIQUE INDEX users_email_unique on users (LOWER(email));');
    }

    //...
}

[tortuetorche]

@GrahamCampbell So maybe we can update the Illuminate\Database\Schema\Grammars\PostgresGrammar compileUnique() method?
To use the CREATE UNIQUE INDEX syntax instead of the ADD CONSTRAINT one.

Or we can override this method if we extends these core class: DatabaseServiceProvider, ConnectionFactory and PostgresConnection.
Then change the DatabaseServiceProvider class in the config/app.php file of our application :astonished:

Or try this solution or read this blog post.

[GrahamCampbell]

Maybe. Ping @taylorotwell.

[tortuetorche]

I think this unique index issue, with PostgreSQL, could be considered as a (low) security issue.
Because the developer doesn't expect to have a sensitive unique index.
So in the Laravel application, a user can sign up twice if he uses the same email or username in lowercase then in uppercase.

NB: This issue isn't specific to the Laravel framework, Ruby on Rails seems to have the same issue and certainly other frameworks.

[tortuetorche]

Or even worst, if your application is a message board or a social network.
Another user could register with a very similar username than as a _popular_ user (with some letters in uppercase). E.G. @taylorotwell and @taylorOtwell
So other users can confuse these two usernames, which are really close.

[lamoni]

That's the exact situation that brought me to discovering the issue.

[CurosMJ]

@GrahamCampbell @taylorotwell Please add bug label to this issue, since this can be a serious security issue for people who are unaware of this issue. For example, if someone's been validating emails with the unique rule before entering into a database which is case sensitive, this would be a terrible situation, allowing users to create multiple accounts with the same emails, using different case versions of the same email, for example, [email protected] and [email protected] . This is quite a serious thing, please re-open this issue.

I would suggest using regexp to query database in a case-insensitive manner in DatabasePresenceVerifier. That would probably solve the issue.

[craigh411]

I work around this issue by overriding the all() method in my FormRequest and converting the unique field to lower case:

    public function all()
    {
        $request = parent::all();
        $request['my_unique_field'] = strtolower($request['my_unique_field']);

        return $request;
    }

This does mean that your values are stored in lowercase, but this is easy enough to fix with mutators if you want to change the case.

[reganjohnson]

Working with Postgresql here... I added in a custom validation to AppServiceProvider.php under the boot() method:

Validator::extend('iunique', function ($attribute, $value, $parameters, $validator) {
    $query = DB::table($parameters[0]);
    $column = $query->getGrammar()->wrap($parameters[1]);
    return ! $query->whereRaw("lower({$column}) = lower(?)", [$value])->count();
});

This can be extended further to accept the other parameters similar to the unique rule, but it's a start that worked for my use case.

Now my $rules looks like this:

protected $rules = [
    'username' => 'required|alpha_dash|min:5|max:18|iunique:users,username',
];

[sangar82]

we need an "official" fix, please!!

[avonian]

@GrahamCampbell I'm sorry maybe I missed the explanation but, why was this issue closed? any chance we can reopen it in the hopes we'll get a proper fix?

[PaoJiao]

im using $table->string('uuid', 32)->charset('utf8mb4 COLLATE utf8mb4_bin')->unique()
got working with MySql

[yingliangzhang]

@reganjohnson Great solution!
Do you know how to use the iunique rule with the Rule class to fluently define it?

For example, with existing rules, you can do Rule::unique('users')->ignore($user->id) ? But when I try to do Rule::iunique(), I got error Method iunique does not exist. Any ideas?

Thanks!

[noahtallen]

I think I'm having the same issue, but with a migration. In the initial creation of the table, I specified the following:

public function up()
{
    Schema::create('posts', function (Blueprint $table) {
       //.......//
      $table->integer('userID');
      $table->integer('articleID');

      $table->foreign('userID')->references('id')->on('users');
      $table->foreign('articleID')->references('id')->on('articles');
   });
}

However, I didn't realize that I needed to have user_id and article_id for an eloquent relationship to work. So I added another migration:

Schema::table('posts', function (Blueprint $table) {
    $table->renameColumn("userID", 'user_id');
    $table->renameColumn("articleID", 'article_id');
});

And when running php artisan migrate, I get the error:

[Illuminate\Database\QueryException]
SQLSTATE[42703]: Undefined column: 7 ERROR:  column "userid" does not exist (SQL: ALTER TABLE posts RENAME COLUMN userID TO user_id)

Interestingly, when running that exact query from pgadmin, I get the exact same error. However, when I surround the userID portion of the query in quotes (as in the following), the query works in pgadmin.
ALTER TABLE posts RENAME COLUMN userID TO user_id)

Thing is, I'm not sure how to get this migration to work "smoothly" since I can't edit that query other than dropping the table and starting over with it, which won't be a big deal.

I wonder if a solution would be to surround all column names with double quotation marks in whatever compiles the query from the migration information.

[noahtallen]

I suppose another solution would be to just stick everything in lowercase all the time for any database.

[skrrp]

@yingliangzhang - I tried looking to see how Laravel itself does it with fluent - it seems to revolve around the Rules\Unique class. I failed to implement something that works in the same way.

What I did end up with is;

    Validator::extend('iunique', function ($attribute, $value, $parameters, $validator) {
        $query = DB::table($parameters[0]);
        $column = $query->getGrammar()->wrap($parameters[1]);

        if (isset($parameters[2])) {
            if (isset($parameters[3])) {
                $idCol = $parameters[3];
            } else {
                $idCol = 'id';
            }

            $query->where($idCol, '!=', $parameters[2]);
        }

        return ! $query->whereRaw("lower({$column}) = lower(?)", [$value])->count();
    });

Which is really ugly, as it means that my validator function for an exclude looks like this;

protected function editValidator(array $data, $id)
{
    return Validator::make($data, [
        'name' => 'required|iunique:tags,name,' . $id . '|string|max:255',
    ]);
}

The full syntax for the rule above is:

iunique:[tableName],[uniqueColName],[excludeId],[idColName]

[midix]

Well, if you really want universal and internationally usable case-insensitive unique, you would have to use casefolds:
https://stackoverflow.com/a/6996550/217823

and I'm not sure how well they are supported by database engines and not sure if Laravel can do anything about that.

Anyway, internationalization is real PITA when developing projects - there are so many libraries and frameworks where i18n is not a first-class citizen causing lots of frustration for developers.

[xainpro]

I am using MongoDb and came across same issue with Case-sensitive "unique" Validator.
Is there any official solution around ?
For now i just fixed it by using a middleware and converting my username (email) field to lowercase in whole app so i do not need to worry about taking care of this issue in already developed parts.
but need to be consistent to use same field name in whole app for username field.

Here is my solution incase anyone want to use this.

Inside
App\Http\Kernel.php file i add a new middleware

\App\Http\Middleware\ConvertEmailToLowerCase::class,

inside this middle ware use a simple conversion

<?php

namespace App\Http\Middleware;


use Illuminate\Foundation\Http\Middleware\TransformsRequest as TransformsRequest;

class ConvertEmailToLowerCase extends TransformsRequest
{
    /**
     * Transform the given value.
     *
     * @param  string  $key
     * @param  mixed  $value
     * @return mixed
     */
    protected function transform($key, $value)
    {
        return $key === 'email' ? \strtolower($value) : $value;
    }
}

[sarathiscookie]

@xainpro, Thank you for this solution. I am also using mongodb and facing unique email problem. I followed you mentioned in your comment but not working this solution for me. I created a middleware and register in kernel. Validation part is in Auth/RegisterController.php. Anything I missed?

public function __construct()
    {
        $this->middleware('guest');

        $this->middleware('uniqueEmail');
    }

protected function validator(array $data)
    {
        return Validator::make($data, [
            'firstName' => 'required|string|max:255',
            'lastName' => 'required|string|max:255',
            'email' => 'required|string|email|max:255|unique:user,usrEmail',
            'password' => 'required|string|min:6|confirmed',
            'dataProtection' => 'required',
            'termsService' => 'required',
        ]);
    }

[xainpro]

please share your middleware code and Kernal code as well

[sarathiscookie]

@xainpro Thank you for your reply. This is my middleware and kernal code.

Middleware

class ConvertEmailToLowerCase
{
    /**
     * Transform the given value.
     *
     * @param  string  $key
     * @param  mixed  $value
     * @return mixed
     */
    protected function transform($key, $value)
    {
        return $key === 'email' ? \strtolower($value) : $value;
    }
}

Kernal.php

protected $middleware = [
        \App\Http\Middleware\ConvertEmailToLowerCase::class
    ];

protected $routeMiddleware = [
        'uniqueEmail' => \App\Http\Middleware\ConvertEmailToLowerCase::class
    ];

[tombenevides]

@reganjohnson, your code works fine when we try to create a new record. When we want to edit a record .. this code does not work, because if I do not change the value that has the validation .. Still the error message will appear saying that we tried to add a record that already exists in the database

Ex: Edit a user and modify only the age and not the username. Still the message error will appear.

For that, at least here, I changed the code to work in this way:

1) I have a hidden field that receive the id of the record;

2) The validation rules have 3 arguments: table, unique_column, id_column;

protected $rules = [
'username' => 'required|alpha_dash|min:5|max:18|iunique:users,username,user_id',
];

3) The helper in AppServiceProvider.php tests if exists id in the request. In this case, the validation will occur with the collection without the edited record. Otherwise, the validation occurs like you implemented:

Validator::extend('iunique', function ($attribute, $value, $parameters, $validator) {
$request = request()->toArray();
if (count($parameters) == 3 && in_array($parameters[2], array_keys($request)))
{
$query = DB::table($parameters[0])->whereNotIn($parameters[2], [$request[$parameters[2]]]);
}else{
$query = DB::table($parameters[0]);
}
$column = $query->getGrammar()->wrap($parameters[1]);
return ! $query->whereRaw("lower({$column}) = lower(?)", [$value])->count();
});

[devcircus]

Sounds like a custom validation rule is needed. You can easily tweak Postgres to have the same behavior as MySQL.

[tylik1]

For PostgreSQL I was able to solve this by altering column type with the following commands:

public function up()
    {
        Schema::create('teams', function (Blueprint $table) {
           ...
            $table->string('name')->unique();
           ...
        });

            Db::statement('CREATE EXTENSION IF NOT EXISTS citext');
            Db::statement('ALTER TABLE teams ALTER COLUMN name TYPE citext');
    }

This makes table column case insensitive, and it also works well with current laravel unique rule, so there is no need to put extra effort in writing custom rules

[tinyoverflow]

@devcircus Could you please share your solution for that?

[szamanr]

@GrahamCampbell is it possible to reopen this issue? i don't understand why it was closed.

[brandonmbanks]

I think this is a great solution for emails in registrations: https://stackoverflow.com/a/48746008/4940954

It enforces lowercase on the front end, so the user will know what to log in with. It won't break existing users by lowering them or adding getters and setters which lower them. Going forward all emails will be lowercase.

[padinko]

I found a solution to use laravel's unique validator with lowering values.

1. first add lower index in migration

    public function up()
    {
        $modelInstance = new User;
        $tableName     = $modelInstance ->getTable();
        $grammar       = $modelInstance->newQuery()->getQuery()->getGrammar();

        $table = $grammar->wrap($tableName);
        $index = $grammar->wrap("{$tableName}_email_lower_index");
        $email = $grammar->wrap('email');

        DB::statement("CREATE INDEX {$index} ON {$table} (lower({$email}))");
    }

    public function down()
    {
        $tableName = (new User)->getTable();

        Schema::table($tableName, function (Blueprint $table) {
            $table->dropIndex("{$table->getTable()}_email_lower_index");
        });
    }

1. then create a custom validator iunique in AppServiceProvider.php in boot, which only handle values and use laravel's unique validator:

    Validator::extend('iunique', function ($attribute, $value, $parameters, $validator) {
        if (isset($parameters[1])) {
            [$connection]  = $validator->parseTable($parameters[0]);
            $wrapped       = DB::connection($connection)->getQueryGrammar()->wrap($parameters[1]);
            $parameters[1] = DB::raw("lower({$wrapped})");
        }
        return $validator->validateUnique($attribute, Str::lower($value), $parameters);
    }, trans('validation.iunique'));

tested: postgresql