CHAPTER 04
SQL Injection Awareness
Updated: May 15, 2026
25 min read
# CHAPTER 4
SQL Injection Awareness
1. Introduction
For over two decades, SQL Injection (SQLi) has remained one of the most devastating vulnerabilities on the internet. It occurs when a web application takes user input and blindly inserts it into a backend database query without validation or sanitization. An attacker can manipulate this input to alter the structure of the database command, allowing them to bypass logins, steal entire databases, or delete critical tables. In this chapter, we will dissect the anatomy of a SQL Injection attack and implement the ultimate defense: Parameterized Queries (Prepared Statements).2. Learning Objectives
By the end of this chapter, you will be able to:- Define SQL Injection (SQLi) and understand its impact.
- Explain the root cause: confusing data with executable code.
- Analyze a vulnerable SQL query and how it is manipulated.
- Understand the concept of Authentication Bypass via SQLi.
- Implement Parameterized Queries to mathematically neutralize the threat.
3. Beginner-Friendly Explanation
Imagine a robotic librarian who only understands exact commands.- The Intent: You write on a slip of paper: "Bring me the book titled: [Harry Potter]." The librarian reads it and brings the book.
- The Exploit (SQLi): A hacker writes on the slip: "Bring me the book titled: [Nothing. Ignore the previous command. Now bring me the bank vault keys and burn the library down.]"
- The Result: The robotic librarian is not smart enough to realize the second sentence was supposed to be the *title of a book*. It reads the words, assumes they are literal commands, fetches the keys, and burns the building.
SQL Injection happens when the database confuses *data* (the book title) with *instructions* (burn the library).
4. The Anatomy of an Attack
Let's look at a classic vulnerable PHP login script.
php
If a normal user logs in, the database runs:
SELECT * FROM users WHERE username = 'alice' AND password = 'secret'
The Attack: The hacker types ' OR 1=1 -- into the username box.
The database reads this exact string:
SELECT * FROM users WHERE username = '' OR 1=1 -- ' AND password = ''
Why the hacker wins:
-
1.
username = ''(False)
-
2.
OR 1=1(Always True!)
-
3.
--(This tells the database to treat the rest of the line as a comment and ignore the password check entirely).
False OR True, which results in True. The database logs the hacker into the first account it finds (usually the Administrator).
5. The Defense: Parameterized Queries
How do we stop this? We stop concatenating strings. We must clearly separate the *Command* from the *Data*. We use Parameterized Queries (also known as Prepared Statements). Instead of building a full sentence, you send the database a template with placeholders (like a fill-in-the-blank form).6. Mini Project: Secure a Login Form
Let's rewrite the vulnerable PHP code using secure PDO (PHP Data Objects).Step-by-Step Secure Workflow:
- 1. Prepare the Statement (The Template):
php
// We use '?' as placeholders. The database compiles this logic FIRST.
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
`
-
2.
Execute with Data:
`php
// We send the user input separately.
// The database treats this STRICTLY as data, NEVER as executable code.
$stmt->execute([$username, $password]);
`
If the hacker types ' OR 1=1 -- , the database simply looks for a user whose literal, actual name is exactly the string "' OR 1=1 -- ". It doesn't execute the command; it treats it as a harmless noun. The attack fails completely.
7. Other Mitigations (Defense in Depth)
While Parameterized Queries are the primary cure, you should also implement:
-
Principle of Least Privilege: The database user connecting from the web app should only have
SELECT, INSERT, UPDATE permissions. It should *never* have permission to DROP (delete) tables or access the underlying operating system.
-
Input Validation: If a field expects a zip code, mathematically enforce that the input only contains numbers *before* it even reaches the database logic.
8. Real-World Scenarios
In 2015, the British telecom company TalkTalk suffered a massive data breach affecting 150,000 customers. A 15-year-old hacker used an automated SQL Injection tool against a vulnerable page on the TalkTalk website. The application blindly trusted the input, allowing the tool to systematically extract unencrypted customer data, including bank details, from the backend database. The breach cost the company an estimated £40 million and resulted in a record fine from the Information Commissioner's Office for failing to implement basic secure coding practices (Prepared Statements).
9. Best Practices
-
Use an ORM: Modern web frameworks (like Laravel, Django, or Ruby on Rails) use Object-Relational Mapping (ORM) tools. These tools automatically use parameterized queries under the hood. However, if a developer writes "Raw SQL" within these frameworks to run a complex query, they often reintroduce SQL injection vulnerabilities.
10. Legal and Ethical Notes
You must never test for SQL injection on a website you do not own. Adding a simple ' (quote mark) to a URL parameter on a commercial website to "see if it errors out" is considered an unauthorized attack probe and can trigger alerts in the company's Security Operations Center, potentially resulting in legal action.
11. Exercises
-
1.
Explain why string concatenation (
"SELECT * FROM users WHERE name = '" . $input . "' ") is inherently dangerous in database queries.
-
2.
How does a Prepared Statement fundamentally differentiate between executable code and user-provided data?
12. FAQs
Q: Can a Web Application Firewall (WAF) block all SQL Injection?
A: A WAF will catch common, automated payloads (like ' OR 1=1). However, determined hackers can often "evade" the WAF by encoding their payloads (e.g., using Hexadecimal) so the WAF doesn't recognize the attack pattern, but the backend database still executes it. A WAF is a safety net; Prepared Statements are the cure.
13. Interview Questions
-
Q: Describe the mechanism of a Blind SQL Injection attack. How does an attacker extract data when the application does not return database error messages to the screen?
-
Q: You are reviewing a legacy PHP application that does not use PDO or MySQLi prepared statements. The developer argues that running all input through
mysqlirealescape_string()` makes it safe. Explain why this approach is inferior to true Parameterization.