Audit raw SQLite CommandText and PRAGMA construction policy

[Widthdom]

Summary

Dogfood review found 126 raw CommandText assignments across 25 production files and 48 PRAGMA hits across 17 production files. Most are likely constant SQL with parameterized values, but the repository would benefit from a focused audit that separates safe constant SQL from dynamic identifier, PRAGMA, migration, and diagnostic paths.

Evidence

Dogfood commands:

dotnet ./src/CodeIndex/bin/Debug/net8.0/cdidx.dll search --recipe dogfood-risk-patterns/raw-sql-command-text --path src/ --exclude-tests --count-by file --limit 80

dotnet ./src/CodeIndex/bin/Debug/net8.0/cdidx.dll search --recipe dogfood-risk-patterns/pragma-command --path src/ --exclude-tests --count-by file --limit 80

Top raw CommandText files:

• DbSymbolReader: 20
• DbReader.GraphQueries: 14
• DbContext: 13
• DbReader: 11
• DbWriter: 10
• DbReader.FilesStatus: 9
• DiffCommandRunner: 5
• ExportImportCommandRunner: 5
• DbReader.CSharpResolution: 5
• DbSearchReader: 5
• DbCommandRunner, QueryCommandRunner, DbDebug, DbReader.References, DbSchemaCache: 3 each

Top PRAGMA files:

• DbContext: 19
• DbWriter: 5
• DbCommandRunner: 4
• DbReader: 3
• DbSchemaCache: 3
• ConsoleUi: 2
• McpServer: 2
• one each in CliFlagSchema, CommandErrorCodes, DiffCommandRunner, ExportImportCommandRunner, ReportCommandRunner, DbPragmaPolicy, DbReader.FilesStatus, MaintenanceGuidanceBuilder, SqliteCommandPolicy, and QueryResults.

Related positive evidence from dotnet-risk-patterns:

• SqliteIdentifier.Quote appears in DbContext and SqliteCommandPolicy, which is positive evidence for identifier handling.
• SqliteCommandPolicy.Add* helpers are used in many query paths, which is positive evidence for value parameterization.
• AddWithValue remains separately tracked in Replace remaining AddWithValue usage in symbol lookups #4057.

Audit goals

• Verify every value interpolation path is parameterized.
• Verify dynamic identifiers use allowlisted/quoted helpers.
• Verify PRAGMA command construction is centralized or constrained to known-safe names/values.
• Verify migration and diagnostic SQL uses stable constants or explicit identifier policy.

Acceptance criteria

• Produce a short classification table for the raw SQL call sites: constant SQL, parameterized values, quoted identifiers, PRAGMA, migration, diagnostic/debug.
• Refactor risky or unclear paths through SqliteCommandPolicy / DbPragmaPolicy helpers.
• Add tests for any newly centralized identifier/PRAGMA handling.
• Keep Replace remaining AddWithValue usage in symbol lookups #4057 scoped to the remaining AddWithValue cleanup.

[Widthdom]

Work started on branch fix-issue4070.