Case-Sensitive char comparing in MySQL

WhisperiN · 03-04-2010, 04:26 AM

Hello Geeks

How you doing?

I need to do a check to make sure an entered string matches the one stored into the db.
For example, a password field.

I know something to achieve that by:

Code:

SELECT something FROM sometbl WHERE string1 LIKE BINARY string2;

Alright, is that the best way, or is there better ways?
The table will be a huge table, that suppose to hold more than 5 millions record, and reading of that table will depend on sensitive string comparison, which means I need a very smart code that perform good.

Hope I explained well..
Thanks

smoker · 03-04-2010, 07:21 AM

If you are designing the database then make sure you know what case the strings will be in by converting every string to upper or lower case before it gets stored.

Convert the input to lower case as you first insert it to the record.

Code:

INSERT INTO sometbl (field1,field2) VALUES(LOWER('string1'),LOWER('string2'));

Once you have records in the table you can then act accordingly.

If your input is string2, and the records are all in lower case then you can select like :

Code:

SELECT something FROM sometbl WHERE field2=LOWER('string2');

If your input is string2, and the records are all in upper case then you can select like :

Code:

SELECT something FROM sometbl WHERE field2=UPPER('string2');

If the database has already got mixed case entries, then you can still follow the same approach but if you are relying on the case to distinguish records you may get errors. Do you reject an entry because it has a different case ? Only you can tell.

graemef · 03-04-2010, 07:35 AM

You will want to index the fields that you are searching on. That way MySQl will be able to optimise your query.

druuna · 03-04-2010, 07:38 AM

Hi,

@smoker: I totally disagree with you on your approach!

The database should hold the data as is (not made fully upper/lower case). If needed(!!) you can let the frontend do the conversion to lower/upper and check this against the entered data.

Simple example:
myPassword, MYPassword and MYPASSWORD are three unique passwords, converting them to upper (or lower) case would increase the possibility of entering the wrong password and still get access.

If speed is a concern, look into indexing (as already mentioned by graemef....).

smoker · 03-04-2010, 08:12 AM

Quote:

Originally Posted by druuna

Hi,

@smoker: I totally disagree with you on your approach!

The database should hold the data as is (not made fully upper/lower case). If needed(!!) you can let the frontend do the conversion to lower/upper and check this against the entered data.

Simple example:
myPassword, MYPassword and MYPASSWORD are three unique passwords, converting them to upper (or lower) case would increase the possibility of entering the wrong password and still get access.

If speed is a concern, look into indexing (as already mentioned by graemef....).

In that case all he has to do is use WHERE field1=string2

Did you read my post ?

Quote:

If the database has already got mixed case entries, then you can still follow the same approach but if you are relying on the case to distinguish records you may get errors. Do you reject an entry because it has a different case ? Only you can tell.

He asked about "Case-Sensitive char comparing"
He didn't specify a field in his select.

Dan04 · 03-04-2010, 08:14 AM

Case-sensitive string comparison is the default, so all you have to do is:

Code:

SELECT SomeColumns FROM SomeTable WHERE ColumnYoureSearching = 'ValueYoureSearchingFor';

druuna · 03-04-2010, 08:20 AM

Hi,

@smoker: Yes I did read your post

The OP did mention a field, the password field to be specific......

But besides that, storing data that is converted to upper/lowercase into a DB is, in general, bad practice. It will get one into trouble in the long run (talking from experience I had with several companies).

Anyway, it's up to the OP to implement what s/he finds best.

smoker · 03-04-2010, 08:34 AM

@druuna
My original point was related to not knowing what the input case was.
If someone searches the db for Hello but the db contains hello, then it won't get a match by default.
So you either have to search for all versions, which takes time, or you ensure that there is only one version possible.
I was just pointing out a way to do that using mysql.

In the case of passwords, I agree with you, but why was he trying to use BINARY ?
He actually said "for example a password field" but he didn't say that was the only case scenario.

druuna · 03-04-2010, 08:51 AM

Quote:

Originally Posted by smoker

@druuna
My original point was related to not knowing what the input case was.
If someone searches the db for Hello but the db contains hello, then it won't get a match by default.
So you either have to search for all versions, which takes time, or you ensure that there is only one version possible.
I was just pointing out a way to do that using mysql.

Yes, I understand you gave a possible solution, which is bad practice as I pointed out.

In your scenario (Hello in the DB and hello as searchstring) you use something like:

SELECT UPPER(SomeColumns) FROM SomeTable WHERE ColumnYoureSearching like UPPER("ValueYoureSearchingFor");

No need to check all possible variations.

graemef · 03-04-2010, 05:15 PM

If your input is mixed case and you want to find all versions then use LIKE which is case insensitive:

Code:

SELECT somefiledlsit FROM mytable WHERE field1 LIKE filed2

WhisperiN · 03-04-2010, 08:32 PM

Oh, great to see my topic went into a good discussion, which bring back great results.

Well, in fact..
I do really need to store data in both upper, and lower case..
That will give me a much wider range to store more unique strings with less letters..
And, that's the whole point.

The stored data into that filed, will be random generated string, upper,lower and numbers combined together..
Examples are:

Eida3Do
jjO298GJj
aNd-s0-0n

Thus, the idea of changing the case, either while comparing, or while storing will not help..!!

Any way, I'll try @graemef idea, to see.
and so, I need to make sure that I'll do it right..

So, is all I need in my case it to create an index on that mentioned field?

Cheers

WhisperiN · 03-04-2010, 09:08 PM

Quote:

Originally Posted by druuna

Hi,
If speed is a concern, look into indexing (as already mentioned by graemef....).

Yeah, really it's..
Since, this website might be so much loaded..
and I need my code to be sharp and smooth.

Thanks

Tinkster · 03-04-2010, 10:45 PM

At the end of the day it depends on how you defined the table/column in question.

By default yourSQL (I refuse to call it "my" :D) will choose case insensitive
storage.