MySQL如何使用预处理语句防SQL注入 参数化查询的安全实践

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

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $user_id = $_GET['id'] ?? ''; // 假设从URL获取用户ID，未经验证的输入

    // 1. 准备SQL语句，使用问号作为占位符
    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");

    // 2. 绑定参数
    $stmt->bindParam(1, $user_id, PDO::PARAM_INT); // 明确指定参数类型很重要

    // 3. 执行语句
    $stmt->execute();

    // 获取结果
    $user = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($user) {
        echo "用户ID: " . $user['id'] . ", 姓名: " . $user['name'];
    } else {
        echo "未找到用户。";
    }

} catch (PDOException $e) {
    echo "数据库操作失败: " . $e->getMessage();
}
?>

$user_id

1 OR 1=1

id

OR 1=1

SELECT * FROM users WHERE username = '{$username}' AND password = '{$password}'

$username

' OR 1=1 --

SELECT * FROM users WHERE username = '' OR 1=1 --' AND password = '{$password}'

--

username = '' OR 1=1

SELECT * FROM users WHERE id = ?

'1 OR 1=1'

'1 OR 1=1'

id

'1 OR 1=1'

<?php
// 连接数据库
$pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8mb4', 'user', 'pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 错误处理

// 插入数据示例
$name = "Alice";
$email = "alice@example.com";
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $email);
$stmt->execute();
echo "新用户已添加。\n";

// 查询数据示例 (命名参数)
$search_term = "%john%"; // 注意：LIKE查询的百分号要包含在参数值中
$stmt = $pdo->prepare("SELECT * FROM users WHERE name LIKE :search_term");
$stmt->bindValue(':search_term', $search_term); // 也可以用bindValue
$stmt->execute();
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($users as $user) {
    echo "ID: {$user['id']}, Name: {$user['name']}, Email: {$user['email']}\n";
}
?>

bindParam

bindValue

bindParam

bindValue

bindValue

mysql-connector-python

import mysql.connector

try:
    conn = mysql.connector.connect(
        host="localhost",
        user="user",
        password="pass",
        database="testdb"
    )
    cursor = conn.cursor()

    # 插入数据示例
    name = "Bob"
    email = "bob@example.com"
    # 使用元组传递参数，驱动会自动处理占位符（通常是%s）
    cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)", (name, email))
    conn.commit()
    print("新用户已添加。")

    # 查询数据示例
    user_id = 1
    cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,)) # 单个参数也要用元组
    user = cursor.fetchone()
    if user:
        print(f"用户ID: {user[0]}, 姓名: {user[1]}, 邮箱: {user[2]}")

except mysql.connector.Error as err:
    print(f"数据库操作失败: {err}")
finally:
    if 'conn' in locals() and conn.is_connected():
        cursor.close()
        conn.close()

%s

execute()

import java.sql.*;

public class MySQLExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb?useSSL=false&serverTimezone=UTC";
        String user = "user";
        String password = "pass";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            // 插入数据示例
            String sqlInsert = "INSERT INTO users (name, email) VALUES (?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(sqlInsert)) {
                pstmt.setString(1, "Charlie");
                pstmt.setString(2, "charlie@example.com");
                pstmt.executeUpdate();
                System.out.println("新用户已添加。");
            }

            // 查询数据示例
            String sqlSelect = "SELECT * FROM users WHERE id = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(sqlSelect)) {
                int userId = 2;
                pstmt.setInt(1, userId);
                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                        System.out.println("用户ID: " + rs.getInt("id") +
                                           ", 姓名: " + rs.getString("name") +
                                           ", 邮箱: " + rs.getString("email"));
                    }
                }
            }

        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
        }
    }
}

Connection.prepareStatement()

PreparedStatement

setXxx()

setString

setInt

executeUpdate()

executeQuery()

SELECT * FROM ? WHERE id = ?

LIKE

LIKE

%

_

CREATE TABLE

ALTER TABLE

DROP TABLE