SQL类Login()函数实现及Prepare statement行数技术咨询

Hey there! Let's fix up your Login() method to use prepared statements (this is non-negotiable to block SQL injection attacks) and clarify how to correctly get row counts with mysqli's prepared statements.

First, a quick note on your current code: directly plugging user input into your SQL query is a huge security risk—attackers could manipulate the username or password fields to run malicious SQL. Prepared statements eliminate this by separating SQL logic from user data entirely.

Rewritten Login Function with Prepared Statements #

Here's the adjusted code, including the proper way to fetch row counts:

public function Login() {
    // Check if required form data exists
    if (!isset($_POST['username']) || !isset($_POST['password'])) {
        return false;
    }

    $dbConnection = $this->DbConnect();
    // Validate database connection first
    if (!$dbConnection) {
        error_log("Database connection failed: " . mysqli_connect_error());
        return false;
    }

    $username = strtolower(trim($_POST['username']));
    $password = $_POST['password'];
    // Important: SHA1 is no longer secure for password storage! See the note below for a better alternative
    $passwordHash = sha1($password);

    // 1. Prepare SQL with placeholders (?) instead of raw variables
    $sql = "SELECT * FROM users WHERE email = ? AND password = ? LIMIT 1";
    $stmt = $dbConnection->prepare($sql);

    if (!$stmt) {
        error_log("Prepare statement failed: " . $dbConnection->error);
        return false;
    }

    // 2. Bind user input to placeholders: "ss" means two string parameters
    $stmt->bind_param("ss", $username, $passwordHash);

    // 3. Execute the prepared statement
    $stmt->execute();

    // 4. Store the result set BEFORE checking row count
    // This step is critical—mysqli doesn't auto-store results for prepared statements
    $stmt->store_result();

    // 5. Get the number of matching rows
    $matchCount = $stmt->num_rows;

    $loginSuccess = false;
    if ($matchCount === 1) {
        // Fetch user data if login is successful
        $stmt->bind_result($userId, $userEmail, $userPassword, /* add other columns from your users table */);
        $stmt->fetch();
        
        // Set session variables or return user data here
        session_start();
        $_SESSION['user_id'] = $userId;
        $_SESSION['user_email'] = $userEmail;

        $loginSuccess = true;
    }

    // Clean up resources
    $stmt->close();
    $dbConnection->close();

    return $loginSuccess;
}

Key Details About Row Counts with Prepared Statements #

• Always call store_result() first: Unlike regular mysqli_query(), prepared statements don't load the result set into memory automatically. Skip this step, and num_rows will return 0 even if there are matching rows.
• Use $stmt->num_rows instead of mysqli_num_rows(): For prepared statements, the row count is a property of the statement object itself, not a separate function call.

Critical Security Upgrade: Replace SHA1 with password_hash() #

SHA1 is outdated and vulnerable to brute-force attacks. PHP has built-in, secure password handling functions:

• When registering a user, hash their password with password_hash($password, PASSWORD_DEFAULT)
• When logging in, fetch the stored hash from the database and verify it with password_verify($password, $storedHash)

Here's how that adjusts your login logic:

// Updated SQL to only fetch the stored password hash
$sql = "SELECT id, email, password FROM users WHERE email = ? LIMIT 1";
$stmt = $dbConnection->prepare($sql);
$stmt->bind_param("s", $username);
$stmt->execute();
$stmt->store_result();

if ($stmt->num_rows === 1) {
    $stmt->bind_result($userId, $userEmail, $storedHash);
    $stmt->fetch();
    // Verify password instead of comparing hashes directly
    if (password_verify($password, $storedHash)) {
        // Login successful
        session_start();
        $_SESSION['user_id'] = $userId;
        $_SESSION['user_email'] = $userEmail;
        return true;
    }
}

内容的提问来源于stack exchange，提问作者Daan Van Dalen