Complete SQL Injection Guide 2024: Prevention, Detection & Defense

Key Takeaways

Use parameterized queries—ALWAYS.
Never concatenate user input into SQL queries.
Input validation is defense-in-depth, not primary defense.

Use ORM frameworks that handle parameterization.
Least privilege for database accounts.
WAFs can help but don't replace secure coding.

1. What is SQL Injection?
2. Types of SQL Injection
3. Exploitation Techniques
4. Detection Methods

5. Prevention Techniques
6. Code Examples
7. Testing for SQLi
8. Frequently Asked Questions

1. What is SQL Injection?

SQL Injection (SQLi) occurs when attackers can insert or "inject" malicious SQL code into queries that an application sends to its database. It happens when user-supplied input is not properly validated or sanitized before being incorporated into SQL queries.

SQLi remains one of the most dangerous web vulnerabilities. It can lead to data theft, data manipulation, authentication bypass, and in some cases, complete server compromise.

Why SQLi Still Exists

Despite being well-understood for 20+ years, SQLi persists because:
• Legacy code using string concatenation
• Developer education gaps
• Rush to production without security review
• Incorrect library/framework usage

2. Types of SQL Injection

Type	Description	Visibility
In-band (Classic)	Error-based or UNION-based	Results visible in response
Blind	Boolean-based or time-based	No direct output
Out-of-band	Uses DNS/HTTP to exfiltrate	External channel

3. Exploitation Techniques

3.1 Classic SQLi Example

# Vulnerable code:
SELECT * FROM users WHERE username = '$username' AND password = '$password'

# Attack input:
username: admin' --
password: anything

# Resulting query:
SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything'
# -- comments out the rest, bypassing password check

3.2 UNION-Based SQLi

# Extracting data from other tables:
' UNION SELECT username, password FROM admin_users --

# First determine column count:
' ORDER BY 1 --
' ORDER BY 2 --
(continue until error)

3.3 Blind SQLi

# Boolean-based:
' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin')='a' --
# If true, page behaves normally; if false, different behavior

# Time-based:
' AND IF(1=1, SLEEP(5), 0) --
# If SQLi works, response delayed by 5 seconds

Impact of SQLi

SQLi can allow attackers to: read entire databases, modify/delete data, bypass authentication, escalate privileges, and potentially gain OS-level access (via xp_cmdshell in SQL Server or LOAD_FILE in MySQL).

4. Detection Methods

Static Analysis (SAST): Scan code for vulnerable patterns
Dynamic Analysis (DAST): Automated testing with payloads
WAF Logs: Monitor for SQLi attempt patterns
Database Audit: Unusual query patterns
Manual Testing: Pen testing, code review

5. Prevention Techniques

5.1 Primary Defense: Parameterized Queries

# Python (with SQLAlchemy)
result = db.execute(
    text("SELECT * FROM users WHERE id = :id"),
    {"id": user_input}
)

# PHP (PDO)
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$user_input]);

# Java (PreparedStatement)
PreparedStatement stmt = conn.prepareStatement(
    "SELECT * FROM users WHERE id = ?");
stmt.setInt(1, userId);

5.2 Additional Defenses

Input Validation: Whitelist allowed characters
Least Privilege: Database accounts with minimal permissions
WAF: Defense-in-depth, not primary
Error Handling: Don't expose database errors
Stored Procedures: Can help if parameterized

6. Code Examples

# VULNERABLE - Never do this!
query = f"SELECT * FROM users WHERE name = '{user_input}'"

# SAFE - Parameterized
cursor.execute("SELECT * FROM users WHERE name = %s", (user_input,))

7. Testing for SQLi

Tool	Type	Use Case
SQLMap	Automated	Finding and exploiting SQLi
Burp Suite	Manual/Auto	Web app testing
OWASP ZAP	Automated	Free scanner

Use Frameworks Properly

Modern frameworks and ORMs (Django, Rails, Spring) use parameterized queries by default. Use them correctly—don't bypass their protections with raw queries. When raw SQL is needed, still use parameterization.

8. Frequently Asked Questions

Is input validation enough to prevent SQLi?

No. Input validation helps but can often be bypassed. Parameterized queries are the only reliable primary defense. Use validation as defense-in-depth, not as the main control.

Do ORMs prevent SQL injection?

Generally yes, when used correctly. ORMs parameterize by default. However, most ORMs also allow raw SQL—if you use raw queries, you must still parameterize manually. Never concatenate user input.

Conclusion

SQL injection remains dangerous because the fix is simple but requires consistent application: use parameterized queries everywhere. Never concatenate user input into SQL. Modern frameworks make this easy—use them correctly. Combine with input validation, least privilege, and monitoring for defense in depth.

Continue Learning:
Web Security XSS Prevention