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

Query example:

select users.username, users.email, orders.id, orders.date, orders.amount
from users, orders 
where users.trid = orders.user_trid 
and users.user_trid in ( 31, 32, 33 ) 
and users.status = 1

Option 1: GET - Query in the URL

https://server.com/query?q=select%20users.username%2C%20users.email%2C%20orders.id%2C%20orders.date%2C%20orders.amount%20from%20users%2C%20orders%20where%20users.trid%20%3D%20orders.user_trid%20and%20users.user_trid%20in%20%28%2031%2C%2032%2C%2033%20%29%20and%20users.status%20%3D%201

Option 2: POST - JSON Payload

[{
    "query": "select users.username, users.email, orders.id, orders.date, orders.amount from users, orders where users.trid = orders.user_trid and users.user_trid in ( 31, 32, 33 ) and users.status = 1"
}]

Return

{
    "users": [{
        "username": "usr_name_1",
        "email": "[e-mail]",
        "orders": [{
            "id": 2543,
            "date": "2020.08.11",
            "amount": 35.99
        }, {
            "id": 2843,
            "date": "2020.10.01",
            "amount": 45.99
        }, {
            "id": 3843,
            "date": "2020.12.23",
            "amount": 395.99
        }]
    }, {
        "username": "usr_name_2",
        "email": "[e-mail]",
        "orders": [{
            "id": 3815,
            "date": "2021.01.11",
            "amount": 35.99
        }, {
            "id": 3176,
            "date": "2021.02.03",
            "amount": 45.99
        }, {
            "id": 3898,
            "date": "2020.03.05",
            "amount": 395.99
        }]
    }]
}

Original issue: https://github.com/siodb/siodb/issues/133

Comments

[tq-bit]

I am curious to understand why you would want to send sql queries over your url. It might expose your API to SQL injection. SQL queries belong into the backend.

For such, you might use stored procedures or query builders. Your API should then be able to take the requested URL query parameters and move them forward to your database model, which in turn uses them for its DB query

Edit: My apologies, I didn't really contribute to your question, did I now?

Assuming you have a separate DB schema / query builder / stored procedure in place, a url with a query could look something like this:

GET: https://server.com/users/?users_trid=orders_trid&users_user_trid=31,32,33&user_status=1

Your controller could then take those params out and forward them to the respective query for the path /users

[Nicolas Penot]

Thank you tq-bit. Actually, we already can get data from table using its path GET: server.com/database/mydb/tables/us....

The idea is to be able to get complex query results in one REST call, thus having SQL to query the data model.

I don't speak about query builder or controller because we have the SQL and REST server process in the database program itself.

You're right, SQL injection are a problem having SQL direct in a GET. In fact, it's just the first step to get the result of queries more elaborated than just GET in the table (as described in my previous comment). Then we're going to have prepared statements feature that you can POST before getting the result through GET + params.

So I think it's more or less corresponding to your feedback. Isn't it?

[tq-bit]

I think I get your idea now.

I'll be honest, doing REST like this is rather unfamiliar to me. The closest lesson learned I've had on what you name prepared statements is a bound operation or bound function in OData, which exposes a procedure in the form of a URL. Like this, you would ever only have to send a single query + payload, therefor staying true to restful principles.

docs.oasis-open.org/odata/odata/v4...

Now adding OData principles just for the sake of dynamic queries might not be worth it, but perhaps there's a thing or two which might come in handy for you.

[Nicolas Penot]

OData principles are in the essence what we should have.
We only need to send SQL to the database server (POST), the database server validates and holds them in a list. Then we call them by their "tag" (GET) with the binding values, e.g. user_id=23&order_id&43...

Also, it will dramatically improve the process to GET data from queries because they're going to be "soft parsed" instead of "hard parsed" at each call.

PS: I didn't know OData, but it's inspiring specifications, thanks tq-bit :)

[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.

[Nicolas Penot]

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.

[Camilo]

In my opinion I would use GET for selects and POST for updates and DELETE for deletes. You can encode them in base64 so you have a single string param.

I find a little weird to send raw sql queries through api rest.

Another approach would be separating the command with the execution.

First you create a queries endpoint that only creates a query resource. It does not execute them.

• POST queries: creates a new query with params

[{
    "query": "select users.username, users.email, orders.id, orders.date, orders.amount from users, orders where users.trid = orders.user_trid and users.user_trid in ( 31, 32, 33 ) and users.status = 1"
}]

Returns an id for that query. Sets its status to pending.

Then you create a new endpoint that executes the queries using the provided id.

• PUT queries/query1234: Executes the query with the id and change its status to done.

• GET queries/query1234: Returns the query result and status.

[Nicolas Penot]

Thank you Camilo :)
I think we're going to use GET to /query?q=.
Your idea of "prepared queries" is very good. Actually, in a later stage, we will be able to POST prepared queries and then GET the result. But we still need to develop the prepared query library.

Example:

POST Payload

[{
        "id": "user_list_with_orders",
        "query": "select users.username, users.email, orders.id, orders.date, orders.amount from users, orders where users.trid = orders.user_trid and users.user_trid in ( :user_trids ) and users.status = :user_status"
    },
    {
        "id": "one_parameter",
        "query": "select name, value from app_settings where name = :parameter_name"
    }
]

GET URL

GET  /query?id=user_list_with_orders&user_trid=31&user_trid=32&user_trid=33&user_status=1

Return

{
    "users": [{
        "username": "usr_name_1",
        "email": "[e-mail]",
        "orders": [{
            "id": 2543,
            "date": "2020.08.11",
            "amount": 35.99
        },{
            "id": 2843,
            "date": "2020.10.01",
            "amount": 45.99
        },{
            "id": 3843,
            "date": "2020.12.23",
            "amount": 395.99
        }]
    },{
        "username": "usr_name_2",
        "email": "[e-mail]",
        "orders": [{
            "id": 3815,
            "date": "2021.01.11",
            "amount": 35.99
        },{
            "id": 3176,
            "date": "2021.02.03",
            "amount": 45.99
        },{
            "id": 3898,
            "date": "2020.03.05",
            "amount": 395.99
        }]
    }]
}

GET  /query?id=one_parameter&parameter_name=params1

{
    "app_settings": [{
        "name": "params1",
        "value": "value_param1"
    }]
}