how to make MySQL case sensitive?
Author
hi,
im working on a persistant online 2d RPG. anyway, i know MySQL is not case sensitive. this means i create accounts in my game with the username "gfilla", but it works if i log in as "GFILLA" or "gFiLlA"... same goes for the password..
now, how can i make it case sensitive for certain things? i know this is something easy and obvious.. do i use the like statement or something? does MySQL even have a like statement? for some reason i can't find it in the docs..
thanks again.
You can do it by making one of the operands a binary string. So where you're probably going:
SELECT * FROM tUser WHERE UserName = 'gfilla';
To get case sensitivity you should change this to:
SELECT * FROM tUser WHERE UserName LIKE BINARY 'gfilla';
Edit for a little more detail:-
Normally, a string comparison in MySQL is case insensitive, meaning 'A' == 'a'. However, when you switch to a binary comparison mode, the comparison engine will look at the literal byte value for each character in the string and compare on that. This usually results in a faster comparison (as it would if you used strcmp over stricmp in C) because the comparison doesn't have to compare the original and case shifted character for each byte in the string. The BINARY keyword will force MySQL to use a binary comparison mode instead of the normal mode (effectively treating the operand as a range of bytes rather than a string).
[Edited by - evolutional on November 24, 2004 12:20:51 PM]
SELECT * FROM tUser WHERE UserName = 'gfilla';
To get case sensitivity you should change this to:
SELECT * FROM tUser WHERE UserName LIKE BINARY 'gfilla';
Edit for a little more detail:-
Normally, a string comparison in MySQL is case insensitive, meaning 'A' == 'a'. However, when you switch to a binary comparison mode, the comparison engine will look at the literal byte value for each character in the string and compare on that. This usually results in a faster comparison (as it would if you used strcmp over stricmp in C) because the comparison doesn't have to compare the original and case shifted character for each byte in the string. The BINARY keyword will force MySQL to use a binary comparison mode instead of the normal mode (effectively treating the operand as a range of bytes rather than a string).
[Edited by - evolutional on November 24, 2004 12:20:51 PM]
Author
hi,
thanks for the reply. this is just what im looking for.
now, on a slightly OT topic - do you think i should make things case sensitive? that is, do you think its wise for me to allow account names and passwords and characters to be spelled the same but with different case? do you think i'll run into problems when there are 2 characters logged in, one named "Bob" and another named "bob"?
im thinking it might cause me some problems, AND, it might be better off to just not allow duplicate names that have different case. that is for in-game characters and accounts. what do the commercial MMORPGs do? IIRC, i dont remember being able to make 2 characters with the same name with different capitalization.
right now im leaning towards making everything case in-sensitive, except passwords. what do you think?
thanks again for any help.
thanks for the reply. this is just what im looking for.
now, on a slightly OT topic - do you think i should make things case sensitive? that is, do you think its wise for me to allow account names and passwords and characters to be spelled the same but with different case? do you think i'll run into problems when there are 2 characters logged in, one named "Bob" and another named "bob"?
im thinking it might cause me some problems, AND, it might be better off to just not allow duplicate names that have different case. that is for in-game characters and accounts. what do the commercial MMORPGs do? IIRC, i dont remember being able to make 2 characters with the same name with different capitalization.
right now im leaning towards making everything case in-sensitive, except passwords. what do you think?
thanks again for any help.
I don't think that you would run technically into any problems (if you take care of the case-sensitive names) but I wouldn't allow persons having the same case-insensitive names. If they use the same name in the game you would allow some confusing... I would do it the way you suggest it: names in-sensitive and password case-sensitive.
Author
thanks Jockel, i think i'll do that then.
EDIT: actually, now im leaning towards making everything, including passwords, case insensitive. just to be consistant. plus, i don't really see a need for a password being sensitive. anyone have an opinion?
[Edited by - graveyard filla on November 24, 2004 11:19:57 PM]
EDIT: actually, now im leaning towards making everything, including passwords, case insensitive. just to be consistant. plus, i don't really see a need for a password being sensitive. anyone have an opinion?
[Edited by - graveyard filla on November 24, 2004 11:19:57 PM]
I'd argue that password should be case sensitive.
Imagine if my password, when entered, was "paSsWoRD". If someone wanted brute force crack that in case-sensitive mode, they'd have to run through each possible iteration of case to get there eg:
"Password", "pAssword", "paSsword" ... etc, would all fail the test and access wouldn't be allowed. However, if you remove the case sensitivity, the password becomes more vulnerable to dictionary attacks as "paSsWoRD" is now equal to "PASSWORD", removing that extra security that was added by case sensitivity.
The username could be insensitive, but I'd say make the password sensitive.
Imagine if my password, when entered, was "paSsWoRD". If someone wanted brute force crack that in case-sensitive mode, they'd have to run through each possible iteration of case to get there eg:
"Password", "pAssword", "paSsword" ... etc, would all fail the test and access wouldn't be allowed. However, if you remove the case sensitivity, the password becomes more vulnerable to dictionary attacks as "paSsWoRD" is now equal to "PASSWORD", removing that extra security that was added by case sensitivity.
The username could be insensitive, but I'd say make the password sensitive.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
