Luke a Pro

Luke Sun

Developer & Marketer

šŸ‡ŗšŸ‡¦
EN|ē®€|ē¹
Ā© 2015ā€“2026 Luke Sun.
All rights reserved.

SQL Injection (SQLi)

Published: Sat Feb 01 2025 | Modified: Fri Feb 06 2026 , 3 minutes reading.

1. Definition

SQL Injection (SQLi) occurs when untrusted user input is dynamically concatenated into a Structured Query Language (SQL) statement. This allows an attacker to manipulate the query structure, enabling them to view, modify, or delete data they are not authorized to access.

2. Technical Explanation

SQL interpreters process commands and data together. If the application constructs queries using string manipulation:

query = "SELECT * FROM users WHERE username = '" + user_input + "'";

An attacker can input ' OR '1'='1. The resulting query becomes:

SELECT * FROM users WHERE username = '' OR '1'='1';

Since '1'='1' is always true, the database returns all rows in the table, bypassing the username check.

Types of SQLi:

  • In-band (Classic): Results are returned in the webpage (e.g., UNION-based).
  • Blind: No data is returned, but the app behaves differently (Time-based or Boolean-based).
  • Out-of-band: Data is exfiltrated via DNS or HTTP requests (rare, db-specific).

3. Attack Flow (Union Based)

sequenceDiagram
    participant Attacker
    participant App
    participant DB as Database

    Attacker->>App: GET /search?q=' UNION SELECT username, password FROM users--
    
    Note over App: Code constructs query:<br/>SELECT title FROM products WHERE name = '' UNION ... --'
    
    App->>DB: Executes malformed query
    
    Note over DB: 'UNION' combines results from 'products' and 'users'
    
    DB-->>App: Returns product list + user credentials
    App-->>Attacker: Displays admin passwords in search results

4. Real-World Case Study: TalkTalk Hack (2015)

Target: TalkTalk (UK Telecom Provider). Vulnerability Class: Blind SQL Injection.

The Breach: Attackers found legacy web pages on TalkTalkā€™s domain that were not maintained but still connected to the core customer database. These pages used a GET parameter that was vulnerable to SQL injection.

The Technique: They used SQLMap (an automated tool) to exploit a Blind SQLi vulnerability.

  • They injected SQL commands to extract database schema information.
  • They dumped personal details of 156,959 customers and 15,000+ bank account numbers.

Impact: TalkTalk suffered a Ā£400,000 fine (a record at the time) and estimated costs of Ā£77 million in lost business and remediation. It highlighted the risk of ā€œZombieā€ legacy code.

5. Detailed Defense Strategies

A. Prepared Statements (Parameterized Queries)

This is the only primary defense. Instead of string concatenation, use the database driverā€™s parameterization feature.

  • Mechanism: The SQL query structure is sent to the DB first and compiled. The user input is sent later and treated strictly as data, not executable code.

  • Example (Java/JDBC):

    String query = "SELECT * FROM users WHERE username = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, userInput); // Safe
ResultSet results = pstmt.executeQuery();

B. Stored Procedures

Encapsulate queries inside the database. However, stored procedures themselves must use parameters and avoid dynamic SQL generation inside the procedure.

C. Least Privilege

The database account used by the web application should only have permissions to:

  • Access the specific tables it needs.
  • Execute only necessary commands (SELECT, INSERT, UPDATE).
  • NEVER have DROP TABLE, GRANT, or administrative rights.
  • NEVER allow accessing the OS file system (e.g., xp_cmdshell).

6. References