Postgrest: PostgREST 6.0.0 breaks filtering by strings with space as +

[neonerd]

Environment

• PostgreSQL version: postgrest/postgrest
• PostgREST version: 6.0.0
• Operating system: Windows Home 64-bit

Description of issue

I use Axios to query PostgREST from Node.JS and browser. In 5.2.0, this request (made by Axios) works, i.e. it returns a row that has systemName='Foo - Bar':

http://localhost:3000/some_table?systemName=eq.Foo+-+Bar

I have tried upgrading to 6.0.0 today and in 6.0.0 this stops working and returns an empty result set. If you replace the "+" by %20, it works again.

Maybe this was caused by #1288?

Anyhow, I'm not sure if this is an issue with Axios encoding the request wrongly, but according to this, it should be valid: https://stackoverflow.com/questions/2678551/when-to-encode-space-to-plus-or-20

Any ideas?

[steve-chavez]

I think Axios is behaving correctly, and the SO answer is also correct and PostgREST complies with the behavior "+ means a space only in application/x-www-form-urlencoded content".

To clarify, %20 always means "space" in a url, but + only means "space" when sending a payload with Content-Type: application/x-www-form-urlencoded, and postgrest respects this since the latest version.

You're right in that #1288 caused the change. We are intending to use the + as part of our syntax.

What I suggest would be changing the + to the more safe %20.

[neonerd]

Yeah, I understand.

It is however unfortunate that Axios sends "+" even when sending a GET payload with Content-Type: application/json, so the only option is to create a custom query builder function that will always use %20 and inject that into Axios. This can also be confusing for people intending to use Axios with PostgREST who do not know about this issue, as this is the default behaviour in Axios. I will probably make an issue in Axios repo then.

Thanks for the reaction.

[chishaku]

@neonerd Thanks for raising this issue. Did you create an issue in axios or did you come to some kind of resolution?

[chishaku]

@steve-chavez I added the header Content-Type: application/x-www-form-urlencoded but my filter that replaces spaces with + still failed.

I ended up reverting to 5.2.0. In my case, even when I replace spaces with %20 in axios, my front end host (netlify) handles the redirect to postgrest and they replace spaces with +.

[nbouscal]

This happens not just in axios, but even using native web APIs in Chrome:

const url = new URL('http://www.example.com');
url.searchParams.append('test', 'string with spaces');
url.search === '?test=string+with+spaces'; // true

The Stack Overflow comment linked above seems to claim that the query string of a URL is itself application/x-www-form-urlencoded content. Whether or not that's a reasonable interpretation of the standards, if native web APIs are treating it that way then it's a real issue.

I'm currently getting around this with string substitution, but it's pretty unfortunate to have to do that.

[steve-chavez]

It is indeed a problem if many libraries and proxies by default turn + into spaces and don't comply with the standard(rfc 1866).

A similar issue ocurred before when we tried to use the Expect header but it turned out Nginx and other proxies stripped this header before reaching out to postgrest and this was not configurable https://github.com/PostgREST/postgrest/issues/748#issuecomment-263037419.

So I guess our only choice now is to go with the flow and revert back to converting the pluses to spaces.

For anyone wanting a quick fix, it'll be a matter of changing this line False to True:

https://github.com/PostgREST/postgrest/blob/ee40e7e0d7367603379b5bf0c7c67f1e16752375/src/PostgREST/ApiRequest.hs#L147-L148

And then build from source.

[nbouscal]

Thanks @steve-chavez. I am a little bit confused why you think it doesn't comply with the standard though. As I read it, 1866 §8.2.2 agrees with the SO comment that the query string should be encoded as application/x-www-form-urlencoded when submitting a form with method GET, and the HTML spec seems to agree as well. Neither of them mention Content-Type, and 7231 §3.1.1.5 explicitly says that that header refers to the payload (which I take to mean that it doesn't refer to the query). I agree that this is an unfortunate waste of the + character, but I think we're stuck with it for the foreseeable future.

[steve-chavez]

@nbouscal In your example https://github.com/PostgREST/postgrest/issues/1348#issuecomment-518753715 the result should be ?test=string%20with%20spaces if the lib complied with the standard. Since spaces should only be converted to + in the context of x-www-form-urlencoded, and you're not specifying that header or even a payload.

[steve-chavez]

But in any case, this will be fixed. Maybe we can use ! instead of + since it's also url safe.