Fix SQL Injection (Legacy & Modern) in Fastify

Fastify's overhead-optimized architecture is irrelevant if your database layer is compromised. SQL Injection (SQLi) in Node.js environments typically occurs when developers bypass the driver's abstraction layer to perform raw string concatenation. To secure a Fastify application, you must enforce strict input validation via JSON Schema and utilize parameterized queries or type-safe query builders.

The Vulnerable Pattern

fastify.get('/api/users', async (request, reply) => {
  const { id } = request.query;
  // CRITICAL VULNERABILITY: Direct string interpolation allows for UNION-based or Blind SQLi
  const query = `SELECT * FROM users WHERE id = ${id}`;
  const result = await fastify.pg.query(query);
  return result.rows;
});

The Secure Implementation

The vulnerable example treats user-supplied data as executable SQL code. An attacker could pass `?id=1; DROP TABLE users;--` to execute arbitrary commands. The secure implementations decouple the SQL logic from the data. By using placeholders ($1) or a query builder, the database driver sends the query template and the parameters separately. The DB engine then treats the input strictly as a literal value, not part of the command. Additionally, leverage Fastify's built-in Ajv integration to validate that 'id' is an integer before it even reaches your controller.

// Method 1: Parameterized Queries (Native Driver)
fastify.get('/api/users/v1', async (request, reply) => {
  const { id } = request.query;
  const result = await fastify.pg.query('SELECT * FROM users WHERE id = $1', [id]);
  return result.rows;
});
// Method 2: Modern Query Builder (Knex.js)
fastify.get(‘/api/users/v2’, async (request, reply) => {
const { id } = request.query;
return await fastify.knex(‘users’).where({ id }).select(’*’);
});