tools-only SQL Injection Prevention
Never construct SQL queries with string concatenation or f-strings. Always use parameterized queries to prevent SQL injection attacks.
Why This Matters
SQL injection is one of the most common and dangerous web vulnerabilities. Attackers can:
- Access unauthorized data
- Modify or delete database records
- Execute admin operations on the database
- In some cases, issue commands to the OS
❌ Incorrect
Problem: User input directly interpolated into SQL query.
def get_user(user_id):
query = f"SELECT * FROM users WHERE id = {user_id}"
result = db.execute(query)
return result
# Vulnerable to: get_user("1 OR 1=1")
# Executes: SELECT * FROM users WHERE id = 1 OR 1=1
# Returns all users!
Why it's dangerous:
- Attacker can inject arbitrary SQL
- Can bypass authentication
- Can extract entire database
- Simple user input becomes code execution
✅ Correct
Solution: Use parameterized queries (prepared statements).
def get_user(user_id: int) -> Optional[Dict[str, Any]]:
"""Safely retrieve user by ID.
Args:
user_id: User ID to look up
Returns:
User record or None if not found
"""
query = "SELECT * FROM users WHERE id = ?"
result = db.execute(query, (user_id,))
return result.fetchone() if result else None
# Safe: user_id is treated as data, not code
# Even malicious input is harmless
Why it's safe:
- Parameters are escaped automatically
- Input treated as data, never as code
- Database driver handles sanitization
- No way to inject SQL syntax
Framework-Specific Solutions
SQLAlchemy (Python)
from sqlalchemy import select, text
# ✅ Using ORM
user = session.query(User).filter(User.id == user_id).first()
# ✅ Using Core with parameters
query = select(users).where(users.c.id == user_id)
# ✅ Using text() with bound parameters
query = text("SELECT * FROM users WHERE id = :id")
result = session.execute(query, {"id": user_id})
Django (Python)
# ✅ Using ORM
User.objects.get(id=user_id)
# ✅ Using raw SQL with parameters
User.objects.raw("SELECT * FROM users WHERE id = %s", [user_id])
Node.js (PostgreSQL)
// ✅ Using parameterized query
const result = await client.query(
'SELECT * FROM users WHERE id = $1',
[userId]
);
Node.js (MySQL)
// ✅ Using placeholder
const [rows] = await connection.execute(
'SELECT * FROM users WHERE id = ?',
[userId]
);
Additional Best Practices
Validate input types
def get_user(user_id: int) -> Optional[User]: if not isinstance(user_id, int): raise ValueError("user_id must be an integer") # ... queryUse ORMs when possible
- ORMs handle parameterization automatically
- Reduces risk of manual SQL errors
- Provides abstraction and type safety
Principle of least privilege
- Database users should have minimal permissions
- Read-only accounts for SELECT operations
- Limits damage from successful injection
Input validation as defense-in-depth
- Parameterization is primary defense
- Validation provides additional layer
- Whitelist allowed characters/patterns