PostgREST .or() Breaks Silently on a Comma

The short version

Interpolating a raw search term into a PostgREST .or() filter via a template literal breaks the filter grammar on a reserved character like a comma. On top of that, supabase-js doesn't throw on a transport or PostgREST failure — it represents it as a {data: null, error} return value — so the moment the caller reads .data ?? [], a broken filter that turns data into null gets treated as an empty array with no exception and no log. Global search's contacts query broke exactly this way. The fix was to split name and email into separate parameterized .ilike() calls and merge the results in JS.

The state it could become

The subject is global search in kimiteras-portal — the source behind both search and the command palette. It queries companies, contacts, projects, contracts, schools, tasks, and documents in one Promise.all round trip. Contacts alone need to match on both name and email, and before the fix it was written like this:

supabase
  .from("contacts")
  .select("id, name, email, company_id, school_id")
  .or(`name.ilike.${like},email.ilike.${like}`)
  .limit(5),

like is the search term formatted into %...%, and wildcard characters (%, _, \) were escaped. What wasn't escaped was the delimiter .or() itself uses: the comma. The result was handled the same way as every other category:

for (const c of contacts.data ?? [])
  hits.push({
    type: "Contact",
    label: c.email ? `${c.name} (${c.email})` : c.name,
    ...
  });

The first argument to .or() is a PostgREST-specific grammar string, name.ilike.<value>,email.ilike.<value>, where a comma is interpreted as a condition separator. If the search term itself contains a comma — someone typing a "Last, First" style name, or pasting a name straight off a business card — the generated string splits into extra conditions for every comma in it, and PostgREST can no longer parse it as a valid filter.

Why

What made this worse was how the failure surfaced. supabase-js query methods are designed not to reject their promise — a transport error and a PostgREST error response are both represented as the same {data, error} shape. This code read only contacts.data ?? [] and didn't even destructure error. When the broken filter made PostgREST return an error, data simply became null, no exception was thrown, and ?? [] quietly treated it as a valid "zero contacts matched" result. Every other category — companies, projects, contracts — still matched fine, so the UI just looked like "a slightly thin result set," with no way to notice that the contacts category alone was vanishing for specific search terms.

Fixing it

Name and email were split into separate parameterized .ilike() queries, with the results merged and deduplicated in JS.

// Query name and email separately with parameterized .ilike and merge in JS. Interpolating
// raw input into .or() lets a reserved PostgREST character like a comma break the filter
// grammar, silently dropping the contacts category (e.g. q="Last, First"). .ilike binds its
// value as a parameter, so reserved characters are safe.
supabase
  .from("contacts")
  .select("id, name, email, company_id, school_id")
  .ilike("name", like)
  .limit(5),
supabase
  .from("contacts")
  .select("id, name, email, company_id, school_id")
  .ilike("email", like)
  .limit(5),

.ilike() binds its second argument as a parameter, so a value containing a comma or . — reserved PostgREST characters — doesn't break the filter grammar itself. Because a name match and an email match can return the same contact twice, the merge side uses a Set to push each ID into hits only once.

const seenContact = new Set<string>();
for (const c of [...(contactsByName.data ?? []), ...(contactsByEmail.data ?? [])]) {
  if (seenContact.has(c.id)) continue; // dedupe a contact matched on both name and email
  seenContact.add(c.id);
  hits.push({ ... });
}

To prevent a repeat, a static-guard test now bans raw interpolation into .or() outright:

it("does not interpolate the search query raw into PostgREST .or() filter grammar (injection prevention)", () => {
  expect(src).not.toContain(".or(`");
  expect(src).not.toContain("ilike.${");
});

Because the test inspects the source string directly, CI fails the moment anyone reintroduces a template literal into .or() for the same reason, no matter why.

The lesson

?? [] is a common idiom for "a safe default when there's no value," but combined with a client like supabase-js that represents failure with the same shape as success, it erases the distinction between "zero rows matched" and "the query itself failed." Here the trigger was a different bug — raw interpolation into .or() — but the underlying danger is shared by every call site that reads data without checking error. In a design that bundles several categories into one request via Promise.all, the category that fails is the one that goes quietly missing, and the fact that every other category returns normally is exactly what makes the gap hard to notice.

Originally published at tech.rebounder.jp.