🔴 HIGH: SQL Injection in user preference queries

SQL Injection Vulnerability

Severity: HIGH (CVSS 8.6)

File: database/user_preferences.py:78-82

Vulnerable Code

def get_user_preferences(user_id):
    # ⚠️ VULNERABLE: String interpolation in SQL query
    query = f"SELECT * FROM user_preferences WHERE user_id = {user_id}"
    result = db.execute(query)  # SQL INJECTION!
    return result.fetchall()

Exploitation

# Normal request
user_id = "12345"
get_user_preferences(user_id)
# Query: SELECT * FROM user_preferences WHERE user_id = 12345

# Malicious request
user_id = "12345 OR 1=1; DROP TABLE user_preferences;--"
get_user_preferences(user_id)
# Query: SELECT * FROM user_preferences WHERE user_id = 12345 OR 1=1; DROP TABLE user_preferences;--
# Result: Entire table deleted!

Impact

• Data Breach: Attacker can dump entire database
• Data Loss: DROP TABLE commands
• Privilege Escalation: Access other users' data
• Compliance: GDPR/SOC2 violation

Affected Endpoints

• GET /api/preferences/{user_id} (PUBLIC API)
• POST /api/recommendations/feedback (user_id param)
• GET /api/history/{user_id} (PUBLIC API)

Remediation

def get_user_preferences_safe(user_id):
    # ✅ SAFE: Parameterized query
    query = "SELECT * FROM user_preferences WHERE user_id = ?"
    result = db.execute(query, (user_id,))  # Parameters escaped automatically
    return result.fetchall()

Testing

9 pytest tests currently FAILING due to this vulnerability:

tests/test_security.py::test_sql_injection_user_id FAILED
tests/test_security.py::test_sql_injection_item_id FAILED
tests/test_security.py::test_sql_injection_category FAILED
...

Action Items

• Fix all string interpolation in SQL queries (12 files)
• Use parameterized queries or ORM (SQLAlchemy)
• Add input validation and sanitization
• Run SQLMap security scan
• Fix failing security tests

Timeline: 1 week

cc: @dmitry @sabrina