Security Wave 3: Architectural (prepared statements, TLS, statement-shape validator)

[jrosskopf]

Part of #21.

Goal: the higher-effort items that meaningfully shift flAPI's security posture. Schedule deliberately — these are larger refactors, not weekend changes.

Tasks

• W3.1 — Prepared-statement path for scalar parameters. PR A landed the standalone helpers (SqlParameterClassifier + PreparedTemplateRewriter) in commit 8bf073d. PR B (ca16217) wires them into SQLTemplateProcessor → QueryExecutor::executeWithBindings → DatabaseManager::executeQuery, so int / double / boolean / date / time / uuid / enum / email / string fields are bound via duckdb_bind_* (NUL-preserving duckdb_bind_varchar_length for strings). Empty binding plans fall back to the historic string-execute path with no behaviour change. validateInt was tightened to reject trailing garbage so values like 1; DROP TABLE can no longer slip through as 1.

• W3.2 — TLS in embedded server. Wired in commit e38c715 via app.ssl_file(https.ssl_cert_file, https.ssl_key_file) at src/api_server.cpp:294. Integration test: test/integration/test_tls_wireup.py.

• W3.3 — Demote regex SQL-injection validator for prepared fields. Implemented in ca16217. When a field classifies to a non-Varchar prepared type (Integer / Double / Boolean / Date / Time) the keyword regex is demoted to a debug-level log line — the prepared bind is the hard defense. Varchar-classified fields (string / uuid / email / enum) keep the regex because flAPI templates still routinely embed them via triple-brace {{{ ... }}} for LIKE patterns. Operators can opt out per-field via preventSqlInjection: false.

Verify

test/integration/test_sql_injection_corpus.py — 37 parameterised end-to-end payloads against int-typed and string-typed endpoints. Every classic injection pattern (UNION, OR 1=1, '--, xkcd 327, comment-evasion) returns zero rows; legitimate values still match. Local run: 580/580 unit, 402/402 integration.

Closing — Wave 3 complete.