9. Knex security

This file supports the Week 2 segment on Knex security and SQL injection prevention.

Example vulnerable endpoint (conceptual)

Consider a Snippets endpoint that allows sorting via a sort query parameter:

snippetsRouter.get("/", async (req, res) => {
  let query = knexInstance.select("*").from("snippets");

  if ("sort" in req.query) {
    const orderBy = req.query.sort.toString();
    if (orderBy.length > 0) {
      query = query.orderByRaw(orderBy); // Vulnerable!
    }
  }

  console.log("SQL", query.toSQL().sql);

  try {
    const data = await query;
    res.json({ data });
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: "Internal server error" });
  }
});

Here, user input is passed directly into orderByRaw, which can allow SQL injection (example: (CASE WHEN (SELECT COUNT(*) FROM users WHERE email='[e-mail]') > 0 THEN email ELSE password END));

Safer pattern with validation

Show how to validate and map the sort value instead:

• Only allow a whitelist of sortable columns (e.g. created_at, title).
• Only allow ASC or DESC as sort directions.
• Use the Query Builder’s orderBy instead of orderByRaw with unchecked strings.

The exact code can be written live during the session.