InfoSec Notes

SQL Injection Attacks and Prevention

Complete guide to SQL injection vulnerabilities covering attack types, exploitation techniques, prevention with parameterized queries, and real-world case studies.

What is SQL Injection?

SQL Injection (SQLi) is a code injection technique that exploits vulnerabilities in applications that construct SQL queries using unsanitized user input. An attacker can manipulate queries to access, modify, or delete unauthorized data, bypass authentication, or execute system commands.

SQL Injection Mechanism

==========================

Normal Query

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

Injected Query (input: ' OR '1'='1):

SELECT * FROM users WHERE username = '' OR '1'='1' AND password=[REDACTED_PASSWORD]' OR '1'='1'

This returns ALL users because '1'='1' is always true!

Types of SQL Injection

Type	Description	Detection Method
In-band (Classic)	Results returned in application response	Direct observation
Error-based	Database errors reveal information	Error messages
Union-based	UNION SELECT to extract data from other tables	Additional data in response
Blind (Boolean)	No data returned, infer from true/false responses	Response differences
Blind (Time-based)	No visible difference, use time delays	Response timing
Out-of-band	Data exfiltrated via DNS/HTTP to attacker server	External channel

Attack Examples

python example

# === VULNERABLE CODE (NEVER DO THIS) ===

def login_vulnerable(username, password):
    """VULNERABLE: Direct string concatenation in SQL."""
    query = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"
    # If username = "admin' --"
    # Query becomes: SELECT * FROM users WHERE username = 'admin' --' AND password = ''
    # The -- comments out the rest, bypassing password check!
    return db.execute(query)

def search_vulnerable(search_term):
    """VULNERABLE: User input in LIKE clause."""
    query = f"SELECT * FROM products WHERE name LIKE '%{search_term}%'"
    # If search_term = "'; DROP TABLE users; --"
    # This could delete the entire users table!
    return db.execute(query)


# === ATTACK PAYLOADS ===

attack_payloads = {
    "Auth bypass": "admin' OR '1'='1' --",
    "Auth bypass 2": "' OR 1=1 --",
    "Union extract": "' UNION SELECT username, password FROM users --",
    "Error-based": "' AND 1=CONVERT(int, (SELECT TOP 1 table_name FROM information_schema.tables)) --",
    "Time-based blind": "' IF(1=1) WAITFOR DELAY '0:0:5' --",
    "Stacked queries": "'; DROP TABLE users; --",
    "File read (MySQL)": "' UNION SELECT LOAD_FILE('/etc/passwd'), NULL --",
}

# === SECURE CODE ===

def login_secure(username, password):
    """SECURE: Using parameterized queries."""
    query = "SELECT * FROM users WHERE username = %s AND password = %s"
    return db.execute(query, (username, password))

def search_secure(search_term):
    """SECURE: Parameterized LIKE query."""
    query = "SELECT * FROM products WHERE name LIKE %s"
    return db.execute(query, (f"%{search_term}%",))


# === ORM APPROACH (Django) ===
# Django ORM automatically parameterizes queries
# User.objects.filter(username=username, password=password_hash)
# This is inherently safe against SQL injection

Prevention Methods

Code example

SQL Injection Prevention Hierarchy:
======================================

1. Parameterized Queries (BEST - use always)
   cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

2. Stored Procedures (with parameterized calls)
   cursor.callproc('get_user', (user_id,))

3. Input Validation (defense in depth)
   - Whitelist allowed characters
   - Validate data types
   - Enforce length limits

4. ORM Usage (abstracts SQL away)
   User.objects.get(id=user_id)

5. WAF Rules (last line of defense)
   - Detect common SQLi patterns
   - Block suspicious requests

6. Least Privilege (limit damage)
   - App DB user has minimal permissions
   - No DROP/ALTER privileges
   - Separate read/write accounts

Real-World Case Study: Heartland Payment Systems (2008)

Attack: SQL injection into a web-facing application led to access to internal payment processing network.

Impact: 130 million credit card numbers stolen, $140 million in losses.

Lessons:

Single SQLi vulnerability compromised entire network
Emphasizes need for network segmentation
Input validation must happen at every layer
Regular penetration testing would have found this

Python SQLi Prevention Framework

Example

import re import sqlite3 from typing import Any, Tuple class SecureDatabase: """Database wrapper enforcing SQL injection prevention.""" def __init__(self, db_path: str): self.conn = sqlite3.connect(db_path) self.conn.execute("PRAGMA journal_mode=WAL") def safe_query(self, query: str, params: tuple = ()) -> list: """Execute query with parameterized inputs only.""" # Reject queries with string formatting indicators if any(marker in query for marker in ['%s', 'f"', "f'", '.format(']): raise SecurityError("Use ? placeholders for parameters") cursor = self.conn.execute(query, params) return cursor.fetchall() def validate_input(self, value: str, allowed_pattern: str = r'^[a-zA-Z0-9_.-]+$') -> str: """Validate input against whitelist pattern.""" if not re.match(allowed_pattern, value): raise ValueError(f"Input contains disallowed characters: {value}") return value def safe_insert(self, table: str, data: dict): """Safe INSERT with automatic parameterization.""" # Whitelist table name (prevent injection in table names) if not re.match(r'^[a-zA-Z_][a-zA-Z0-9_]*$', table): raise ValueError(f"Invalid table name: {table}") columns = ', '.join(data.keys()) placeholders = ', '.join(['?' for _ in data]) query = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})" self.conn.execute(query, tuple(data.values())) self.conn.commit() class SecurityError(Exception): pass

Interview Questions

What is the difference between in-band and blind SQL injection?

In-band SQLi returns results directly in the application response (you can see extracted data). Blind SQLi shows no direct output — you infer information through boolean conditions (page changes) or time delays (WAITFOR DELAY). Blind is harder to exploit but equally dangerous.

Why are parameterized queries effective against SQL injection?

Parameterized queries separate SQL code from data. The database engine compiles the query structure first, then treats all parameters as literal data values — never as SQL code. Even if the input contains SQL syntax, it's treated as a string value, not executed.

Can an ORM fully prevent SQL injection?

ORMs prevent SQLi for standard queries since they auto-parameterize. However, raw SQL queries through the ORM (e.g., Django's .raw() or .extra()) can still be vulnerable. Always use parameterized queries even in raw ORM queries.

How would you detect SQL injection attempts in logs?

Look for: single quotes, SQL keywords (UNION, SELECT, DROP), comment markers (-- , /*), boolean logic (OR 1=1), time functions (SLEEP, WAITFOR), semicolons in inputs, and unusual error rates. WAFs and SIEM correlation rules can automate this detection.

What is second-order SQL injection?

Data is safely stored initially but later used unsafely in another query. Example: A username containing SQL is stored safely, but when an admin page builds a query using that stored username without parameterization, the injection executes. Prevention: parameterize ALL queries, even with "trusted" stored data.

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for SQL Injection Attacks and Prevention.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this Information Security topic.

Search Terms

information-security, information security, information, security, web, sql, injection, sql injection attacks and prevention

Continue learning this concept

Web SecurityWeb Authentication MechanismsComprehensive guide to web authentication including sessions, JWT, OAuth 2.0, and passwordless authentication methods.Web SecurityCross-Site Scripting (XSS)Complete guide to XSS vulnerabilities including reflected, stored, and DOM-based XSS attacks with prevention techniques, CSP headers, and practical exploitation examples.Web SecurityCross-Site Request Forgery (CSRF)Understanding CSRF attacks, how they exploit authenticated sessions, prevention with tokens and SameSite cookies, and implementing anti-CSRF protection.Web SecurityFile Upload SecuritySecuring file upload features against malicious uploads including shell injection, path traversal, MIME type attacks, and implementing proper server-side validation.Web SecurityHTTPS and SSL/TLSDeep dive into HTTPS, SSL/TLS protocol handshake, certificate verification, cipher suites, and secure transport layer communication.Web SecuritySecure Coding Practices — Information SecurityEssential secure coding practices for web developers covering input validation, output encoding, error handling, and OWASP guidelines for building resilient applications.

PreviousSession Management Security NextIntroduction to Web Security