Python Framework Security Patterns

Python Framework Security Patterns

Python frameworks emphasize explicit security through clear APIs:

# Django ORM - Security through abstraction
from django.db.models import Q, F, Count
from django.db import connection
from datetime import datetime, timedelta

class DjangoSecureQueries:
    @staticmethod
    def secure_user_search(search_term, filters):
        # Django ORM automatically escapes all parameters
        queryset = User.objects.all()
        
        # Text search - automatically parameterized
        if search_term:
            queryset = queryset.filter(
                Q(username__icontains=search_term) |
                Q(email__icontains=search_term) |
                Q(profile__full_name__icontains=search_term)
            )
        
        # Complex filters with automatic SQL injection prevention
        if filters.get('created_after'):
            queryset = queryset.filter(date_joined__gte=filters['created_after'])
        
        if filters.get('roles'):
            queryset = queryset.filter(groups__name__in=filters['roles'])
        
        # Aggregation queries - also secure
        return queryset.annotate(
            order_count=Count('orders'),
            last_order_date=Max('orders__created_at')
        ).select_related('profile').prefetch_related('groups')
    
    @staticmethod
    def raw_query_example(user_id, status):
        # Raw SQL with parameter substitution
        with connection.cursor() as cursor:
            # Use %s placeholders, not string formatting
            cursor.execute("""
                SELECT u.id, u.username, COUNT(o.id) as order_count
                FROM users_user u
                LEFT JOIN orders_order o ON u.id = o.user_id
                WHERE u.id = %s AND o.status = %s
                GROUP BY u.id, u.username
            """, [user_id, status])
            
            columns = [col[0] for col in cursor.description]
            return [dict(zip(columns, row)) for row in cursor.fetchall()]
    
    @staticmethod
    def extra_select_safe(category_id):
        # Even 'extra' queries can be parameterized
        return Product.objects.extra(
            select={
                'is_featured': "featured_until > %s",
                'discount_amount': "price * (discount_percentage / 100)"
            },
            select_params=[datetime.now()],
            where=["category_id = %s"],
            params=[category_id]
        )

# SQLAlchemy - Flask and others
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import select, and_, or_

class SQLAlchemySecureQueries:
    def __init__(self, database_url):
        self.engine = create_engine(database_url)
        self.Session = sessionmaker(bind=self.engine)
    
    def secure_filter_example(self, filters):
        session = self.Session()
        try:
            # Query construction with automatic parameterization
            query = session.query(Product)
            
            if filters.get('name'):
                query = query.filter(Product.name.like(f"%{filters['name']}%"))
            
            if filters.get('min_price'):
                query = query.filter(Product.price >= filters['min_price'])
            
            if filters.get('categories'):
                query = query.filter(Product.category_id.in_(filters['categories']))
            
            # Complex conditions
            if filters.get('availability') == 'in_stock':
                query = query.filter(
                    and_(
                        Product.stock > 0,
                        Product.is_active == True
                    )
                )
            
            return query.all()
        finally:
            session.close()
    
    def raw_sql_safe_example(self, user_id, order_status):
        with self.engine.connect() as conn:
            # Using bound parameters with raw SQL
            result = conn.execute(
                text("""
                    SELECT users.name, COUNT(orders.id) as order_count
                    FROM users
                    LEFT JOIN orders ON users.id = orders.user_id
                    WHERE users.id = :user_id
                      AND orders.status = :status
                    GROUP BY users.name
                """),
                {"user_id": user_id, "status": order_status}
            )
            return list(result)