PHP如何转义SQL字符_PHPSQL字符转义函数使用教程

mysqli_real_escape_string

<?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_input_name = "O'Malley"; // 假设这是用户输入
    $user_input_email = "test@example.com";

    // 使用占位符进行预处理
    $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
    $stmt->bindParam(':name', $user_input_name);
    $stmt->bindParam(':email', $user_input_email);
    $stmt->execute();

    echo "数据插入成功！";

    // 查询示例
    $search_term = "O'Malley";
    $stmt = $pdo->prepare("SELECT * FROM users WHERE name = :name");
    $stmt->bindParam(':name', $search_term);
    $stmt->execute();
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    print_r($results);

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

<?php
$mysqli = new mysqli("localhost", "your_user", "your_password", "testdb");

if ($mysqli->connect_errno) {
    echo "连接MySQL失败: " . $mysqli->connect_error;
    exit();
}

$user_input_name = "O'Malley"; // 假设这是用户输入
$user_input_email = "test@example.com";

// 准备语句
$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
if ($stmt === false) {
    echo "准备语句失败: " . $mysqli->error;
    exit();
}

// 绑定参数
// 'ss' 表示两个参数都是字符串类型
$stmt->bind_param("ss", $user_input_name, $user_input_email);

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

if ($stmt->affected_rows > 0) {
    echo "数据插入成功！";
} else {
    echo "数据插入失败: " . $stmt->error;
}

$stmt->close();
$mysqli->close();
?>

mysqli_real_escape_string

<?php
$mysqli = new mysqli("localhost", "your_user", "your_password", "testdb");

if ($mysqli->connect_errno) {
    echo "连接MySQL失败: " . $mysqli->connect_error;
    exit();
}

$user_input = "O'Malley"; // 假设这是用户输入

// 使用 mysqli_real_escape_string 进行转义
$escaped_input = $mysqli->real_escape_string($user_input);

// 然后才能安全地拼接到SQL查询中
$sql = "INSERT INTO users (name) VALUES ('" . $escaped_input . "')";

if ($mysqli->query($sql) === TRUE) {
    echo "数据插入成功！";
} else {
    echo "Error: " . $sql . "<br>" . $mysqli->error;
}

$mysqli->close();
?>

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

username

admin' OR '1'='1

password

SELECT * FROM users WHERE username = 'admin' OR '1'='1' AND password = '随便什么'

'1'='1'

admin

admin' OR '1'='1

mysqli_real_escape_string

'

"

\

\

mysqli_real_escape_string

<?php
// 假设你已经建立了一个MySQLi连接
$mysqli = new mysqli("localhost", "your_user", "your_password", "testdb");

if ($mysqli->connect_errno) {
    die("连接MySQL失败: " . $mysqli->connect_error);
}

// 示例：用户输入的可能包含特殊字符的字符串
$user_comment = "I'm a user; DROP TABLE users;"; // 这是一个恶意输入示例
$user_name = "O'Reilly"; // 这是一个正常的包含单引号的输入

// 在将字符串拼接到SQL查询之前，对其进行转义
$escaped_comment = $mysqli->real_escape_string($user_comment);
$escaped_name = $mysqli->real_escape_string($user_name);

// 现在，你可以安全地将转义后的字符串拼接到SQL查询中
// 注意：只对字符串值进行转义，数字、布尔值等不应通过此函数处理
$sql_insert_comment = "INSERT INTO comments (user_id, comment_text) VALUES (1, '" . $escaped_comment . "')";
$sql_insert_user = "INSERT INTO users (name) VALUES ('" . $escaped_name . "')";

echo "转义后的恶意评论: " . $escaped_comment . "\n";
echo "转义后的正常姓名: " . $escaped_name . "\n";

// 执行查询
if ($mysqli->query($sql_insert_comment)) {
    echo "评论插入成功！\n";
} else {
    echo "评论插入失败: " . $mysqli->error . "\n";
}

if ($mysqli->query($sql_insert_user)) {
    echo "用户插入成功！\n";
} else {
    echo "用户插入失败: " . $mysqli->error . "\n";
}

$mysqli->close();
?>