security-audit

# Security Audit Perform a security audit of the backend codebase, focusing on vulnerabilities that could be exploited by external attackers. Check every category below systematically. ## Audit Categories ### 1. SQL & Query Performance (DoS via slow queries) Scan all files in `src/query-builders/` and any raw SQL in `src/controllers/` or `src/utils/`. For each query, check: - **CTE materialization**: Does any `ORDER BY` on a CTE wrapper force full materialization before `LIMIT` is applied? PostgreSQL must fully materialize and sort before it can limit, turning a lazy evaluation into a full scan. - **Missing LIMIT**: Are there queries that return unbounded results? An attacker who controls data volume (e.g., pushing keys to a Stellar contract) can force massive result sets. - **Recursive CTEs without termination guards**: Could a recursive query run indefinitely or for an attacker-controlled number of iterations? - **Sequential scalar subqueries**: Does the query pattern cause N+1-style execution where N is attacker-controlled? - **Missing indexes**: Are WHERE/ORDER BY clauses hitting columns without indexes? Check against the Prisma schema. For each issue found, explain: - What the vulnerability is - How an attacker could exploit it (concrete steps) - The impact (connection pool exhaustion, CPU saturation, memory, etc.) - A suggested fix ### 2. Connection Pool Exhaustion Check `src/utils/connect.ts` and database configuration: - Is the Prisma connection pool explicitly configured, or relying on defaults? - Is there a `statement_timeout` set on the database connection or individual queries? - Are there any long-running queries that could hold connections hostage? - Could concurrent requests to any endpoint exhaust the pool? ### 3. Rate Limiting Gaps Check `src/index.ts` and all route files in `src/routes/`: - Is rate limiting applied globally? What are the limits? - Are expensive endpoints (queries that do full table scans, recursive CTEs, external API calls) protected with stricter per-route rate limits? - Can the rate limit be bypassed (e.g., via headers, missing trust proxy config)? ### 4. Input Validation & Injection Check all controllers in `src/controllers/` and route definitions: - Are user inputs validated before being used in queries? - Is `Prisma.raw()` used with user-controlled values? (This bypasses parameterization) - Are there any string concatenation patterns in SQL construction? - Are query parameters, path parameters, and body fields all validated? ### 5. Resource Exhaustion (non-SQL) Check controllers and middleware: - Are response payloads bounded? Could an endpoint return gigabytes of data? - Are there any endpoints that make external API calls without timeouts? - Is `server.requestTimeout` appropriate for all endpoints? - Could an attacker trigger expensive computation (e.g., large JSON parsing, regex backtracking)? ### 6. Information Disclosure Check error handling and responses: - Do error responses leak stack traces, internal paths, or database details? - Does the `/health` endpoint expose sensitive information in production? - Are there debug modes that could be enabled by an attacker? ## Output Format Present findings as a prioritized list: ``` ## Findings ### [CRITICAL/HIGH/MEDIUM/LOW] Title - **Location**: file:line - **Vulnerability**: What's wrong - **Exploit scenario**: How an attacker would use this - **Impact**: What happens if exploited - **Suggested fix**: How to fix it ``` If no issues are found in a category, briefly state that the category was checked and is clean. ## Known Past Vulnerabilities (for reference) - **March 2026**: `ORDER BY` on recursive CTE wrapper in `buildKeysQuery` (`src/query-builders/keys.ts`) forced full materialization, enabling DoS via connection pool exhaustion. Fixed by removing the redundant `ORDER BY`.