Sorting Safely in C#

Clients want to choose the order of the products: ?sort=price for the cheapest first, and &order=desc for the most expensive first. Sorting is SQL's ORDER BY, and it goes before LIMIT: the database sorts all the matching rows, then cuts out the page.

This time a $ parameter can't help. A parameter stands for a value, and ORDER BY needs the name of a column, so the name has to be glued into the SQL text. Glued text is risky: whatever a visitor writes would become part of your SQL. The safe way is an allowlist: glue in only a word from your own list, and refuse anything else with 400. On another route, it looks like this:

sort = sort ?? "id";
if (!new List<string> { "id", "ordered", "status" }.Contains(sort)) {
    return Results.Json(new { error = "unknown sort" }, statusCode: 400);
}
cmd.CommandText = "SELECT * FROM orders ORDER BY " + sort;

Your GET /products keeps its filters and pages, and now also sorts by sort (id by default), in the direction order (asc by default):

GET /products?sort=price&per_page=3  →  200  {"products": [{"id": 4, "name": "Pen", "price": 2, …}, {"id": 2, "name": "Notebook", "price": 7, …}, {"id": 7, "name": "Stapler", "price": 9, …}], "total": 8, "next_page": 2}
GET /products?sort=price&order=desc&per_page=2  →  200  {"products": [{"id": 8, "name": "Gift card", "price": 50, …}, {"id": 3, "name": "Lamp", "price": 29, …}], "total": 8, "next_page": 2}
GET /products?category=office&sort=name&order=desc  →  200  {"products": [{"id": 7, "name": "Stapler", …}, {"id": 4, "name": "Pen", …}, {"id": 2, "name": "Notebook", …}], "total": 3, "next_page": null}
GET /products?sort=stock&order=desc&page=2&per_page=3  →  200  {"products": [{"id": 1, "name": "Mug", "stock": 10, …}, {"id": 5, "name": "Plant", "stock": 6, …}, {"id": 6, "name": "Teapot", "stock": 4, …}], "total": 8, "next_page": 3}
GET /products?per_page=2  →  200  {"products": [{"id": 1, "name": "Mug", …}, {"id": 2, "name": "Notebook", …}], "total": 8, "next_page": 2}
GET /products?sort=password  →  400  {"error": "sort must be id, name, price or stock"}
GET /products?sort=price; DROP TABLE products  →  400  {"error": "sort must be id, name, price or stock"}
GET /products?sort=price&order=sideways  →  400  {"error": "order must be asc or desc"}

Your task: in the /products route, give sort and order their defaults (both are already parameters of the route), add them to the SQL with ORDER BY before the LIMIT, and let through only id, name, price or stock for sort, and asc or desc for order. Anything else answers 400 with the errors above.

Assignment
Follow the Coding Tutorial to add sorting to GET /products, with an allowlist for sort and one for order.

Hint
Check both values right after the check of page and per_page, before any query runs: if (!new List<string> { … }.Contains(sort)) with your four names, and the same for order with "asc" and "desc".