Preventing SQL Injection in PHP Using mysqli_real_escape_string

When developing websites that interact with databases, user input must be sanitized to prevent SQL injection attacks, where malicious users inject SQL code to compromise or destroy data.

The recommended solution in PHP is to use the mysqli_real_escape_string function, which escapes special characters in strings before they are included in SQL statements.

<?php
// 连接到数据库
$mysqli = new mysqli("localhost", "root", "", "mydatabase");

// 检查连接是否成功
if ($mysqli->connect_errno) {
    echo "连接失败：" . $mysqli->connect_error;
    exit();
}

// 获取用户输入
$username = $_POST['username'];
$password = $_POST['password'];

// 对用户输入进行转义
$username = mysqli_real_escape_string($mysqli, $username);
$password = mysqli_real_escape_string($mysqli, $password);

// 构建SQL查询语句
$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";

// 执行查询
$result = $mysqli->query($query);

// 检查查询结果
if ($result->num_rows > 0) {
    // 登录成功
    echo "登录成功！";
} else {
    // 登录失败
    echo "用户名或密码不正确！";
}

// 关闭数据库连接
$mysqli->close();
?>

The code first establishes a MySQLi connection and checks for errors, retrieves the posted username and password, escapes them with mysqli_real_escape_string, builds a SELECT query using the escaped values, executes it, and checks the result to determine login success or failure, finally closing the connection.

By consistently escaping user input with mysqli_real_escape_string, developers can ensure that inputs are treated as plain strings rather than executable SQL, effectively mitigating SQL injection risks and enhancing database security.