Reporting Engine Security

// ❌ 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 };
}

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');
  }
}

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'),
});

-- 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'));

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;
$$;

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

-- 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)