使用PDO登录脚本

Question

I'm implementing a login script which isn't working correctly. I want users to be able to login with either their usernames or emails. I have two tables:

user - contains login information(username, password, email, isactive)

userprofile - contains profile information

ISSUES/ERRORS:

1. Logging in with the email addresses doesn't work.
2. If the username alone is entered, leaving the password field empty, the user is still logged in regardless.

THE CODE(isactive checks if a user's account has been activated after email verification)

    $uname = htmlspecialchars($_POST['username']);
    $pword = htmlspecialchars($_POST['password']);
    $isActive = 1;
    $getId = 0;
        try{
    $stmt = $db->prepare("SELECT * FROM user WHERE username = :username OR email = :email AND password = :password AND isactive = :isactive");
    $stmt->execute(array(':username' => $uname, ':email' => $uname, ':password' => $pword, ':isactive' => $isActive));
    $numrows = $stmt->fetch(PDO::FETCH_ASSOC);
    //to enable me count number of rows returned
    $number = $stmt->fetch(PDO::FETCH_NUM);


    $_SESSION['username'] = $numrows['username'];
    $getId = $numrows['Id'];//get the id of the user

    }catch(PDOException $ex){
    echo 'QUERY ERROR: ' . $ex->getMessage();
      }


    /*this checks to see that the user has a profile (userId is a foreign key, thus  user.Id = userprofile.userId always)*/
     try{
       $query = $db->prepare("SELECT * from userprofile WHERE userId = :userId");
       $query->execute(array(':userId' => $getId));
       $row = $query->fetchAll();

       }catch(PDOException $exc){
    echo 'QUERY ERROR: ' . $exc->getMessage();
     }

       //Check results and log user in
        if(count($number) == 1 && count($row) == 1){

        header("Location: index.php");
    }
        else {$errorMessage = "<p style='color:#ff851b'>Invalid username or password</p>";}

What do i need to modify to get this working? Thanks

Answer 1

You can easily include the row count in your query as well:

And adjust the query :

SELECT *, count(*) AS numrows 
FROM user 
WHERE (username = :username OR email = :email) AND 
       password = :password AND isactive = :isactive 

Please make the following changes:

$stmt->execute(array(':username' => $uname, 
                     ':email' => $uname, 
                     ':password' => $pword, 
                     ':isactive' => $isActive));

$row = $stmt->fetch(PDO::FETCH_ASSOC);

if(!$row){
   throw new Exception('User not found');
}

//get user data
$numrows = (int)$row['numrows'];
if($numrows === 1){
    //found a user
    $_SESSION['username'] = $row['username'];
    $id = $row['Id'];
}