Sorting Safely in Python

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 route, it looks like this:

sort = request.args.get("sort", "id")
if sort not in ["id", "ordered", "status"]:
    return jsonify({"error": "unknown sort"}), 400
rows = db.execute("SELECT * FROM orders ORDER BY " + sort).fetchall()

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 get_products, read sort and order, 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 sort not in [...] with your four names, and the same for order with "asc" and "desc".