thrilltrack-explorer/supabase/migrations/20251016195944_43e0e937-c5f3-4991-93d9-467caf0337b3.sql

-- Fix database functions missing SET search_path protection
-- This prevents schema poisoning attacks

-- Fix has_aal2 function
CREATE OR REPLACE FUNCTION public.has_aal2()
RETURNS boolean
LANGUAGE sql
STABLE SECURITY DEFINER
SET search_path = public
AS $function$
  SELECT COALESCE((auth.jwt()->>'aal')::text = 'aal2', false);
$function$;

-- Fix generate_deletion_confirmation_code function
CREATE OR REPLACE FUNCTION public.generate_deletion_confirmation_code()
RETURNS text
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $function$
DECLARE
  code TEXT;
BEGIN
  code := LPAD(FLOOR(RANDOM() * 1000000)::TEXT, 6, '0');
  RETURN code;
END;
$function$;

-- Fix hash_ip_address function
CREATE OR REPLACE FUNCTION public.hash_ip_address(ip_text text)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE
SECURITY DEFINER
SET search_path = public
AS $function$
BEGIN
  -- Use SHA256 hash with salt
  RETURN encode(
    digest(ip_text || 'thrillwiki_ip_salt_2025', 'sha256'),
    'hex'
  );
END;
$function$;