How to escape string characters in supabase js OR query?

Question

I'm trying to write a query that matches rows where a user-provided string matches one of two text array columns.

For example, imagine a table messages with text columns greeting and goodbye:

// WORKS

const greeting = 'Hello there'
const goodbye = 'Goodbye everyone'

const { data, error } = await supabase
  .from('messages')
  .select()
  .or(`greeting.eq.${greeting},goodbye.eq.${goodbye}`)

This works fine. But, when the query value includes a comma, it breaks (doesn't find the correct rows). That makes sense, because I'm not escaping anything.

// DOESN'T WORK

const greeting = 'Hello, there'     // <-- comma
const goodbye = 'Goodbye, everyone' // <-- comma

const { data, error } = await supabase
  .from('messages')
  .select()
  .or(`greeting.eq.${greeting},goodbye.eq.${goodbye}`)

(syntax via https://supabase.com/docs/reference/javascript/or)

What is the correct way to escape strings for PostgREST in supabase-js? Which characters should I be escaping?

I want to be able to give it arbitrary text and make sure it will match correctly, not just work for commas.

Answer 1

It looks like the reserved characters are:

• ,
• .
• :
• (
• )
• "
• \

Via https://postgrest.org/en/stable/api.html#reserved-characters

If filters include PostgREST reserved characters(,, ., :, ()) you’ll have to surround them in percent encoded double quotes %22 for correct processing.

If the value filtered by the in operator has a double quote ", you can escape it using a backslash \". A backslash itself can be used with a double backslash \\.

Some HTTP libraries might encode URLs automatically(e.g. axios). In these cases you should use double quotes "" directly instead of %22.

In my case, using Supabase, directly using double quotes seems to work:

const { data, error } = await supabase
  .from('messages')
  .select()
  .or(`greeting.eq."${greeting}",goodbye.eq."${goodbye}"`) // <== note the quotes