> ## Documentation Index
> Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Reporting Engine Security

> Last Updated: 2025-11-25 Status: ✅ Implemented

**Last Updated:** 2025-11-25\
**Status:** ✅ Implemented

## Overview

The Encore Health OS Reporting Engine allows users to create custom SQL queries for generating reports. This document outlines the security measures implemented to prevent SQL injection, data breaches, and performance abuse.

## Critical Security Measures

### 1. Parameterized Query Execution

**Problem:** String replacement of parameters (`{{param}}`) is vulnerable to SQL injection.

**Solution:** Use PostgreSQL prepared statements with numbered parameters (`$1`, `$2`, etc.).

```typescript theme={null}
// ❌ VULNERABLE
for (const [key, value] of Object.entries(params)) {
  sql = sql.replaceAll(`{{${key}}}`, value);
}

// ✅ SECURE
function buildParameterizedQuery(baseSql: string, params: Record<string, any>) {
  let paramIndex = 1;
  const paramValues = [];
  const paramMap = new Map();

  const sql = baseSql.replace(/\{\{(\w+)\}\}/g, (match, paramName) => {
    if (!params.hasOwnProperty(paramName)) {
      throw new Error(`Missing parameter: ${paramName}`);
    }
    
    if (!paramMap.has(paramName)) {
      paramValues.push(params[paramName]);
      paramMap.set(paramName, `$${paramIndex}`);
      paramIndex++;
    }
    
    return paramMap.get(paramName);
  });

  return { sql, paramValues };
}
```

### 2. Query Complexity Validation

**Problem:** Complex queries can cause performance degradation or denial of service.

**Solution:** Implement query complexity limits:

* **Max 5 JOINs** - Prevents cartesian product explosions
* **Max 3 subqueries** - Limits nested query depth
* **No UNION ALL** - Prevents expensive full table scans
* **No window functions** - Deferred until performance impact is understood

```typescript theme={null}
function validateQueryComplexity(sql: string): void {
  const joinCount = (sql.match(/\bJOIN\b/gi) || []).length;
  if (joinCount > 5) {
    throw new Error('Query too complex: maximum 5 JOINs allowed');
  }

  const subqueryCount = (sql.match(/\bSELECT\b/gi) || []).length - 1;
  if (subqueryCount > 3) {
    throw new Error('Query too complex: maximum 3 subqueries allowed');
  }

  if (/\bUNION\s+ALL\b/i.test(sql)) {
    throw new Error('UNION ALL not allowed in reports');
  }

  if (/\bOVER\s*\(/i.test(sql)) {
    throw new Error('Window functions not allowed');
  }
}
```

### 3. Enhanced Audit Logging

**Problem:** Lack of visibility into executed queries makes incident response difficult.

**Solution:** Log all report executions with:

* Actual executed SQL (with resolved parameters)
* User ID and timestamp
* IP address and user agent
* Execution time and row count
* Success/failure status

```typescript theme={null}
await supabase.from('pf_report_executions').insert({
  report_id,
  executed_by: userId,
  params,
  query_sql: sql, // ✅ Log actual SQL
  row_count: results.length,
  execution_time_ms: executionTime,
  status: 'completed',
  ip_address: req.headers.get('x-forwarded-for'),
  user_agent: req.headers.get('user-agent'),
});
```

### 4. Row-Level Security (RLS)

All queries execute under the authenticated user's RLS context:

```sql theme={null}
-- Reports table RLS ensures users only access their org's reports
CREATE POLICY "Users can view org reports"
ON pf_reports FOR SELECT
USING (has_org_access(auth.uid(), organization_id));

-- Report executions log multi-tenant access
CREATE POLICY "Users can view own executions"
ON pf_report_executions FOR SELECT
USING (executed_by = auth.uid() OR has_role(auth.uid(), 'org_admin'));
```

### 5. Database Function Security

The `execute_report_query` function runs with `SECURITY DEFINER` but:

* Only allows `SELECT` statements
* Blocks dangerous keywords (`DROP`, `ALTER`, `INSERT`, `UPDATE`, `DELETE`, etc.)
* Enforces row limit (10,000 rows max)
* Enforces query timeout (30 seconds max)

```sql theme={null}
CREATE OR REPLACE FUNCTION execute_report_query(
  query_sql TEXT,
  param_values JSONB DEFAULT '[]',
  max_rows INTEGER DEFAULT 10000,
  timeout_ms INTEGER DEFAULT 30000
) RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
SET statement_timeout = '30s'
AS $$
DECLARE
  result JSONB;
  normalized_query TEXT;
BEGIN
  normalized_query := LOWER(TRIM(query_sql));
  
  -- ONLY allow SELECT
  IF NOT (normalized_query LIKE 'select %') THEN
    RAISE EXCEPTION 'Only SELECT queries allowed';
  END IF;
  
  -- Block dangerous keywords
  IF normalized_query ~ '(drop|alter|create|insert|update|delete|truncate|grant|revoke)' THEN
    RAISE EXCEPTION 'Query contains forbidden keywords';
  END IF;
  
  -- Execute with limit and timeout
  EXECUTE format('SELECT json_agg(row_to_json(t.*)) FROM (%s LIMIT %s) t', query_sql, max_rows)
  INTO result;
  
  RETURN COALESCE(result, '[]'::jsonb);
END;
$$;
```

## Security Best Practices

### For Developers

1. **Never bypass parameterization** - Always use `buildParameterizedQuery()`
2. **Validate all inputs** - Check parameter types and ranges
3. **Test with malicious inputs** - Use SQL injection test cases
4. **Monitor query patterns** - Look for anomalous query structures
5. **Review audit logs regularly** - Detect suspicious activity

### For Report Authors

1. **Use parameters for all user inputs** - Never concatenate user data into SQL
2. **Test with minimal data first** - Verify query performance before production
3. **Request only needed columns** - Avoid `SELECT *`
4. **Use indexes** - Ensure WHERE clauses use indexed columns
5. **Review generated SQL** - Use dry-run mode to inspect query structure

### For Security Reviewers

1. **Audit `pf_report_executions` logs** - Look for:
   * Failed queries with injection patterns
   * Queries with excessive execution time
   * High-volume queries from single users
   * Queries accessing sensitive tables

2. **Monitor resource usage** - Alert on:
   * Query execution time > 10 seconds
   * Row counts > 5,000
   * Multiple failed queries from same user

3. **Review report definitions** - Flag reports that:
   * Join more than 3 tables
   * Use subqueries excessively
   * Query audit log tables
   * Access PHI/PII without justification

## Known Limitations

### 1. No Read-Only Database Connection

**Current State:** Reports execute with full database permissions (constrained by RLS).

**Recommendation:** For production, create a read-only PostgreSQL role:

```sql theme={null}
CREATE ROLE reports_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reports_readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO reports_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reports_readonly;

-- Use separate connection pool for reports
-- Update execute_report_query to use reports_readonly role
```

### 2. No Query Result Caching

**Current State:** Every report execution re-runs the full query.

**Recommendation:** Implement Redis caching for frequently-run reports:

* Cache key: `report:${report_id}:${JSON.stringify(params)}`
* TTL: 1 hour (configurable per report)
* Invalidation: On report definition update

### 3. No Rate Limiting

**Current State:** No limit on number of reports a user can run.

**Recommendation:** Implement rate limiting:

* Max 10 reports per minute per user
* Max 100 reports per hour per organization
* Exponential backoff for repeated failures

## Incident Response

### SQL Injection Detected

1. **Immediate Actions:**
   * Disable affected report
   * Revoke user access if malicious
   * Review audit logs for data exfiltration

2. **Investigation:**
   * Extract all queries from `pf_report_executions` for user
   * Check for unusual table access patterns
   * Review RLS policy effectiveness

3. **Remediation:**
   * Patch vulnerability
   * Force password reset for affected users
   * Notify security team

### Performance Degradation

1. **Immediate Actions:**
   * Identify long-running queries in `pg_stat_activity`
   * Kill queries exceeding timeout threshold
   * Temporarily disable expensive reports

2. **Investigation:**
   * Analyze query plans (`EXPLAIN ANALYZE`)
   * Check for missing indexes
   * Review query complexity violations

3. **Remediation:**
   * Add indexes to frequently-queried columns
   * Reduce query complexity limits if needed
   * Educate report authors on query optimization

## Testing Checklist

* [ ] SQL injection prevention (see test cases below)
* [ ] Query complexity limits enforced
* [ ] Parameterized queries work correctly
* [ ] Audit logs capture all executions
* [ ] RLS policies prevent cross-org access
* [ ] Timeout enforced for long queries
* [ ] Row limit enforced

### SQL Injection Test Cases

```sql theme={null}
-- Test 1: Basic injection
SELECT * FROM pf_users WHERE name = '{{name}}'
-- Params: { name: "'; DROP TABLE pf_users; --" }
-- Expected: Error (parameterization prevents injection)

-- Test 2: UNION-based injection
SELECT id, name FROM pf_organizations WHERE id = '{{org_id}}'
-- Params: { org_id: "' UNION SELECT user_id, password FROM auth.users --" }
-- Expected: Error (forbidden keyword)

-- Test 3: Stacked queries
SELECT * FROM pf_profiles WHERE id = '{{profile_id}}'
-- Params: { profile_id: "'; DELETE FROM pf_profiles WHERE '1'='1" }
-- Expected: Error (multiple statements not allowed)
```

## References

* [OWASP SQL Injection Prevention Cheat Sheet](https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html)
* [PostgreSQL Security Best Practices](https://www.postgresql.org/docs/current/sql-prepare.html)
* [Supabase RLS Documentation](https://supabase.com/docs/guides/auth/row-level-security)

***

**Maintained by:** Platform Security Team\
**Next Review:** 2025-12-25