Unique Lowercase validator

Yii 2.0 General Discussions
1

Hi,

I’ve just installed Yii2 advanced template, and still learning - haven’t produced any useful code yet.

When a user signs up from the signup page of the advanced template, the email address is checked by the Unique validator:

    ['UserEmail', 'trim'],
    ['UserEmail', 'required'],
    ['UserEmail', 'email'],
    ['UserEmail', 'string', 'max' => 255],
    ['UserEmail', 'unique', 'targetClass' => '\common\models\User', 'message' => 'This email address has already been taken.']

However, it does not do enough, and so I want to add code to UniqueValidator.php to support this:

        ['UserEmail', 'unique', 'case' => 'lower', 'targetClass' => '\common\models\User', 'message' => 'This email address has already been taken.'],

I was hoping someone could point out how to make the changes - where to find the array of properties so I can add the new ‘case’ property with allowed values of lower or any (any must be the default, so the behaviour remains unchanged if the case property is not used and also how to ensure the sql I use will work on most databases.

The reason for the changes is that email addresses like "FirstMiddleLast@EmailAddress.com" can be left in the database as the user prefers it, but the unique check is done using only lowercase, so that "Firstmiddlelast@EmailAddress.com" is picked up as a duplicate email address.

Best Regards,
Richard.

1 Like
2

Try adding filter before unique validation

[
    ['UserEmail'],
    'filter',
     [
         'filter' => 'strtolower',
         'skipOnArray' => true,
     ]
],
3

I don’t think that will have the same effect. A filter will change the user’s input so that lower case is stored in the db, but what I need to do is store what the user types in mixed case, but validate on lower case.

4

Hi @RichardPillay, welcome to the forum.

case property for UniqueValidator seems a nice idea to me, though I would call it ignoreCase.

You have to write your own validator extending \yii\validator\UniqueValidator. The first step is to read the source code of it.

1 Like
5

Thanks for the reply. That makes sense, sort of. I still like the idea of simply adding a property to the existing UniqueValidator and submitting it back to be added to the codebase, but I’ll keep that for when I’ve learnt a little more. I’m finding it quite a lot to learn all at once - figuring out how routes work and so on, but I guess that’s always the way when you first start with something new.

I think your idea of extending the existing validator so that there’s a new one that works slightly differently is a little quicker off the mark, and less likely to result in bugs while I still don’t understand the system fully. Where I was heading with it when I asked the question was to modify the existing UniqueValidator.php so that when the ignoreCase property was passed to it, it would simply change the sql to fetch lower(column) and compare against lower(userinput). This turned out not be so simple as all the sql is built up bit by bit in a manner that’s not just a linear path. Definitely a possibility of breaking the code under certain conditions.

1 Like
6

Yeah, that’s exactly what I’d like to see. :+1:

I also looked into the source code and found it not that simple and easy, it’s too complicated and sophisticated for me. So extending the existing \yii\validators\UniqueValidator might not be a good idea for the moment. I would rather want to write my own simple one for the app specific needs.
Guide > Creating Validators

7

Query like “lower(username) = some-string” most likely will trigger full scan, so it’s better to define database column as case insensitive.
Like COLLATE utf8mb4_unicode_ci in MySQL or citext in PostgreSQL.

1 Like
8

Hi @batyrmastyr, welcome to the forum.

Thank you for the comment. I totally didn’t know that.

9

This would have been a really great solution and solved the problem with no code changes, but the sticking point is that Postgres only has citext but no varchar equivalent. This will make other code changes necessary - the email standard restricts addresses to 254 characters so that means all inserts will have to be length checked in code.

If you’re going to have to make code changes anyway, the preferable option is to stay with the original idea of adding the ignorecase property to the existing function, as that will remain non-db-specific.

10

From my experience, UniqueValidator doesn’t guarantee uniqueness - once in a two weeks I see a user that clicks fast enough to trigger two inserts, so you have to rely on database.
As for length validation - varchar = text + check constraint in Postgres, so you can easily add email citext check(length(email) < 255).
I suppose it’s a good idea to check email address length in EmailValidator.

11

I didn’t’ think about whether there was an option to limit the length of the text field. The citext does make everything more seamless, and it will work everywhere, even from raw SQL. Problem in my mind is still the db portability, but I’m quite happy to limit myself to Postgres. Having said that, I’ve almost got things figured out on a code-based solution. It works in principle, but I’ve introduced a bug somewhere that I’ve got to track down.

12

The validator will check the email address length, since it adds very little effort. But it is essential that it be enforced at the DB level as well, because you don’t know where the input will come from over the years.

Do you know if it is possible to add a citext collation to a varchar field and whether a collation can be used for this? My feeling is that collation affects only the sorting of indexes, but not the data, so that the best way will still be the citext field with the length constraint.