[SOLVED] SQL Query Using real_escape_string Failing in PHP?

PHP
Written by MALIK SHAFI

This PHP script handles user authentication by securely connecting to a MySQL database, retrieving user details, and verifying credentials using prepared statements to prevent SQL injection. It also uses password_verify() to safely compare the entered password with the stored hashed password. If successful, the user is logged in, and a session is created for further authentication.

PHP Code (with Errors):

code<?php

$is_invalid = false;

if ($_SERVER["REQUEST_METHOD"] === "POST") {
    
    $mysqli = require __DIR__ . "database.php";
    
    $sql = "SELECT * FROM users WHERE email = '%s' AND password_hash = '%s'"; 
    $mysqli->real_escape_string($_POST["email"],$_POST["password_hash"]);
    
    $result = $mysqli->query($sql);
    
    $user = $result->fetch_assoc();
    
    if ($user) {
            
        session_start();
        
        session_regenerate_id();
        
        $_SESSION["user_id"] = $user["id"];
        
        header("Location: home.php");
        exit;
    }
    
    $is_invalid = true;
}
?>

<!doctype html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <!-- Custom Styles -->
    <link rel="stylesheet" type="text/css" href="css/styles.css">
    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.0.0/dist/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
    <title>HOME</title>
  </head>

  <body>
  <!-- Navigation Bar -->
  <div class="nav">
    <a href="home.php">Home</a>
    <a href="public.php">Public</a>
    <a href="private.php">Private</a>
    <a class="active" href="login.php">Sign In</a>
  </div>
    <!-- Grid -->
    <div class="container">
      <div class="row">
        <div class="col">
          <!--Login-->
          <h1>Sign In</h1>
            
            <?php if ($is_invalid): ?>
                <em>Invalid login</em>
            <?php endif; ?>
            
            <form method="post">
                <label for="email">email</label>
                <input type="email" name="email" id="email"
                    value="<?= htmlspecialchars($_POST["email"] ?? "") ?>">
                
                <label for="password">Password</label>
                <input type="password" name="password" id="password">
                
                <button>Log in</button>
            </form>
        </div>
    </div>
    
    <!-- JavaScript -->
    <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/popper.js@1.12.9/dist/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.0.0/dist/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
  </body>
</html>

Issues in the Code:

  1. File Path Issue: The file inclusion is incorrect. It is missing a / in require __DIR__ . "database.php"; which should be require __DIR__ . "/database.php";.
  2. SQL Injection Vulnerability: The current code is vulnerable to SQL injection because of how it’s constructing the query using sprintf and real_escape_string(). The proper approach is to use prepared statements.
  3. Password Hashing: Passwords are being compared directly, but they should be compared using the password_verify() function to ensure security.
  4. Escaping Error: real_escape_string() accepts one parameter at a time, so calling it with two parameters is incorrect.
  5. Query Execution: The query itself is being executed without proper preparation and sanitization.

Fixed PHP Code:

code<?php

$is_invalid = false;

if ($_SERVER["REQUEST_METHOD"] === "POST") {
    
    // Include the database connection
    $mysqli = require __DIR__ . "/database.php";  // Fixing missing slash
    
    // Prepare a SQL statement to prevent SQL injection
    $sql = "SELECT * FROM users WHERE email = ?";
    $stmt = $mysqli->prepare($sql);
    
    // Bind the email parameter to the SQL query
    $stmt->bind_param("s", $_POST["email"]);
    $stmt->execute();
    
    // Fetch the result from the query
    $result = $stmt->get_result();
    $user = $result->fetch_assoc();
    
    // Verify the password using password_verify
    if ($user && password_verify($_POST["password"], $user["password_hash"])) {
        
        // Start a new session
        session_start();
        
        // Regenerate session ID for security
        session_regenerate_id();
        
        // Store user ID in session
        $_SESSION["user_id"] = $user["id"];
        
        // Redirect to home page
        header("Location: home.php");
        exit;
    }
    
    // Set invalid login flag if authentication fails
    $is_invalid = true;
}
?>

<!doctype html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <!-- Custom Styles -->
    <link rel="stylesheet" type="text/css" href="css/styles.css">
    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.0.0/dist/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
    <title>Sign In</title>
  </head>

  <body>
  <!-- Navigation Bar -->
  <div class="nav">
    <a href="home.php">Home</a>
    <a href="public.php">Public</a>
    <a href="private.php">Private</a>
    <a class="active" href="login.php">Sign In</a>
  </div>
    <!-- Grid -->
    <div class="container">
      <div class="row">
        <div class="col">
          <!-- Login -->
          <h1>Sign In</h1>
            
            <?php if ($is_invalid): ?>
                <em>Invalid login</em>
            <?php endif; ?>
            
            <form method="post">
                <label for="email">Email</label>
                <input type="email" name="email" id="email"
                    value="<?= htmlspecialchars($_POST["email"] ?? "") ?>" required>
                
                <label for="password">Password</label>
                <input type="password" name="password" id="password" required>
                
                <button>Log in</button>
            </form>
        </div>
      </div>
    </div>
    
    <!-- JavaScript -->
    <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/popper.js@1.12.9/dist/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.0.0/dist/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
  </body>
</html>

Improving PHP Login Security with Prepared Statements

In web applications, authentication is one of the most critical processes to secure. Often, developers construct SQL queries dynamically by embedding user inputs directly into the query, which opens the door to SQL injection attacks. These attacks can allow a malicious user to manipulate the query to gain unauthorized access to data.

The Problem

In the original code example, the following SQL query was constructed:

code$sql = "SELECT * FROM users WHERE email = '%s' AND password_hash =

Related blog posts

Load more
Get In Touch
Useful Resources

2024 © FSIBLOG. All Rights Reserved.