Prevent SQL injection when php queries the database

There are two primary methods for preventing SQL injection in PHP:

1. Use of preprocessed statements

Prepared statements involve preparing a query statement in advance and then passing parameters into it; this approach helps prevent SQL injection attacks. In PHP, you can implement prepared statements using the PDO or mysqli extensions. Below is an example of how to use prepared statements with the mysqli extension:

// 数据库连接
$host = 'localhost';
$user = 'username';
$pass = 'password';
$dbName = 'database_name';
$db = mysqli_connect($host, $user, $pass, $dbName);
if (!$db) {
  die("无法连接到数据库: " . mysqli_connect_error());
}

// 防止SQL注入
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
  $username = $_POST['username'];
  $password = $_POST['password'];
  
  $stmt = $db->prepare("SELECT * FROM users WHERE username=? AND password=?");
  $stmt->bind_param("ss", $username, $password);
  $stmt->execute();
  $result = $stmt->get_result();

  // 处理查询结果
}

In the above code, we have used MySQLi.prepare()A function is used to construct the query statement, where parameter placeholders (?) are employed to replace the actual parameters. Then, it is used.bind_param()A function is used to bind parameters; here, the first parameter "ss" indicates that all subsequent parameters are of string type. This ensures correct parameter passing and mitigates the risk of SQL injection.

2. Escape special characters

In addition to using prepared statements, we can also escape special characters, for example by usingmysqli_real_escape_stringThe `escape` function escapes special characters into safe characters. The following is an example of using this function to escape special characters:

// 数据库连接
$host = 'localhost';
$user = 'username';
$pass = 'password';
$dbName = 'database_name';
$db = mysqli_connect($host, $user, $pass, $dbName);
if (!$db) { 
  die("无法连接到数据库: " . mysqli_connect_error()); 
}

// 防止SQL注入
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
  $username = mysqli_real_escape_string($db, $_POST['username']);
  $password = mysqli_real_escape_string($db, $_POST['password']);
  
  $sql = "SELECT * FROM users WHERE username='$username' AND password='$password'";
  $result = mysqli_query($db, $sql);

  // 处理查询结果
}

In the above code, we have usedmysqli_real_escape_stringThe function escapes the data, ensuring that the input does not contain special characters, thereby preventing SQL injection attacks.