Matching exact case

Duke_Henry

Darn, I borrowed my PHP Bible out to a friend, and need to figure out this minor bug.

I am attempting to write some code to run a username verification procedure.

Now, the bug is this:

let's say you have

user: JON
password: RULZ (stored in a mysql database as such).

(I know the password should be encrypted, but part of the program is being able to display passwords for users, so no MD5 or password just yet).

Anyway, if I login with password rulz, it allows me to login.

Normally, this is o.k., but I don't want to confuse the users, and we have beaten into their brains to always type their password as they know it. (Many have uppercase passwords).

So, how to I ensure that the case is an exact match?

The way I am checking the user input is through this code:

// check if username is unique
$result = mysql_query("select * from users
where username='$username'
and password = ('$password')");
if (!$result)
return 0;

if (mysql_num_rows($result)>0)
return 1;
else
return 0;

Thanks

hi-liter

BINARY The BINARY operator casts the string following it to a binary string. This is an easy way to force a column comparison to be case sensitive even if the column isn't defined as BINARY or BLOB:
mysql> select "a" = "A";
-> 1
mysql> select BINARY "a" = "A";
-> 0
BINARY was introduced in MySQL Version 3.23.0.
Note that in some context MySQL will not be able to use the index efficiently
when you cast an indexed column to BINARY.
If you want to compare a blob case-insensitively you can always convert the blob to upper
case before doing the comparison:
SELECT 'A' LIKE UPPER(blob_col) FROM table_name;
We plan to soon introduce casting between different character sets to make string comparison
even more flexible.

page 403, mysql manual.

hth.

it also looks like you could change the data type on the column to be BINARY, and that would do too.

"If you want a column always to be treated in case-sensitive fashion, declare it as BINARY. "

http://www.mysql.com/doc/en/Case_sensitivity.html

Duke_Henry

Thanks, I found the same answer last night!

Declared password column as binary - and it worked beautifully!

Mucho Thanko