In my project I do not have access to the dev and test database and so it becomes very hard to debug sometimes.
So I added an api to fetch data from the database by passing the sql query in the post body for the debugging purposes from swagger.
The api looks like this
[HttpPost("fetch-database")]
[Produces(MediaTypeNames.Application.Json)]
[Authorize(Roles = ClaimRoles.Admin)]
public async Task<ContentResult> FetchDatabase(string query)
{
var result = await masterlookup.FetchDatabaseAsync(query);
return Content(result, "application/json");
}
The very very strange thing that is happening is that it works for some queries while return 403 Forbidden for some queries.
So if I pass this query, it works
select * from Table t
But if I add order by clause to the query like this
select * from Table t order by t.d desc
or if I select specific columns like this
select t.id, t.name from Table t
It fails with a forbidden message
<html>
<head><title>403 Forbidden</title></head>
<body>
<center><h1>403 Forbidden</h1></center>
<hr><center>Microsoft-Azure-Application-Gateway/v2</center>
</body>
</html>
This is really strange because it should not matter what is the content of the string that I am passing.
Any idea what could be going wrong?
Finally solved it by making the query parameter being read by request body using FromBody attribute.
So even when the method type was post it was still sending the parameter query in query string. So now forcing the parameter to be read from the request body.
Looks like something was not being allowed when the whole query was going through query string.
So basically I changed the signature of the method like this
public async Task<ContentResult> FetchDatabase([FromBody]string query)