Sorting Safely in Java

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 ? placeholder can't help. A ? 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 table, with sort read by @RequestParam(defaultValue = "id") String sort, it looks like this:

if (!List.of("id", "ordered", "status").contains(sort)) {
    return ResponseEntity.status(400).body(Map.of("error", "unknown sort"));
}
List<Map<String, Object>> rows = db.queryForList("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: the opener of getProducts already reads sort and order, with id and asc as defaults. 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 (!List.of(...).contains(sort)) with your four names, and the same for order with "asc" and "desc".