Security Best Practices

// ✅ Safe: Parameterized query (automatic)
const user = await repo
  .query()
  .where("email", "=", userInput)
  .findOne();

// ❌ Dangerous: Raw SQL with user input
const result = await orm.rawQuery(
  `SELECT * FROM users WHERE email = '${userInput}'`
);

// ✅ Safe: Raw query with parameters
const result = await orm.rawQuery(
  "SELECT * FROM users WHERE email = ?",
  [userInput]
);

NEON_DATABASE_URL=postgresql://user:password@localhost:5432/myapp
REDIS_URL=redis://localhost:6379

import { DBType, type DBConfig } from "stabilize-orm";

export const dbConfig: DBConfig = {
  type: DBType.Postgres,
  connectionString: process.env.DATABASE_URL, // From environment
  // Never commit credentials to version control
};

.env
.env.local
.env.*.local

import { z } from "zod";

// Define validation schema
const userSchema = z.object({
  email: z.string().email().max(255),
  name: z.string().min(1).max(100),
  age: z.number().int().min(0).max(150),
});

// Validate before creating
async function createUser(input: unknown) {
  try {
    const validated = userSchema.parse(input);
    return await repo.create(validated);
  } catch (error) {
    if (error instanceof z.ZodError) {
      throw new Error("Invalid input: " + error.message);
    }
    throw error;
  }
}

// ❌ Bad: No authorization check
async function getPost(postId: number) {
  return await postRepo.findById(postId);
}

// ✅ Good: Verify ownership
async function getPost(postId: number, userId: number) {
  const post = await postRepo
    .query()
    .where("id", "=", postId)
    .where("userId", "=", userId) // Ensure user owns the post
    .findOne();

  if (!post) {
    throw new Error("Post not found or access denied");
  }

  return post;
}

// ✅ Better: Use query scopes
export const Post = defineModel({
  tableName: "posts",
  columns: { /* ... */ },
  scopes: {
    ownedBy: (query, userId: number) =>
      query.where("userId", "=", userId),
  },
});

// Usage
const userPosts = await postRepo.scope("ownedBy", currentUserId).find();

import { hash, verify } from "@node-rs/argon2";

// Hash password before storing
async function createUser(email: string, password: string) {
  const hashedPassword = await hash(password, {
    memoryCost: 19456,
    timeCost: 2,
    outputLen: 32,
    parallelism: 1,
  });

  return await userRepo.create({
    email,
    password: hashedPassword, // Store hashed password
  });
}

// Verify password on login
async function login(email: string, password: string) {
  const user = await userRepo.query().where("email", "=", email).findOne();

  if (!user) {
    throw new Error("Invalid credentials");
  }

  const isValid = await verify(user.password, password);

  if (!isValid) {
    throw new Error("Invalid credentials");
  }

  return user;
}

// ✅ Good: Use transaction for related operations
async function transferFunds(fromId: number, toId: number, amount: number) {
  await orm.transaction(async (trx) => {
    // Deduct from sender
    const sender = await accountRepo.findById(fromId, trx);
    if (sender.balance < amount) {
      throw new Error("Insufficient funds");
    }
    await accountRepo.update(fromId, {
      balance: sender.balance - amount,
    }, trx);

    // Add to receiver
    const receiver = await accountRepo.findById(toId, trx);
    await accountRepo.update(toId, {
      balance: receiver.balance + amount,
    }, trx);

    // If any operation fails, entire transaction rolls back
  });
}

import { Ratelimit } from "@upstash/ratelimit";
import { Redis } from "@upstash/redis";

const redis = new Redis({
  url: process.env.REDIS_URL!,
  token: process.env.REDIS_TOKEN!,
});

const ratelimit = new Ratelimit({
  redis,
  limiter: Ratelimit.slidingWindow(5, "1 m"), // 5 requests per minute
});

async function login(email: string, password: string, ip: string) {
  // Check rate limit
  const { success } = await ratelimit.limit(ip);

  if (!success) {
    throw new Error("Too many login attempts. Please try again later.");
  }

  // Proceed with login
  // ...
}

export const User = defineModel({
  tableName: "users",
  columns: { /* ... */ },
  hooks: {
    afterCreate: async (user) => {
      await auditRepo.create({
        action: "USER_CREATED",
        userId: user.id,
        timestamp: new Date(),
        metadata: { email: user.email },
      });
    },
    afterUpdate: async (user, changes) => {
      await auditRepo.create({
        action: "USER_UPDATED",
        userId: user.id,
        timestamp: new Date(),
        metadata: { changes },
      });
    },
    afterDelete: async (user) => {
      await auditRepo.create({
        action: "USER_DELETED",
        userId: user.id,
        timestamp: new Date(),
      });
    },
  },
});

-- Create read-only user for reporting
CREATE USER reporting_user WITH PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_user;

-- Create app user with limited permissions
CREATE USER app_user WITH PASSWORD 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Don't grant DROP, TRUNCATE, or ALTER permissions to app users