SQL injections

Lesson#11 of 12 in topic Theory

1. What Is SQL Injection?

SQL Injection is a vulnerability that happens when user input is inserted into an SQL query without proper validation or parameterization.

If an attacker can control part of the query, they can:

  • Bypass login

  • Read sensitive data

  • Modify or delete data

  • In some cases, take over the server



2. Vulnerable Example (Classic Login Form)


❌ Vulnerable PHP Code (DO NOT USE)

<?php
$conn = new mysqli("localhost", "root", "", "test");

$username = $_POST['username'];
$password = $_POST['password'];

$sql = "SELECT * FROM users 
        WHERE username = '$username' 
        AND password = '$password'";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "Login successful";
} else {
    echo "Invalid credentials";
}
?>


Why Is This Dangerous?

Because user input is directly inserted into the SQL query.

If someone types:

username: admin
password: ' OR '1'='1


The query becomes:

SELECT * FROM users 
WHERE username = 'admin' 
AND password = '' OR '1'='1'

And since '1'='1' is always true, the login can be bypassed.



3. How SQL Injection Works (Step by Step)

  1. The application builds SQL as a string.

  2. User input is concatenated directly.

  3. The database executes the final string.

  4. Malicious input changes query logic.

The database cannot distinguish between code and data unless you use parameterized queries.


4. Common SQL Injection Techniques


4.1 Authentication Bypass

Input:

' OR 1=1 --

Example query:

SELECT * FROM users WHERE username = '' OR 1=1 -- ' AND password = ''

-- comments out the rest of the query.

Result: attacker logs in without password.



4.2 Data Extraction

If a page shows user data by ID:

$id = $_GET['id'];
$sql = "SELECT * FROM products WHERE id = $id";

Attacker sends:

?id=1 OR 1=1

Query becomes:

SELECT * FROM products WHERE id = 1 OR 1=1

All products are returned.



4.3 UNION-Based Injection

If results are displayed, attacker may try:

?id=1 UNION SELECT username, password FROM users --

This merges results from another table into the response.



4.4 Error-Based SQLi

If database errors are displayed, attacker may use:

?id=1'

This may reveal:

  • Database type

  • Table names

  • SQL structure

Never show raw database errors in production.



5. Realistic Modern Example (Laravel)

You’re into Laravel, so here’s a dangerous example:

❌ Vulnerable

$users = DB::select("SELECT * FROM users WHERE email = '$email'");

Even inside Laravel, raw queries are dangerous.


6. The Proper Defense


✅ 1. Use Prepared Statements (Parameterized Queries)

Safe PHP (MySQLi)

$stmt = $conn->prepare(
    "SELECT * FROM users WHERE username = ? AND password = ?"
);

$stmt->bind_param("ss", $username, $password);
$stmt->execute();

Here:

  • ? are placeholders

  • Input is sent separately

  • Database treats it strictly as data

Even if user enters:

' OR 1=1 --

It becomes a string value — not executable SQL.


✅ 2. PDO Example (Better Practice)

$stmt = $pdo->prepare(
    "SELECT * FROM users WHERE username = :username AND password = :password"
);

$stmt->execute([
    'username' => $username,
    'password' => $password
]);


✅ 3. Laravel Eloquent (Safe by Default)

$user = User::where('email', $email)->first();

Eloquent automatically uses parameter binding.


7. Additional Protection Measures

✔ Validate Input

  • Use type casting:

    $id = (int) $_GET['id'];

✔ Limit Database Privileges

  • App user should NOT have:

    • DROP privileges

    • SUPER privileges

✔ Hide Database Errors

Disable error display in production.

✔ Use Web Application Firewall (WAF)


8. Why Escaping Is Not Enough

Old method:

$username = mysqli_real_escape_string($conn, $_POST['username']);

This is better than nothing but:

  • Easy to misuse

  • Not reliable across encodings

  • Doesn’t protect against all cases



9. Blind SQL Injection (Concept Overview)

Sometimes application:

  • Does not show errors

  • Does not show query results

Attacker then uses logic-based queries:

Example:

?id=1 AND 1=1
?id=1 AND 1=2

If page behavior changes, attacker can extract data bit by bit.

This is slower but still dangerous.

Previous
Next