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

저장 프로시저

최소 권한 원칙

요약

빠른 확인