Use mysqli::stmt_init to implement a secure login verification mechanism

When developing PHP websites, the user login function is almost an integral part of it. However, if user input is accidentally processed, it is vulnerable to SQL injection attacks. mysqli::stmt_init is a method provided by the mysqli extension, which can be effectively prevented from SQL injection with preprocessing statements. This article will use a specific example to describe how to use it to implement a secure login authentication mechanism.

Why prevent SQL injection?

SQL injection is an attack technology in which an attacker tampers with the original query logic by injecting malicious SQL statements into the input field. For example, if you splice SQL directly:

 $sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";

An attacker can enter 'OR '1'='1 as the username or password, so that the query is always true and bypass authentication.

The role of mysqli::stmt_init and preprocessing statements

mysqli::stmt_init is used to initialize a mysqli_stmt object. It is the first step in implementing preprocessing statements. It can strictly distinguish SQL from user input, thereby effectively preventing injection attacks.

Sample code: Secure login verification

Here is an example of a secure login form verification:

 <?php
// Database connection
$mysqli = new mysqli("localhost", "db_user", "db_password", "my_database");

// Check the connection
if ($mysqli->connect_errno) {
    die("Connection failed: " . $mysqli->connect_error);
}

// User submission username and password
$username = $_POST['username'];
$password = $_POST['password'];

// Initialize a preprocessing statement object
$stmt = $mysqli->stmt_init();

// write SQL Query statement，use ? Placeholder
$sql = "SELECT id, password_hash FROM users WHERE username = ?";

// Preparation statement
if ($stmt->prepare($sql)) {
    // Bind parameters（s express string type）
    $stmt->bind_param("s", $username);

    // Execution statement
    $stmt->execute();

    // Get results
    $stmt->bind_result($user_id, $password_hash);

    // Determine whether there is any result
    if ($stmt->fetch()) {
        // Verify password（Assume that the database is stored password_hash() Results）
        if (password_verify($password, $password_hash)) {
            echo "Login successfully，Welcome users ID：$user_id";
            // Set up a session or jump，For example：
            // header("Location: https://m66.net/dashboard.php");
        } else {
            echo "Error password";
        }
    } else {
        echo "The username does not exist";
    }

    // Close statement
    $stmt->close();
} else {
    echo "SQL Statement preparation failed: " . $mysqli->error;
}

// Close the connection
$mysqli->close();
?>

summary

By using bind_param with mysqli::stmt_init with preprocessing statements, we can effectively prevent SQL injection attacks. Preprocessing statements should always be used in development to process user input, especially when database operations are involved. In addition, functions such as password_hash() and password_verify() should be used to enhance password security.

If you are building a login system or user authentication module, use this method first, which is much safer than traditional string splicing.