CODEWITHSUNDEEP
php
IDontKnow
IDontKnow

Reputation: 355

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

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.

Upvotes: 11

Views: 2405

Answers (4)

Francesco de Guytenaere
Francesco de Guytenaere

Reputation: 4833

MySQL

It depends on your table collation. If it ends with _ci, then it already is case insensitive when you do a nonbinary string comparison, which by default they already are. However, if it ends with _cs or _bin, then it is case sensitive.

If your table collation is case sensitive and you want to do a case insensitive query, you can add COLLATE to the query, like so:

... WHERE row_username COLLATE collation_you_want_ci = :username LIMIT 1

If you want to do a case sensitive query on a _ci collation, without having to resort to converting strings to upper- or lowercase, you can use BINARY:

... WHERE BINARY row_username = :username LIMIT 1

PostgreSQL

In PostgreSQL the default is case sensitive for string comparisons. You can use ILIKE, ~* or LOWER but note that these have potential performance implications concerning your indexes. Use the citext data type or a function-based index.

Upvotes: 10

Ashwini G.
Ashwini G.

Reputation: 264

You can try the following solution:

$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));
$stmt->execute();
$row = $stmt->fetch();

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

Upvotes: 3

LetsSeo
LetsSeo

Reputation: 875

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.

Upvotes: 11

Ravi Hirani
Ravi Hirani

Reputation: 6539

Use mysql LOWER in your query and use strtolower() PHP function when you bind param:-

$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)); // use strtolower function
$stmt->execute();
$row = $stmt->fetch();

Hope it will help you :)

Upvotes: 3

Related Questions