حقن SQL والاستعلامات ذات المعاملات

ما المقصود بحقن SQL؟

مثال تقليدي

<?php
// VULNERABLE: attacker enters username = "' OR '1'='1"
$sql = "SELECT * FROM users WHERE username = '".$_GET["username"]."'";
// Becomes: SELECT * FROM users WHERE username = '' OR '1'='1'
// Returns ALL users!

مثال على استخراج البيانات

// Input: ' UNION SELECT username, password FROM admin_users--
// This appends a second SELECT returning admin credentials

العبارات المُجهزة هي الحل

<?php
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
$stmt->execute([$_GET["username"]]);
// Safe even if username = "' OR 1=1--"

العناصر النائبة المسماة

<?php
$stmt = $pdo->prepare(
    "SELECT * FROM users WHERE email = :email AND active = :active"
);
$stmt->execute([":email" => $email, ":active" => true]);

Eloquent آمن افتراضيًا

<?php
// Safe:
User::where("email", $email)->first();

// UNSAFE: raw query with user input!
DB::select("SELECT * FROM users WHERE email = ".$email);

الاستعلامات الخام

<?php
// Safe raw query:
$users = DB::select("SELECT * FROM users WHERE email = ?", [$email]);

// Or with named bindings:
$users = DB::select("SELECT * FROM users WHERE email = :email", [":email" => $email]);

حقن جملة LIKE

<?php
$search = str_replace(["%", "_", "\\"], ["\%", "\_", "\\\\"], $userInput);
$stmt = $pdo->prepare("SELECT * FROM products WHERE name LIKE ?");
$stmt->execute(["%$search%"]);

حقن ORDER BY

<?php
$allowedColumns = ["name", "price", "created_at"];
$col = in_array($request->sort, $allowedColumns) ? $request->sort : "name";
$dir = $request->dir === "desc" ? "desc" : "asc";
// Then safely build the query string

الإجراءات المخزنة

مبدأ أقل قدر من الصلاحيات

الملخص

تحقق سريع