[SOLVED] CaseSensitive MySQL

[otuatail]

Having a problem with Username and Password. If I have an entry with

User = "ABCDE" and pwd = "VWXYZ"

 

and I enter "abcde" and "vwxyz" on an entry form. The following query will always work.

 

$sql = "SELECT UserID FROM Users WHERE user = '" . $User . "' AND pwd = '" . $PWD . "'";

 

This still works. I want it to fail over case sensitivity.

 

Desmond.

[GingerRobot]

Any particular reason? Its usually a bad idea to have case sensitive usernames. As for passwords, yes, they should be case sensitive, but you should also hash them first which would resolve that issue.

[otuatail]

Ok I wasn't going to make User case sensitive but what is this hash them first?

[GingerRobot]

Take a look at the md5() function. The idea is that whever you go to do something with a password, you apply the md5() function to it, to hash the string. So, when someone registers, the password you store is the hashed version. When someone logs in, you hash the password they provide, and check this against the password stored in the database.

[cooldude832]

also make sure you escape the user name so someone can't inject your mysql

[AndyB]

... I want it to fail over case sensitivity.

 

Desmond.

 

Then use a BINARY compare ... WHERE binary fieldname = 'wHaTeVeR'

[otuatail]

Ok I can get my head around MD5 but don't understand (escape the user)

[GingerRobot]

Basically, you need to validate all user input to make sure that a malicious user can't do damage to your database, or get information that they shouldn't have access to.

 

The mysql_real_escape_string() function is a good place to start. If you're interested in how a user might do damage, google SQL injection.

[otuatail]

Ok the pest way to store the Password is MD5 and then do an MD5 check. The example here is

if (md5($str) === '1f3870be274f6c49b3e31a0c6728957f')

 

This is a 32 bit string will it always be 32 bit as some kind of CRC value. If so I can modify the database to

pwd` varchar(32) default NULL,

 

desmond.