View Full Version : Case sensative mysql query.

07-26-2011, 11:19 AM
$user = mysql_real_escape_string($_POST['username']);

$Select = "SELECT * FROM `users` WHERE `username` = '".$user."' AND `password` = '".$pass."'";

I currently have this set up (thats just a snippet) however it says login okay if i enter either User, USER, user etc for the username, anyway i can make it case sensative so say User would login but user would fail?

07-26-2011, 01:47 PM
This is all the information I could find.

case sensitivity comes from collation. so if the collation is set as cs (case sensitive) then your query condition will be CS as well.

You could convert everything to lowercase to prevent this from happening.

$str = "Mary Had A Little Lamb and She LOVED It So";
$str = strtolower($str);
echo $str; // Prints mary had a little lamb and she loved it so

07-26-2011, 02:01 PM
The OPs problem is case insensitivity which is what your script does as well. Best place to post this question would be the mysql forum.

07-26-2011, 02:45 PM
Yeah i just thought maybe this is more of a MySql problem, too used to posting in PHP :P. Ill put it there see what the guys there have to say :P

07-26-2011, 03:12 PM
I don't think I've ever needed to perform a case sensitive comparison before, but http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html indicates all you need is a binary string type.

SELECT * FROM `users`
WHERE `username` = '".$user."' COLLATE latin1_bin AND `password` = '".$pass."' COLLATE latin1_bin

Try that.

07-26-2011, 03:24 PM
That works, thanks a lot :)