Raw Queries and Dynamic SQL in ORMs
You'll learn how even ORMs can become vulnerable when you bypass their safe defaults with raw SQL or dynamic string building.
The Escape Hatch Problem
ORMs like SQLAlchemy, Django ORM, or Entity Framework protect you by generating parameterized queries automatically. But they all offer "escape hatches" for complex queries—raw SQL execution. When you use these features carelessly, you reintroduce the very SQL injection vulnerabilities the ORM was designed to prevent.
Unsafe ORM Patterns
Pattern 1: Raw queries with string concatenation
# UNSAFE - Django ORM raw query
User.objects.raw(f"SELECT * FROM users WHERE email = '{user_input}'")
Pattern 2: String concatenation in filters
# UNSAFE - SQLAlchemy filter
db.query(User).filter(f"username = '{user_input}'")
Both examples look innocuous but allow injection attacks because user_input flows directly into SQL strings.
The Safe Approach
When you must use raw queries, use parameterization:
# SAFE - parameterized raw query
User.objects.raw("SELECT * FROM users WHERE email = %s", [user_input])
The ORM still handles parameter binding securely, even though you're writing SQL yourself.
When to Use Raw Queries Safely
Legitimate cases include:
- Database-specific functions not supported by the ORM
- Performance-critical queries with complex joins
- Bulk operations requiring native SQL
Always ask: "Can the ORM's query builder do this?" If yes, use it. If no, use parameterized raw queries.
Key Takeaway: ORMs protect you by default, but raw query features bypass that safety—always use parameterized placeholders (like %s or ?) instead of string concatenation or f-strings when executing raw SQL through an ORM.