PHP如何过滤SQL关键字_PHPSQL关键字过滤函数使用教程

PDO

mysqli

<?php
try {
    $dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
    $username = 'your_username';
    $password = 'your_password';

    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 设置错误模式为抛出异常

    $user_input_id = $_GET['id'] ?? '';
    $user_input_name = $_POST['name'] ?? '';

    // 示例1：通过占位符绑定参数（推荐）
    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id AND name = :name");
    $stmt->bindParam(':id', $user_input_id, PDO::PARAM_INT); // 明确指定参数类型
    $stmt->bindParam(':name', $user_input_name, PDO::PARAM_STR);
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    // print_r($result);

    // 示例2：通过问号占位符绑定参数
    $stmt = $pdo->prepare("INSERT INTO products (name, price) VALUES (?, ?)");
    $product_name = 'New Widget';
    $product_price = 19.99;
    $stmt->execute([$product_name, $product_price]); // 数组形式传递参数
    // echo "Affected rows: " . $stmt->rowCount();

} catch (PDOException $e) {
    // 生产环境中不应直接输出错误信息，应记录日志
    error_log("Database error: " . $e->getMessage());
    // echo "An error occurred. Please try again later.";
}
?>

mysqli

<?php
$conn = new mysqli('localhost', 'your_username', 'your_password', 'testdb');

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$user_input_email = $_GET['email'] ?? '';
$user_input_status = $_POST['status'] ?? '';

// 示例1：查询
$stmt = $conn->prepare("SELECT username, registration_date FROM members WHERE email = ? AND status = ?");
if ($stmt === false) {
    // 错误处理，例如日志记录
    error_log("Prepare failed: " . $conn->error);
    // die("Prepare failed: " . $conn->error);
}

// 绑定参数，'ss'表示两个参数都是字符串类型
$stmt->bind_param('ss', $user_input_email, $user_input_status);
$stmt->execute();
$result = $stmt->get_result(); // 获取结果集
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        // print_r($row);
    }
}
$stmt->close();

// 示例2：插入
$stmt = $conn->prepare("INSERT INTO logs (action, timestamp) VALUES (?, NOW())");
if ($stmt === false) {
    error_log("Prepare failed: " . $conn->error);
}
$action_log = 'User logged in';
$stmt->bind_param('s', $action_log); // 's'表示一个字符串类型参数
$stmt->execute();
// echo "New record created successfully.";
$stmt->close();

$conn->close();
?>

mysqli_real_escape_string

mysqli_real_escape_string

<?php
$conn = new mysqli('localhost', 'your_username', 'your_password', 'testdb');
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$user_search_term = $_GET['term'] ?? '';

// 在将字符串拼接到SQL语句之前进行转义
$escaped_search_term = $conn->real_escape_string($user_search_term);

// 这种方式依然不推荐，因为它容易遗漏，且不如参数化查询安全
$sql = "SELECT * FROM articles WHERE title LIKE '%" . $escaped_search_term . "%'";
$result = $conn->query($sql);
// ... 处理结果 ...

$conn->close();
?>

SELECT * FROM users WHERE id = ?

' OR 1=1 --

mysqli_real_escape_string

mysqli

mysql

'

"

\

mysqli_real_escape_string

UNION

SELECT

OR

DROP

DELETE