SQL Injection and How It Works

This lesson explains SQL injection, a common and dangerous attack technique covered in OCR J277 Section 1.4. SQL injection targets databases through web applications and can result in data theft, data modification, or complete system compromise.

---

What Is SQL?

SQL (Structured Query Language) is the standard language used to communicate with and manage relational databases. Web applications commonly use SQL to:

• Retrieve user data (e.g. usernames, passwords)
• Insert new records (e.g. registering a new user)
• Update existing records (e.g. changing a password)
• Delete records

For example, when you log in to a website, the application might run an SQL query like:

SELECT * FROM users WHERE username = 'john' AND password = 'secret123'

If this query returns a matching record, the user is granted access.

---

What Is SQL Injection?

SQL injection is an attack where malicious SQL code is inserted into input fields (such as login forms or search boxes) that are then passed to a database query. If the application does not properly validate or sanitise user input, the injected SQL is executed by the database server.

How SQL injection works — step by step:

1. A website has a login form with username and password fields.
2. The application builds an SQL query using the input directly:

   SELECT * FROM users WHERE username = '[input]' AND password = '[input]'
   

3. Instead of entering a genuine username, the attacker enters:
   ```sql
   ' OR 1=1 --
   

4. The resulting SQL query becomes:

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

5. OR 1=1 is always true, so the query returns all users. The -- is an SQL comment that ignores the rest of the query.
6. The attacker is logged in without a valid password.

OCR Exam Tip: You do not need to write SQL code in the exam, but you must be able to explain what SQL injection is and how it works. Describe it as: "entering malicious SQL code into an input field to manipulate the database query."

The flowchart below contrasts a safe login flow with one that is vulnerable to SQL injection.

flowchart TD
  A[User submits login form] --> B{"Input validated &<br/>parameterised?"}
  B -- Yes --> C["Database treats<br/>input as data"]
  C --> D[Query runs safely]
  D --> E{Credentials match?}
  E -- Yes --> F[Login granted]
  E -- No --> G[Login refused]
  B -- No --> H["Input concatenated<br/>into SQL string"]
  H --> I["Attacker injects<br/>’ OR 1=1 --"]
  I --> J[Query logic altered]
  J --> K["Authentication bypassed:<br/>data exposed"]
  style F fill:#d4f4dd
  style G fill:#fff4d4
  style K fill:#fde2e2

---

What Can SQL Injection Do?

Action	Description
Bypass authentication	Log in without valid credentials
Read sensitive data	Access usernames, passwords, personal information
Modify data	Change, insert, or delete records in the database
Execute admin operations	Shut down the database or escalate privileges
Access the file system	Read or write files on the server (in extreme cases)

Real-world impact:

• In 2015, the TalkTalk data breach exposed the personal data of 157,000 customers through an SQL injection attack.
• The company was fined £400,000 by the Information Commissioner's Office (ICO).

---

Why Does SQL Injection Work?

SQL injection exploits poor input validation. If a web application directly inserts user input into an SQL query without checking or sanitising it, the database cannot distinguish between legitimate commands and injected malicious code.

The root cause:

• The application trusts user input.
• User input is concatenated directly into SQL queries.
• No checks are performed to remove or escape special characters like ' and --.

---

How to Prevent SQL Injection

1. Input Validation / Sanitisation

Check all user input before using it in a query. Remove or escape special characters that could alter the SQL command.

2. Parameterised Queries (Prepared Statements)

Use parameterised queries where the SQL structure is defined first and user input is passed as parameters separately. This ensures the database treats input as data, not as executable code.

3. Access Rights

Limit the database permissions of the web application account. If the account only has read access, an attacker cannot modify or delete data even if injection succeeds.

4. Error Handling

Do not display detailed database error messages to users. Error messages can reveal table names, column names, and database structure that help attackers refine their injection.

Prevention Method	How It Helps
Input validation	Removes dangerous characters from input
Parameterised queries	Separates code from data — most effective defence
Limited access rights	Restricts what injected code can do
Error handling	Prevents attackers learning database structure

OCR Exam Tip: If asked how to prevent SQL injection, always mention input validation/sanitisation and parameterised queries — these are the two key answers the mark scheme expects. Explain that parameterised queries separate the SQL structure from user input.

---

Summary

SQL injection is a serious threat that targets web applications connected to databases. It works by inserting malicious SQL code into input fields to manipulate database queries. The most effective prevention methods are input validation and parameterised queries. For your OCR J277 exam, make sure you can explain the attack process, its potential impact, and at least two prevention methods.

---

Worked Example: Defending a Login Form Step by Step

Consider a fictional online bookshop, PageTurner Books, whose developer has been asked to harden the customer login form. The current code builds the database query by joining user input directly into a string — the classic pattern that enables SQL injection. The worked example below walks through how the team identifies the weakness and redesigns the login so injection is prevented by design.

Step 1 — Review the existing query. The developer sees that when a customer submits their email and password, the server constructs a query along the lines of SELECT * FROM users WHERE email = '[input email]' AND password_hash = '[input hash]'. Because the user input is dropped straight into the query string, any apostrophe or SQL keyword submitted by the customer will be parsed as part of the query rather than treated as data.