How to prevent SQL injection attacks in PHP development

How to prevent SQL injection attacks in PHP development

SQL injection attacks refer to dynamically constructing SQL statements in web applications and then executing these SQL statements on the database, allowing attackers to perform malicious operations. Or an attack method to obtain sensitive data. For this attack method, developers need to take protective measures to ensure the security of web applications. This article will introduce how to prevent SQL injection attacks in PHP development.

1. Parameter binding

In PHP, you can use PDO or mysqli extension library to implement parameter binding and execute SQL statements and input parameters separately. This method can prevent parameters from containing SQL exception characters and statements and avoid internal SQL injection attacks.

Taking PDO as an example, the code is as follows:

//准备SQL语句
$sql = "SELECT name, age, email FROM user WHERE id = :id AND age > :age";
$stmt = $pdo->prepare($sql);
 
//绑定参数
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->bindParam(':age', $age, PDO::PARAM_INT);
 
//执行查询语句
$stmt->execute();

2. String escape

Before storing user input data into the database, you need to use escape function to escape strings. In PHP, the mysql_real_escape_string() and mysqli_real_escape_string() functions can achieve escaping. This method is a low-level preventive measure and is recommended to be used in conjunction with other safer protective measures.

Code example:

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

//转义字符串
$username = mysql_real_escape_string($username);
$password = mysql_real_escape_string($password);

//准备SQL，然后查询
$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
mysql_query($sql);

3. Input checking

Input checking refers to filtering and validating input parameters to ensure that the input is legal and meets expectations Type and format. This approach avoids some potential SQL injection vulnerabilities. For example, if the parameter of the query is a number, you can use the is_numeric() function to check whether it is a number. If the parameter of the query is a string, you can use the ctype_alpha() function to check whether it contains only letters.

//对用户输入进行检查
if (is_numeric($_GET['id'])) {
    $id = $_GET['id'];
    
    //查询数据库
    $sql = "SELECT * FROM users WHERE id = $id";
    mysql_query($sql);
}

4. Database permission control

When developing web applications, you can use specific accounts and permissions to connect to the database. When setting up accounts and permissions, careful consideration should be given to ensuring that only necessary data and operations are authorized. For example, a read-only account cannot perform INSERT, UPDATE, and DELETE operations, which greatly reduces the risk of SQL injection attacks.

Summary:

This article introduces four methods to prevent SQL injection attacks in PHP development, including parameter binding, string escaping, input checking and database permission control. Developers should choose appropriate protection measures based on their application scenarios and needs to ensure the security of web applications. At the same time, upgrading the PHP version and updating the database software in a timely manner are also effective means to prevent SQL injection attacks.

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