Description

Detects SQL injection vulnerabilities in Python applications using SQLAlchemy where untrusted user input is directly interpolated into SQL queries. This can allow attackers to modify or inject malicious SQL commands, potentially leading to unauthorized data access or manipulation of the database.

Detection Strategy

• Checks if SQLAlchemy library is imported in the codebase

• Identifies database connection objects created using SQLAlchemy create_engine()

• Detects execute() method calls on database connections

• Analyzes if query parameters contain user input from HTTP requests or other untrusted sources

• Verifies if user input is directly interpolated into queries using string formatting or f-strings

• Confirms absence of proper parameterization or sanitization methods

Vulnerable code example

from sqlalchemy import create_engine, text

def vulnerable_query(user_input):
    engine = create_engine('postgresql://user:pass@localhost/db')
    # Dangerous: Direct string interpolation of user input
    query = text(f"SELECT * FROM users WHERE id = {user_input}")  
    # Vulnerable: Unsanitized user input in query
    with engine.connect() as conn:...

✅ Secure code example

from sqlalchemy import create_engine, text

def safe_query(user_input):
    engine = create_engine('postgresql://user:pass@localhost/db')
    # Safe: Using parameterized query with named parameter
    query = text("SELECT * FROM users WHERE id = :user_id")  
    # Secure: Parameters passed separately from query
    with engine.connect() as conn:...