[object Object]

← back to Wallco Ai

SEC-3: strip payout_details / commission_rate from public designer routes

72ddc0e75b860ef4a1c341c4d2fb88ab4dda1253 · 2026-05-23 11:47:25 -0700 · Steve Abrams

src/marketplace/index.js:359 and :961 — the public /api/marketplace/
designers/:slug and the SEO server-rendered /designers/:slug both did
`SELECT * FROM mp_designer_profiles WHERE slug=$1`, returning the full
row to anonymous callers. Leaked columns included:

  - payout_details (JSONB bank / PayPal routing)
  - payout_method
  - commission_rate
  - license_rate_nonexclusive
  - license_rate_exclusive
  - rights_agreement_at
  - commission_agreement_at
  - user_id (internal FK)
  - onboarding_completed_at, onboarding_steps

The existing redact at line 371 only nulled phone/contact_email/address_*
on the response object — payout_details was never touched. Same defect
class as commit 75da858 (local_path leak from /api/designs).

Fix:
- Added PUBLIC_DESIGNER_COLUMNS whitelist near the top of marketplace/
  index.js (~30 safe columns).
- Added CONTACT_PII_COLUMNS list — fetched in a SECOND query, only when
  the row's public_contact_consent=true. Defense in depth: if consent
  flips later, no stale PII rides along in the response.
- Both public sites (line 360 + line 961) now use PUBLIC_DESIGNER_COLUMNS.
- Authed dashboard route (line 236) and onboarding.js (lines 52, 77)
  intentionally still SELECT * — those run behind the designer cookie
  and the designer is allowed to see their own financial settings.

Adds one extra round-trip to PG for the rare consent=true case; the
common consent=false case still does one SELECT.

Files touched

Diff

commit 72ddc0e75b860ef4a1c341c4d2fb88ab4dda1253
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sat May 23 11:47:25 2026 -0700

    SEC-3: strip payout_details / commission_rate from public designer routes
    
    src/marketplace/index.js:359 and :961 — the public /api/marketplace/
    designers/:slug and the SEO server-rendered /designers/:slug both did
    `SELECT * FROM mp_designer_profiles WHERE slug=$1`, returning the full
    row to anonymous callers. Leaked columns included:
    
      - payout_details (JSONB bank / PayPal routing)
      - payout_method
      - commission_rate
      - license_rate_nonexclusive
      - license_rate_exclusive
      - rights_agreement_at
      - commission_agreement_at
      - user_id (internal FK)
      - onboarding_completed_at, onboarding_steps
    
    The existing redact at line 371 only nulled phone/contact_email/address_*
    on the response object — payout_details was never touched. Same defect
    class as commit 75da858 (local_path leak from /api/designs).
    
    Fix:
    - Added PUBLIC_DESIGNER_COLUMNS whitelist near the top of marketplace/
      index.js (~30 safe columns).
    - Added CONTACT_PII_COLUMNS list — fetched in a SECOND query, only when
      the row's public_contact_consent=true. Defense in depth: if consent
      flips later, no stale PII rides along in the response.
    - Both public sites (line 360 + line 961) now use PUBLIC_DESIGNER_COLUMNS.
    - Authed dashboard route (line 236) and onboarding.js (lines 52, 77)
      intentionally still SELECT * — those run behind the designer cookie
      and the designer is allowed to see their own financial settings.
    
    Adds one extra round-trip to PG for the rare consent=true case; the
    common consent=false case still does one SELECT.
---
 src/marketplace/index.js | 39 +++++++++++++++++++++++++++++++++++----
 1 file changed, 35 insertions(+), 4 deletions(-)

diff --git a/src/marketplace/index.js b/src/marketplace/index.js
index 3491e2a..7407c68 100644
--- a/src/marketplace/index.js
+++ b/src/marketplace/index.js
@@ -66,6 +66,25 @@ const MP_SECRET = (() => {
   }
   return v;
 })();
+// SEC-3 (2026-05-23): explicit public-safe column list for mp_designer_profiles.
+// EXCLUDED on purpose:
+//   - user_id (internal FK)
+//   - commission_rate, license_rate_nonexclusive, license_rate_exclusive (financial)
+//   - payout_method, payout_details (bank/PayPal routing)
+//   - rights_agreement_at, commission_agreement_at (internal legal)
+//   - onboarding_completed_at, onboarding_steps (internal progress)
+//   - phone, contact_email, address_line1, address_line2, postal_code (PII —
+//     surfaced separately and only when public_contact_consent=true)
+const PUBLIC_DESIGNER_COLUMNS = [
+  'id','slug','display_name','studio_name','bio','website_url','instagram_url',
+  'twitter_url','tiktok_url','pinterest_url','facebook_url','linkedin_url','youtube_url',
+  'location','city','state_region','country','avatar_url','cover_url','style_tags',
+  'is_claimed','is_verified','is_founding','status','points','level',
+  'public_contact_consent','created_at','updated_at',
+].join(', ');
+// Contact-PII columns, fetched in a separate query gated on public_contact_consent.
+const CONTACT_PII_COLUMNS = ['phone','contact_email','address_line1','address_line2','postal_code'];
+
 function signDesigner(designerId) {
   const payload = Buffer.from(JSON.stringify({ id: designerId, t: Date.now() })).toString('base64url');
   const sig = crypto.createHmac('sha256', MP_SECRET).update(payload).digest('base64url');
@@ -357,7 +376,8 @@ function mount(app) {
   // public_contact_consent=true. Website + city/state/country always public — they're
   // the basics that make the designer findable.
   app.get('/api/marketplace/designers/:slug', async (req, res) => {
-    const d = await one(`SELECT * FROM mp_designer_profiles WHERE slug=$1`, [req.params.slug]);
+    // SEC-3: explicit public column list — never SELECT * here.
+    const d = await one(`SELECT ${PUBLIC_DESIGNER_COLUMNS} FROM mp_designer_profiles WHERE slug=$1`, [req.params.slug]);
     if (d && d.status === 'approved') {
       const patterns = await many(
         `SELECT id, title, slug, thumbnail_url, original_image_url, style_tags, color_tags
@@ -367,8 +387,17 @@ function mount(app) {
       const badges = await many(`SELECT badge_key, badge_name, icon FROM mp_designer_badges WHERE designer_id=$1 ORDER BY awarded_at`, [d.id]);
       const collections = await many(`SELECT id, title, slug, cover_image_url FROM mp_collections WHERE designer_id=$1 AND status='published' ORDER BY created_at DESC`, [d.id]);
       const videos = await onboarding.listVideos(d.id, { onlyApproved: true }).catch(() => []);
-      if (!d.public_contact_consent) {
-        ['phone','contact_email','address_line1','address_line2','postal_code'].forEach((k) => { d[k] = null; });
+      // Contact-PII is fetched separately and only attached if the designer
+      // has consented. Defense in depth: if the consent flag flips later,
+      // cached responses won't carry stale PII.
+      if (d.public_contact_consent) {
+        const pii = await one(
+          `SELECT ${CONTACT_PII_COLUMNS.join(', ')} FROM mp_designer_profiles WHERE id=$1`,
+          [d.id]
+        );
+        if (pii) Object.assign(d, pii);
+      } else {
+        CONTACT_PII_COLUMNS.forEach((k) => { d[k] = null; });
       }
       // Heuristic city/state split for cards/grids that consume designer detail directly.
       if (d.location && typeof d.location === 'string' && d.location.indexOf(',') >= 0) {
@@ -958,7 +987,9 @@ function mount(app) {
   async function renderDesignerPage(pageName, req, res) {
     try {
       const slug = req.params.slug;
-      const d = await one(`SELECT * FROM mp_designer_profiles WHERE slug=$1`, [slug]);
+      // SEC-3: public SEO page reads only the columns designerMetaBlock needs;
+      // never expose financial/PII fields even via server-rendered HTML.
+      const d = await one(`SELECT ${PUBLIC_DESIGNER_COLUMNS} FROM mp_designer_profiles WHERE slug=$1`, [slug]);
       if (d && d.status === 'approved') {
         return sendSeoPage(pageName, seo.designerMetaBlock(d), res);
       }

← ad389f7 SEC-2: harden /_devlogin and prod secret fallbacks (fail-clo  ·  back to Wallco Ai  ·  SEC-4: require auth on Gemini-burning endpoints + rate-limit 74da46f →