Implement SQL query safety validation to prevent injection vulnerabilities

[moshesham]

Problem

In infra/AnswerValidator.py, SQL queries are executed without safety validation. While queries are currently loaded from trusted files, there's no protection if user input is ever accepted in the future.

Location

File: infra/AnswerValidator.py, line 85

def execute_query(self) -> None:
    # ...
    self.answer_df = pd.read_sql_query(self.query, conn, index_col=None)

Security Concern

• Potential SQL injection if user input is ever accepted
• No validation that queries don't contain malicious patterns
• Future feature additions could introduce vulnerabilities

Proposed Solution

Add query validation before execution:

def _validate_query_safety(self, query: str) -> bool:
    """
    Validate that query doesn't contain dangerous patterns.
    
    Args:
        query: SQL query string to validate
        
    Returns:
        True if query is safe
        
    Raises:
        SecurityError: If query contains forbidden operations
    """
    # Only allow SELECT queries for practice platform
    forbidden_keywords = ['DROP', 'DELETE', 'UPDATE', 'INSERT', 
                         'ALTER', 'CREATE', 'TRUNCATE', 'EXEC']
    
    query_upper = query.strip().upper()
    
    # Ensure query starts with SELECT or WITH (for CTEs)
    if not (query_upper.startswith('SELECT') or query_upper.startswith('WITH')):
        raise SecurityError("Only SELECT queries are allowed")
    
    # Check for forbidden keywords
    for keyword in forbidden_keywords:
        if keyword in query_upper:
            raise SecurityError(f"Query contains forbidden operation: {keyword}")
    
    return True

Tasks

• Create SecurityError exception class
• Implement _validate_query_safety() method
• Call validation in execute_query() before execution
• Add unit tests for query validation
• Add tests for malicious query detection
• Document security assumptions in code comments
• Update README with security documentation

Impact

• Prevents potential SQL injection attacks
• Makes future feature development safer
• Establishes security best practices

References

• PROJECT_REVIEW.md Issue Fix requirements.txt - Remove stdlib modules and add version constraints #8
• OWASP SQL Injection Prevention: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html