告别SQL注入和繁琐操作：Dibi如何使用Composer让PHP数据库编程更安全高效

composer require dibi/dibi

use Dibi\Connection;

$database = new Connection([
    'driver'   => 'mysqli',
    'host'     => 'localhost',
    'username' => 'root',
    'password' => 'your_password',
    'database' => 'your_database',
    'charset'  => 'utf8',
]);

// 也可以使用静态方法，方便全局访问
// dibi::connect([...]);

// 传统方式（危险！）
// $name = $_GET['name'];
// $sql = "SELECT * FROM users WHERE name = '$name'"; // SQL注入风险

// Dibi方式（安全！）
$userName = 'John Doe';
$isActive = true;

// 使用问号占位符
$result = $database->query('SELECT * FROM users WHERE name = ? AND active = ?', $userName, $isActive);

// 使用修饰符 %s (字符串), %i (整数), %b (布尔值) 等
$result = $database->query('SELECT * FROM users WHERE name = %s AND active = %b', $userName, $isActive);

// 对于数组参数，Dibi也能智能处理
$ids = [10, 20, 30];
$result = $database->query('SELECT * FROM users WHERE id IN (%i)', $ids);
// 生成的SQL: SELECT * FROM users WHERE id IN (10, 20, 30)

$userData = [
    'name' => 'Alice',
    'email' => 'alice@example.com',
    'age' => 30,
];

// 插入数据 (%v: values)
$database->query('INSERT INTO users %v', $userData);
// 生成的SQL: INSERT INTO `users` (`name`, `email`, `age`) VALUES ('Alice', 'alice@example.com', 30)

// 更新数据 (%a: assoc)
$database->query('UPDATE users SET %a WHERE id = ?', $userData, 123);
// 生成的SQL: UPDATE `users` SET `name`='Alice', `email`='alice@example.com', `age`=30 WHERE id = 123

// 动态WHERE条件 (%and, %or)
$searchConditions = [
    'status' => 'active',
    'city' => 'New York',
];
$result = $database->query('SELECT * FROM users WHERE %and', $searchConditions);
// 生成的SQL: SELECT * FROM users WHERE `status` = 'active' AND `city` = 'New York'

$result = $database->query('
    SELECT c.id AS customer_id, c.name AS customer_name,
           o.id AS order_id, o.order_number, o.amount
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    WHERE c.id IN (1, 2)
');

// 使用 'customer_id|order_id' 描述符，将结果按客户ID和订单ID嵌套
$allData = $result->fetchAssoc('customer_id|order_id');

// 遍历结果，轻松获取嵌套数据
foreach ($allData as $customerId => $customerOrders) {
    echo "客户ID: " . $customerId . "\n";
    foreach ($customerOrders as $orderId => $order) {
        echo "  订单ID: " . $orderId . ", 订单号: " . $order->order_number . ", 金额: " . $order->amount . "\n";
    }
}
/* 输出示例：
客户ID: 1
  订单ID: 101, 订单号: ORD-001, 金额: 100.00
  订单ID: 102, 订单号: ORD-002, 金额: 250.50
客户ID: 2
  订单ID: 201, 订单号: ORD-003, 金额: 50.00
*/

try {
    $database->begin();
    $database->query('UPDATE accounts SET balance = balance - ? WHERE id = ?', 100, $fromAccountId);
    $database->query('UPDATE accounts SET balance = balance + ? WHERE id = ?', 100, $toAccountId);
    $database->commit();
    echo "转账成功！";
} catch (Dibi\Exception $e) {
    $database->rollback();
    echo "转账失败：" . $e->getMessage();
}