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.
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%22for 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