P1 findings: Database layer (raw BEGIN/COMMIT, batch_insert, WAL pragma, column injection)

[TusanHomichi]

Summary

Multiple P1-level issues found in the database layer.

Findings

1. Raw BEGIN/COMMIT instead of transaction API

• Location: conary-core/src/db/
• Issue: Manual SQL BEGIN/COMMIT statements bypass rusqlite's transaction API, risking nested transaction mismatches.
• Fix: Use Connection::transaction() consistently.

2. batch_insert performance

• Location: conary-core/src/db/
• Issue: Batch inserts not using prepared statements efficiently; re-preparing per row.
• Fix: Prepare once, execute many.

3. WAL pragma ordering

• Location: conary-core/src/db/
• Issue: WAL mode pragma set after other pragmas that depend on it, causing those pragmas to be ineffective.
• Fix: Set journal_mode=WAL before other performance pragmas.

4. Column name injection

• Location: conary-core/src/db/
• Issue: Dynamic column names constructed from user input without validation in some query builders.
• Fix: Validate column names against an allowlist.