如何在Nest JS（类Node）中验证用户输入无SQL注入？

Hey there! I totally get that you’ve been messing around with sanitize functions and coming up empty—SQL injection protection is tricky, but in NestJS, we’ve got some rock-solid approaches that are way more reliable than rolling your own sanitization. Let’s break this down step by step.

Forget writing custom sanitize functions—parameterized queries are the most effective way to stop SQL injection. They work by separating your SQL logic from user input: the database treats input as pure data, not executable code.

In NestJS, if you’re using raw SQL (though I’d recommend an ORM first), always use parameter placeholders instead of string concatenation. Here’s how to do it with TypeORM’s DataSource:

async getUserById(userId: string) {
  // Use $1, $2, etc. for PostgreSQL, ? for MySQL
  const rawQuery = 'SELECT * FROM users WHERE id = $1';
  return this.dataSource.query(rawQuery, [userId]);
}

NestJS plays seamlessly with ORMs like TypeORM (built-in support) and Prisma, both of which automatically use parameterized queries under the hood. This eliminates the risk of accidental SQL injection entirely, because you never directly mix user input with SQL strings.

Example with TypeORM Repository: #

// In your service
async getUserById(userId: string) {
  return this.userRepository.findOneBy({ id: userId });
}

Example with Prisma: #

// In your service
async getUserById(userId: string) {
  return this.prisma.user.findUnique({
    where: { id: userId },
  });
}

Even when building complex queries, use the ORM’s query builder instead of raw strings. For TypeORM:

async getUsersByRole(role: string, limit: number) {
  return this.userRepository
    .createQueryBuilder('user')
    .where('user.role = :role', { role })
    .limit(limit)
    .getMany();
}

While parameterized queries/ORMs are your main defense, adding input validation can catch malformed or suspicious input early. Use NestJS’s built-in class-validator and ValidationPipe to enforce rules on user input.

For example, if you expect a user ID to be a UUID, you can validate it like this:

// DTO file (e.g., user.dto.ts)
import { IsString, IsUUID } from 'class-validator';

export class GetUserDto {
  @IsString()
  @IsUUID('4', { message: 'User ID must be a valid UUID' })
  userId: string;
}

Then enable validation in your controller:

// In your controller
import { Controller, Get, Param, UsePipes, ValidationPipe } from '@nestjs/common';
import { GetUserDto } from './user.dto';

@Controller('users')
export class UserController {
  constructor(private readonly userService: UserService) {}

  @Get(':userId')
  @UsePipes(new ValidationPipe())
  async getUser(@Param() params: GetUserDto) {
    return this.userService.getUserById(params.userId);
  }
}

You can also use regex patterns to restrict allowed characters for inputs like usernames:

@Matches(/^[a-zA-Z0-9_]+$/, { message: 'Username can only contain letters, numbers, and underscores' })
username: string;

• Least Privilege: Make sure your database user only has the permissions it needs (e.g., no DROP or ALTER access for an app that only reads/writes data).
• Avoid Dynamic SQL: If you absolutely need to build dynamic queries, use your ORM’s safe methods (like TypeORM’s queryBuilder conditional methods) instead of string concatenation.
• Keep Dependencies Updated: Regularly update your ORM, database driver, and NestJS packages to patch any security vulnerabilities.

A Critical Note: #

Never rely solely on input sanitization to prevent SQL injection. Attackers can bypass most custom sanitize functions with cleverly crafted inputs. Parameterized queries and ORMs are the only truly reliable defenses.

内容的提问来源于stack exchange，提问作者Theo Chazalon