使用预处理语句无法提取数据库密码哈希的技术求助

Hey there, let's work through this—you've got the registration side sorted with password_hash(), which is great, so the login block is just a matter of fixing how you fetch and verify that stored hash. Here are the most common issues and how to fix them:

1. Your SQL Query Isn't Selecting the Hashed Password Field #

First, double-check your login query. If you're not explicitly selecting the password_hash (or whatever you named your password field) column, you won't have access to it. For example, if you're doing something like:

SELECT username FROM users WHERE username = ?

That's missing the password hash field! You need to include it:

SELECT username, password_hash FROM users WHERE username = ?

2. Your Database Password Field Is Too Short #

PASSWORD_DEFAULT generates a hash that's roughly 60 characters long (this can vary slightly with algorithm updates). If your database column is set to VARCHAR(50) or shorter, the hash gets truncated when stored. This means the value you retrieve is incomplete, and password_verify() will fail every time.

Fix this by altering your table to set the password field to VARCHAR(255) (it's safe to use a longer length than needed here):

ALTER TABLE users MODIFY COLUMN password_hash VARCHAR(255) NOT NULL;

3. You're Not Properly Fetching the Query Result #

Even if your query is correct, you need to actually retrieve the hash from the result set. Let's walk through examples for both PDO and MySQLi, since you're using prepared statements.

Example with PDO: #

// Assume you have a PDO connection setup already
$username = $_POST['username'];
$password = $_POST['password'];

// Prepare and execute the query
$stmt = $pdo->prepare("SELECT password_hash FROM users WHERE username = ?");
$stmt->execute([$username]);

// Fetch the result (we only expect one row for a valid username)
$user = $stmt->fetch(PDO::FETCH_ASSOC);

// Verify the password
if ($user && password_verify($password, $user['password_hash'])) {
    // Login successful!
    echo "Welcome back, " . htmlspecialchars($username);
} else {
    // Invalid username or password
    echo "Invalid credentials";
}

Example with MySQLi: #

// Assume you have a MySQLi connection setup already
$username = $_POST['username'];
$password = $_POST['password'];

// Prepare and bind
$stmt = $mysqli->prepare("SELECT password_hash FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$stmt->execute();

// Get the result and fetch the hash
$result = $stmt->get_result();
$user = $result->fetch_assoc();

// Verify
if ($user && password_verify($password, $user['password_hash'])) {
    // Success
    echo "Welcome back, " . htmlspecialchars($username);
} else {
    // Failure
    echo "Invalid credentials";
}

Critical Reminder #

Never try to hash the input password and compare it directly to the stored hash! password_hash() uses a random salt each time, so even the same password will produce different hashes. Always use password_verify()—it handles the salt comparison automatically.

If you're still stuck, double-check for typos in your column names, make sure your connection is working, and verify that the stored hash in the database is a full 60+ characters (not truncated).

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