[Security/Medium]: SQL injection in realtime API via unescaped tags parameter

[sulthonzh]

Description

The realtime streams API endpoint (realtime.v1.runs) constructs a PostgreSQL WHERE clause by directly interpolating user-provided tags without SQL escaping. This creates a SQL injection vulnerability in the /realtime/v1/runs endpoint.

Vulnerability Details

File: apps/webapp/app/services/realtimeClient.server.ts

Function: streamRuns()

Vulnerable code (line 171):

if (params.tags) {
  whereClauses.push(`"runTags" @> ARRAY[${params.tags.map((t) => `'${t}'`).join(",")}]`);
}

The tags array from user-provided search parameters is mapped to wrapped strings without escaping, allowing injection of SQL characters.

Steps to Reproduce

1. Trigger a request to /realtime/v1/runs?tags=<payload> with valid authentication
2. Observe that the tags parameter is used to construct a SQL WHERE clause
3. The unescaped tags are then sent to Electric (PostgreSQL) in the query parameter where

Example malicious payload (demonstrates syntax injection):

?tags=test' OR '1'='1

This would construct:

"runTags" @> ARRAY['test' OR '1'='1']

Impact

• SQL injection in realtime streams API
• Potential to manipulate query logic beyond intended tag filtering
• Could lead to information disclosure or denial of service
• Affects authenticated users with read:tags or read:runs permissions

Mitigating factors:

• Requires valid JWT authentication with appropriate permissions
• The WHERE clause is within an ARRAY literal context and uses the @> (array contains) operator, which limits but does not prevent injection
• Tags parameter is validated through Zod schema as string().optional() before use

Suggested Fix

Use parameterized queries or proper SQL escaping. Two approaches:

Option 1: Escape tags using a proper SQL escape function

if (params.tags) {
  const escapedTags = params.tags.map((t) => `'${t.replace(/'/g, "''")}'`).join(",");
  whereClauses.push(`"runTags" @> ARRAY[${escapedTags}]`);
}

Option 2: Use parameterized queries (if Electric supports them)

// Electric/Sync implementation would need to support parameterized WHERE clauses
// This is the preferred approach but may require backend changes

Option 3: Validate and sanitize tags

if (params.tags) {
  // Ensure tags only contain safe characters
  const sanitizedTags = params.tags.filter(t => /^[a-zA-Z0-9_-]+$/.test(t));
  whereClauses.push(`"runTags" @> ARRAY[${sanitizedTags.map((t) => `'${t}'`).join(",")}]`);
}

Recommend implementing Option 1 as an immediate fix and Option 2 for long-term security.

Environment

• Version: 3.0.0 (commit 596a9bb)
• OS: macOS (review environment)
• Node.js: v25.9.0

Additional Notes

Similar patterns may exist in other realtime endpoints (realtime.v1.runs.$runId, realtime.v1.batches.$batchId). A code-wide audit of whereClause construction is recommended.