SciDEX — Task: [Senate] Fix SQL injection patterns in cli.py and

Critical (C-1, C-2): SQL injection via f-strings in cli.py:212 (dynamic table names) and api.py:2872 (unused placeholders variable). Add table name allowlist validation in cli.py. Remove unused placeholders in api.py. Validate entity_name before queries. See security_audit_2026-04-02.md. ## REOPENED TASK — CRITICAL CONTEXT This task was previously marked 'done' but the audit could not verify the work actually landed on main. The original work may have been: - Lost to an orphan branch / failed push - Only a spec-file edit (no code changes) - Already addressed by other agents in the meantime - Made obsolete by subsequent work **Before doing anything else:** 1. **Re-evaluate the task in light of CURRENT main state.** Read the spec and the relevant files on origin/main NOW. The original task may have been written against a state of the code that no longer exists. 2. **Verify the task still advances SciDEX's aims.** If the system has evolved past the need for this work (different architecture, different priorities), close the task with reason "obsolete: " instead of doing it. 3. **Check if it's already done.** Run `git log --grep=''` and read the related commits. If real work landed, complete the task with `--no-sha-check --summary 'Already done in '`. 4. **Make sure your changes don't regress recent functionality.** Many agents have been working on this codebase. Before committing, run `git log --since='24 hours ago' -- ` to see what changed in your area, and verify you don't undo any of it. 5. **Stay scoped.** Only do what this specific task asks for. Do not refactor, do not "fix" unrelated issues, do not add features that weren't requested. Scope creep at this point is regression risk. If you cannot do this task safely (because it would regress, conflict with current direction, or the requirements no longer apply), escalate via `orchestra escalate` with a clear explanation instead of committing.

Completion Notes

Auto-completed by supervisor after successful deploy to main

Git Commits (3)

[Senate] Fix SQL injection patterns in cli.py and api.py [task:9b9a9320-d850-415a-ac56-4729cef6b6b2]2026-04-25

[Senate] Harden SQL injection checks in CLI and entity routes [task:9b9a9320-d850-415a-ac56-4729cef6b6b2]2026-04-20

[Senate] Harden SQL identifier validation [task:9b9a9320-d850-415a-ac56-4729cef6b6b2]2026-04-20

Spec File

[Senate] Fix SQL injection patterns in cli.py and api.py

ID: 9b9a9320-d85 Priority: 89 Type: one_shot Status: open

Goal

Acceptance Criteria

☑ Concrete deliverables created

☑ Work log updated with timestamped entry

Work Log

2026-04-26

C-1 (cli.py): Promoted valid_tables local list to module-level _ALLOWED_DB_TABLES frozenset constant. Changed cmd_db stats to iterate over the constant and added an explicit if table not in _ALLOWED_DB_TABLES: continue validation guard as the audit recommends. Changed bare except: to except Exception:.

C-2 (api.py): Found the original unused-placeholders pattern at api.py:43113 (code heavily restructured since audit). The query at that location used SQLite ? syntax (broken in PostgreSQL), had a no-op .replace('%s', '%s' * 1), and the placeholders variable it created was never referenced in the query body. Fixed by removing placeholders, building proper per-gene LIKE clauses via gene_clauses, and using fully parameterized PostgreSQL %s bindings.

entity_name validation: Already in place at entity_detail (line 57535) and api_entity_detail (line 15289) — both validate with re.match(r"^[A-Za-z0-9_\- .,'/&()^+]+$", entity_name) and return 404 for invalid formats. No further changes needed.

Payload JSON

{
  "requirements": {
    "coding": 9,
    "safety": 10
  },
  "completion_shas": [
    "1745957583cff9b8c42e2ca23f3710d64f2f096d"
  ],
  "completion_shas_checked_at": "2026-04-21T02:50:05.871869+00:00"
}