Tutorials


PHP MySQL Verifying user credentials


Verifying user credentials (such as email/username and password) using mysqli_query() in PHP MySQL procedural style involves checking if the entered credentials match the records stored in the database. You retrieve the user's hashed password from the database and then use the password_verify() function to compare the stored hash with the plain-text password entered by the user.

Steps for Verifying User Credentials

  1. Create the login form (HTML).
  2. Retrieve user input in PHP.
  3. Connect to the database.
  4. Run a query with mysqli_query() to check if the user exists.
  5. Fetch the stored user data (e.g., hashed password).
  6. Verify the password using password_verify().
  7. Handle login success or failure.

Example: Verifying User Credentials

Step 1: HTML Form

Here’s a simple login form where the user provides their email and password.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>User Login</title>
</head>
<body>
    <h2>User Login</h2>
    <form action="login.php" method="POST">
        <label for="email">Email:</label>
        <input type="email" id="email" name="email" required><br><br>

        <label for="password">Password:</label>
        <input type="password" id="password" name="password" required><br><br>

        <input type="submit" value="Login">
    </form>
</body>
</html>

Step 2: PHP Code to Verify Credentials (login.php)

This PHP script handles the form submission, retrieves the user's data from the database, and verifies the password.

<?php
// Step 1: Check if the form was submitted
if ($_SERVER['REQUEST_METHOD'] == 'POST') {

    // Step 2: Capture and sanitize the input data
    $email = trim($_POST['email']);
    $password = trim($_POST['password']);

    // Sanitize the email
    $email = filter_var($email, FILTER_SANITIZE_EMAIL);

    // Step 3: Validate the input
    if (empty($email) || empty($password)) {
        die("Both fields are required.");
    }

    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        die("Invalid email format.");
    }

    // Step 4: Connect to the MySQL database
    $servername = "localhost";
    $db_username = "root";
    $db_password = "";
    $dbname = "test_db";

    // Create the connection
    $conn = mysqli_connect($servername, $db_username, $db_password, $dbname);

    // Check if the connection is successful
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    // Step 5: Write the SQL query to check for the user's email
    $sql = "SELECT * FROM users WHERE email = '$email'";
    $result = mysqli_query($conn, $sql);

    // Step 6: Check if the query returned a result
    if (mysqli_num_rows($result) == 1) {
        // Fetch the user data
        $user = mysqli_fetch_assoc($result);
        $hashed_password = $user['password'];

        // Step 7: Verify the entered password with the hashed password
        if (password_verify($password, $hashed_password)) {
            // Password matches, user is authenticated
            session_start();
            $_SESSION['user_id'] = $user['id'];
            $_SESSION['username'] = $user['username'];
            echo "Login successful! Welcome, " . $_SESSION['username'];
            // Redirect to a protected page
            // header("Location: dashboard.php");
        } else {
            // Password doesn't match
            echo "Invalid password.";
        }
    } else {
        // No user found with that email
        echo "No user found with that email.";
    }

    // Step 8: Close the database connection
    mysqli_close($conn);
}
?>

Step-by-Step Breakdown:

  1. Sanitize and Validate Input:

    • The user input (email and password) is captured and sanitized to prevent harmful data from being processed.
    • The email is validated using filter_var() to ensure it's in a valid format.

  2. Connect to the Database:

    • The script connects to the MySQL database using mysqli_connect(). The database contains the user's credentials (hashed passwords).

  3. Run a SQL Query:

    • The mysqli_query() function is used to execute a SQL query that searches for the user by their email.
    • In this example, the email is directly embedded into the query, which is not secure. It's better to use prepared statements to avoid SQL injection.

  4. Fetch the User Data:

    • If a user is found, their details (like the hashed password) are retrieved from the database using mysqli_fetch_assoc().

  5. Verify the Password:

    • The password_verify() function checks if the plain-text password provided by the user matches the hashed password stored in the database.
    • If they match, the user is authenticated and can be logged in.

  6. Session Management:

    • When the login is successful, a session is started using session_start(). Relevant user information (such as user_id and username) is stored in session variables.

  7. Close the Database Connection:

    • After the query is executed and the credentials are verified, the connection to the database is closed using mysqli_close().

Important Notes:

1. SQL Injection Vulnerability

This example uses mysqli_query() with dynamic input directly embedded into the SQL query. This is vulnerable to SQL injection attacks if a malicious user manipulates the input.

Solution: Use Prepared Statements

// Use prepared statement instead of directly embedding variables in the SQL query
$sql = "SELECT * FROM users WHERE email = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, 's', $email);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

2. Password Hashing and Verification

Using password_hash() to store hashed passwords and password_verify() to verify them is critical for securely handling passwords. Storing passwords in plain text is a major security risk.

  • password_hash() is used to hash passwords when a user registers or changes their password.
  • password_verify() is used to compare the hashed password stored in the database with the plain-text password entered by the user.

3. Session Security

Once the user is authenticated, you should use proper session management to keep the user logged in securely. Some additional security measures include:

  • Using session_regenerate_id() to prevent session fixation attacks.
  • Using HTTPS to ensure that session data is transmitted securely.

Conclusion:

Verifying user credentials using mysqli_query() in PHP MySQL procedural style involves:

  • Connecting to the database.
  • Querying for the user’s credentials.
  • Verifying the password using password_verify().
  • Managing the session upon successful login.


Next Previous




@aCodeTutorials All Rights Reserved
privacy policy | about