Fix SQL Injection (Legacy & Modern) in Pyramid

SQL Injection in Pyramid is the result of failing to separate data from instructions. Whether you are using raw DB-API connections or SQLAlchemy, the vulnerability arises when untrusted request parameters are concatenated directly into SQL strings. In a Pyramid context, this typically happens within views where 'request.params' are fed into 'dbsession.execute()'. To kill this bug, you must move from manual string interpolation to parameterized queries or ORM abstraction.

The Vulnerable Pattern

@view_config(route_name='user_profile', renderer='json')
def get_user(request):
    user_id = request.params.get('id')
    # VULNERABLE: F-strings or .format() allow attackers to inject SQL payloads
    # Attack: ?id=1' OR '1'='1
    sql = f"SELECT secret_data FROM users WHERE id = '{user_id}'"
    result = request.dbsession.execute(sql).fetchone()
    return {'data': result[0] if result else None}

The Secure Implementation

The vulnerable code treats user input as part of the SQL command, allowing an attacker to manipulate the query logic. The secure implementation uses 'parameterized queries'. By using the ':id' placeholder and passing a dictionary to 'execute()', SQLAlchemy ensures the database driver treats the input strictly as data. This prevents characters like single quotes from breaking the SQL context. For modern Pyramid apps, using the ORM (Query API) is the gold standard as it abstracts the SQL generation entirely, making injection nearly impossible unless you use 'literal_column' or 'text' unsafely.

from sqlalchemy import text
@view_config(route_name=‘user_profile’, renderer=‘json’)
def get_user(request):
user_id = request.params.get(‘id’)
# SECURE (Legacy/Raw Style): Use bind parameters via text()
query = text(“SELECT secret_data FROM users WHERE id = :id”)
result = request.dbsession.execute(query, {‘id’: user_id}).fetchone()
# SECURE (Modern/ORM Style): Use the SQLAlchemy Query API
# result = request.dbsession.query(User).filter(User.id == user_id).first()

return {'data': result[0] if result else None}</code></pre>