第13课: PDO与数据库安全

PDO连接

<?php
try {
    $pdo = new PDO(
        "mysql:host=localhost;dbname=testdb",
        "username",
        "password"
    );
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("连接失败: " . $e->getMessage());
}
?>

预处理语句

<?php
// 插入数据
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->execute([
    ':name' => '张三',
    ':email' => 'zhang@example.com'
]);

// 查询数据
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([1]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
?>

事务处理

<?php
try {
    $pdo->beginTransaction();

    $pdo->exec("INSERT INTO users (name) VALUES ('李四')");
    $pdo->exec("UPDATE accounts SET balance = balance - 100");

    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    echo "事务失败: " . $e->getMessage();
}
?>

防止SQL注入

$sql = "SELECT * FROM users WHERE name = '$_POST[name]'";

$stmt = $pdo->prepare("SELECT * FROM users WHERE name = ?");
$stmt->execute([$_POST['name']]);

批量操作

<?php
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");

$users = [
    ['张三', 'zhang@example.com'],
    ['李四', 'li@example.com'],
    ['王五', 'wang@example.com']
];

foreach ($users as $user) {
    $stmt->execute($user);
}
?>

获取结果

<?php
// 获取所有结果
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 获取单行
$user = $stmt->fetch(PDO::FETCH_ASSOC);

// 获取单个值
$count = $pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
?>