Case insensitive SQL queries with utf8mb4 (Laravel)

Question

I'm trying to make a account system that only allows unique usernames to be created. SQL queries used to be case insensitive, but after changing the charset to utf8mb4 it's case sensitive meaning people can use duplicate usernames. (I changed to utf8mb4 to allow emojis and other symbols in user bios, so reverting back to the old charset isn't an option)

I've tried changing the connection collation, however everything I try gives me error 500. I need something that won't require me to add "strtolower($username)" in every query.

Example query:

if(DB::table('users')->where('username', $username)->count() > 0){
    return response()->json(['status'=>'error','message'=>'Username is taken']);
}

SQL configuration:

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', 'localhost'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

When a user tries to use a duplicate username the server should return:

{'status': 'error, 'message': 'Username is taken'}

but the user is actually able to create an account.

Please provide `SHOW CREATE TABLE` and an example of a problem pair of user names. — Rick James, Aug 19 '19 at 23:15

score 1 · Accepted Answer · answered Aug 21 '19 at 01:24

1

Fixed by changing all table collations to utf8mb4_unicode_ci.

ALTER TABLE <table> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

answered Aug 21 '19 at 01:24

d0x

41
6

Kenny Horna · Answer 2 · 2019-08-19T23:34:58.917

You could define a mutator in your model. This method will be called everytime you want to store data:

class User extends Model
{
    /**
     * Set the user's username in lowercase.
     *
     * @param  string  $value
     * @return void
     */
    public function setUsernameAttribute($value)
    {
        $this->attributes['username'] = strtolower($value);
    }
}

Now if you have declared that column as unique in your migration (as follows):

Schema::create('users', function (Blueprint $table) {
    // ...
    $table->string('username')->unique();
});

Then it will throw an exception error because it will detect that the system is trying to create a duplicated record. So just handle this exception to return the proper response to the user.

use Illuminate\Database\QueryException;

// ...

try
{
    $user = User::create($data);
}
catch (QueryException $e)
{
    $errorCode = $e->errorInfo[1];

    if ($errorCode == 1062)
    {
        return response()->json(['status'=>'error', 'message'=>'Username is taken']);
    }
}

But then, you'd need to add this everywhere.. so just add it in your exception handler and then you're good to go.

Note: I made use of this another question to elaborate my answer.

I've not been using Laravel for long so I'm not sure exactly what I'm supposed to do. Does it mean I'll have to replace all queries? Or will it be case insensitive for all queries? Also, I've got more tables that have this problem, is there not a way to change the charset to make it case insensitive but still allow special characters? — d0x, Aug 20 '19 at 00:21

Case insensitive SQL queries with utf8mb4 (Laravel)

2 Answers2