Check value if exist with uppercase and lowercase letters

Question

I want to check if username exists in Database but I have problem Ex : If I have username = jack and another register with username = JaCk

It accepts I want to check the full username with lowercase and uppercase and refuse such cases (jack - Jack - JaCk ....)

$stmtr = $DB_con->prepare("SELECT username FROM users WHERE username=:username");
$stmtr->bindparam(":username", $val['username']);
$stmtr->execute();
$row=$stmtr->fetch(PDO::FETCH_ASSOC);

if($rowr['username']==$val['username']) {
echo "Exists";
}

What did you set for collation? You also don't need a second comparison, if you get a result it matched.. — user3783243, Aug 28 '18 at 03:13

score 2 · Answer 1 · answered Aug 28 '18 at 03:14

2

There is a typo. In line:

if($rowr['username']==$val['username']) {

it should be:

if($row['username']==$val['username']) {

Also, by default, MySQL performs case-insensitive query. Note the _ci part in your DB collation (e.g. utf8_general_ci), it means case insensitive. You have to perform extra checking in PHP to check for different upper- and lowercase.

answered Aug 28 '18 at 03:14

Raptor

53,206
45
230
366

Changing the collations is also an option, then just need to check if there is a result. – user3783243 Aug 28 '18 at 03:15
@Raptor whats is perform extra checking in PHP to check for different upper- and lowercase. ?? thats whats I want – Don Ji Aug 28 '18 at 03:17
@user3783243 Agree, but for compatibility and migration-ability, I suggest to use case-insensitive collations. See [this](https://stackoverflow.com/questions/11578591/what-are-the-advantages-and-disadvantages-of-explicitly-specifying-collation-in) for discussion in details. – Raptor Aug 28 '18 at 03:18
The PHP comparison checking `==` already can check for upper and lowercase. i.e. `'Jack' == 'jack'` will return `false`. – Raptor Aug 28 '18 at 03:22

Bobby Axe · Accepted Answer · 2018-08-28T03:42:42.180

2

 if( strtolower($row['username'])==strtolower($val['username']) ) { echo "Exists";}

You can also replace strtolower with strtoupper that will convert a string to upper case.

NOTE: That for this case strtolower and strtoupper will result in the same outcome as noted by @user3783243

Also if your trying to compare both values with Case Sensitivity that is you don't want if('JAck'== 'jAck') to return true then use strict comparison ===

 if('JAck'== 'jAck') //will return true
 if('JAck'=== 'jAck') //will retrun false

edited Aug 28 '18 at 03:42

answered Aug 28 '18 at 03:21

Bobby Axe

1,491
13
29

1

OP asks for reverse. He wants to check for upper and lowercase. – Raptor Aug 28 '18 at 03:22
i understand the misconception but do consider this line in the question **If I have username = jack and another register's with username = JaCk It accepts** – Bobby Axe Aug 28 '18 at 03:28
1

Okay, guess that was inverted, `strtoupper` should be removed though, it will have the same affect as `tolower`. – user3783243 Aug 28 '18 at 03:29
@user3783243 yes that's true just letting the user know he can use that as well. – Bobby Axe Aug 28 '18 at 03:32
@BobbyAxe This answer your other question https://stackoverflow.com/questions/24873771/how-to-prevent-xss-attacks-in-php – Havenard Aug 30 '18 at 03:15
@Havenard thanks i wanted to improve the original question but on second thought i deleted it thanks for the info i have been doing a lot of reading on it if you think its still useful you can vote for it to be opened though – Bobby Axe Aug 30 '18 at 03:18

score 0 · Answer 3 · answered Aug 28 '18 at 03:33

You can using UPPER_CASE in SQL query:

$stmtr = $DB_con->prepare("SELECT username FROM users WHERE UPPER(username)=:username");
$stmtr->bindparam(":username", strtoupper($val['username']));

But I think you should store username as uppercase or lowercase when user register. It is better than using UPPER in SQL

Check value if exist with uppercase and lowercase letters

3 Answers3