SQL Injection (SQLi) remains one of the most critical web application vulnerabilities, consistently ranking in the OWASP Top 10. In this post, we’ll explore what SQL injection is, how it works, and how to protect against it.

What is SQL Injection?

SQL Injection is a code injection technique that exploits vulnerabilities in an application’s database layer. Attackers can manipulate SQL queries by inserting malicious SQL code through user input fields.

How Does It Work?

Consider a simple login form that constructs an SQL query like this:

1SELECT * FROM users WHERE username = 'admin' AND password = 'password123';

If the application doesn’t properly sanitize input, an attacker could enter:

1Username: admin' --
2Password: (anything)

This transforms the query into:

1SELECT * FROM users WHERE username = 'admin' -- ' AND password = 'anything';

The -- comments out the rest of the query, effectively bypassing authentication!

Types of SQL Injection

1. Classic SQLi (In-Band)

The attacker uses the same channel to inject SQL code and retrieve results.

1# Vulnerable Python code
2username = request.form['username']
3query = f"SELECT * FROM users WHERE username = '{username}'"
4cursor.execute(query)

2. Blind SQLi

The attacker doesn’t see direct output but can infer information based on application behavior.

1SELECT * FROM products WHERE id = 1 AND 1=1; -- Returns normally
2SELECT * FROM products WHERE id = 1 AND 1=2; -- Returns nothing

3. Out-of-Band SQLi

Uses different channels for injection and data retrieval (e.g., DNS or HTTP requests).

Real-World Example: Union-Based Attack

1' UNION SELECT null, username, password FROM users --

This technique combines results from the original query with a malicious query to extract data from other tables.

Prevention Techniques

1. Parameterized Queries (Prepared Statements)

Best Practice:

1# Secure Python code using parameterized queries
2username = request.form['username']
3cursor.execute("SELECT * FROM users WHERE username = ?", (username,))

1// Secure PHP code with PDO
2$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
3$stmt->execute(['username' => $username]);

2. Input Validation

Whitelist allowed characters and validate input length:

1function validateUsername(username) {
2    const regex = /^[a-zA-Z0-9_-]{3,20}$/;
3    return regex.test(username);
4}

3. Least Privilege Principle

Database users should only have permissions they need:

1-- Don't do this
2GRANT ALL PRIVILEGES ON *.* TO 'webapp'@'localhost';
3
4-- Do this instead
5GRANT SELECT, INSERT, UPDATE ON mydb.users TO 'webapp'@'localhost';

4. Web Application Firewalls (WAF)

Deploy WAFs to detect and block common SQL injection patterns.

Testing for SQL Injection

Warning: Only test applications you have permission to test!

Basic Test Payloads

 1'
 2''
 3`
 4``
 5,
 6"
 7""
 8/
 9//
10\
11\\
12;
13' OR '1
14' OR 1 -- -
15" OR "" = "
16" OR 1 = 1 -- -
17' OR '' = '

Tools for Testing

  • SQLMap: Automated SQL injection tool
  • Burp Suite: Web security testing platform
  • OWASP ZAP: Open-source web app scanner

SQLMap Example

1# Basic SQLMap usage
2sqlmap -u "http://example.com/page?id=1" --dbs
3
4# With authentication
5sqlmap -u "http://example.com/page?id=1" --cookie="SESSIONID=abc123" --dbs
6
7# Dump specific database
8sqlmap -u "http://example.com/page?id=1" -D mydb --tables

Key Takeaways

  1. Never trust user input - Always validate and sanitize
  2. Use parameterized queries - This is your primary defense
  3. Apply least privilege - Limit database permissions
  4. Keep software updated - Patch known vulnerabilities
  5. Monitor and log - Detect suspicious database activity

Resources

Conclusion

SQL Injection remains a critical threat, but it’s entirely preventable with proper coding practices. The key is to never construct SQL queries using string concatenation with user input. Always use parameterized queries and implement multiple layers of defense.

Stay curious, stay secure!