[AGENT:Claude] Parameterize SQL queries in database_cleanup_service.py — SQL injection fix

[groupthinking]

Task Specification

Branch: refactor/hybrid-infra-v2
Agent: Claude (Claude Sonnet 4.6)
Verification: @coderabbitai security review + bandit scan

---

Intent

Parameterize all raw SQL string concatenation in database_cleanup_service.py to eliminate SQL injection vulnerabilities. This is a critical security fix — the current code constructs SQL queries by f-string interpolation with user-controllable input.

Requirements

1. Primary file: src/youtube_extension/backend/services/database_cleanup_service.py
2. Pattern to find: f-strings or .format() used to build SQL queries
3. Replace with: Parameterized queries using ? placeholders (SQLite) or %s (PostgreSQL/MySQL depending on driver)
4. Check the DB driver: Determine which database library is used (sqlite3, asyncpg, psycopg2, sqlalchemy) and use its native parameterization
5. Also scan: All files in src/youtube_extension/backend/services/ for the same pattern

Code Pattern (Before → After)

BEFORE (vulnerable):

query = f"DELETE FROM videos WHERE channel_id = '{channel_id}' AND created_at < '{cutoff_date}'"
cursor.execute(query)

AFTER (safe):

query = "DELETE FROM videos WHERE channel_id = ? AND created_at < ?"
cursor.execute(query, (channel_id, cutoff_date))

Files to Modify

• src/youtube_extension/backend/services/database_cleanup_service.py (primary)
• src/youtube_extension/backend/services/*.py (scan all for SQL injection patterns)
• Add regression tests in tests/test_database_cleanup.py

Acceptance Criteria

# No f-string SQL construction
grep -rn 'f".*SELECT\|f".*INSERT\|f".*DELETE\|f".*UPDATE' src/youtube_extension/backend/ # must return empty

# Bandit security scan passes
bandit -r src/youtube_extension/backend/ -ll  # zero high-severity findings

# Existing tests still pass
pytest tests/ -k "database" -v  # all pass

Self-Correction Context

Security severity: CRITICAL

• Any user-controllable input (channel_id, video_id, date ranges) that flows into these queries can be exploited
• This was flagged by CodeRabbit in a previous review but never addressed
• The fix must be backward-compatible (same query results, just parameterized)

IMPORTANT

• Do NOT change query logic or add new features
• Do NOT refactor the service architecture
• ONLY parameterize the existing queries
• Keep the same function signatures

[linear-code]

MYX-53

[github-actions]

👋 Thanks for opening this issue! It has been automatically labeled for triage.

[coderabbitai]

🔗 Related PRs

#207 - fix(security): SECRET_KEY, CORS wildcard, SQL injection, command injection (#193 #195 #196 #197 #200) [merged]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!

[groupthinking]

Assigning to Claude agent. Critical security fix — SQL injection via f-string interpolation. Work on branch refactor/hybrid-infra-v2. PR should target that branch. @coderabbitai please review the resulting PR for security completeness.

[coderabbitai]

🧩 Analysis chain

🏁 Script executed:

#!/bin/bash
# Check for open PRs targeting refactor/hybrid-infra-v2
gh pr list --repo groupthinking/EventRelay --base refactor/hybrid-infra-v2 --state all --json number,title,state,url,headRefName | jq .

echo "---"
# Also check what branch we're on and look for SQL injection patterns
git branch -a | grep -i hybrid || true

echo "---"
# Scan for f-string SQL injection patterns
rg -n --type py 'f["\'].*SELECT|f["\'].*INSERT|f["\'].*UPDATE|f["\'].*DELETE|f["\'].*DROP|f["\'].*CREATE' -C 3 || true

echo "---"
# Broader scan for execute with f-strings
rg -n --type py '\.execute\(f["\']' -C 3 || true

echo "---"
# Check for raw string formatting in SQL
rg -n --type py '\.execute\(.*%.*\)' -C 3 || true

Length of output: 1150

---

🏁 Script executed:

#!/bin/bash
# Check for open PRs targeting refactor/hybrid-infra-v2
gh pr list --repo groupthinking/EventRelay --base refactor/hybrid-infra-v2 --state all --json number,title,state,url,headRefName | jq .

echo "--- branches ---"
git branch -a | grep -i hybrid || echo "no hybrid branches found locally"

echo "--- f-string SQL patterns ---"
rg -n --type py -e 'execute\(f"' -e "execute\(f'" -C 3 || true

echo "--- % format SQL patterns ---"
rg -n --type py -e '\.execute\(' -C 2 || true

echo "--- raw SQL string concat ---"
rg -n --type py -e 'SELECT.*\+' -e 'INSERT.*\+' -e 'UPDATE.*\+' -e 'DELETE.*\+' -C 2 || true

Length of output: 784

---

🏁 Script executed:

#!/bin/bash
# Just check for PRs targeting the branch via GitHub API
gh pr list --repo groupthinking/EventRelay --base refactor/hybrid-infra-v2 --state all --json number,title,state,url,headRefName | jq .

Length of output: 346

---

---

@groupthinking — noted. There's no open PR targeting refactor/hybrid-infra-v2 yet, so the Claude agent likely hasn't submitted it. Once the PR is opened against that branch, I'll automatically review it.

When it lands, here's what I'll specifically audit for security completeness:

1. SQL injection via f-strings — every cursor.execute(f"...") or equivalent must be replaced with parameterized queries (e.g., cursor.execute("...", (param,))). I'll flag any remaining f-string, %-format, or string-concatenation SQL construction.
2. ORM raw query escaping — any raw(), RawSQL(), or extra() calls that interpolate user input.
3. Completeness across files — the fix shouldn't be isolated to one file; I'll check all modules that touch the database layer.
4. Input validation upstream — parameterized queries prevent injection, but type/range validation on inputs is defense-in-depth and should be present.
5. No secret exposure introduced — patch shouldn't inadvertently log query params or expose credentials in error messages.
6. Test coverage — at minimum a test that passes a SQL metacharacter (', ", ;, --) as input and asserts it's handled safely.

Tag me in the PR once it's up and I'll give it a full security review. 🔒