使用php从不同的表登录

Question

Good evening, I am just trying out something new (to me atleast) on how to login from different tables. Table1 has the cand_id (username) while Table2 has the pincode (password). The server doesn't display any error when I try to login but its not working. My code below:

if (isset($_POST['cand_id'])) {
    //escapes special characters in a string
    $cand_id = mysqli_real_escape_string($con,$cand_id);
    $pincode = mysqli_real_escape_string($con,$pincode);

    //Checking is user existing in the database or not
    $query = "SELECT cand_id 
              FROM candidates 
              WHERE cand_id='$cand_id' 
              UNION 
              SELECT pincode 
              FROM cand_login 
              WHERE pincode='$pincode'";
    $result = mysqli_query($con,$query) or die(mysql_error());
    $rows = mysqli_num_rows($result);

    if($rows==1) {
        $_SESSION['cand_id'] = $cand_id;
        // Redirect user to index.php
        header("Location: home.php");
    } else {
        echo "<div class='form'>
              <h3>cand_id/pincode is incorrect.</h3>
              <br/>Click here to <a href='log.php'>Login</a></div>";
    }
} else {
}

What do I need to make this work?

Answer 1

Lots of things wrong here.

• SQL injection. See comment above. Don't rely on the real_escape_string() functions to prevent SQL injection, they alone are not sufficient.
• You're doing a UNION which would normally return two rows but you're explicitly checking for one row. If you change the code to check for two rows then you have even bigger problems:
  • Since you're pulling any row that matches the userid and then any row that matches the PIN, this would allow any user to login with any PIN.
  • UNION removes duplicates. Which means if a user has the same PIN as their userid, the query will get reduced to a single row and the login would fail.
  • If you use then UNION ALL to not remove duplicates, then you'll potentially get more than one row when people have the same PIN. I.e., as soon as somebody creates a PIN that somebody else has, then nobody with that PIN will be able to login.
• You need to either JOIN the tables or use a WHERE clause on the cand_login table to ensure that the rows you're selecting pertain only to the one user that's logging in. Right now you're asking "Does this user exist, and does this PIN exist?" You need to ask "Does this user exist and does this user's PIN match what I was given?"