How to avoid SQL injection attacks in PHP files?

With the continuous development of Internet technology, the security issues of websites and applications have attracted more and more attention. Among them, SQL injection attack is a common attack method, especially for websites and applications written in PHP. Therefore, this article will introduce how to avoid SQL injection attacks in PHP files to ensure the security of websites and applications.

1. Use prepared statements

Prepared statements are an important way to prevent SQL injection attacks. It separates SQL statements and parameters, thereby avoiding the possibility of attacks caused by improper input. Commonly used prepared statements in PHP include PDO and MySQLi. The following is an example of using PDO:

//连接数据库
$pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');
//预处理SQL语句
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password');
//绑定参数
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
//执行查询
$stmt->execute();

2. Parameterized query

Parameterized query is similar to a prepared statement, and also separates SQL statements and parameters. The difference is that parameterized query refers to using placeholders to replace parameters in the SQL statement and passing the parameters to the query function individually. Here is an example using MySQLi:

//连接数据库
$conn = mysqli_connect('localhost', 'username', 'password', 'test');
//参数化查询
$stmt = mysqli_prepare($conn, 'SELECT * FROM users WHERE username = ? AND password = ?');
mysqli_stmt_bind_param($stmt, 'ss', $username, $password);
mysqli_stmt_execute($stmt);

3. Filtering input

Before using user input as SQL query criteria, it should be properly filtered and validated. Commonly used filtering functions in PHP include htmlspecialchars() and strip_tags(), which can filter out HTML tags and special characters. For example:

//使用htmlspecialchars()过滤用户输入
$username = htmlspecialchars($_POST['username'], ENT_QUOTES, 'UTF-8');
$password = htmlspecialchars($_POST['password'], ENT_QUOTES, 'UTF-8');
//查询用户
$stmt = $pdo->query("SELECT * FROM users WHERE username = '{$username}' AND password = '{$password}'");

4. Check variable type

Before using user input as SQL query conditions, you should also check whether its variable type is correct. For example, when the user input is a string, it needs to be enclosed in quotation marks; when the user input is a number, it does not need to be enclosed in quotation marks. Commonly used type checking functions in PHP include is_numeric() and is_string(), which can help us check whether variables are numbers and strings. For example:

$username = $_POST['username'];
if (is_string($username)) {
    //将字符串用引号括起来
    $username = "'" . $username . "'";
}
$password = $_POST['password'];
if (is_string($password)) {
    //将字符串用引号括起来
    $password = "'" . $password . "'";
}
//查询用户
$stmt = $pdo->query("SELECT * FROM users WHERE username = {$username} AND password = {$password}");

In short, SQL injection attack is a dangerous and common attack method, and effective measures must be taken to prevent it. This article introduces several methods to avoid SQL injection attacks, such as prepared statements, parameterized queries, filtering input, and checking variable types. I hope it will be helpful to PHP developers.

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