Security

Chapter 36: Security Best Practices

Securing TypeORM Applications

36.1 Security Overview

Security in TypeORM applications involves protecting data, preventing unauthorized access, and mitigating common vulnerabilities.

                    Security Layers
    ================================================================================

    +------------------+
    |   Application    |  <-- Authentication, Authorization
    +------------------+
           |
           v
    +------------------+
    |     API Layer    |  <-- Input validation, Rate limiting
    +------------------+
           |
           v
    +------------------+
    |     TypeORM      |  <-- Query safety, Data sanitization
    +------------------+
           |
           v
    +------------------+
    |    Database      |  <-- Access control, Encryption
    +------------------+

    Key Security Concerns:
    - SQL Injection
    - Data exposure
    - Unauthorized access
    - Sensitive data handling

    ================================================================================

36.2 SQL Injection Prevention

Using Parameterized Queries

// BAD: String concatenation (SQL injection vulnerable)
const email = req.body.email;  // Could be: "'; DROP TABLE users; --"
const users = await dataSource.query(
  `SELECT * FROM users WHERE email = '${email}'`
);

// GOOD: Parameterized query
const users = await dataSource.query(
  'SELECT * FROM users WHERE email = $1',
  [email]
);

// GOOD: QueryBuilder with parameters
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.email = :email', { email })
  .getMany();

// GOOD: Find options
const user = await userRepository.findOne({
  where: { email },
});

// GOOD: Repository methods
const user = await userRepository.findOneBy({ email });

QueryBuilder Security

// BAD: Raw string interpolation
const name = req.query.name;
const users = await userRepository
  .createQueryBuilder('user')
  .where(`user.name = '${name}'`)  // VULNERABLE!
  .getMany();

// GOOD: Parameterized where clause
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.name = :name', { name })
  .getMany();

// GOOD: Using where with object
const users = await userRepository
  .createQueryBuilder('user')
  .where({ name })
  .getMany();

// For dynamic column names, use whitelist
const validColumns = ['name', 'email', 'createdAt'];
const sortBy = validColumns.includes(req.query.sortBy)
  ? req.query.sortBy
  : 'createdAt';

const users = await userRepository
  .createQueryBuilder('user')
  .orderBy(`user.${sortBy}`, 'ASC')
  .getMany();

Escaping User Input

import { escape } from 'sqlstring';

export function sanitizeColumnName(name: string): string {
  // Whitelist of valid column names
  const validColumns = [
    'id', 'name', 'email', 'createdAt', 'updatedAt', 'status'
  ];

  if (!validColumns.includes(name)) {
    throw new Error('Invalid column name');
  }

  return name;
}

export function sanitizeOrderDirection(direction: string): 'ASC' | 'DESC' {
  const upper = direction.toUpperCase();
  if (upper !== 'ASC' && upper !== 'DESC') {
    throw new Error('Invalid order direction');
  }
  return upper as 'ASC' | 'DESC';
}

// Usage
const sortBy = sanitizeColumnName(req.query.sortBy);
const order = sanitizeOrderDirection(req.query.order);

const users = await userRepository
  .createQueryBuilder('user')
  .orderBy(`user.${sortBy}`, order)
  .getMany();

36.3 Sensitive Data Protection

Excluding Sensitive Fields

import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  email: string;

  @Column({ select: false })  // Excluded from default queries
  password: string;

  @Column({ select: false })  // Excluded from default queries
  refreshToken: string;

  @Column({ select: false })
  twoFactorSecret: string;
}

// When querying, password is not included
const user = await userRepository.findOne({ where: { id: 1 } });
console.log(user.password);  // undefined

// Explicitly select if needed
const user = await userRepository
  .createQueryBuilder('user')
  .addSelect('user.password')
  .where('user.id = :id', { id: 1 })
  .getOne();

Using Class Transformer

import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';
import { Exclude, Expose } from 'class-transformer';

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  email: string;

  @Exclude()  // Excluded from serialization
  @Column()
  password: string;

  @Exclude()
  @Column()
  refreshToken: string;

  @Expose()  // Computed property
  get initials(): string {
    return this.name.split(' ').map(n => n[0]).join('');
  }
}

// Controller with ClassSerializerInterceptor
@Controller('users')
@UseInterceptors(ClassSerializerInterceptor)
export class UsersController {
  @Get(':id')
  async findOne(@Param('id') id: string): Promise<User> {
    return this.usersService.findOne(+id);
    // Password and refreshToken will be excluded from response
  }
}

36.4 Data Encryption

Column-Level Encryption

import {
  EntitySubscriberInterface,
  EventSubscriber,
  BeforeInsert,
  BeforeUpdate,
  AfterLoad,
} from 'typeorm';
import { createCipheriv, createDecipheriv, randomBytes } from 'crypto';

const ALGORITHM = 'aes-256-gcm';
const KEY = Buffer.from(process.env.ENCRYPTION_KEY, 'hex');

function encrypt(text: string): string {
  const iv = randomBytes(16);
  const cipher = createCipheriv(ALGORITHM, KEY, iv);
  let encrypted = cipher.update(text, 'utf8', 'hex');
  encrypted += cipher.final('hex');
  const authTag = cipher.getAuthTag();
  return `${iv.toString('hex')}:${authTag.toString('hex')}:${encrypted}`;
}

function decrypt(encrypted: string): string {
  const [ivHex, authTagHex, data] = encrypted.split(':');
  const iv = Buffer.from(ivHex, 'hex');
  const authTag = Buffer.from(authTagHex, 'hex');
  const decipher = createDecipheriv(ALGORITHM, KEY, iv);
  decipher.setAuthTag(authTag);
  let decrypted = decipher.update(data, 'hex', 'utf8');
  decrypted += decipher.final('utf8');
  return decrypted;
}

@EventSubscriber()
export class EncryptionSubscriber implements EntitySubscriberInterface {
  beforeInsert(event: any) {
    this.encryptFields(event.entity);
  }

  beforeUpdate(event: any) {
    this.encryptFields(event.entity);
  }

  afterLoad(event: any) {
    this.decryptFields(event);
  }

  private encryptFields(entity: any) {
    if (entity.ssn) {
      entity.ssn = encrypt(entity.ssn);
    }
    if (entity.creditCard) {
      entity.creditCard = encrypt(entity.creditCard);
    }
  }

  private decryptFields(entity: any) {
    if (entity.ssn) {
      entity.ssn = decrypt(entity.ssn);
    }
    if (entity.creditCard) {
      entity.creditCard = decrypt(entity.creditCard);
    }
  }
}

Using Database-Level Encryption

// PostgreSQL pgcrypto extension
// Migration
export class EnablePgcrypto1700000001 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`CREATE EXTENSION IF NOT EXISTS pgcrypto`);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP EXTENSION IF EXISTS pgcrypto`);
  }
}

// Entity with encrypted column
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({
    type: 'bytea',
    transformer: {
      to(value: string): Buffer {
        // Encrypt on save
        return Buffer.from(value);
      },
      from(value: Buffer): string {
        // Decrypt on read
        return value?.toString() || '';
      },
    },
  })
  ssn: string;
}

36.5 Access Control

Row-Level Security

import { SetMetadata } from '@nestjs/common';

export const TENANT_ID_KEY = 'tenant_id';

export const TenantAware = () => SetMetadata('tenant_aware', true);

// src/common/interceptors/tenant-filter.interceptor.ts
import {
  Injectable,
  NestInterceptor,
  ExecutionContext,
  CallHandler
} from '@nestjs/common';
import { Observable } from 'rxjs';
import { map } from 'rxjs/operators';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';

@Injectable()
export class TenantFilterInterceptor implements NestInterceptor {
  constructor(
    @InjectDataSource()
    private dataSource: DataSource,
  ) {}

  intercept(context: ExecutionContext, next: CallHandler): Observable<any> {
    const request = context.switchToHttp().getRequest();
    const tenantId = request.user?.tenantId;

    // Set session variable for row-level security
    if (tenantId) {
      this.dataSource.query(
        `SET app.current_tenant = '${tenantId}'`
      );
    }

    return next.handle();
  }
}

// PostgreSQL Row-Level Security Policy
// CREATE POLICY tenant_policy ON users
//   USING (tenant_id = current_setting('app.current_tenant')::uuid);

Permission-Based Access

import {
  Injectable,
  CanActivate,
  ExecutionContext,
  ForbiddenException
} from '@nestjs/common';
import { Reflector } from '@nestjs/core';
import { UsersService } from '../../users/users.service';

@Injectable()
export class PermissionGuard implements CanActivate {
  constructor(
    private reflector: Reflector,
    private usersService: UsersService,
  ) {}

  async canActivate(context: ExecutionContext): Promise<boolean> {
    const requiredPermission = this.reflector.get<string>(
      'permission',
      context.getHandler(),
    );

    if (!requiredPermission) {
      return true;
    }

    const request = context.switchToHttp().getRequest();
    const userId = request.user?.id;

    if (!userId) {
      throw new ForbiddenException('User not authenticated');
    }

    const hasPermission = await this.usersService.hasPermission(
      userId,
      requiredPermission,
    );

    if (!hasPermission) {
      throw new ForbiddenException('Insufficient permissions');
    }

    return true;
  }
}

// Usage
@Controller('admin')
@UseGuards(PermissionGuard)
export class AdminController {
  @Get('users')
  @RequirePermission('users:read')
  async getUsers() {
    // Only users with 'users:read' permission can access
  }
}

36.6 Audit Logging

Audit Log Entity

import { Entity, PrimaryGeneratedColumn, Column, CreateDateColumn } from 'typeorm';

@Entity()
export class AuditLog {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  userId: number;

  @Column()
  action: string;  // CREATE, UPDATE, DELETE, READ

  @Column()
  entity: string;  // User, Post, etc.

  @Column()
  entityId: string;

  @Column({ type: 'jsonb', nullable: true })
  oldValue: Record<string, any>;

  @Column({ type: 'jsonb', nullable: true })
  newValue: Record<string, any>;

  @Column()
  ipAddress: string;

  @Column({ nullable: true })
  userAgent: string;

  @CreateDateColumn()
  timestamp: Date;
}

Audit Subscriber

import {
  EntitySubscriberInterface,
  EventSubscriber,
  InsertEvent,
  UpdateEvent,
  RemoveEvent,
} from 'typeorm';
import { AuditLog } from './audit-log.entity';

@EventSubscriber()
export class AuditSubscriber implements EntitySubscriberInterface {
  async afterInsert(event: InsertEvent<any>) {
    await this.logAction(event, 'CREATE', null, event.entity);
  }

  async afterUpdate(event: UpdateEvent<any>) {
    await this.logAction(event, 'UPDATE', event.databaseEntity, event.entity);
  }

  async afterRemove(event: RemoveEvent<any>) {
    await this.logAction(event, 'DELETE', event.databaseEntity, null);
  }

  private async logAction(
    event: any,
    action: string,
    oldValue: any,
    newValue: any,
  ) {
    // Skip audit log entity itself
    if (event.metadata.targetName === 'AuditLog') {
      return;
    }

    const request = event.queryRunner.data?.request;

    const auditLog = event.manager.create(AuditLog, {
      userId: request?.user?.id || 0,
      action,
      entity: event.metadata.targetName,
      entityId: String(event.entity?.id || event.databaseEntity?.id),
      oldValue: this.sanitize(oldValue),
      newValue: this.sanitize(newValue),
      ipAddress: request?.ip,
      userAgent: request?.headers?.['user-agent'],
    });

    await event.manager.save(auditLog);
  }

  private sanitize(entity: any): Record<string, any> | null {
    if (!entity) return null;

    const sanitized = { ...entity };

    // Remove sensitive fields
    delete sanitized.password;
    delete sanitized.refreshToken;
    delete sanitized.twoFactorSecret;

    return sanitized;
  }
}

36.7 Input Validation

DTO Validation

import {
  IsString,
  IsEmail,
  MinLength,
  MaxLength,
  Matches,
  IsOptional,
  IsEnum,
} from 'class-validator';

export class CreateUserDto {
  @IsString()
  @MinLength(2)
  @MaxLength(50)
  name: string;

  @IsEmail()
  email: string;

  @IsString()
  @MinLength(8)
  @MaxLength(32)
  @Matches(
    /((?=.*\d)|(?=.*\W+))(?![.\n])(?=.*[A-Z])(?=.*[a-z]).*/,
    { message: 'Password too weak' }
  )
  password: string;

  @IsOptional()
  @IsEnum(['admin', 'user', 'moderator'])
  role?: string;
}

// Controller with validation
@Controller('users')
@UsePipes(new ValidationPipe({ whitelist: true, forbidNonWhitelisted: true }))
export class UsersController {
  @Post()
  async create(@Body() createUserDto: CreateUserDto) {
    return this.usersService.create(createUserDto);
  }
}

Custom Validators

import {
  ValidatorConstraint,
  ValidatorConstraintInterface,
  registerDecorator,
  ValidationOptions,
} from 'class-validator';

@ValidatorConstraint({ name: 'noSqlInjection', async: false })
export class NoSqlInjectionConstraint implements ValidatorConstraintInterface {
  private sqlKeywords = [
    'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'DROP', 'UNION',
    'OR 1=1', 'AND 1=1', '--', ';', '/*', '*/',
  ];

  validate(value: string): boolean {
    if (typeof value !== 'string') return true;

    const upperValue = value.toUpperCase();
    return !this.sqlKeywords.some(keyword =>
      upperValue.includes(keyword.toUpperCase())
    );
  }

  defaultMessage(): string {
    return 'Input contains potentially dangerous SQL patterns';
  }
}

export function NoSqlInjection(validationOptions?: ValidationOptions) {
  return function (object: Object, propertyName: string) {
    registerDecorator({
      target: object.constructor,
      propertyName: propertyName,
      options: validationOptions,
      validator: NoSqlInjectionConstraint,
    });
  };
}

// Usage
export class SearchDto {
  @IsString()
  @NoSqlInjection()
  query: string;
}

36.8 Security Headers

Helmet Integration

import { NestFactory } from '@nestjs/core';
import { AppModule } from './app.module';
import helmet from 'helmet';

async function bootstrap() {
  const app = await NestFactory.create(AppModule);

  // Security headers
  app.use(helmet());

  // CORS configuration
  app.enableCors({
    origin: process.env.ALLOWED_ORIGINS?.split(',') || ['http://localhost:3000'],
    methods: ['GET', 'POST', 'PUT', 'DELETE', 'PATCH'],
    credentials: true,
    allowedHeaders: ['Content-Type', 'Authorization'],
  });

  // Rate limiting
  app.use(
    rateLimit({
      windowMs: 15 * 60 * 1000,  // 15 minutes
      max: 100,  // Limit each IP to 100 requests per window
    })
  );

  await app.listen(3000);
}
bootstrap();

36.9 Security Checklist

                    Security Checklist
    ================================================================================

    [ ] 1. Use parameterized queries (never string concatenation)
    [ ] 2. Validate all user input with DTOs
    [ ] 3. Exclude sensitive fields from responses
    [ ] 4. Encrypt sensitive data at rest
    [ ] 5. Implement proper authentication
    [ ] 6. Implement role-based access control
    [ ] 7. Log all security-relevant events
    [ ] 8. Use HTTPS in production
    [ ] 9. Set security headers (Helmet)
    [ ] 10. Implement rate limiting
    [ ] 11. Keep dependencies updated
    [ ] 12. Use environment variables for secrets
    [ ] 13. Implement CSRF protection
    [ ] 14. Set secure cookie options
    [ ] 15. Regular security audits

    ================================================================================

36.10 Summary

                    Security Quick Reference
    +------------------------------------------------------------------+
    |                                                                   |
    |    Threat             | Prevention                                |
    |    -------------------|------------------------------------------|
    |    SQL Injection     | Parameterized queries                     |
    |    Data Exposure     | Exclude sensitive fields                  |
    |    Unauthorized Access| Authentication & authorization           |
    |    Data Theft        | Encryption at rest                        |
    |    Input Attacks     | Validation & sanitization                 |
    |                                                                   |
    |    Best Practices    | Description                              |
    |    -------------------|------------------------------------------|
    |    Parameterized     | Always use parameters in queries          |
    |    Validation        | Validate all input with DTOs              |
    |    Exclusion         | Hide sensitive fields from responses      |
    |    Encryption        | Encrypt sensitive data                    |
    |    Audit             | Log all security events                   |
    |    Headers           | Use security headers                      |
    |                                                                   |
    +------------------------------------------------------------------+

Next Chapter

Chapter 37: Environment Configuration

Last Updated: February 2026