TLDR: PostgREST's .not('column', 'in', '(1,2,3)') builds a raw filter string — you own the sanitization. One .map(Number).filter() call per call site is all it takes.

the setup

I'm building a KOL CRM (key opinion leader management system) for a client — webinar scheduling, audience segments, topics, traffic sources, the whole thing.

The standard pattern for syncing many-to-many junction tables looks like this: upsert the rows you want to keep, then prune the rest.

.not('topic_id', 'in', `(${topicIds.join(',')})`)

I had that exact pattern in 7 places across 4 files. It worked perfectly. I shipped it.

the assumption that almost bit me

My security council (an autonomous dev council I run on the codebase between shipping sessions) flagged it during a sweep.

My first instinct was "those IDs came from the database anyway — they're already safe."

WRONG.

Those IDs arrive in the request body. The client sends topicIds: [1, 2, 3]. My server action was faithfully passing them straight through to the filter string without ever checking that they were actually integers.

Here's the thing about supabase-js: .eq(col, val) is parameterized. PostgREST handles the escaping under the hood. It's genuinely safe.

.not('column', 'in', ...) is not the same animal.

You're constructing a raw filter expression — a literal string like (1,2,3) that goes straight into the query. If topicIds contains 1) OR 1=1--, PostgREST faithfully passes that along.

The friendly API hid the PostgREST semantics. I assumed the library was safe all the way down. It isn't.

the fix that worked

One line before every .not('in', ...) call:

const safeTopicIds = topicIds.map(Number).filter(n => !isNaN(n) && n > 0)

Then swap in the sanitized array:

.not('topic_id', 'in', `(${safeTopicIds.length > 0 ? safeTopicIds.join(',') : '0'})`)

Seven spots. Four files: audience-segments.ts, kols.ts, topics.ts, and the webinars API route.

The empty-array case falls back to '0' — a valid integer that won't match any real ID, so the prune correctly deletes everything (which is exactly right when you're removing all associations).

the cousin bug

While I was in there, I documented the sibling class: .ilike(col, value) is a different injection — % and _ are PostgreSQL LIKE wildcards, and PostgREST exposes no ESCAPE clause. Strip wildcards before any .ilike() call on untrusted input.

Different mechanic, same root cause: the library's friendliness hides what's happening underneath.

why this matters to me

I've been building on Supabase long enough to feel comfortable. That comfort is exactly the danger.

.eq() being safe trained me to assume the whole client was safe. It isn't.

The rule I'm keeping now: if I'm building a string and handing it to a PostgREST filter, every value in that string gets validated. Full stop.

P.S. The council sweep that caught this ran in about 15 minutes. One pass. That's a pretty good return on a security audit.