[SOLVED] Login Case Sensitive issue

[realjumper]

I know this has been covered before, in fact I have come up against this before but I don't remember the answer, and my search hasn't come with one either.

 

My problem is that with this simple Login script, username seems to be case sensitive.....and consequently would give lots of problems for users. So, I want the search of the DB to case insensitive.

 

I am currently using LDAP authentication, but I need to return to Mysql authentication for a short while.

 

Here's the code:

 

$username = $_POST['username'];
$passwd = $_POST['passwd'];
  

$result = mysql_query("SELECT sid,pword FROM email WHERE sid = '$username' && pword = '$passwd'") 
or die(mysql_error()); 
        
$row= mysql_fetch_array( $result );

if ($username == $row[sid] && $passwd == $row[pword])
{
echo "authenticated";
}

else{
      echo "Go Away";
      }

 

The username in the DB is stored as 'JSmith'. If the user types in the username as 'JSmith', authentication is granted....but...if the user types in JSMITH, or jsmith...authentication fails.

 

As there are about 800 users currently in the DB, and there is all sorts of case combinations for their usernames, this presents a problem, as you can imaging!!

 

What's the best thing to do? Any ideas?

 

Thanks :-)

[teng84]

dude heres what you can do

 

you can query for the password and once you have the username of that pword you

have to convert it to lower case and compare it to user input that is also converted in lower case

 

for sure password is case sensitive

[realjumper]

Thanks teng84...that's the conclusion I reached too, but I was hoping for something a bit more secure. I have just found this article....

 

"This problem is rare and happens due to MySQL collations settings.

 

Try to find out if your database/tables are set to utf8_bin or other collation (via phpMyAdmin or ask hosting provider). Any collation that ends in "_bin" or "_cs" is case sensitive. If you would like to use utf8 without it being case sensitive, try modifying your databases to use utf8_general_ci instead. All of the _ci collations are case insensitive."

 

If you login to your cPanel account and go to MySQL -> PHPMyAdmin, you can browse your databases and tables there, as well as change the collation for each database and table."

 

....so I'll check that out too :-)

[teng84]

so you mean it is ok to have case insensitive password?

[realjumper]

No, it isn't!!!

[ss32]

why arent you encrypting the passwords?

[teng84]

No, it isn't!!!

thats whats gonna happen using your idea

[realjumper]

Yes, that's right teng84. I'm going to go about it a different way....I'll let you know the result :-)

[realjumper]

why arent you encrypting the passwords?

 

I will be using md5, but that isn't the issue here :-)

[redarrow]

see if this works dont no my self ok.

<?php

$username = $_POST['username'];
$passwd = $_POST['passwd'];
  
if( strtoupper($username) || strtolower($username) ){

$result = mysql_query("SELECT sid,pword FROM email WHERE sid = '$username' && pword = '$passwd'") 
or die(mysql_error()); 
        
$row= mysql_fetch_array( $result );

if ($username == $row[sid] && $passwd == $row[pword])
{
echo "authenticated";
}

else{
      echo "Go Away";
      }
}
?>

[akitchin]

you can simply LOWERCASE() both usernames when you compare them.  might as well use MySQL to its full advantage.

 

$result = mysql_query("SELECT sid,pword FROM email WHERE LOWERCASE(sid) = LOWERCASE('$username') && pword = '$passwd'") 

 

PS:  redarrow's solution will not work.

[redarrow]

so it would work if you said $username=strtolower($username);

 

so in essance this would work

<?php

$username = strtolower($_POST['username']);
$passwd = $_POST['passwd'];
  
$result = mysql_query("SELECT sid,pword FROM email WHERE sid = '$username' && pword = '$passwd'") 
or die(mysql_error()); 
        
$row= mysql_fetch_array( $result );

if ($username == $row[sid] && $passwd == $row[pword])
{
echo "authenticated";
}

else{
      echo "Go Away";
      }

?>

[akitchin]

.. AND if you made the username in the database lower case when comparing them, otherwise the cases may still not match.

[deadimp]

Huh, by default any string comparisons I do on MySQL by default are case-insensitive. Have you checked up on that collation thing yet?

[redarrow]

you can solve this by selecting the database names

then strtolower all names then update the names colum i think.

 

wouldnt that be easer.

[realjumper]

you can simply LOWERCASE() both usernames when you compare them.  might as well use MySQL to its full advantage.

 

$result = mysql_query("SELECT sid,pword FROM email WHERE LOWERCASE(sid) = LOWERCASE('$username') && pword = '$passwd'") 

 

PS:  redarrow's solution will not work.

 

 

FUNCTION mydatabase.LOWERCASE does not exist - is the result!!

[maxudaskin]

A good thing to do is this:

 

Use this in the registration page and login page.

<?php
$user = $_POST['username'];
$user = strtolower($user)
?>

[realjumper]

A good thing to do is this:

 

Use this in the registration page and login page.

<?php
$user = $_POST['username'];
$user = strtolower($user)
?>

 

 

That's true, but I have over 600 users already so that won't help the situation :-)

[realjumper]

you can simply LOWERCASE() both usernames when you compare them.  might as well use MySQL to its full advantage.

 

$result = mysql_query("SELECT sid,pword FROM email WHERE LOWERCASE(sid) = LOWERCASE('$username') && pword = '$passwd'") 

 

PS:  redarrow's solution will not work.

 

 

FUNCTION mydatabase.LOWERCASE does not exist - is the result!!

 

This works!! :-)

 

$result = mysql_query("SELECT sid,pword FROM email WHERE LOWER(sid) = LOWER('$username') && pword = '$passwd'") 

[redarrow]

chage all usernames in database to lowercase as shown below, and chage the

form registry input name to insert to database to

lowercase

 

 

<?php

//database connection.

$username = $_POST['username'];

$query1 = "SELECT username from email "; 

$result=mysql_query($query1)or die("mysql_error()"); 
       
while($row=mysql_fetch_assoc( $result )){

$row['username']=$username;

$x=strtolower($username);

$query2="update email set username='$x' where username='".$row['username']."'";

$result2=mysql_query($query2)or die("mysql_error()");

}
?>

[realjumper]

Okay....here's what I have done. This works fine.....

 

$username = $_POST['username'];

$passwd = md5($_POST['passwd']);

// Query the db
$result = mysql_query("SELECT sid,pword FROM email WHERE LOWER(sid) = LOWER('$username') && pword = '$passwd'") 
or die(mysql_error());

$row= mysql_fetch_array( $result ); 

$username = strtolower($row[username]);

//try to authenticate against mysql
  
  if($username == $row[username] && $passwd == $row[pword])
  {
  echo "Authenticated";
  }

   //or else try to authenticate against LDAP
     
   elseif($username != $row[username] || $passwd != $row[pword])  
        {
         // lots of LDAP code here
        }
        
        //or, authentication failed because the account doesn't exist
        
        else{
              echo "Go Away";
              }

[akitchin]

good to see it working.  sorry, i sometimes mix up the function names - the MySQL manual is your friend (good to see you fixed it on your own).

[realjumper]

Thanks for everyones help :-)