harden pid interpolation in staging insert (independent-review follow-up)

4d1ee3689e5eaa6844c6c9a2a5448cd6b7d2e94e · 2026-09-22 14:19:26 -0700 · Steve Abrams

Kimi + local codex review flagged the one remaining raw interpolation on the
staging insert: pid was interpolated trusting Shopify always returns a numeric
id. A string pid (API-shape change, error body, or a different code path) would
re-open SQL injection into dw_unified. Validate pid as safe-integer digits
before emitting a bare bigint literal; also avoids Number() precision loss
above 2^53. Belongs with 060357d.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_0142i1ci5jdMrmc8DNr6RjUS

Files touched

M server.js

Diff

commit 4d1ee3689e5eaa6844c6c9a2a5448cd6b7d2e94e
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Sep 22 14:19:26 2026 -0700

    harden pid interpolation in staging insert (independent-review follow-up)
    
    Kimi + local codex review flagged the one remaining raw interpolation on the
    staging insert: pid was interpolated trusting Shopify always returns a numeric
    id. A string pid (API-shape change, error body, or a different code path) would
    re-open SQL injection into dw_unified. Validate pid as safe-integer digits
    before emitting a bare bigint literal; also avoids Number() precision loss
    above 2^53. Belongs with 060357d.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
    Claude-Session: https://claude.ai/code/session_0142i1ci5jdMrmc8DNr6RjUS
---
 server.js | 8 +++++++-
 1 file changed, 7 insertions(+), 1 deletion(-)

diff --git a/server.js b/server.js
index 1b07073..7bbc3c8 100644
--- a/server.js
+++ b/server.js
@@ -2703,8 +2703,14 @@ async function createNewItem(p, b64, dryRun) {
     // price is user input (String(p.price)); emit it only as a validated finite number, never a raw
     // interpolated string — a numeric column can't take a quoted literal, so it must be gated as a number.
     const priceLit = (price != null && Number.isFinite(+price)) ? +price : 'NULL';
+    // pid is a Shopify bigint id: validate it as safe-integer digits rather than TRUST its type. A
+    // string pid (API-shape change / error body / a different code path) must never reach raw SQL
+    // interpolation; emitting bare validated digits also avoids Number() precision loss above 2^53.
+    // (Independent review flagged this raw ${pid} interpolation as the one remaining vector.)
+    const pidStr = (typeof pid === 'number' && Number.isSafeInteger(pid)) ? String(pid) : (typeof pid === 'string' ? pid : '');
+    const pidLit = /^\d{1,19}$/.test(pidStr) ? pidStr : 'NULL';
     const stageSQL = `insert into new_items_staging (dw_sku, mfr_sku, vendor, vid, pattern_name, color, price, specs, shopify_product_id, created_via)
-      values (${pgLit(dwsku)},${pgLit(mfr)},${pgLit(vreg.vendor)},${pgLit(vreg.vid || '')},${pgLit(name)},${pgLit(color)},${priceLit},${pgLit(specsJson)}::jsonb,${pid || 'NULL'},'scan') on conflict do nothing`;
+      values (${pgLit(dwsku)},${pgLit(mfr)},${pgLit(vreg.vendor)},${pgLit(vreg.vid || '')},${pgLit(name)},${pgLit(color)},${priceLit},${pgLit(specsJson)}::jsonb,${pidLit},'scan') on conflict do nothing`;
     execFile(PSQL, ['-d', DW_DB, '-c', 'create table if not exists new_items_staging (id bigserial primary key, dw_sku text, mfr_sku text, vendor text, vid text, pattern_name text, color text, price numeric, shopify_product_id bigint, created_via text, created_at timestamptz default now()); alter table new_items_staging add column if not exists specs jsonb; ' + stageSQL], { timeout: 8000 }, () => {});
     // FileMaker WALLPAPER master — real create (dedupe on Series + Mfr Pattern so a re-run never dupes).
     let fmResult = { committed: false, skipped: 'FileMaker disabled or not configured' };

← 060357d fix SQL injection in create-item staging insert (dollar-quot · back to Dw Photo Capture · auto-data-snapshot: 2026-09-23T03:44:04 (1 data files) — dat a7f6dc5 →