Skip to content

Archived: 2025-11-01 Reason: Completed - Bug fix implemented and deployed Original Ticket: BUG-004

SQL Injection Vulnerability Fix - Implementation Plan

1. Executive Summary & Feature Overview

1.1 Feature Description

  • Feature Name: SQL Injection Vulnerability Remediation & Search Input Sanitization Utility
  • Feature Type: Critical Security Bug Fix + Infrastructure Enhancement
  • Priority Level: CRITICAL
  • Estimated Timeline: 2-3 hours

1.2 Problem Statement

  • Current State: Multiple search endpoints across the application use Django ORM's icontains lookup with unsanitized user input from request.GET.get(). While Django ORM provides parameterization protection, the lack of input validation creates several risks:
  • DoS attacks via extremely long search strings or regex-like patterns
  • Information leakage through error messages
  • Potential bypass of ORM protections through edge cases

  • SQL wildcard characters (%, _) can cause unexpected behavior

  • Pain Points:

  • Security vulnerability in production code
  • No centralized input sanitization strategy
  • Multiple vulnerable endpoints (7+ locations identified)

  • Each endpoint duplicates sanitization logic (if any exists)

  • User Impact:

  • All users performing searches (authenticated and unauthenticated)
  • Platform stability affected by potential DoS

  • Producer data potentially exposed through search enumeration

  • Business Value:

  • Prevents security incidents and data breaches
  • Ensures platform reliability and uptime
  • Maintains customer trust and compliance
  • Reduces technical debt through centralized utility

1.3 Expected Outcomes

  • Success Metrics:
  • Zero successful injection attacks in penetration testing
  • All identified vulnerable endpoints patched
  • Search performance maintained or improved

  • 100% test coverage for sanitization utility

  • User Experience Goals:

  • No degradation in search functionality
  • Searches remain fast and relevant

  • Error messages remain helpful but don't leak data

  • Technical Goals:

  • Reusable, well-tested sanitization utility
  • Consistent input validation across all search endpoints
  • Clear documentation for future development
  • Monitoring/logging for suspicious search patterns

2. Stakeholder Analysis & Requirements

2.1 Affected Users & Systems

  • Primary Users:
  • Event attendees searching for shows
  • Producers searching for shows, orders, and customers

  • Admin users in Django admin panel

  • Secondary Users:

  • API consumers (if public API exists)

  • Internal monitoring/security teams

  • System Components:

  • apps/api/portal/views.py - Producer portal views
  • apps/api/api/views.py - Public API views
  • apps/api/knowledge/views.py - Knowledge base/articles
  • apps/api/tickets/admin.py - Django admin

  • apps/api/utils/ - New utility module location

  • Integration Points:

  • Django ORM query construction
  • GET request parameter parsing
  • Error handling and logging systems

2.2 Functional Requirements

Must-Have Features: 1. Sanitize all search input to remove SQL wildcards (%, _, \) 2. Limit maximum search string length (200 characters recommended) 3. Strip whitespace from search inputs 4. Reject empty/whitespace-only searches 5. Apply sanitization to all 7+ identified vulnerable endpoints 6. Maintain current search functionality and relevance

Should-Have Features: 1. Logging of suspicious search patterns 2. Rate limiting consideration for search endpoints 3. Standardized error messages that don't leak information 4. Unit tests for edge cases and attack vectors

Could-Have Features: 1. Search query metrics/analytics 2. Advanced pattern detection (e.g., script tags, command injection attempts) 3. Configurable sanitization policies per endpoint 4. Search input normalization (Unicode, case handling)

Won't-Have Features: 1. Full-text search implementation changes 2. Frontend validation changes (backend-first approach) 3. Search algorithm optimization (separate concern)

2.3 Non-Functional Requirements

  • Performance:
  • Sanitization should add < 1ms overhead per request
  • No degradation in search query execution time

  • No N+1 query issues introduced

  • Security:

  • Protection against SQL injection via wildcards
  • Protection against DoS via length limiting
  • Protection against information leakage

  • Comprehensive test suite with known attack vectors

  • Accessibility:

  • Not applicable (backend-only change)

  • Browser/Platform Support:

  • Not applicable (backend-only change)

  • Reliability:

  • Must not break existing search functionality
  • Graceful handling of edge cases
  • No exceptions for valid user input

3. Current State Analysis

3.1 Codebase Research Methodology

Tools Used: - mcp__serena__search_for_pattern for finding all icontains usage - mcp__serena__search_for_pattern for finding all request.GET.get usage - Read tool for examining vulnerable code sections - mcp__serena__list_dir for understanding project structure

3.2 Existing Architecture & Patterns

  • Tech Stack:
  • Backend: Django (Python)
  • ORM: Django ORM with PostgreSQL

  • Framework: Django REST Framework (inferred)

  • Architecture Pattern:

  • Monolithic Django application with multiple apps
  • Class-based views and function-based views

  • RESTful API endpoints

  • Code Organization: 

    apps/api/
├── portal/        # Producer portal (authenticated)
├── api/           # Public API
├── knowledge/     # Knowledge base/articles
├── tickets/       # Ticket management + admin
├── utils/         # Shared utilities (TARGET for new code)
└── ...

  • Data Flow:

  • User submits search via GET request
  • View extracts search parameter: request.GET.get('search') or request.GET.get('q')
  • Search applied directly to queryset with .filter(Q(...__icontains=search))
  • Results serialized and returned

3.3 Relevant Existing Code

Identified Vulnerable Locations:

  1. apps/api/portal/views.py:614-619 (ORIGINAL REPORTED BUG) 

    search = request.GET.get('search')
if search:
    shows_queryset = shows_queryset.filter(
        Q(title__icontains=search) |
        Q(description__icontains=search)
    )

  2. apps/api/portal/views.py:1123-1130 (Orders search) 

    search = request.GET.get("search")
if search:
    orders_queryset = orders_queryset.filter(
        Q(first_name__icontains=search)
        | Q(last_name__icontains=search)
        | Q(email__icontains=search)
        | Q(id__icontains=search)
    )

  3. apps/api/api/views.py:27-36 (Public shows search - upcoming) 

    query = request.GET.get("q", "")
upcoming_shows = Show.objects.filter(
    Q(title__icontains=query)
    | Q(description__icontains=query)
    | Q(venue__name__icontains=query)
    | Q(venue__address__icontains=query)
    | Q(venue__city__icontains=query)
    | Q(venue__state__icontains=query)
    | Q(venue__zip_code__icontains=query),
    ...
)

  4. apps/api/api/views.py:42-48 (Public shows search - past) 

    past_shows = Show.objects.filter(
    Q(title__icontains=query)
    | Q(description__icontains=query)
    | Q(venue__name__icontains=query)
    ... # Same pattern as upcoming
)

  5. apps/api/knowledge/views.py:329-331 (Article search) 

    Q(title__icontains=query_text) |
Q(content__icontains=query_text) |
Q(excerpt__icontains=query_text)

  6. apps/api/tickets/admin.py:742-745 (Admin panel search) 

    Q(first_name__icontains=search_query)
| Q(last_name__icontains=search_query)
| Q(email__icontains=search_query)
| Q(uuid__icontains=search_query)

  7. apps/api/portal/filters.py:84-85 (Filter class) 

    models.Q(title__icontains=value) |
models.Q(description__icontains=value)

3.4 Current Dependencies

  • Core Dependencies:
  • Django (version TBD - check requirements.txt)
  • PostgreSQL database

  • Django REST Framework (inferred from API structure)

  • Development Dependencies:

  • pytest or Django TestCase (for testing)

  • No additional dependencies needed for this fix

  • Version Compatibility:

  • Solution must work with current Django version
  • No breaking changes to existing API contracts

3.5 Potential Conflicts & Constraints

  • Technical Debt:
  • No existing centralized input validation
  • Inconsistent search parameter naming (search vs q vs query)

  • No existing security utilities module

  • Legacy Code:

  • Django admin customization may have different update patterns

  • Filter classes vs view-based searches need different approaches

  • Resource Constraints:

  • Must maintain sub-second search response times

  • Cannot introduce database-level changes

  • Compliance Requirements:

  • OWASP Top 10 compliance (A03:2021 - Injection)
  • PCI DSS if payment data is searchable (not identified in search fields)

4. Research & Best Practices

4.1 Industry Standards Research

Security Best Practices:

  1. OWASP Input Validation Guidelines:
  2. Whitelist allowed characters when possible
  3. Set reasonable length limits
  4. Sanitize special characters that have semantic meaning

  5. Validate on server-side (never trust client)

  6. Django Security Best Practices:

  7. Django ORM provides parameterization, but doesn't validate input semantics
  8. Always validate and sanitize user input before ORM operations
  9. Use Django's built-in validators when applicable

  10. Log suspicious patterns for monitoring

  11. SQL Wildcard Protection:

  12. % - matches any sequence of characters (LIKE wildcard)
  13. _ - matches any single character (LIKE wildcard)
  14. \ - escape character in SQL
  15. These should be removed or escaped for icontains operations

4.2 Framework/Library Research

Django ORM icontains Behavior: - Translates to SQL LOWER(field) LIKE LOWER('%value%') - Already wraps input with % wildcards - User-provided % or _ characters can cause: - Over-broad matches (e.g., % matches everything) - Performance issues with complex patterns - Unexpected results for users

Official Documentation: - Django QuerySet API - Field lookups - Django Security Overview - Python re module for regex sanitization

4.3 Case Studies & Examples

Similar Implementations: - Most Django applications use a centralized utils/validators.py or utils/security.py - Common pattern: Create decorator or utility function that wraps input sanitization - Example from Django community: 

def sanitize_search_input(search_string, max_length=200):
    """Sanitize search input to prevent injection and DoS."""
    if not search_string:
        return None

    # Strip whitespace
    cleaned = search_string.strip()

    # Limit length
    cleaned = cleaned[:max_length]

    # Remove SQL wildcards
    cleaned = re.sub(r'[%_\\]', '', cleaned)

    # Return None if empty after sanitization
    return cleaned if cleaned else None

Performance Studies: - Regex substitution on strings < 200 chars: ~0.1-0.5ms - Negligible impact on overall request time

5. Solution Design

5.1 Proposed Architecture

High-Level Design:

  1. Create Centralized Utility (apps/api/utils/security.py):
  2. sanitize_search_input(value, max_length=200) - Main sanitization function
  3. safe_search_filter(queryset, search_value, *fields) - Optional helper for common pattern

  4. Comprehensive docstrings with usage examples

  5. Update All Vulnerable Endpoints:

  6. Import and apply utility before ORM queries
  7. Maintain existing functionality

  8. Add consistent null/empty handling

  9. Add Comprehensive Tests (apps/api/utils/tests/test_security.py):

  10. Unit tests for sanitization function
  11. Integration tests for attack vectors
  12. Edge case tests (Unicode, empty strings, etc.)

Data Model Changes: - None required

API Design: - No API contract changes - Internal implementation only

UI/UX Design: - No frontend changes - Users experience identical search behavior - Invalid searches return empty results gracefully

Integration Strategy: 

# Before (vulnerable):
search = request.GET.get('search')
if search:
    queryset = queryset.filter(Q(title__icontains=search))

# After (secure):
from apps.api.utils.security import sanitize_search_input

search = sanitize_search_input(request.GET.get('search'))
if search:
    queryset = queryset.filter(Q(title__icontains=search))

5.2 Technology Decisions

New Dependencies: - None - using Python standard library (re module)

Alternative Solutions Considered:

  1. Django Validators:
  2. ❌ Too heavyweight for this use case
  3. ❌ Raises exceptions rather than sanitizing

  4. ✅ Our approach: Silent sanitization is better UX for search

  5. Third-party sanitization libraries (bleach, etc.):

  6. ❌ Designed for HTML/XSS, not SQL wildcards
  7. ❌ Additional dependency overhead

  8. ✅ Our approach: Lightweight, purpose-built solution

  9. Database-level validation:

  10. ❌ Would require schema changes
  11. ❌ Doesn't address application-level DoS
  12. ✅ Our approach: Application-level protection

Proof of Concepts: - Not required - straightforward implementation

5.3 Security Considerations

Threat Model:

Threat Mitigation
SQL Injection via wildcards Remove %, _, \ characters
DoS via long strings Limit to 200 characters
DoS via regex patterns ORM handles this; length limit helps
Information leakage via errors Sanitization prevents malformed queries
Unicode bypass attacks Python re.sub handles Unicode

Authentication/Authorization: - No changes to existing auth - Public and authenticated endpoints both protected

Data Protection: - Prevents enumeration attacks via search - Maintains existing data access controls

Security Testing: - Penetration testing with OWASP attack strings - Fuzzing with random input - Edge case testing (see Test Plan section)

6. Implementation Plan

6.1 Development Phases

Phase 1: Foundation [Timeline: 30 minutes] - [x] Research and document vulnerable locations - [ ] Create apps/api/utils/security.py module - [ ] Implement sanitize_search_input() function - [ ] Add comprehensive docstrings - Deliverable: Reusable sanitization utility with documentation

Phase 2: Core Implementation [Timeline: 45 minutes] - [ ] Fix apps/api/portal/views.py:614 (show search - ORIGINAL BUG) - [ ] Fix apps/api/portal/views.py:1123 (order search) - [ ] Fix apps/api/api/views.py:27 (public API upcoming shows) - [ ] Fix apps/api/api/views.py:42 (public API past shows) - [ ] Fix apps/api/knowledge/views.py:329 (article search) - [ ] Fix apps/api/tickets/admin.py:742 (admin search) - [ ] Fix apps/api/portal/filters.py:84 (filter class) - Deliverable: All identified vulnerabilities patched

Phase 3: Testing & Documentation [Timeline: 45 minutes] - [ ] Create apps/api/utils/tests/test_security.py - [ ] Write unit tests for sanitization function - [ ] Write integration tests with attack vectors - [ ] Test all updated endpoints manually - [ ] Update any relevant documentation - Deliverable: Complete test coverage and verified fixes

6.2 Detailed Task Breakdown

Task Files Affected Est. Hours Dependencies Priority
Create security utility module apps/api/utils/security.py 0.25h None P0
Fix portal show search (ORIGINAL) apps/api/portal/views.py 0.15h Security module P0
Fix portal order search apps/api/portal/views.py 0.10h Security module P0
Fix public API show search apps/api/api/views.py 0.15h Security module P0
Fix knowledge article search apps/api/knowledge/views.py 0.10h Security module P0
Fix admin panel search apps/api/tickets/admin.py 0.10h Security module P1
Fix filter class apps/api/portal/filters.py 0.10h Security module P1
Create comprehensive tests apps/api/utils/tests/test_security.py 0.50h Security module P0
Manual testing & validation All above 0.25h All fixes P0
Documentation updates README, comments 0.15h All fixes P2

Total Estimated Time: 2.25 hours

6.3 File Change Summary

New Files: - apps/api/utils/security.py - Sanitization utility module - apps/api/utils/tests/ - Test directory (if doesn't exist) - apps/api/utils/tests/__init__.py - Test package init - apps/api/utils/tests/test_security.py - Security utility tests

Modified Files: - apps/api/portal/views.py - Add sanitization to 2 search locations - apps/api/api/views.py - Add sanitization to public search - apps/api/knowledge/views.py - Add sanitization to article search - apps/api/tickets/admin.py - Add sanitization to admin search - apps/api/portal/filters.py - Add sanitization to filter class

Deleted Files: - None

7. Testing Strategy

7.1 Test Coverage Plan

Unit Tests for sanitize_search_input():

# apps/api/utils/tests/test_security.py

class TestSanitizeSearchInput:
    def test_normal_search_string(self):
        """Normal search strings pass through."""
        assert sanitize_search_input("hello world") == "hello world"

    def test_removes_sql_wildcards(self):
        """SQL wildcards are removed."""
        assert sanitize_search_input("hello%world") == "helloworld"
        assert sanitize_search_input("hello_world") == "helloworld"
        assert sanitize_search_input("hello\\world") == "helloworld"

    def test_limits_length(self):
        """Strings longer than max_length are truncated."""
        long_string = "a" * 300
        result = sanitize_search_input(long_string, max_length=200)
        assert len(result) == 200

    def test_strips_whitespace(self):
        """Leading/trailing whitespace is removed."""
        assert sanitize_search_input("  hello  ") == "hello"

    def test_empty_string_returns_none(self):
        """Empty strings return None."""
        assert sanitize_search_input("") is None
        assert sanitize_search_input("   ") is None

    def test_none_returns_none(self):
        """None input returns None."""
        assert sanitize_search_input(None) is None

    def test_only_wildcards_returns_none(self):
        """Strings with only wildcards return None."""
        assert sanitize_search_input("%%%___") is None

    def test_sql_injection_attempts(self):
        """Known SQL injection strings are sanitized."""
        assert sanitize_search_input("'; DROP TABLE shows; --") == "'; DROP TABLE shows; --"  # No quotes/dashes removed
        assert sanitize_search_input("1' OR '1'='1") == "1' OR '1'='1"  # Safe with parameterization

    def test_unicode_strings(self):
        """Unicode strings are handled correctly."""
        assert sanitize_search_input("héllo wörld") == "héllo wörld"
        assert sanitize_search_input("你好世界") == "你好世界"

    def test_special_characters_allowed(self):
        """Non-wildcard special chars are allowed."""
        assert sanitize_search_input("hello@world.com") == "hello@world.com"
        assert sanitize_search_input("rock & roll") == "rock & roll"

Integration Tests:

class TestSearchEndpointSecurity:
    def test_portal_show_search_with_wildcards(self):
        """Portal show search rejects wildcards."""
        response = client.get('/api/portal/shows/?search=%')
        assert response.status_code == 200
        assert len(response.json()['results']) == 0  # Empty, not all shows

    def test_public_api_with_long_string(self):
        """Public API handles excessively long search strings."""
        long_search = "a" * 1000
        response = client.get(f'/api/search/?q={long_search}')
        assert response.status_code == 200
        # Should not timeout or error

    def test_knowledge_search_no_information_leakage(self):
        """Error messages don't leak database info."""
        response = client.get('/api/knowledge/?q=\' OR 1=1--')
        assert response.status_code == 200
        assert 'error' not in response.json() or 'database' not in response.json().get('error', '').lower()

Attack Vector Tests:

Test with these known malicious inputs: - '; DROP TABLE shows; -- - 1' OR '1'='1 - admin'-- - % (matches everything) - _ (matches any character) - \\ (escape character) - a * 10000 (very long string) - %_%_%_% (complex pattern) - Unicode edge cases: \u0000, \uffff

7.2 Test Environment Requirements

  • Development:
  • Local Django test database
  • pytest or Django TestCase

  • Mock user authentication if needed

  • Staging:

  • Full integration testing with real database
  • Penetration testing with OWASP ZAP or Burp Suite

  • Load testing with search endpoints

  • Production:

  • Feature flag for gradual rollout (optional)
  • Enhanced monitoring for first 48 hours

7.3 Acceptance Criteria

Definition of Done: - [x] All functional requirements implemented - [ ] sanitize_search_input() utility created and documented - [ ] All 7 identified vulnerable endpoints patched - [ ] All unit tests pass (15+ test cases) - [ ] All integration tests pass - [ ] Attack vector tests pass (0 successful injections) - [ ] Code review completed - [ ] Manual testing completed with malicious inputs - [ ] Performance benchmarks met (< 1ms overhead) - [ ] No search functionality regressions - [ ] Security review completed - [ ] Documentation updated

Specific Test Cases Must Pass: - ✅ Normal searches work identically to before - ✅ Wildcard characters removed from searches - ✅ Long strings (>200 chars) truncated - ✅ Empty searches return no results - ✅ All OWASP injection attempts blocked - ✅ Search response time < previous + 1ms - ✅ No database errors or exceptions

8. Risk Assessment & Mitigation

8.1 Technical Risks

Risk Probability Impact Mitigation Strategy
Breaking existing search functionality Medium High Comprehensive testing; feature flag for rollback
Performance degradation Low Medium Benchmark tests; optimize regex if needed
Incomplete coverage of vulnerable endpoints Medium High Thorough codebase search; peer review
Edge cases not handled (Unicode, etc.) Low Low Extensive unit tests with edge cases
Overly aggressive sanitization Medium Low Keep allowed characters broad; only remove SQL wildcards

8.2 Resource Risks

  • Timeline Risks:
  • Risk: Testing reveals additional vulnerable endpoints

  • Mitigation: Built buffer time; prioritize critical paths first

  • Skill Gaps:

  • Risk: Team unfamiliar with security best practices

  • Mitigation: Clear documentation; security training session

  • External Dependencies:

  • Risk: None identified (no external dependencies)

8.3 Rollback Strategy

  • Feature Flags:
  • Not required (backend-only, low risk)

  • If needed: Django settings variable to toggle sanitization

  • Database Migrations:

  • None required

  • Deployment Strategy:

  • Standard deployment (no special requirements)
  • Deploy during low-traffic period as precaution

  • Monitor error rates and search metrics for 24 hours

  • Rollback Plan:

  • Git revert to previous commit
  • Estimated rollback time: < 5 minutes
  • No data loss or migration rollback needed

9. Deployment & Operations

9.1 Deployment Plan

  • Environment Progression:
  • Dev: Immediate testing after implementation
  • Staging: 1-2 hours of testing + penetration testing

  • Production: Deploy after staging validation

  • Database Changes:

  • None required

  • Configuration Updates:

  • None required (could add SEARCH_MAX_LENGTH setting for configurability)

  • Monitoring Setup:

  • Add logging for sanitized searches (optional)
  • Monitor search endpoint error rates
  • Track search result counts (detect if all returning empty)

9.2 Monitoring & Observability

Key Metrics: - Search endpoint response times (should remain stable) - Search endpoint error rates (should remain stable or decrease) - 4xx/5xx error counts (should not increase) - Search result counts (should remain similar distribution)

Alerting: - Alert if search endpoint error rate > 5% (critical) - Alert if search response time > 2x baseline (warning) - Alert if 100% of searches return empty (critical - sanitization bug)

Logging: 

# Optional: Log suspicious patterns
if len(original_search) > 200 or any(c in original_search for c in '%_\\'):
    logger.warning(f"Suspicious search pattern detected: {original_search[:50]}")

Health Checks: - No specific health check changes needed - Existing endpoint health checks sufficient

9.3 Support & Maintenance

Documentation: - Inline code documentation (docstrings) - Usage examples in security.py - Update security practices documentation (if exists) - Add to developer onboarding materials

Training: - Team walkthrough of vulnerability and fix - Security best practices session (optional) - Update code review checklist to include input validation

Ongoing Maintenance: - Review sanitization logic during Django upgrades - Periodic security audits (quarterly recommended) - Monitor for new search endpoints in code reviews

10. Success Measurement

10.1 Success Metrics

Technical Metrics: - Before Fix: - Vulnerable endpoints: 7+ - Attack success rate: High (DoS possible, wildcards work) - Average search time: X ms

  • After Fix:
  • Vulnerable endpoints: 0
  • Attack success rate: 0% (all tests pass)
  • Average search time: X + 0.5ms (< 1ms overhead)
  • Test coverage: 100% for security module

User Metrics: - Search usage remains stable (no drop-off) - Search result relevance remains stable - No increase in user support tickets about search

Business Metrics: - Security audit findings: -7 critical vulnerabilities - Compliance status: Improved (OWASP A03:2021 addressed) - Platform uptime: Maintained or improved

10.2 Review Schedule

  • 1 Day Post-Deploy:
  • Review error logs
  • Verify no search functionality regressions

  • Confirm metrics stable

  • 1 Week Post-Deploy:

  • Full security audit/penetration test
  • User feedback analysis

  • Performance metrics review

  • 1 Month Post-Deploy:

  • Retrospective on implementation
  • Identify any missed vulnerabilities
  • Plan additional security hardening if needed

11. Appendices

Appendix A: Code Examples

Utility Implementation (apps/api/utils/security.py):

"""
Security utilities for input validation and sanitization.
"""
import re
from typing import Optional


def sanitize_search_input(
    search_value: Optional[str],
    max_length: int = 200
) -> Optional[str]:
    """
    Sanitize user search input to prevent SQL injection and DoS attacks.

    This function:
    - Strips leading/trailing whitespace
    - Limits length to prevent DoS
    - Removes SQL wildcard characters (%, _, \)
    - Returns None for empty/invalid input

    Args:
        search_value: The raw search string from user input
        max_length: Maximum allowed length (default: 200)

    Returns:
        Sanitized search string, or None if empty/invalid

    Examples:
        >>> sanitize_search_input("hello world")
        'hello world'

        >>> sanitize_search_input("hello%world")
        'helloworld'

        >>> sanitize_search_input("   ")
        None

        >>> sanitize_search_input("a" * 300)
        'aaa...'  # Truncated to 200 chars

    Security Notes:
        - Protects against SQL wildcard injection
        - Prevents DoS via extremely long strings
        - Does NOT protect against XSS (use appropriate output encoding)
        - Should be used with Django ORM parameterization (already present)
    """
    if not search_value:
        return None

    # Strip whitespace
    cleaned = search_value.strip()

    # Limit length to prevent DoS
    if len(cleaned) > max_length:
        cleaned = cleaned[:max_length]

    # Remove SQL wildcard characters
    # % matches any sequence of characters
    # _ matches any single character
    # \ is the escape character
    cleaned = re.sub(r'[%_\\]', '', cleaned)

    # Return None if nothing left after sanitization
    return cleaned if cleaned else None

Usage Example (Before & After):

# apps/api/portal/views.py

# BEFORE (VULNERABLE):
search = request.GET.get('search')
if search:
    shows_queryset = shows_queryset.filter(
        Q(title__icontains=search) |
        Q(description__icontains=search)
    )

# AFTER (SECURE):
from apps.api.utils.security import sanitize_search_input

search = sanitize_search_input(request.GET.get('search'))
if search:
    shows_queryset = shows_queryset.filter(
        Q(title__icontains=search) |
        Q(description__icontains=search)
    )

Appendix B: Attack Vectors Reference

Test these inputs to verify protection:

  1. SQL Injection Attempts:
  2. '; DROP TABLE shows; --
  3. 1' OR '1'='1
  4. admin'--

  5. ' UNION SELECT * FROM users--

  6. Wildcard Exploitation:

  7. % (should match nothing, not everything)
  8. _ (should match nothing)
  9. %test% (nested wildcards)

  10. test% (trailing wildcard)

  11. DoS Attempts:

  12. String of 10,000 characters
  13. String of 100,000 characters

  14. Complex regex patterns: %_%_%_%_%_%

  15. Edge Cases:

  16. Empty string: ``
  17. Whitespace only:
  18. Unicode: café, 你好
  19. Special chars: hello@world.com, rock & roll!
  20. Mixed: hello%_world 123

Expected Behavior After Fix: - SQL injection attempts: Safely handled by ORM + sanitization - Wildcards: Removed, search for literal text - Long strings: Truncated to 200 chars - Empty/whitespace: Returns no results (None) - Unicode/special: Works correctly

Appendix C: File Reference

All Files Requiring Changes:

apps/api/
├── utils/
│   ├── security.py              [NEW] - Sanitization utility
│   └── tests/
│       ├── __init__.py          [NEW if needed]
│       └── test_security.py     [NEW] - Tests
├── portal/
│   ├── views.py                 [MODIFY] - Lines ~614 and ~1123
│   └── filters.py               [MODIFY] - Line ~84
├── api/
│   └── views.py                 [MODIFY] - Lines ~27 and ~42
├── knowledge/
│   └── views.py                 [MODIFY] - Line ~329
└── tickets/
    └── admin.py                 [MODIFY] - Line ~742

Quick Implementation Checklist

Phase 1: Foundation ✓

  • Research complete
  • Create apps/api/utils/security.py
  • Implement sanitize_search_input() with docstrings
  • Create apps/api/utils/tests/test_security.py

Phase 2: Fix Vulnerabilities

  • Fix apps/api/portal/views.py:614 (show search) ⚠️ CRITICAL
  • Fix apps/api/portal/views.py:1123 (order search) ⚠️ CRITICAL
  • Fix apps/api/api/views.py:27 (public upcoming) ⚠️ CRITICAL
  • Fix apps/api/api/views.py:42 (public past) ⚠️ CRITICAL
  • Fix apps/api/knowledge/views.py:329 (articles)
  • Fix apps/api/tickets/admin.py:742 (admin)
  • Fix apps/api/portal/filters.py:84 (filters)

Phase 3: Testing & Validation

  • Write 15+ unit tests
  • Test all attack vectors
  • Manual testing of each endpoint
  • Performance benchmarking
  • Code review
  • Security review

Phase 4: Deployment

  • Deploy to staging
  • Penetration testing
  • Deploy to production
  • Monitor for 24 hours

Document Information: - Created: 2025-10-20 - Version: 1.0 - Author: Security Remediation Team - Status: READY FOR IMPLEMENTATION - Estimated Timeline: 2-3 hours - Priority: CRITICAL

This plan provides a complete roadmap for fixing the SQL injection vulnerability and creating a reusable security utility. The implementation follows Django best practices, includes comprehensive testing, and can be completed within the estimated 2-hour timeframe while providing long-term value through the reusable sanitization utility.