Security First

SQL Injection Prevention

User-provided values are never interpolated into SQL text — values are bound via parameter placeholders (dialect-specific, e.g. $1 or ?)

Parameter Bound: 100%; All user-provided values use placeholders
Value Interpolation: Zero; User data never appears in SQL text
Order Preservation: Guaranteed; Placeholders map to params 1:1
Security Tests: Current; Injection attempt coverage

Understanding SQL Injection

SQL injection remains one of the most critical web application vulnerabilities

SQL injection occurs when untrusted data is incorporated into SQL text without safe parameter binding. Attackers can manipulate query meaning to access unauthorized data, modify records, or trigger unintended operations.

Common Attack Patterns

Authentication Bypass

admin' OR '1'='1

Attempts to bypass login by making the WHERE condition always true

Data Exfiltration

' UNION SELECT password FROM users--

Uses UNION to attempt extracting data from other tables

Destructive Commands

'; DROP TABLE users; --

Attempts to append extra statements to delete data

Parameter Order Bug Abuse

Exploit: If placeholders and params are mismatched, authorization checks may use the wrong values

Example: WHERE userId=$1 AND isAdmin=$2, but params=[true, 123] instead of [123, true] can incorrectly grant admin access

Multi-Layer Protection Strategy

Every query passes through multiple security validation layers

Layer 1: Automatic Parameter Binding
All user-provided values are converted into bound parameters. Values do not enter SQL text as literals.
- User values are represented using dialect-specific placeholders (e.g. $1 or ?)
- Array inputs are parameterized (expanded placeholders or array parameters depending on dialect/strategy)
- NULL and optional values handled without concatenating user input into SQL text
- Date/time values are parameterized
- JSON values are parameterized
Layer 2: Field Name Validation
Every field name is validated against the Prisma schema metadata before query generation.
- Only schema-defined fields are allowed
- Relation filters validated via schema metadata
- No arbitrary field names accepted
- Unsupported/computed fields rejected where applicable
- Prototype pollution payloads are rejected (e.g. __proto__, constructor)
Layer 3: Identifier Sanitization
Table names, column names, and aliases are validated and safely quoted/escaped as required.
- Control characters rejected
- Reserved keywords are quoted when needed
- Schema qualification supported where configured
- Double-quote escaping (or dialect equivalent) for identifiers
- Maximum identifier length enforced per dialect
Layer 4: Operator Validation
Only known, safe operators are allowed for each field type.
- String operators: contains, startsWith, endsWith
- Numeric operators: lt, lte, gt, gte
- Array operators: in, notIn with type validation
- Logical operators: AND, OR, NOT validated structurally
- Unknown operators rejected immediately
Layer 5: Parameter Order Guarantee
Strict ordering ensures each placeholder maps to exactly one parameter in insertion order, preventing mismatches.
- Sequential counter prevents reordering
- Lockstep SQL building and param array construction
- No intermediate buffer or reordering operations
- Test verification: generated SQL and params always align
- One-to-one correspondence: placeholder N → params[N-1] (or equivalent mapping for the dialect)

Formal Security Guarantees

Proof sketches for injection resistance under stated design assumptions (parameter binding, no raw-SQL bypass, correct driver usage)

Theorem 1: Value Isolation

For all user-provided values V, there exists no execution path where V appears as SQL text in the generated query string.

By construction, all code paths that handle user values call addParameter(value) which: 1. Stores the value in a separate params array 2. Returns a placeholder token for the SQL text 3. Only the placeholder token is appended into the SQL string 4. The database driver receives SQL text and parameter values separately ∴ User values are not parsed as SQL syntax

const addParameter = (params: any[], value: any) => {
  params.push(value)
  const index = params.length
  return `$${index}`
}

Theorem 2: Field Name Closure

The set of field names F in any generated query is a subset of schema-defined fields S, i.e., F ⊆ S.

For every field reference: 1. Field name extracted from query object 2. Lookup performed against schema metadata 3. If the field does not exist in the schema, an error is thrown 4. Only validated fields reach SQL generation ∴ Arbitrary field names cannot appear in SQL

const validateField = (field: string, model: Model) => {
  if (!model.fields.has(field)) {
    throw new Error(`Field ${field} does not exist`)
  }
  return model.fields.get(field)
}

Theorem 3: Operator Safety

For any operator O applied to field F, O is a member of the allowed operators for F's type T.

Operator validation algorithm: 1. Extract field type T from schema 2. Define allowed_ops(T) = { valid operators for type T } 3. For operator O in query: - If O ∉ allowed_ops(T), throw error - Else apply operator using parameter binding ∴ Only type-appropriate operators can be used

const ALLOWED_OPS: Record<string, string[]> = {
  String: ['contains', 'startsWith', 'endsWith'],
  Int: ['lt', 'lte', 'gt', 'gte']
}
if (!ALLOWED_OPS[fieldType]?.includes(operator)) {
  throw new Error('Invalid operator')
}

Theorem 4: Identifier Safety

All SQL identifiers I are validated to prevent control characters and to ensure safe quoting/escaping per dialect.

Identifier processing: 1. Reject if contains control characters 2. Escape internal quote characters per dialect rules 3. Quote identifiers when required (reserved keywords or special characters) 4. Enforce per-dialect identifier length limits ∴ Identifiers cannot break SQL syntax or introduce injected tokens

const quoteIdentifier = (id: string) => {
  if (/[\x00-\x1F]/.test(id)) {
    throw new Error('Invalid characters')
  }
  const escaped = id.replace(/"/g, '""')
  const needsQuoting = /[^a-z0-9_]/i.test(id) || isReservedKeyword(id)
  return needsQuoting ? `"${escaped}"` : id
}

Theorem 5: Parameter Order Consistency

For every placeholder position N emitted into the SQL text, params[N-1] contains the exact value intended for that position, with no reordering or mismatch.

Parameter ordering guarantee: 1. A single tracker maintains insertion order 2. Each add() call appends the value to params and immediately emits the next placeholder token 3. SQL text construction and params construction proceed in lockstep 4. No intermediate reordering operations occur ∴ One-to-one correspondence is maintained throughout

class ParameterTracker {
  private params: any[] = []

  add(value: any): string {
    this.params.push(value)
    return `$${this.params.length}`
  }

  getParams(): any[] {
    return this.params
  }
}

const tracker = new ParameterTracker()
const sql = `WHERE email = ${tracker.add(email)} AND age > ${tracker.add(age)}`
const params = tracker.getParams()

Comprehensive Security Test Coverage

The current suite validates protection against common SQL injection vectors and edge cases

Value Parameterization (basic.test.ts)

Strings with quotes: user'with'quotes
Strings with semicolons: user;extra
SQL keywords as values: DROP TABLE users
Complex injection: '; DROP TABLE users; --
Union attacks: ' UNION SELECT * FROM users--
Boolean attacks: admin' OR '1'='1
Comment injection: test@example.com' -- comment

Field Name Validation (identifiers.test.ts)

Reject malicious field names in SELECT
Reject malicious field names in WHERE
Reject malicious field names in ORDER BY
Reject SQL injection in field names
Reject non-existent fields
Reject prototype pollution: __proto__, constructor

LIKE Pattern Safety (like-patterns.test.ts)

Wildcard injection: %' OR '1'='1
Underscore injection: test_' OR '1'='1
Backslash handling: test\\'; DROP--
Multiple wildcards: %_%'; DROP--
Case insensitive injection: '; UNION SELECT--

Array Operator Safety (array-operators.test.ts)

IN with malicious arrays: ['; DROP--', 'UNION SELECT--']
NOT IN with injection: ['; TRUNCATE--', 'DELETE FROM--']
Empty array handling
Large array validation (100+ items)
Mixed type array handling

Edge Cases (edge-cases.test.ts)

Unicode injection: \u0027 OR \u00271\u0027=\u00271
Hex-encoded injection: 0x31=0x31--
URL encoded: %27%3B%20DROP%20TABLE
Stacked queries: '; DROP TABLE users; SELECT
Time-based blind (dialect-specific): WAITFOR DELAY '00:00:05'--
UNION-based: UNION ALL SELECT null, password
Second-order injection attempts

Parameter Order Verification (basic.test.ts)

Sequential placeholder positions
Parameter array matches placeholder order
Complex queries maintain order across conditions
Nested OR/AND conditions preserve parameter sequence
Relation filters maintain correct parameter mapping

Safe vs. Unsafe: Code Comparison

❌ Unsafe: String Concatenation

unsafe.ts

const email = req.body.email
const sql = "SELECT * FROM users WHERE email = '" + email + "'"

✅ Safe: Automatic Parameter Binding

safe.ts

const email = req.body.email
const { sql, params } = toSQL('User', 'findMany', {
  where: { email }
})

const result = { sql, params }

Implementation Details

Parameter Management

Centralized parameter tracking ensures every user-provided value is parameter bound and order-stable

class ParameterTracker {
  private params: any[] = []

  add(value: any): string {
    this.params.push(value)
    return `$${this.params.length}`
  }

  getParams(): any[] {
    return this.params
  }
}

Field Validation

Schema-based validation prevents arbitrary field access

function validateField(
  fieldName: string,
  model: ModelInfo
): FieldInfo {
  const field = model.fields.get(fieldName)
  if (!field) {
    throw new Error(
      `Field "${fieldName}" does not exist`
    )
  }
  return field
}

Identifier Quoting

Dialect-aware identifier validation and quoting prevents identifier-based syntax injection

function quoteIdentifier(identifier: string): string {
  if (/[\x00-\x1F]/.test(identifier)) {
    throw new Error('Invalid control characters')
  }

  const escaped = identifier.replace(/"/g, '""')
  const needsQuoting = /[^a-z0-9_]/i.test(identifier) || isReservedKeyword(identifier)

  if (needsQuoting) {
    return `"${escaped}"`
  }

  return identifier
}

Security Best Practices

Never Disable Validation

Do not bypass schema validation or field checking. These are critical security layers.

Do: Use the library as designed with full validation
Don't: Bypass schema validation or inject raw SQL into query text

Keep Schema Updated

Ensure your Prisma schema accurately reflects your database structure.

Do: Run prisma db pull and prisma generate after schema changes
Don't: Use outdated DMMF or schema definitions

Validate Input Types

TypeScript provides compile-time safety, but runtime validation adds defense in depth.

Do: Use Prisma's generated types for query arguments
Don't: Cast user input to any before passing to queries

Monitor Query Patterns

Log and monitor generated SQL and parameters in production to detect anomalies and misuse.

Do: Enable query logging and review patterns
Don't: Run in production without monitoring

Security Through Design

SQL injection protection is built into every layer of this library. Review the security tests and implementation details directly.

View Security Tests View Source Code