Case insensitive authentication CakePHP 3

Question

I want to make the username check on my CakePHP app case insensitive.

Currently my Auth component is loaded like this:

$this->loadComponent('Auth', [
    'loginAction' => [
        'controller' => 'Users',
        'action' => 'login'
    ],
    'authError' => 'You need to be logged in',
    'authenticate' => [
        'Form' => [
            'fields' => ['username' => 'username', 'password' => 'password'],
            'finder' => 'auth',
        ]
    ],
    'loginRedirect' => [
        'controller' => 'Pages',
        'action' => 'display',
        'home'
    ],
    'logoutRedirect' => [
        'controller' => 'login'
    ]
]);

And my AuthFinder looks like this:

/**
 * @param Query $query
 * @param array $options
 * @return Query
 */
public function findAuth(Query $query, array $options)
{
    return $query->contain(['Contacts'])
        ->where([
            'activated' => 'yes',
            'LOWER(username)' => strtolower($options['username'])
        ]);
}

However trying to log in with my uppercase username does not work. How can I do this?

EDIT 1:

This is being done on a Postgres DB. So I don't have _ci collation available to me

EDIT 2:

This is an edit for @dakota's comment:

/src/Model/Table/UsersTable.php (line 116)
object(Cake\ORM\Query) {

    '(help)' => 'This is a Query object, to get the results execute or iterate it.',
    'sql' => 'SELECT Users.id AS "Users__id", Users.contact_id AS "Users__contact_id", Users.username AS "Users__username", Users.password AS "Users__password", Users.last_login AS "Users__last_login", Users.last_login_ip AS "Users__last_login_ip", Users.password_reset_key AS "Users__password_reset_key", Users.password_reset_date AS "Users__password_reset_date", Users.is_super_user AS "Users__is_super_user", Users.configured AS "Users__configured", Users.activated AS "Users__activated", Contacts.id AS "Contacts__id", Contacts.firstname AS "Contacts__firstname", Contacts.lastname AS "Contacts__lastname", Contacts.id_number AS "Contacts__id_number", Contacts.company_id AS "Contacts__company_id", Contacts.has_own_transport AS "Contacts__has_own_transport", Contacts.vehicle_license_type AS "Contacts__vehicle_license_type", Contacts.max_travel_distance AS "Contacts__max_travel_distance", Contacts.description AS "Contacts__description", Contacts.first_language AS "Contacts__first_language", Contacts.second_language AS "Contacts__second_language", Contacts.facebook AS "Contacts__facebook", Contacts.twitter AS "Contacts__twitter", Contacts.linkedin AS "Contacts__linkedin", Contacts.profile_image AS "Contacts__profile_image", Contacts.cell_number AS "Contacts__cell_number", Contacts.date_of_birth AS "Contacts__date_of_birth", Contacts.gender AS "Contacts__gender", Contacts.address AS "Contacts__address", Contacts.coordinates AS "Contacts__coordinates", Contacts.is_agent AS "Contacts__is_agent", Contacts.rate AS "Contacts__rate" FROM users Users INNER JOIN contacts Contacts ON Users.id = (Contacts.id) WHERE (Users.username = :c0 AND activated = :c1 AND LOWER(username) = :c2)',
    'params' => [
        ':c0' => [
            'value' => 'myemail@domain.com',
            'type' => 'string',
            'placeholder' => 'c0'
        ],
        ':c1' => [
            'value' => 'yes',
            'type' => 'string',
            'placeholder' => 'c1'
        ],
        ':c2' => [
            'value' => 'myemail@domain.com',
            'type' => null,
            'placeholder' => 'c2'
        ]
    ],
    'defaultTypes' => [
        'Users__id' => 'integer',
        'Users.id' => 'integer',
        'id' => 'integer',
        'Users__contact_id' => 'integer',
        'Users.contact_id' => 'integer',
        'contact_id' => 'integer',
        'Users__username' => 'string',
        'Users.username' => 'string',
        'username' => 'string',
        'Users__password' => 'string',
        'Users.password' => 'string',
        'password' => 'string',
        'Users__last_login' => 'timestamp',
        'Users.last_login' => 'timestamp',
        'last_login' => 'timestamp',
        'Users__last_login_ip' => 'string',
        'Users.last_login_ip' => 'string',
        'last_login_ip' => 'string',
        'Users__password_reset_key' => 'string',
        'Users.password_reset_key' => 'string',
        'password_reset_key' => 'string',
        'Users__password_reset_date' => 'timestamp',
        'Users.password_reset_date' => 'timestamp',
        'password_reset_date' => 'timestamp',
        'Users__is_super_user' => 'string',
        'Users.is_super_user' => 'string',
        'is_super_user' => 'string',
        'Users__configured' => 'string',
        'Users.configured' => 'string',
        'configured' => 'string',
        'Users__activated' => 'string',
        'Users.activated' => 'string',
        'activated' => 'string',
        'Contacts__id' => 'integer',
        'Contacts.id' => 'integer',
        'Contacts__firstname' => 'string',
        'Contacts.firstname' => 'string',
        'firstname' => 'string',
        'Contacts__lastname' => 'string',
        'Contacts.lastname' => 'string',
        'lastname' => 'string',
        'Contacts__id_number' => 'string',
        'Contacts.id_number' => 'string',
        'id_number' => 'string',
        'Contacts__company_id' => 'string',
        'Contacts.company_id' => 'string',
        'company_id' => 'string',
        'Contacts__has_own_transport' => 'string',
        'Contacts.has_own_transport' => 'string',
        'has_own_transport' => 'string',
        'Contacts__vehicle_license_type' => 'string',
        'Contacts.vehicle_license_type' => 'string',
        'vehicle_license_type' => 'string',
        'Contacts__max_travel_distance' => 'string',
        'Contacts.max_travel_distance' => 'string',
        'max_travel_distance' => 'string',
        'Contacts__description' => 'text',
        'Contacts.description' => 'text',
        'description' => 'text',
        'Contacts__first_language' => 'integer',
        'Contacts.first_language' => 'integer',
        'first_language' => 'integer',
        'Contacts__second_language' => 'integer',
        'Contacts.second_language' => 'integer',
        'second_language' => 'integer',
        'Contacts__facebook' => 'string',
        'Contacts.facebook' => 'string',
        'facebook' => 'string',
        'Contacts__twitter' => 'string',
        'Contacts.twitter' => 'string',
        'twitter' => 'string',
        'Contacts__linkedin' => 'string',
        'Contacts.linkedin' => 'string',
        'linkedin' => 'string',
        'Contacts__profile_image' => 'string',
        'Contacts.profile_image' => 'string',
        'profile_image' => 'string',
        'Contacts__cell_number' => 'string',
        'Contacts.cell_number' => 'string',
        'cell_number' => 'string',
        'Contacts__date_of_birth' => 'date',
        'Contacts.date_of_birth' => 'date',
        'date_of_birth' => 'date',
        'Contacts__gender' => 'string',
        'Contacts.gender' => 'string',
        'gender' => 'string',
        'Contacts__address' => 'string',
        'Contacts.address' => 'string',
        'address' => 'string',
        'Contacts__coordinates' => 'string',
        'Contacts.coordinates' => 'string',
        'coordinates' => 'string',
        'Contacts__is_agent' => 'string',
        'Contacts.is_agent' => 'string',
        'is_agent' => 'string',
        'Contacts__rate' => 'decimal',
        'Contacts.rate' => 'decimal',
        'rate' => 'decimal'
    ],
    'decorators' => (int) 0,
    'executed' => false,
    'hydrate' => true,
    'buffered' => true,
    'formatters' => (int) 0,
    'mapReducers' => (int) 0,
    'contain' => [
        'Contacts' => []
    ],
    'matching' => [],
    'extraOptions' => [
        'username' => 'myemail@domain.com'
    ],
    'repository' => object(App\Model\Table\UsersTable) {

        'registryAlias' => 'Users',
        'table' => 'users',
        'alias' => 'Users',
        'entityClass' => 'App\Model\Entity\User',
        'associations' => [
            (int) 0 => 'contacts',
            (int) 1 => 'users'
        ],
        'behaviors' => [],
        'defaultConnection' => 'default',
        'connectionName' => 'default'

    }

}

If you're using MySQL, you should be able to set the collation on the users table to a `_ci` (case insensitive) option, and you won't have to do any special coding. — Greg Schmidt, May 14 '18 at 20:48
@GregSchmidt I'm using Postgres. Will check if I can do something similar there. — Bird87 ZA, May 15 '18 at 06:36
What is the generated sql? (You can just wrap the finder body in `debug()`) — Dakota, May 21 '18 at 08:06

score 2 · Accepted Answer · answered May 22 '18 at 00:00

2

If you look at the very end of your query, you'll see that it has:

WHERE (Users.username = :c0 AND activated = :c1 AND LOWER(username) = :c2)

The conditions you've provided are only the second two; Cake is automatically putting the first one in there for you. You obviously don't want that, but you should be able to use the third parameter to the where function to replace all conditions:

return $query->contain(['Contacts'])
    ->where([
        'activated' => 'yes',
        'LOWER(username)' => strtolower($options['username'])
    ], [], true);

answered May 22 '18 at 00:00

Greg Schmidt

5,010
2
14
35

I was never aware of the extra options for the where condition. I honestly need to make more effort to read the docs. Thanks Greg. – Bird87 ZA May 22 '18 at 04:34
It is also possible tu use `orWhere()` method without additional arguments (but probably less effective). – r34 Dec 06 '18 at 07:23

Case insensitive authentication CakePHP 3

1 Answers1