"If" never true when field is nonexistent?

[Eirik]

Hi,

I'm setting up a member ship area consisting of the Email address as the username, and a password. Because there won't be any sensitive information in this membership area until the user starts using it, I have set the default password to 0, or nonexistent, meaning that I did not use the "NOT NULL" property when creating the field in MySQL and I have not inserted any information into it.

Now I've created a log in screen consisting of a field for the Email address and the password. All test function as they should, except for the one validating the password.

When I log in I leave the password field blank because there exists no password in the database. However, for some strange reason, the test I'm performing does not seem to work. Here's the code. It should be pretty self-explenary.

# Check to see whether the password was correct or not.
$sql = "SELECT Email, Password FROM Member WHERE Email LIKE '$Email' AND Password LIKE '$Password'";
$result = mysql_query($sql);
    
$row = mysql_fetch_array($result);

# If the password was correct
# (I check against the Email field just in case there
# would be some error testing against an empty password)
if ($row["Email"]) {
echo("You are successfully logged in.");
}

# If the password was not correct
else {
?>
<H2>Password incorrect</H2>
} 

Any idea why this doesn't work?

Thanks in advance !

[freakysid]

An empty string evaluates to false in PHP.

isset($row["Email"]) may produce the desired result.

[Eirik]

Hi again,

I might be missing something here, but shouldn't the Email variable contain a value no matter what? Wouldn't I rather have to use the isset function if I was testing against the Password variable?

Thanks for explaining things to a stupid newbie.

[freakysid]

I'm not that experienced in PHP either - and have been said to often exhibit a stupified demeanour.

However, OK - I think I muddled your application logic. This might be the problem. If you have not explicitely set a value for Password - and it therefore has a NULL value in the database - that's your problem there. Yes chief, its the old three value logic trap!

A NULL value in MySQL (and RDMS in general) does not evaluate to TRUE or FALSE (but can be thought of as being of UNKNOWN value). So if the relevent row in your table (WHERE Email LIKE $'email' evaluates to true) has a NULL value for the Password field then (AND Password LIKE '$PASSWORD') will be FALSE because an empty string is not equal to NULL as far as MySQL is concerned.

This is a little confusing because PHP uses two value logic where NULL evaluates to FALSE.

Anyway, the bottom line here is that if the value of Password is NULL then your result set will return no matching results.

So maybe this SQL query will work:

$sql*=*"SELECT*Email,*Password*
        FROM*Member*
        WHERE*Email*LIKE*'$Email'*
        AND*( Password*LIKE*'$Password'
                     OR Password IS NULL)"; 

[Eirik]

Hi again,

I tried your suggestion as it seemed very reasonable. However, now the if test evaluates to true all the time instead of false (regardless of whether I insert a password or nor). Can't understand why, though.

Here's the code as it appears in the script now:

# CHECK WHETHER THE USER EXISTS IN THE DATABASE
$sql = "SELECT Email FROM Member WHERE Email LIKE '$Email'";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);

# IF MEMBER EXISTS
if ($row["Email"]) {

$sql = "SELECT Email, Password FROM Member WHERE Email LIKE '$Email' AND ( Password LIKE '$Password' OR Password IS NULL)";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);

if ($row["Email"]){
# VALID MEMBER
}
else {
# VALID MEMBER, BUT UNVALID PASSWORD
} 

I was thinking that the reason could be that the $row["Email"] variable existed already, but when I tried to test against isset($row["Password"]), instead, it turned out never true instead.

I understand if you're running out of ideas, but you wouldn't happen to have one left?

[freakysid]

How about this logic?

#*CHECK*WHETHER*THE*USER*EXISTS*IN*THE*DATABASE
$sql*=*"SELECT*Email,*Password*FROM*Member*
        WHERE*Email*LIKE*'$Email'*
        AND*(*Password*LIKE*'$Password'*OR*Password*IS*NULL)";
        
   $result*=*mysql_query($sql);
   $row*=*mysql_fetch_array($result);

#*CHECK THAT*MEMBER*EXISTS
if*($row["Email"])*{
   # MEMBER EXISTS
   # NOW CHECK PASSWORD
   if*($row["Password"]){
      #*MEMBER PASSWORD VALIDATED
   }
   elseif (isset($row["Password"]))*{
      #*VALID*MEMBER,*BUT*MEMBER PASSWORD IS NULL
   } 
   else {
      #*VALID*MEMBER,*BUT*UNVALID*PASSWORD
   }
}
else {
   # MEMBER DOES NOT EXIST
} 

Note that with PHP 4.0.4 you now have the function is_null() which could be used instead of isset($row["Password"]) in the code above.

[Anarchos]

An easier and more elegant way to do this is to say

if (mysql_num_rows($result) > 0) { } 

Also, you should change LIKE to =.

[Eirik]

Hi guys,

Thanks so much for trying to help me out. I first tried out freakysid's suggestion, but that returned the "# VALID MEMBER, BUT UNVALID PASSWORD" regardless of whether I inserted a password or not.

I then tried replacing the isset function with the function suggested by Anarchos, but then I was presented with the "
# VALID MEMBER, BUT MEMBER PASSWORD IS NULL" regardless of whether I inserted a password or not.

I don't consider myself a quiter, but I'm starting to wonder if it would be better to have the Email address as the password as well by default instead of having an empty field as default...

[Anarchos]

You should add my code in between these two lines:

$result = mysql_query($sql);
$row = mysql_fetch_array($result);