Encoding request parameters to prevent query injection

[vraxis]

Description of issue

When constructing a request to PostgREST, it's unclear if there is a mechanism to escape strings such that they can prevent query injection into the request. Is there some way to do this I'm perhaps missing?

As an example, say there is a field "description" and a field "sensitive," the latter of which a user should not be able to query or discern its value. I would normally pass the description field like so in a query string

?description= like."{description}"

A malicious user could craft this value of description somedesc%22%26sensitive%3D%22Y%22%26description%3Dlike.%22somedesc

which in turn would be parsed and return records matching the description who also have a sensitive value of Y. Even if I encoded the string myself, the malicious user could just pass the unencoded values which would result in the same issue. Any advice would be fantastic here.

[steve-chavez]

it's unclear if there is a mechanism to escape strings such that they can prevent query injection into the request.

Yes, we escape strings in:

postgrest/src/PostgREST/Private/QueryFragment.hs

Lines 52 to 59 in 7af54c5

	pgFmtLit :: SqlFragment -> SqlFragment
	pgFmtLit x =
	let trimmed = trimNullChars x
	escaped = "'" <> replace "'" "''" trimmed <> "'"
	slashed = replace "\\" "\\\\" escaped in
	if "\\" `isInfixOf` escaped
	then "E" <> slashed
	else slashed

(This is similar to libpq's PQEscapeLiteral)

What would give you peace of mind regarding SQLi?

Sometime ago we had an idea about running sqlmap on our CI. Or perhaps docs would suffice?

Also, at some point we'll switch the GET requests to parametrized queries so that we won't need escaping.

[vraxis]

I get that there is escaping at the PostgREST layer to prevent sql injection. My point was more how do I prevent the specific scenario I gave where I do see that a crafted value gets interpreted as two separate url parameters. Or are you suggesting I can escape the value before adding it to the url and it will no longer be treated as two parameters?

[steve-chavez]

Ah, in that case I think it would be easier to not expose the "sensitive" column to the user, at the database level.

You could do that by granting access to specific columns in your table:

GRANT SELECT(id, description) ON your_table TO your_user;

Or by creating a VIEW of your table(as suggested on schema isolation) that doesn't include the "sensitive" column:

CREATE VIEW api.your_table_view AS SELECT id, description FROM your_table;

[steve-chavez]

@vraxis Did my suggestion helped you?

[vraxis]

It was helpful thanks. However, I'm also worried about updates or deletes since, even if I don't expose sensitive data, it would be possible to maliciously update or delete different records than I had intended by supplying user data that was shaped to modify the url parameters string. Is that fair to say?

[wolfgangwalther]

You are afraid of injections into your request's query string. This is entirely a thing of the client, i.e. while constructing your query string you must escape & properly in the user input you get. I guess all libraries to create query strings will do that automatically, already.

But please note: This does not give any "security" at all. Users can manually run requests to your api server with any query string they like, so you must expect that.

If you want to limit access for a certain user to only some rows, you are probably looking for Row Level Security. You can also implement something like this yourself in a view with security_barrier and possibly check_option (https://www.postgresql.org/docs/current/sql-createview.html). Also see this example in the PostgREST docs: http://postgrest.org/en/latest/auth.html#roles-for-each-web-user

[vraxis]

@wolfgangwalther See my example in the opening question, I did escape & with %26 and it didn't work, it was unencoded and parsed as a normal & in the url. Is there some other escape mechanism I'm missing?

And I would never expose PostgREST publically, my use case would be one where there is always some sort of intermediary service that constructs calls to PostgREST. The row level security stuff is certainly something I'll be looking into, but is just harder to do for some kinds of data without a clear relationship to the user.

[wolfgangwalther]

@wolfgangwalther See my example in the opening question, I did escape & with %26 and it didn't work, it was unencoded and parsed as a normal & in the url. Is there some other escape mechanism I'm missing?

Ah, sorry, I missed that. So you were not afraid of the unescaped & in your query string, but you already escaped it properly, but PostgREST still treated it as a separator between parameters? I didn't test this, but if it behaves like this, this feels at least unexpected to me as well, I would probably consider this a bug as well.

There's something else going on as well. You're "escaping" your value with percent encoded double quotes, because of postgrest reserved characters (as mentioned in the docs here: http://postgrest.org/en/latest/api.html#reserved-characters). The thing is, for a basic operator like the one you're using this actually doesn't work. I stumbled across this some time ago as well, but wasn't able to write this stuff up, yet. It seems to only apply for the in. operator - the docs are at least misleading in that regard.

The whole URL escape stuff needs a little bit of care. I can construct a couple of examples that would fail to be properly translated to an SQL query.

And I would never expose PostgREST publically, my use case would be one where there is always some sort of intermediary service that constructs calls to PostgREST.

For me, personally, that would kind of defeat the whole purpose of PostgREST. I am using PostgREST exactly because I don't need that extra layer. But that's for another day ;)

[wolfgangwalther]

but you already escaped it properly, but PostgREST still treated it as a separator between parameters? I didn't test this, but if it behaves like this, this feels at least unexpected to me as well, I would probably consider this a bug as well.

@steve-chavez If you agree here, that should probably be marked as a bug.

[wolfgangwalther]

So I did test it and it works as it should. With our test fixtures:

• GET /authors?name=eq.Anne%20Frank returns 1 row.

• GET /authors?name=eq.Anne%20Frank&a= returns an error, because the parameter a is missing an operator.

• GET /authors?name=eq.Anne%20Frank%26a= returns 0 row.

Clearly the escaped & in the last request is interpreted as part of the value. So if you escape your query string on the client side correctly, then you will not suddenly create additional parameters to your request.

The problem I see here, is that the docs are very much misleading. At http://postgrest.org/en/latest/api.html#unicode-support it reads:

If filters include PostgREST reserved characters(,, ., :, ()) you’ll have to surround them in percent encoded double quotes %22 for correct processing.

The way it is written, I would expect this to always be the case. But this is in fact only supported for the in. operator (and maybe arrays, not sure) and inside logical operators.

This will not work, even though it should according to documentation:

• GET /authors?name=eq.%22Anne%20Frank%22

But this works:

• GET /authors?and=(name.eq.%22Anne%20Frank%22)

For a client-side library this is a pain to quote correctly, because context is needed. I'd consider the docs to be right and the behaviour to be a bug.

Another problem is that, to my knowledge, it's not possible to use quotes as part of the value once in a "must quote" situation. So quotes can't be escaped in a quoted string.