SQL QUERY: case sensitive results

[everythingrock]

Hey,

I’m working on a login script. The user information is stored in a database, so once they type in their information, it uses this query to verify their info:

mysql_query(“SELECT id FROM er_users WHERE username=’{$username}’ AND password=’{$password}’”);

However, the way it works now, say their username is “nick”, they could type in either Nick or nick and it would allow them to login, I don’t want that. I want it to be where if they registered as “nick” they would have to type that, exactly, enable to login. So if they typed “Nick” it wouldn’t allow them to login.

Is there anyway to make the above query only return a result if $username and $password both perfectly match (case sensitive) with the database entry?

Thanks,

Nick

[Bane]

First thing never save a password in clear! Use something like MD5 when you save in the first time and use MD5 on the value to compare.

As for case insensitivity, in MySQL, using LIKE will compare insensitive on VARCHAR and TEXT fields if I remember well. Otherwise, the common solution is to either uppercase or lowercase both values in the comparision.

Note that you will have to uppercase or lowercase the value before hashing it with MD5 as it will always be insensitive, but that’s usually better for a password anyway.

[everythingrock]

I’m sorry I don’t really understand what you’re telling me to do?

Could you go into some more detail as to what I am to do to fix my problem?

Thanks a ton,

Nick

[milesburton]

Well technically you shouldn’t do this (not that it stops me ) but you could try this

<!-- insert MySQL statement -->
$count = mysql_num_rows($sql);
IF($count){
 // Authorized
}else{
 // Unauthorized
}

However, you should consider Bane’s advice. It is a major security risk using unencrypted passwords in a database.

MD5 is a “one way” encryption tool, this means that you can encode the input variable and check it against the already encoded password variable from the database.

But the down side of this is you cannot find out what the password was, as you can only encrypt it… You are unable to dycrypt it (without brute force)[/code]

[lig]

In order to make something like a user name case sensitive you need to make the databse field BINARY. MySQL is not normally case sensitive.

Reference:
http://dev.mysql.com/doc/mysql/en/Case_ … ators.html

[everythingrock]

Thank you guys!!

The BINARY thing works perfectly.

Do you think I should use MD5? If I should be using it, could one of you tell me how to do it?

Thanks again,

-Nick

[lig]

MD5 is an encryption method that helps to secure your data. You can also use crypt, the mcrypt extension, or use the MySQL function PASSWORD. Whether or not to use it is really up to you. If you are just looking for a way to keep the avarage person out of a site then you really don’t need it (keep the honest man honest), but if you need to provide ANY kind of security to something then you need to encrypt the passwords. What ever you decide to do to the passwords remember whatever you do to the passwords to put them in… to compare the passwords you must do everything again backwards.

References:
http://dev.mysql.com/doc/mysql/en/Password_hashing.html
http://www.php.net/manual/en/ref.mcrypt.php
http://www.php.net/manual/en/function.crypt.php
http://www.php.net/manual/en/function.md5.php
you may also want to look here… there are a couple of additional things for encryption
http://www.php.net/manual/en/ref.strings.php

[everythingrock]

Thanks, I’ll look into it.

[peg110]

Do you think I should use MD5? If I should be using it, could one of you tell me how to do it?

I think that you absolutely should use MD5 (or some other encryption method). Even though, as lig points out, you might be keeping the “Honest Person Honest”, many people will use the same password for multiple sites. (I personally rotate about a half dozen ones for my different logins). The point here is that if it’s NOT encrypted then YOU, or anyone who has access (legit or otherwise) to your database can obtain that info and use it fraudulently.

How do you use it? it’s easy in PHP.

[php]

// Encrypt password
$encrypted_password = md5($UNencrypted_password);

// Add some code to store the encrypted password in database.

//Validate password

// First get the (Now) encrypted password from database
// and store it in a variable and compare it against submitted password
If ($encrypted_pwd_from_db == md5($submitted_UNencrypted_Password) ) {
//User is Authorized
} else {
//User is UNauthorized
}
[/php]