Handle Postgrest special characters

[inian]

Postgrest defines the some special characters which need to be handled separately.

This doesn't work

await postgrest
.from('table')
.select('*')
.where('name', 'file (1).jpg')

but double quoting the filter works.

await postgrest
.from('table')
.select('*')
.where('name', '"file (1).jpg"')

It would be better if the library checks if filters have special characters and automatically adds the double quotes. It seems cleaner than handling it at an application level.
WDYT?

[soedirgo]

That's odd, I tried to reproduce this with:

$ npm run test:db
$ curl -s 'http://localhost:3000/users' -H 'Content-Type: application/json' -d '{"username":"file (1).jpg"}'

And then:

import { PostgrestClient } from '@supabase/postgrest-js'
const postgrest = new PostgrestClient('http://localhost:3000')
postgrest
  .from('users')
  .select()
  .eq('username', 'file (1).jpg')
  .then(({ data, error }) => console.log(data, error))
// prints:
// [
//   {
//     username: 'file (1).jpg',
//     data: null,
//     age_range: null,
//     status: 'ONLINE',
//     catchphrase: null
//   }
// ] null

Maybe the field actually has " in it?

More generally re: quoting, there has been some problems when automatically escaping on special characters: #131, #149, though these are on column names rather than values. I suspect there are certain filters for which it's safe to automatically escape special characters for values, but I haven't found an obvious one yet. Another problem is .eq('username', 'supabot') is incompatible with .eq('username', '"supabot"').

[inian]

Field does not have " in it.

eq works but in doesn't. Can you try this?

const body = await supabase
      .from("objects")
      .select("*")
      .in("name", ["authenticated/test-image-001 (1).png"]);

Also this seems to be a regression. A previous version of postgrest-js handled this without issues. I can find out which version the regression happened if it helps.

Edit: this was working in postgrest v0.22.0 and stopped working in v0.24.0

[soedirgo]

I see. I remember I removed quoting on in on #155.

I did some tests and it seems like always quoting makes it not match values that have " in it, i.e. if I do:

$ curl -s 'http://localhost:3000/users' -H 'Content-Type: application/json' -d '{"username":"(\")"}'

Then there's no way to match it.

@steve-chavez any way to get around this? Niche case though... either way, I think it's acceptable to " automatically for values.

[steve-chavez]

Then there's no way to match it.

@soedirgo I can match a (") value with eq:

curl 'l:3000/projects?name=eq.(")'
[{"id":3432423,"name":"(\")","client_id":null,"earnings":100}]

-- or

curl "l:3000/projects?name=eq.(%22)"
[{"id":3432423,"name":"(\")","client_id":null,"earnings":100}]

Not with in though.

curl 'l:3000/projects?name=in.(%22(%22%22)%22)'
[]

Rare case, but it can be filed as bug in the postgrest repo.

(Won't work inside the or/and filters as well)