Implementing Security Best Practices
1. Preventing SQL Injection
// Safe — parameterized
await prisma.$queryRaw`SELECT * FROM "User" WHERE email = ${email}`;
// Safe composition
const where = Prisma.sql`WHERE role = ${role}`;
await prisma.$queryRaw`SELECT * FROM "User" ${where}`;Warning: Never concatenate user input into
$queryRawUnsafe. Use the tagged template form or Prisma.sql for safe parameterization.| API | Safe? |
|---|---|
$queryRaw`...` | Yes — parameterized |
$queryRawUnsafe | Only with sanitized input |
Prisma.sql | Composable, parameterized |
2. Sanitizing User Input
| Tool | Detail |
|---|---|
| Zod / Valibot | Schema validation |
| class-validator | Decorator-based |
| DOMPurify | Strip HTML for rich text |
3. Implementing Field-Level Security
const safe = prisma.$extends({
result: {
user: { passwordHash: { needs: { passwordHash: true }, compute: () => undefined } }
}
});| Strategy | Detail |
|---|---|
| Omit fields | Use extension to strip secrets |
| Select allow-list | Default select in repo |
4. Managing Database Credentials
| Practice | Detail |
|---|---|
| Secrets manager | AWS SM / Vault / Doppler |
| Never commit | Use .env.* + gitignore |
| Rotate | Periodic + on incident |
5. Using Environment Variables
DATABASE_URL="postgresql://user:pass@host:5432/db?schema=public"| Tip | Detail |
|---|---|
| Per env | .env.development, .env.test, .env.production |
| Validate | Zod env schema on boot |
6. Implementing Row-Level Security
| Approach | Detail |
|---|---|
| PG RLS | Native policies via raw SQL |
| App-level | tenantId filter via extension |
| SET LOCAL | Pass user context per tx |
7. Encrypting Sensitive Data
| Layer | Detail |
|---|---|
| At rest | DB TDE / disk encryption |
| Field-level | App encrypts before insert (AES-GCM) |
| KMS | AWS KMS / GCP KMS |
8. Implementing Audit Logging
const audited = prisma.$extends({
query: {
$allModels: {
async $allOperations({ model, operation, args, query }) {
const result = await query(args);
if (["create","update","delete"].includes(operation)) {
await prisma.auditLog.create({ data: { model, op: operation, args: args as any } });
}
return result;
}
}
}
});| Store | Detail |
|---|---|
| Same DB | Easy joins, transactional |
| Separate | Tamper resistance, scale |
| Append-only | WORM storage / S3 Object Lock |
9. Managing Database Permissions
| Principle | Detail |
|---|---|
| Least privilege | App user ≠ migration user |
| GRANT/REVOKE | Per schema/table |
| No superuser | For runtime |
10. Implementing Rate Limiting
| Layer | Detail |
|---|---|
| API gateway | Cloudflare / Kong / NGINX |
| App-level | express-rate-limit + Redis |
| DB-level | Statement timeout, connection cap |