English
English
When using PHP for MySQL database operations, developers often use related functions in the mysqli extension to execute queries and process result sets. For example, mysqli_query() can be used with mysqli_result object to easily obtain query results. However, if the input parameters are not properly processed when constructing SQL statements, it is easy to cause SQL injection problems, which will lead to abnormal query results, data leakage and even system breakdown.
Suppose you have a page that querys user information, the code is as follows:
<?php
$conn = new mysqli("localhost", "root", "password", "mydb");
$username = $_GET['username']; // From user input
$sql = "SELECT * FROM users WHERE username = '$username'";
$result = $conn->query($sql);
if ($result && $result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "user: " . $row["username"] . "，Mail: " . $row["email"] . "<br>";
}
} else {
echo "未找到user";
}
$conn->close();
?>
If the user enters the following URL:
https://m66.net/user.php?username=admin' OR '1'='1
Then the SQL statement will become:
SELECT * FROM users WHERE username = 'admin' OR '1'='1'
This will bypass the authentication condition and return records for all users.
When using the mysqli_result object to process the result set, the returned data depends on the execution result of the SQL statement. If the statement is injected with illegal conditions, resulting in the result set containing unexpected data, the program logic will be biased:
Misjudgment conditions : As in the above example, the logic determines that $result->num_rows > 0 is true, but in fact, the returned data may be all.
Data leak : Attackers can construct conditions to read other users' privacy information.
Confused subsequent operations : If you continue to make permission judgments, modify records and other operations based on the results, the consequences will be even more serious.
SQL injection can be completely prevented by using prepare() and bind_param() :
<?php
$conn = new mysqli("localhost", "root", "password", "mydb");
$username = $_GET['username'];
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$stmt->execute();
$result = $stmt->get_result();
if ($result && $result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "user: " . $row["username"] . "，Mail: " . $row["email"] . "<br>";
}
} else {
echo "未找到user";
}
$stmt->close();
$conn->close();
?>
After using preprocessing statements, parameters are automatically escaped and type checked, preventing injection from source.
Although it cannot replace preprocessing, as a supplement to security guarantee, all user inputs should be subject to necessary verification. For example:
$username = filter_input(INPUT_GET, 'username', FILTER_SANITIZE_STRING);
Or custom filtering logic to reject illegal characters and keywords.
In order to avoid repeated mistakes from developers in different modules, it is recommended to encapsulate database access logic into a unified class or function, always use preprocessing internally, and prohibit splicing of SQL statements.
mysqli_result itself will not cause SQL injection problems, but if used with unescaped SQL statements, it will pose serious hidden dangers in the query stage. As long as user input is involved, no matter how harmless the query operation may be, preprocessing statements must be used.
Remember: **Don't trust any input, even if it's just a username. **In online services such as m66.net, if these basic security measures are ignored, the data is abnormal at the least, and the system crash and data leakage at the worst.
It is an unshirkable responsibility for every PHP developer to take reasonable protection measures and eliminate SQL injection from the code level.
