[PHP-DB] How to make it case sensitive?
I have a web site backup with PHP MySQL. I write a script, so a member can log in into the member area. I build a database for the username, password, and some other information for the member. I set the username (which type is varchar) as the primary key. For the authentication scheme I use : ?php .. .. $link = get_database(); //custom function to connect to MySQL server and select the database $query = SELECT * FROM user WHERE username = '$login' AND password = PASSWORD('$password'); if (!($result=mysql_query($query, $link))) { die(sprintf(Error: %d %s, mysql_errno($link), mysql_error($link))); } if(!mysql_num_rows($result)) { show_login_form(Invalid Login);//custom function to print out a login form with a message as the function argument exit; } else { $guest = mysql_fetch_object($result); } mysql_free_result($result); ? The script is just fine until I notice that the username is case insensitive. So, if a member has a username andy he can log in using andy, Andy, aNdY, andY, and so on. I also notice that I can't enter a new member with username Andy, anDY, and so on as they will duplicate the existing andy. Is this the natural behaviour of MySQL or is there anyway to make it case sensitive? I'm using PHP 4.0.6 MySQL 3.23.39. That's all folks! Thanks before. Denny Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] How to make it case sensitive?
On Fri, Sep 21, 2001 at 05:02:14PM +0100, Denny Suvanto wrote: The script is just fine until I notice that the username is case insensitive. So, if a member has a username andy he can log in using andy, Andy, aNdY, andY, and so on. I also notice that I can't enter a new member with username Andy, anDY, and so on as they will duplicate the existing andy. Is this the natural behaviour of MySQL or is there anyway to make it case sensitive? I'm using PHP 4.0.6 MySQL 3.23.39. You have to make the column case sensitive, binary will accomplish this. Something like: username varchar(20) binary -- Jason Stechschulte [EMAIL PROTECTED] -- I think it's a new feature. Don't tell anyone it was an accident. :-) -- Larry Wall on s/foo/bar/eieio in [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] How to make it case sensitive?
On Fri, 21 Sep 2001 14:28:52 -0400, [EMAIL PROTECTED] (Jason Stechschulte) wrote: On Fri, Sep 21, 2001 at 05:02:14PM +0100, Denny Suvanto wrote: The script is just fine until I notice that the username is case insensitive. So, if a member has a username andy he can log in using andy, Andy, aNdY, andY, and so on. I also notice that I can't enter a new member with username Andy, anDY, and so on as they will duplicate the existing andy. Is this the natural behaviour of MySQL or is there anyway to make it case sensitive? I'm using PHP 4.0.6 MySQL 3.23.39. You have to make the column case sensitive, binary will accomplish this. Something like: username varchar(20) binary Another way to tackle the problem is to store the data into field in a set case by running strtoupper or strtolower on the data before you store it or retrieve it. It will also likely require less CPU power than running a fuzzy regexp based search. - Dave -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]