CODEWITHSUNDEEP
supabasepostgrestsupabase-js
Sam Pierce Lolla
Sam Pierce Lolla

Reputation: 239

How to escape string characters in supabase js OR query?

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.

Upvotes: 3

Views: 1537

Answers (2)

Nick
Nick

Reputation: 237

There doesn't seem to be a way to do this, at least based on all my readings and attempts. Escaping characters doesn't seem to work. Not sure what your use case is, but as an alternative try textSearch.

https://supabase.com/docs/guides/database/full-text-search?queryGroups=language&language=js

Upvotes: 0

Sam Pierce Lolla
Sam Pierce Lolla

Reputation: 239

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

Upvotes: 4

Related Questions