Prevent SQL Injection in Custom RPC Functions

-- ❌ SQL injection via string concatenation in PostgreSQL function
CREATE OR REPLACE FUNCTION search_rules(search_term text)
RETURNS SETOF rule AS $$
BEGIN
  -- Attacker can inject: ' OR 1=1 --
  -- Or worse: '; DROP TABLE rule; --
  RETURN QUERY EXECUTE
    'SELECT * FROM rule WHERE title LIKE ''%' || search_term || '%''';
END;
$$ LANGUAGE plpgsql;

// app/api/rules/search/route.ts
export async function GET(request: Request) {
  const { searchParams } = new URL(request.url)
  const query = searchParams.get('q') ?? ''
 
  // ❌ User input goes directly into the vulnerable function
  const { data } = await supabase.rpc('search_rules', {
    search_term: query  // attacker sends: "' OR 1=1 --"
  })
 
  return Response.json(data)
}

-- ✅ Parameterized query prevents injection
CREATE OR REPLACE FUNCTION search_rules(search_term text)
RETURNS SETOF rule AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT * FROM rule WHERE title ILIKE $1'
    USING '%' || search_term || '%';
END;
$$ LANGUAGE plpgsql;

-- ✅ Static SQL with parameters — no EXECUTE needed
CREATE OR REPLACE FUNCTION search_rules(search_term text)
RETURNS SETOF rule AS $$
  SELECT * FROM rule
  WHERE title ILIKE '%' || search_term || '%'
  ORDER BY created_at DESC
  LIMIT 50;
$$ LANGUAGE sql STABLE;

-- ✅ Dynamic ORDER BY with format('%I') for identifier escaping
CREATE OR REPLACE FUNCTION list_rules(
  sort_column text DEFAULT 'created_at',
  sort_dir text DEFAULT 'desc'
)
RETURNS SETOF rule AS $$
BEGIN
  -- Validate sort_column against allowlist
  IF sort_column NOT IN ('created_at', 'title', 'impact', 'updated_at') THEN
    RAISE EXCEPTION 'Invalid sort column: %', sort_column;
  END IF;
 
  IF sort_dir NOT IN ('asc', 'desc') THEN
    RAISE EXCEPTION 'Invalid sort direction: %', sort_dir;
  END IF;
 
  -- %I safely quotes identifiers (prevents injection)
  RETURN QUERY EXECUTE
    format('SELECT * FROM rule ORDER BY %I %s LIMIT 100', sort_column, sort_dir);
END;
$$ LANGUAGE plpgsql STABLE;

# Find all RPC calls that might pass user input
grep -rn ".rpc(" src/ --include="*.ts" --include="*.tsx"
# Find EXECUTE statements in migrations (check for parameterization)
grep -rn "EXECUTE" supabase/migrations/ --include="*.sql"
# Find string concatenation in SQL functions
grep -rn "||" supabase/migrations/ --include="*.sql"