SQL Injection

What is SQL Injection?

SQL injection is a security vulnerability that allows an attacker to execute arbitrary SQL commands on a database by injecting malicious SQL code through user inputs.

How does SQL Injection work?

Attackers exploit poorly sanitized user inputs by inserting SQL commands that get executed by the database. For example:

// Vulnerable code
$query = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "'";

An attacker could input admin'; DROP TABLE users; -- to execute destructive commands.

---

Prevention Guidelines

• Never concatenate user input directly into SQL queries,
• Use prepared statements for all database interactions,
• Validate and sanitize all user inputs,
• Use appropriate PDO parameter types (PDO::PARAM_INT, PDO::PARAM_STR),
• Enable PDO error mode: $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION),
• Limit database privileges for application users,
• Regularly update PHP and database software.

---

Preventing SQL Injection with PDO

1. Use Prepared Statements

Prepared statements separate SQL logic from data by pre-compiling the query structure. This prevents user input from being interpreted as SQL commands, making injection attacks impossible.

$pdo = new PDO($dsn, $username, $password);
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->execute([$_POST['username'], $_POST['password']]);
$user = $stmt->fetch();

---

2. Named Parameters

Named parameters provide a more readable alternative to positional parameters. They make queries easier to maintain and reduce the risk of parameter misalignment in complex queries.

$stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (:username, :email)");
$stmt->execute([
    ':username' => $_POST['username'],
    ':email' => $_POST['email']
]);

---

3. Bind Parameters Explicitly

Explicit parameter binding allows you to specify data types (string, integer, etc.) and provides fine-grained control over how data is handled. This adds an extra layer of type safety.

$stmt = $pdo->prepare("UPDATE users SET email = ? WHERE id = ?");
$stmt->bindParam(1, $_POST['email'], PDO::PARAM_STR);
$stmt->bindParam(2, $_POST['id'], PDO::PARAM_INT);
$stmt->execute();