React Native+Express+PostgreSQL用户密码验证查询问题咨询

Hey there! Let's work through this problem together—you're on the right track with the double condition idea, but there are a few critical details you need to address first.

First off: Yes, you absolutely need to use the exact same hashing method that was used to store the password in the database before comparing it to the stored hash. The database holds a hashed version of the password, so plaintext input will never match directly.

Also, your current code has a big security risk: directly interpolating username into the SQL query leaves you vulnerable to SQL injection attacks. We'll fix that with parameterized queries too.

Let's break this down with a practical example. I'll assume you used a common hashing library like bcrypt (super popular for password storage) when saving the passwords—if you used something else like argon2, the logic is similar, just swap out the library calls.

1. First, confirm your hashing library #

If you haven't already, install the same library used for password storage (e.g., bcrypt):

npm install bcrypt

2. Rewrite your getUserId method #

Instead of trying to compare passwords directly in SQL, we'll first fetch the stored hash for the username, then use the hashing library to verify the plaintext password against it. We'll also use parameterized queries to avoid injection:

const bcrypt = require('bcrypt');

// Switching to async/await for cleaner code (you can adapt to callbacks if needed)
static async getUserId(username, plaintextPassword, callback) {
  try {
    // Parameterized query: $1 is a placeholder for the username parameter
    const queryResult = await db.query(
      'SELECT userid, password FROM employee WHERE username = $1',
      [username]
    );

    // Check if the username exists
    if (queryResult.rows.length === 0) {
      // Username not found—return null or an error (your choice)
      return callback(null, null);
    }

    const user = queryResult.rows[0];
    // Use bcrypt's built-in compare method to check password match
    const isPasswordValid = await bcrypt.compare(plaintextPassword, user.password);

    if (isPasswordValid) {
      // Password matches—return the userID
      return callback(null, user.userid);
    } else {
      // Password doesn't match
      return callback(null, null);
    }
  } catch (error) {
    // Handle any database or library errors
    return callback(error, null);
  }
}

3. Why this works #

• Parameterized queries: Using $1 and passing the username as an array parameter prevents SQL injection, which is a critical security fix.
• Hashing consistency: bcrypt.compare handles all the heavy lifting—since bcrypt stores the salt directly in the hashed password string, you don't need to track separate salt values. It will automatically use the same salt and algorithm to hash the plaintext input and compare it to the stored hash.
• Clean error handling: Separating the username check and password comparison lets you handle cases where the username doesn't exist or the password is wrong (you can choose to return the same error message for both to prevent attackers from enumerating valid usernames).

• Never compare plaintext passwords in SQL: Even if you could generate the hash in PostgreSQL, doing so would expose you to injection risks and could lead to inconsistencies between your backend code and database functions.
• Know your hashing method: If you didn't use bcrypt, make sure you use the exact same algorithm (e.g., argon2, pbkdf2) that was used to store the password. Different hashing algorithms produce incompatible outputs, so matching the method is non-negotiable.
• Avoid callback hell: Async/await makes the code much easier to read and maintain than nested callbacks, but if your project relies on callbacks, you can rewrite the bcrypt.compare call using its callback-based syntax.

内容的提问来源于stack exchange，提问作者yesIamFaded