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 $queryRawUnsafeOnly with sanitized input Prisma.sqlComposable, parameterized
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