🔒 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 ?)

100%
Parameter bound
All user-provided values use placeholders
Zero
Value interpolation
User data never appears in SQL text
Guaranteed
Order preservation
Placeholders map to params 1:1
137
Security tests
Injection attempt coverage (current suite)

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

137 tests validate protection against common SQL injection vectors and edge cases (current suite)

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

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

✅ Safe: Automatic Parameter Binding

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