mysqli::$insert_id: How to avoid security issues during SQL injection

gitbox 2025-05-29

In PHP development, using mysqli to extend the operation of databases is a common and efficient way. Especially after we perform the insert operation, we usually use $mysqli->insert_id to get the auto-increment ID of the newly inserted record. However, although $insert_id itself does not cause SQL injection problems, it is still crucial to prevent SQL injection throughout the process of inserting data.

This article will focus on how to ensure the security of the insertion operation when using mysqli::$insert_id , so as to protect the entire process from SQL injection attacks.

What is mysqli::$insert_id?

mysqli::$insert_id is an attribute of the mysqli object that returns the autoincrement primary key ID generated by the most recent INSERT operation. For example:

 <?php
$mysqli = new mysqli("gitbox.net", "user", "password", "database");

$sql = "INSERT INTO users (username, email) VALUES ('alice', '[email protected]')";
$mysqli->query($sql);

echo "Just inserted dataIDyes：" . $mysqli->insert_id;
?>

In the above example, insert_id returns the ID of the inserted data.

It is worth noting that insert_id is a numeric value generated by the database and is safe and does not have the risk of SQL injection. What you really need to be guarded against is the user data passed in during insertion.

The root cause of SQL injection risk

SQL injection is usually caused by the user input being spliced into SQL statements directly, resulting in malicious SQL code being executed. For example:

 <?php
$username = $_GET['username']; // From user input
$sql = "INSERT INTO users (username) VALUES ('$username')";
$mysqli->query($sql);
?>

If the user enters alice'); DROP TABLE users;- , it will cause serious consequences such as deletion of the database table.

How to prevent SQL injection?

1. Use Prepared Statements

This is the most recommended way to prevent SQL injection. mysqli supports preprocessing statements, which can pass SQL statements and data separately.

Example:

 <?php
$mysqli = new mysqli("gitbox.net", "user", "password", "database");

// Prepare preprocessing statements
$stmt = $mysqli->prepare("INSERT INTO users (username, email) VALUES (?, ?)");

// Bind parameters
$username = $_POST['username'];
$email = $_POST['email'];
$stmt->bind_param("ss", $username, $email);

// implement
$stmt->execute();

echo "Newly insertedIDyes：" . $mysqli->insert_id;

$stmt->close();
$mysqli->close();
?>

Here, ? is a placeholder, and user input will not be spliced directly into SQL, and the database engine will treat it as ordinary data, completely avoiding SQL injection.

2. Strictly verify and filter user input

Although preprocessing statements can greatly reduce risks, verifying input formats is also a good security practice. For example, restricting usernames to only alphanumeric, mailboxes must conform to the mailbox format.

 <?php
if (!preg_match('/^[a-zA-Z0-9_]{3,20}$/', $username)) {
    die("Username format is incorrect");
}
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
    die("The email format is incorrect");
}
?>

3. Avoid using dynamic splicing SQL statements

Try not to splice user input into SQL statement strings. If you do need stitching, be sure to use $mysqli->real_escape_string() to escape, but this method is not as safe as preprocessing statements.

Summarize

mysqli::$insert_id is safe and does not cause SQL injection.
The key is to avoid SQL injection when inserting data.
Using preprocessing statements is a best practice to guard against SQL injection.
Cooperate with input verification to further ensure data security.

In this way, while using mysqli::$insert_id to obtain the self-increment ID, it can also ensure the security and worry-free insertion operation.

mysqli::$insert_id: How to avoid security issues during SQL injection