107 lines
3.7 KiB
Markdown
107 lines
3.7 KiB
Markdown
# Purchase Lead Security Fix
|
|
|
|
## Issue Summary
|
|
The `purchase_lead` function had a potential security vulnerability where the frontend was passing a client-controlled `creditsCost` parameter, which could theoretically allow providers to purchase high-value leads for arbitrary low prices.
|
|
|
|
## Root Cause
|
|
- **Migration 00010**: Created `purchase_lead(UUID, UUID, INTEGER)` with client-controlled price parameter
|
|
- **Migration 00012**: Created safe 2-arg version `purchase_lead(UUID, UUID)` that gets price from server
|
|
- **Problem**: Frontend code still called the function with 3 arguments, passing `p_credits_cost`
|
|
|
|
## Security Fix Applied
|
|
|
|
### 1. Database Migration (00058)
|
|
Created migration `drop_insecure_purchase_lead_overload` that:
|
|
- Explicitly drops any 3-argument version of `purchase_lead` if it exists
|
|
- Adds documentation comment to the safe 2-arg function
|
|
- Ensures only the secure version remains in the database
|
|
|
|
### 2. Frontend API Fix
|
|
**File**: `src/db/api.ts`
|
|
- Removed `creditsCost` parameter from `leadPurchasesApi.purchase()` function signature
|
|
- Removed `p_credits_cost` from the RPC call parameters
|
|
- Added comment documenting that price is determined server-side
|
|
|
|
**Before**:
|
|
```typescript
|
|
async purchase(providerId: string, leadId: string, creditsCost: number = 10) {
|
|
const { data, error } = await supabase.rpc('purchase_lead', {
|
|
p_provider_id: providerId,
|
|
p_lead_id: leadId,
|
|
p_credits_cost: creditsCost, // ❌ Client-controlled
|
|
});
|
|
}
|
|
```
|
|
|
|
**After**:
|
|
```typescript
|
|
async purchase(providerId: string, leadId: string) {
|
|
const { data, error } = await supabase.rpc('purchase_lead', {
|
|
p_provider_id: providerId,
|
|
p_lead_id: leadId, // ✅ Price from server
|
|
});
|
|
}
|
|
```
|
|
|
|
### 3. Component Update
|
|
**File**: `src/components/provider/LeadDetailModal.tsx`
|
|
- Updated call to `leadPurchasesApi.purchase()` to only pass 2 arguments
|
|
- Added comment documenting server-side price determination
|
|
|
|
## How the Secure Function Works
|
|
|
|
The safe `purchase_lead(UUID, UUID)` function:
|
|
|
|
1. **Gets price from database**:
|
|
```sql
|
|
SELECT final_price INTO v_lead_price
|
|
FROM leads
|
|
WHERE id = p_lead_id;
|
|
```
|
|
|
|
2. **Uses calculated/override price**:
|
|
- `final_price` = `override_price` (if set by admin) OR `calculated_price`
|
|
- `calculated_price` is computed based on activities (balloon, ATV, tours, etc.)
|
|
- Base price is 20 credits, with multipliers for premium activities
|
|
|
|
3. **Atomic transaction**:
|
|
- Locks wallet row with `FOR UPDATE`
|
|
- Verifies sufficient balance
|
|
- Deducts correct amount
|
|
- Records purchase with actual price paid
|
|
- Creates transaction record
|
|
|
|
## Verification
|
|
|
|
```sql
|
|
-- Only the safe 2-arg function exists
|
|
SELECT
|
|
proname,
|
|
pg_get_function_arguments(oid) as args
|
|
FROM pg_proc
|
|
WHERE proname = 'purchase_lead';
|
|
|
|
-- Result:
|
|
-- purchase_lead | p_provider_id uuid, p_lead_id uuid
|
|
```
|
|
|
|
## Impact
|
|
- ✅ **Security**: Clients can no longer specify arbitrary prices
|
|
- ✅ **Integrity**: All purchases use server-calculated pricing
|
|
- ✅ **Audit**: `lead_purchases.price_paid` accurately reflects actual cost
|
|
- ✅ **Consistency**: Frontend and backend are now aligned
|
|
|
|
## Testing Recommendations
|
|
1. Verify lead purchase flow works correctly
|
|
2. Confirm correct price is deducted from wallet
|
|
3. Check that high-value leads (with balloon, ATV, etc.) charge appropriate amounts
|
|
4. Verify admin price overrides work correctly
|
|
5. Test insufficient balance scenarios
|
|
|
|
## Related Files
|
|
- `supabase/migrations/00010_create_purchase_lead_function.sql` (original vulnerable version)
|
|
- `supabase/migrations/00012_add_lead_pricing_system.sql` (safe version created)
|
|
- `supabase/migrations/00058_drop_insecure_purchase_lead_overload.sql` (cleanup migration)
|
|
- `src/db/api.ts` (API layer)
|
|
- `src/components/provider/LeadDetailModal.tsx` (UI component)
|