The role and value of stmt_init in preventing SQL injection

M66 2025-06-02

mysqli::stmt_init is a method in the PHP MySQLi extension that is used to initialize a prepared statement. Prepared statements are a mechanism for executing SQL queries, which can separate SQL queries from variables entered by users, effectively avoiding SQL injection attacks. Through this method, the developer can first create an empty statement object, bind parameters and execute them in subsequent operations.

Principles to prevent SQL injection attacks

The principle of SQL injection attack is that the attacker injects malicious SQL code into the fields entered by the user (such as the username, password, etc. of the login form), and directly splice it into the SQL query in an improper way. In this way, the attacker can modify the original SQL statement to perform malicious operations. To avoid this, MySQLi provides prepared statements and binding parameters.

With the help of mysqli::stmt_init , developers can avoid SQL injection through the following steps:

Separate query structure and data: mysqli::stmt_init is used to initialize an SQL query template, where the query structure is fixed, and the actual input data is passed to the database through parameter binding. This means that any data entered by the user is not spliced directly with the SQL statement, but is passed to the database as a parameter.
Binding parameters: Through the bind_param method, developers can bind user input to the parameters of SQL query. These input data are processed and escaped by the MySQL server to ensure that they are not executed as SQL code. For example, strings will be automatically quoted and numbers will be processed appropriately. In this way, even if the user enters malicious SQL code, it cannot affect the execution of the query.
Execute query: The query will be executed only after all parameters are bound. In this way, even if the user enters a string containing SQL injection code, the entire query statement will not be tampered with, ensuring the security of the data.

How to improve query security through mysqli::stmt_init ?

With mysqli::stmt_init and preprocessing statements, we can fundamentally reduce the risk of SQL injection attacks. Here are the steps to achieve this:

Initialization statement: Use mysqli::stmt_init to create an empty preprocessing statement object.

 $conn = new mysqli('localhost', 'username', 'password', 'database');
$stmt = $conn->stmt_init();

Prepare SQL query: Use the prepare method to create a prepared SQL statement template. Note that the user input part in the SQL query uses placeholders (for example ? ) instead.
```
 $sql = "SELECT * FROM users WHERE username = ? AND password = ?";
if ($stmt->prepare($sql)) {
    // Further operation
}
```
Binding parameters: Use the bind_param method to bind the user input value with the placeholder in the SQL query template. The second parameter of this method represents the type of the parameter, such as s represents a string, i represents an integer, etc.
```
 $stmt->bind_param('ss', $username, $password);
```
Execute query: Finally, the SQL query with parameters bound to be executed through the execute method.
```
 $stmt->execute();
```

In this way, user input in SQL queries will always be treated as data rather than SQL code, thus avoiding SQL injection attacks.

Sample code: Login verification

Here is a practical example that demonstrates how to prevent SQL injection using mysqli::stmt_init :

 <?php
$conn = new mysqli('localhost', 'username', 'password', 'database');

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

// Get user input
$username = $_POST['username'];
$password = $_POST['password'];

// Initialize preprocessing statements
$stmt = $conn->stmt_init();

if ($stmt->prepare("SELECT * FROM users WHERE username = ? AND password = ?")) {
    // Bind parameters
    $stmt->bind_param('ss', $username, $password);

    // Execute a query
    $stmt->execute();

    // Get results
    $result = $stmt->get_result();

    if ($result->num_rows > 0) {
        echo "Login successfully！";
    } else {
        echo "Invalid username or password。";
    }

    // Close statement
    $stmt->close();
}

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

In this example, even if the user input contains malicious code (such as admin' OR '1'='1 ), the input does not affect the structure of the SQL query, because it is processed as a parameter.

mysqli::stmt_init