Prepared Statements — SQL Injection থেকে সুরক্ষা

SQL Injection

User input-এ SQL ঢুকিয়ে DB hack — ওয়েবের #১ ঝুঁকি।

// ❌ ভুল
$email = $_POST["email"];
$pass = $_POST["password"];
$sql = "SELECT * FROM users WHERE email='$email' AND password='$pass'";

// Hacker: email = "admin@x.com' --"
// Query → WHERE email='admin@x.com' -- ' AND password=''
// "--" comment, password check skip → admin login! 💥

সমাধান: Prepared Statement ⭐

<?php
$stmt = $conn->prepare("SELECT * FROM users WHERE email=? AND password=?");
$stmt->bind_param("ss", $email, $pass);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();
$stmt->close();
?>

Type Codes

• s — string
• i — integer
• d — double (float)
• b — blob

$stmt->bind_param("siid", $name, $age, $uid, $balance);

INSERT

$stmt = $conn->prepare("INSERT INTO users (name, email, password, age) VALUES (?, ?, ?, ?)");
$hash = password_hash($_POST["password"], PASSWORD_BCRYPT);
$age = (int)$_POST["age"];
$stmt->bind_param("sssi", $name, $email, $hash, $age);

if ($stmt->execute()) {
    echo "ID: " . $conn->insert_id;
}
$stmt->close();

UPDATE

$stmt = $conn->prepare("UPDATE users SET name=?, age=? WHERE id=?");
$stmt->bind_param("sii", $name, $age, $id);
$stmt->execute();
echo $stmt->affected_rows;
$stmt->close();

DELETE

$stmt = $conn->prepare("DELETE FROM users WHERE id=?");
$stmt->bind_param("i", $id);
$stmt->execute();
if ($stmt->affected_rows > 0) echo "মুছে গেছে";
$stmt->close();

SELECT একাধিক Row

$stmt = $conn->prepare("SELECT * FROM users WHERE age>=? AND city=?");
$stmt->bind_param("is", $minAge, $city);
$stmt->execute();

$result = $stmt->get_result();
$users = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();

Reuse Statement

$stmt = $conn->prepare("INSERT INTO logs (message) VALUES (?)");
foreach ($messages as $msg) {
    $stmt->bind_param("s", $msg);
    $stmt->execute();
}
$stmt->close();

⭐ সোনালী নিয়ম

User input যুক্ত করতে সবসময় Prepared Statement। কখনোই string concat দিয়ে SQL বানাবেন না।