CodePark/ questions / Injection attacks
START PRACTICINGPRACTICE
Backend question · Auth & security › Injection attacks

Explain SQL injection and how you prevent it in a Node backend, including when you use an ORM.

SQL injection happens when the code joins user input into a query string. The input can then change the query's logic. Two classic examples:

  • The payload ' OR '1'='1 skips a login check.
  • UNION SELECT pulls data from other tables.

The main defence is parameterised queries, also called prepared statements. The values are sent separately from the SQL text, so they can't change it.

ORMs and query builders do this in their normal APIs. But they don't protect raw queries, or query pieces you build with string interpolation. So knex.raw, or a raw Sequelize query with input inserted into the string, is just as vulnerable.

You can't use parameters for identifiers, such as table names or ORDER BY columns. Instead, map those through an allowlist (a fixed list of allowed values).

Add more layers of protection (defence in depth):

  • a database user with only the permissions it needs (least privilege)
  • input validation
  • generic error messages that don't show SQL errors
  • Input joined into SQL can change the query's structure.
  • Use parameterised queries, also called prepared statements.
  • ORMs are safe only when you use their parameterising APIs.
  • Use an allowlist for identifiers like ORDER BY columns.
  • Use a least-privilege database user, and never send SQL errors to clients.