TL;DR
Validation is defense in depth. Even with parameterized queries, you should validate input types, lengths, and formats before they reach your database. These prompts help you add validation layers using Zod, Joi, or custom validators.
Schema Validation with Zod
Create Zod schemas to validate input before database operations.
For these database tables, create validation schemas:
Table: users
- email: valid email, max 255 chars
- username: alphanumeric, 3-30 chars
- age: optional, integer 13-120
Table: posts
- title: string, 1-200 chars
- content: string, max 50000 chars
- status: enum (draft, published, archived)
- tags: array of strings, max 10 items
Create:
- Insert schemas (all required fields)
- Update schemas (all fields optional)
- Query/filter schemas (for search params)
Include proper error messages for each validation.
Type Coercion and Sanitization
Add input sanitization for database operations.
Input sources:
- URL parameters (always strings)
- JSON body (may have wrong types)
- Form data (strings)
Create sanitization helpers that:
- Coerce string IDs to integers safely
- Trim whitespace from strings
- Normalize email to lowercase
- Strip HTML/scripts from text fields
- Parse dates from various formats
- Handle null vs undefined vs empty string
Language: TypeScript/JavaScript/Python
Return validation errors rather than throwing for invalid input.
Query Parameter Validation
Create validation for database query parameters.
Parameters to validate:
- Pagination: page (int >= 1), limit (int 1-100)
- Sorting: orderBy (whitelist of columns), order (asc/desc)
- Filtering: status, dateFrom, dateTo, search
- IDs: must be valid format (UUID, integer, etc)
Requirements:
- Whitelist allowed orderBy columns
- Validate date ranges make sense (from < to)
- Limit search string length
- Set sensible defaults for missing params
- Reject unexpected parameters
Framework: Express/Fastify/Next.js API routes
Create middleware or helper functions for reuse.
Validation isn't a replacement for parameterization: Always use parameterized queries even with validated input. Validation catches bad data early but doesn't prevent injection on its own.
Database Constraint Validation
Create validation that matches my database constraints.
Database schema:
- users.email: VARCHAR(255) UNIQUE NOT NULL
- users.username: VARCHAR(50) UNIQUE, regex ^a-z0-9_+$
- posts.title: VARCHAR(200) NOT NULL
- posts.author_id: FOREIGN KEY to users.id
Create validation that:
- Enforces the same length limits as VARCHAR
- Matches CHECK constraints and regex patterns
- Pre-validates foreign key IDs exist (optional)
- Returns friendly error messages, not DB errors
Bonus: Generate validation from Prisma/Drizzle schema if available.
Pro tip: Validate at the API boundary, not deep in your code. Catch bad input early in your request handlers before it propagates through your application.
Should I validate on the frontend and backend?
Yes. Frontend validation improves UX with instant feedback. Backend validation is required for security since frontend validation can be bypassed.
How do I handle validation for optional fields?
Use .optional() or .nullable() in your schema. Distinguish between a field being absent, null, and an empty string based on your requirements.
Scan for Missing Validation
Find database operations that lack proper input validation.
Start Free Scan