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%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