How do I prevent the same (upper/lowercase in-sensitive) usernames in PHP?

Question

I can prevent multiple users from registering with same username with the code below:

$sqlusercheck = "SELECT (SELECT COUNT(row_username) FROM table_users WHERE row_username = :username LIMIT 1) as checkusername";
$stmt = $database->prepare($sqlusercheck);
$stmt->bindValue(':username', $username);
$stmt->execute();
$row = $stmt->fetch();

if ($row->checkusername > 0) {
    $this->error[] = "This username is already taken.";
    $success = false;
}

So, if there is Abc in the database, the user can't take the username Abc, but he anyway can register with ABC, abc, abC, etc., which I want to prevent.

As a possible solution, I don't want to strtolower() username. Instead I want Abc user to login with the his password regardless if he wrote to login input ABC, abc, abC, etc.

Why do I want this?

I observed that a user can sign-up with username abc on his computer. But when he want to login on his mobile device, device auto-capitalize first letter and user can't login. Or vice versa.

So, why I don't want strtolower()?

Because there are users also want to shown as abC, and registered themselves on purpose like that. So their usernames will shown as they registered.

Answer 1

In this case you can benefit from mysql's LOWER() and php's strtolower() function. (Don't worry, only when you are checking the availability.)

$sqlusercheck = "SELECT (SELECT COUNT(row_username) FROM table_users WHERE LOWER(row_username) = :username LIMIT 1) as checkusername";
$stmt = $database->prepare($sqlusercheck);
$stmt->bindValue(':username', strtolower($username);

If you change your query like that, it will make both of values lowercase and compare if they are the same.

If they are, your error will be appear.

If they are not, it will be added to your database as the way user type it.

Note: For the login as you described you should make the same changes on your login query.