SQL Injection Prevention: Complete Guide with Code Examples

What is SQL Injection?

SQL injection is one of the most critical web vulnerabilities, affecting 39% of web applications according to OWASP. It occurs when an attacker inserts malicious SQL code into input fields, allowing them to manipulate database queries.

Real-World Impact

• Target (2013): Lost 40 million credit card numbers via SQL injection
• Healthcare.gov (2015): Exposed 21 million ID numbers
• Equifax (2017): 147 million records compromised

---

Types of SQL Injection

1. Error-Based SQL Injection

Displays database errors to extract information.

Example SQL:

SELECT * FROM users WHERE username = 'admin' AND password = 'pass'
-- Attack: admin' OR '1'='1
-- Result: Bypasses authentication

2. Union-Based SQL Injection

Merges attacker's query with original query to extract data.

3. Blind SQL Injection

No direct error feedback. Attacker infers data via response times/behavior.

4. Stacked Queries

Executes multiple SQL statements (if supported by database).

---

Prevention Methods

Method 1: Parameterized Queries (BEST)

Use parameter placeholders instead of string concatenation.

Python Example:

cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

Node.js Example:

connection.query('SELECT * FROM users WHERE id = ?', [userId])

Java Example:

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM users WHERE id = ?");
stmt.setString(1, userId);
ResultSet rs = stmt.executeQuery();

Method 2: Input Validation

Validate all user inputs server-side:

• Allowlist expected characters
• Enforce data types
• Limit input length

Method 3: Web Application Firewall

Deploy WAF rules to detect SQL injection patterns:

SecRule ARGS "@rx (?i:union.*select|select.*from|insert.*into|delete)"

Method 4: Least Privilege Database Users

Create database users with minimal necessary permissions:

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE ON database.* TO 'app_user'@'localhost';
-- Never grant DROP, ALTER, or administrative privileges

---

Detection & Testing

Using OWASP ZAP

OWASP ZAP automatically scans for SQL injection vulnerabilities.

Using SQLMap

Command-line tool for detecting SQL injection:

sqlmap -u "http://target.com/page?id=1" --dbs

Manual Testing Payloads

• ' OR '1'='1
• admin' --
• 1 UNION SELECT NULL, NULL
• ' AND SLEEP(5) --

---

SQL Injection Prevention Checklist

• Use parameterized queries exclusively
• Implement server-side input validation
• Use ORMs when possible (Sequelize, Hibernate, SQLAlchemy)
• Apply principle of least privilege to DB users
• Enable SQL error suppression in production
• Deploy Web Application Firewall
• Implement comprehensive logging
• Regular penetration testing
• Security code reviews
• Keep database software updated

---

Key Takeaways

1. Always use parameterized queries - No exceptions, no concatenation
2. Input validation is NOT enough - Use as defense-in-depth only
3. Database user permissions matter - Limit what app can do
4. Monitor for suspicious activity - Detection is critical
5. Test regularly - Automated + manual testing essential

---

Resources

• OWASP SQL Injection: https://owasp.org/www-community/attacks/SQL_Injection
• CWE-89: SQL Injection
• PortSwigger SQL Injection Guide
• NIST Secure Coding Standards