How to prevent SQL injection attacks in PHP forms?

How to prevent SQL injection attacks in PHP forms?

SQL injection attack is a very common and dangerous security vulnerability in web development. Attackers use this vulnerability to inject malicious code into the database, thereby destroying the integrity of the database and even obtaining sensitive information. In PHP forms, the main method to prevent SQL injection attacks is to strictly filter and encode the data entered by the user. This article details how to prevent SQL injection attacks, with code examples.

1. Use prepared statements

Preprocessed statements are a technology that sends SQL statements to the database parser for compilation and parsing before executing them. It can effectively prevent SQL injection attacks because it uses parameterized queries to separate user-entered data from SQL statements. PHP's PDO provides a method to use prepared statements. The example is as follows:

// 连接数据库
$dsn = 'mysql:host=localhost;dbname=test';
$username = 'root';
$password = 'password';

try {
    $pdo = new PDO($dsn, $username, $password);
    // 设置错误模式为异常
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo '数据库连接失败：' . $e->getMessage();
}

// 准备SQL语句
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username');

// 绑定参数
$stmt->bindParam(':username', $username);

// 执行查询
$stmt->execute();

// 获取结果
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 输出结果
foreach($result as $row) {
    echo $row['username'];
}

In the above code, we prepare a SQL statement through the prepare() method, which uses a Parameter :username to replace the username entered by the user. Then, the actual username is bound to the parameter through the bindParam() method, and finally the query is executed and the results are obtained. In this way, no matter what the user input is, it will not affect the SQL statement.

2. Use filter functions

PHP provides some filter functions for filtering and cleaning user-entered data. These functions can help us remove special characters and SQL keywords from the input string to prevent SQL injection attacks. Among them, mysqli_real_escape_string() is a commonly used filter function. The example is as follows:

// 连接数据库
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "test";

$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检查连接是否成功
if (!$conn) {
    die("数据库连接失败: " . mysqli_connect_error());
}

// 过滤用户输入数据
$username = mysqli_real_escape_string($conn, $_POST['username']);
$password = mysqli_real_escape_string($conn, $_POST['password']);

// 执行查询
$sql = "SELECT * FROM users WHERE username = '{$username}' AND password = '{$password}'";
$result = mysqli_query($conn, $sql);

// 处理结果
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "用户名为： " . $row["username"];
    }
} else {
    echo "用户名或密码错误";
}

// 关闭连接
mysqli_close($conn);

In the above code, we use the mysqli_real_escape_string() function to filter user input Filter the username and password, and splice the filtered string into the SQL statement. In this way, even if the input contains special characters or SQL keywords, they will be escaped to avoid SQL injection attacks.

To sum up, there are many ways to prevent SQL injection attacks in PHP forms, among which the use of prepared statements and filter functions are common and effective methods. No matter which method is used, ensure that user-entered data is strictly filtered and encoded to ensure website security and data integrity. At the same time, developers should also conduct regular security reviews and vulnerability scans to promptly repair possible security vulnerabilities and protect the information security of the website and users.

The above is the detailed content of How to prevent SQL injection attacks in PHP forms?. For more information, please follow other related articles on the PHP Chinese website!