DEV Community

Discussion on: Should SQL Query through REST API be a GET or a POST?

Collapse
cariehl profile image
Cooper Riehl

In my experience designing APIs, I always follow one of two approaches:

  1. Use the HTTP verb that corresponds with the desired action (i.e. GET retrieves existing data, POST creates new data, PUT updates existing data, DELETE removes existing data).
  2. Always use POST.

I default to #1, but there are some practical reasons to consider #2. On one project, for example, some of our URIs could get really, really long. We needed to support IE8, which can only handle URIs up to 2083 characters in length. Thus, we ended up using POST for all our API calls, with the query parameters in the POST body.

Like the other commenter though, I'm a little confused why you're accepting generic, raw SQL queries in your endpoints, rather than providing specific endpoints to access specific data. I would've expected your example GET endpoint to accept a list of filtering parameters rather than an entire raw SQL query, i.e. GET /userOrders?user_trids=31,32,33&status=1.

I don't know the details of your project, but I would definitely second the recommendation to look into a tried-and-tested framework like OData as a way to perform complex queries via a public API. Allowing people to call your API with arbitrary SQL queries seems like a recipe for disaster SQL injection attacks.

Collapse
niolap profile image
Nicolas Penot Author

Thank you Cooper :)
True that building an application that will use SQL query with user variable would be crazy without prepared statement. That's not the Goal. The SQL queries are just a simple first step/test feature to rapidly get data from the database.
I keep in mind the GET /userOrders?user_trids=31,32,33&status=1 that seems the best way to go.
The project is more about offering REST features (GET/POST/PATCH/DELETE/PUT) on database table including SQL queries (for more complex operations involving joins for instance) to interact with a database. Of course, as developer, we must then encapsulate that in our application code. It's not meant to get exposed directly to final user.