Parameterized Queries for SQL and NoSQL Injection: Secure Implementation Guide

Unparameterized database calls are the single most common path from a web request to a full database compromise. When query structure and runtime data share the same string, the database parser cannot distinguish intent from payload — an attacker who can inject ' OR 1=1; -- into a login form can read every row in the users table. This guide covers the complete implementation of parameterized queries and prepared statements for both relational and document databases, from driver-level binding through ORM edge cases and CI enforcement. It is part of the Injection Attack Prevention cluster within Vulnerability Patterns & Web Mitigation Strategies.

For the threat-modeling perspective on where injection sits in a system’s risk surface, see how trust boundaries frame every data access layer crossing.

Prerequisites

  • Node.js 18+ or Python 3.10+ project with an active database connection
  • pg or mysql2 for SQL; mongodb driver or Mongoose for NoSQL
  • Semgrep CLI available locally or in CI (npm install -g @semgrep/semgrep or pip install semgrep)
  • Basic familiarity with your ORM (Prisma, TypeORM, or Mongoose)

Expected Outcomes

  • All SQL queries transmitted via bound parameters, never string concatenation
  • NoSQL query objects validated against an explicit operator allowlist before execution
  • ORM raw-query escape hatches audited and wrapped in type-safe helpers
  • Semgrep CI gate blocking any new unparameterized database call on every pull request

Step 1: Enforce Driver-Level Parameter Binding for SQL

The safest SQL parameterization happens at the lowest possible layer: the database driver. Both pg (PostgreSQL) and mysql2 (MySQL/MariaDB) support native prepared statements that transmit the query plan and the data payload through separate protocol channels. Even if an attacker supplies a value containing SQL syntax, the driver serialises it as an opaque string literal — never as executable query text.

import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

// SECURE: positional parameter binding ($1, $2, ...)
export async function getUserById(userId: string): Promise<User | null> {
  const query = 'SELECT id, email, role FROM users WHERE id = $1 AND is_active = true';
  const { rows } = await pool.query<UserRow>(query, [userId]);
  return rows[0] ?? null;
}

// SECURE: multi-parameter insert
export async function createAuditEntry(
  userId: string,
  action: string,
  ipAddress: string
): Promise<void> {
  const sql = `
    INSERT INTO audit_log (user_id, action, ip_address, created_at)
    VALUES ($1, $2, $3, NOW())
  `;
  await pool.query(sql, [userId, action, ipAddress]);
}

For mysql2, use the ? placeholder convention and the execute method (which uses true server-side prepared statements) rather than query (which uses client-side escaping):

import mysql from 'mysql2/promise';

const connection = await mysql.createConnection(process.env.MYSQL_URL!);

// SECURE: server-side prepared statement via execute()
const [rows] = await connection.execute(
  'SELECT id, email FROM users WHERE email = ? AND is_active = 1',
  [req.body.email]
);

Dynamic LIKE and ORDER BY — the parameterization edge cases

Parameters bind literals only. Two constructs commonly bypass this:

LIKE wildcards — the percent signs are string literals, not SQL syntax, so they can be concatenated safely onto the validated search term before binding:

// SECURE: wildcard is added in application code; the bound value remains safe
const searchTerm = `%${req.query.search.replace(/[%_\\]/g, '\\$&')}%`;
const { rows } = await pool.query(
  "SELECT name FROM products WHERE name ILIKE $1 ESCAPE '\\'",
  [searchTerm]
);

ORDER BY identifiers — column names and sort directions are SQL keywords; they cannot be parameterized. Validate them against a compile-time allowlist:

const SORTABLE_FIELDS = ['created_at', 'name', 'price', 'updated_at'] as const;
type SortField = typeof SORTABLE_FIELDS[number];

function resolveSortField(input: unknown): SortField {
  return SORTABLE_FIELDS.includes(input as SortField)
    ? (input as SortField)
    : 'created_at';
}

const sortField = resolveSortField(req.query.sort);
const direction = req.query.dir === 'DESC' ? 'DESC' : 'ASC';

// SECURE: identifier comes from a closed set defined in source code
const { rows } = await pool.query(
  `SELECT * FROM products ORDER BY ${sortField} ${direction}`
);

Never accept a sort field that was not defined at compile time. The allowlist is the control, not parameterization.

Step 2: Sanitize NoSQL Query Objects Against Operator Injection

Document databases parse query payloads as structured objects rather than text strings, so injection looks different: an attacker supplies a MongoDB operator key ($gt, $where, $regex) inside a field value rather than SQL syntax. The underlying risk — user-supplied input controlling query logic — is identical, but the mitigation layer is the query-object constructor rather than the protocol binding.

The data-flow diagram below shows the two injection paths and where each control intercepts them.

SQL vs NoSQL Injection Data Flow Diagram showing an HTTP request splitting into a SQL path (intercepted by prepared statement binding) and a NoSQL path (intercepted by operator-key sanitization) before reaching their respective databases. HTTP Request (untrusted input) Application Layer (route handler / data access) SQL path NoSQL path Prepared Statement Binding ($1, ?) separates plan from data Object Sanitization Strip / reject unknown $ operator keys PostgreSQL / MySQL (trusted query context) MongoDB / DocumentDB (trusted query context)

Strip operator keys with an explicit allowlist 

// Operators you intentionally support; reject everything else
const ALLOWED_OPERATORS = new Set([
  '$eq', '$ne', '$gt', '$gte', '$lt', '$lte', '$in', '$nin'
]);

function sanitizeQueryObject(obj) {
  if (typeof obj !== 'object' || obj === null) return obj;
  if (Array.isArray(obj)) return obj.map(sanitizeQueryObject);

  const clean = {};
  for (const [key, value] of Object.entries(obj)) {
    if (key.startsWith('$') && !ALLOWED_OPERATORS.has(key)) {
      throw new Object(`Disallowed query operator: ${key}`);
    }
    clean[key] = sanitizeQueryObject(value);
  }
  return clean;
}

// Usage in a route handler
const safeFilter = sanitizeQueryObject(req.body.filter);
const results = await db.collection('users').find(safeFilter).toArray();

The $where operator is absent from the allowlist intentionally — it executes server-side JavaScript and must never be accepted from user input.

Schema validation as the API-boundary gate

Use Zod’s .strict() mode to reject any unknown keys (including $-prefixed operators) before the query object even reaches the database layer:

import { z } from 'zod';

const UserSearchSchema = z.object({
  email:    z.string().email().optional(),
  username: z.string().min(3).max(32).optional(),
  status:   z.enum(['active', 'suspended', 'deleted']).optional()
}).strict(); // rejects keys not listed above — including $gt, $where, $regex

export function validateSearchInput(input: unknown) {
  const result = UserSearchSchema.safeParse(input);
  if (!result.success) {
    throw new Error(
      'Invalid query parameters: ' +
      result.error.errors.map(e => e.message).join(', ')
    );
  }
  return result.data;
}

Schema validation and object sanitization are complementary: the schema gates the API surface; the sanitizer is a defence-in-depth fallback for query objects assembled from multiple sources.

Step 3: Audit ORM Raw-Query Escape Hatches

ORMs like Prisma, TypeORM, and Mongoose parameterize standard model operations automatically. The injection risk resurfaces when developers reach for raw-query methods for complex joins, aggregations, or migrations that the ORM abstraction cannot express.

Prisma: tagged template literals vs $queryRawUnsafe 

const userId = req.params.id; // untrusted

// VULNERABLE — interpolation bypasses parameterization
const bad = await prisma.$queryRawUnsafe(
  `SELECT * FROM orders WHERE user_id = ${userId}`
);

// SECURE — Prisma's tagged template literal binds values automatically
const safe = await prisma.$queryRaw`
  SELECT id, total, status
  FROM orders
  WHERE user_id = ${userId}
  AND status = 'pending'
`;

Never use $queryRawUnsafe with user-supplied values. Treat it identically to raw pool.query with string concatenation.

Mongoose: ObjectId casting and aggregation pipelines

Mongoose casts ObjectId strings automatically in standard queries, but aggregation pipelines bypass that casting. Always construct pipeline stages explicitly:

import mongoose from 'mongoose';

const userId = req.params.id;

// SECURE: explicit ObjectId cast isolates the type boundary
const pipeline = [
  { $match: { userId: new mongoose.Types.ObjectId(userId) } },
  { $group: { _id: '$category', total: { $sum: '$amount' } } },
  { $sort: { total: -1 } }
];

const results = await Order.aggregate(pipeline);

Wrap the ObjectId constructor in a try/catch — an invalid id string throws and reveals whether the field is a valid MongoDB ObjectId, which itself can be an information-disclosure vector. Validate the format with a regex before casting.

Audit logging without leaking parameter values

Log query templates and execution metadata at the data-access layer, but exclude bound parameter values from log entries to prevent credential leakage:

async function trackedQuery<T>(
  label: string,
  queryFn: () => Promise<T>
): Promise<T> {
  const start = Date.now();
  try {
    const result = await queryFn();
    console.log(JSON.stringify({
      event: 'db_query',
      label,
      durationMs: Date.now() - start
      // values intentionally omitted
    }));
    return result;
  } catch (err) {
    console.error(JSON.stringify({ event: 'db_query_error', label, error: (err as Error).message }));
    throw err;
  }
}

// Usage
const user = await trackedQuery('getUserById', () =>
  pool.query('SELECT id, email FROM users WHERE id = $1', [userId])
);

Step 4: Deploy a SAST CI Gate to Block Unparameterized Queries

The steps above establish the implementation standard. A Semgrep CI gate enforces it continuously by failing every pull request that introduces a new string-concatenated database call.

# .semgrep/rules/injection-detection.yml
rules:
  - id: sql-string-interpolation
    message: "SQL injection risk: query built with string interpolation. Use parameterized queries."
    severity: ERROR
    languages: [typescript, javascript]
    patterns:
      - pattern: $POOL.query(`...${$VAR}...`)
      - pattern: $POOL.query("..." + $VAR + "...")

  - id: prisma-raw-unsafe
    message: "$queryRawUnsafe with dynamic values is a SQL injection vector. Use $queryRaw tagged template."
    severity: ERROR
    languages: [typescript]
    pattern: $PRISMA.$queryRawUnsafe(`...${$VAR}...`)

  - id: nosql-direct-spread
    message: "NoSQL injection risk: user input spread directly into find() filter. Sanitize operators first."
    severity: ERROR
    languages: [javascript, typescript]
    pattern: $COLL.find({...$REQ.body})
# .github/workflows/security-scan.yml
name: SAST Injection Scan
on: [pull_request]

jobs:
  semgrep:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: returntocorp/semgrep-action@v1
        with:
          config: >-
            p/default
            .semgrep/rules/injection-detection.yml
          publishToken: ${{ secrets.SEMGREP_APP_TOKEN }}
          failOn: error

Add a pre-commit hook for immediate developer feedback:

{
  "husky": {
    "hooks": { "pre-commit": "lint-staged" }
  },
  "lint-staged": {
    "*.{ts,js}": ["semgrep --config .semgrep/rules/injection-detection.yml --error"]
  }
}

Any semgrep: ignore suppression must be reviewed by a security engineer. Configure branch protection to require this CI check before merge.

Verification

Run the following to confirm parameterization is in effect across the codebase:

# Scan for raw string concatenation in DB calls (should return zero findings)
semgrep --config .semgrep/rules/injection-detection.yml --error src/

# Verify no $queryRawUnsafe calls with dynamic values remain
grep -rn '\$queryRawUnsafe' src/ | grep -v '// semgrep: ignore'

# For MongoDB: confirm $where is absent from all query objects in source
grep -rn '\$where' src/

# Run your integration test suite targeting the data access layer
npm test -- --testPathPattern=src/db

Expected: Semgrep exits 0, $queryRawUnsafe grep finds no unreviewed callsites, $where grep finds nothing in query construction code, and the database integration tests pass.

Troubleshooting

Symptom Likely cause Fix
ERROR: bind message supplies 2 parameters but prepared statement "" requires 1 Parameter count in the values array does not match the placeholder count in the SQL string Count $1, $2, … placeholders and ensure the values array has the same length
Semgrep rule matches false positives on a validated helper function Pattern is too broad Add pattern-not-inside to exclude the safe wrapper, or add a targeted // semgrep: ignore with a justification comment
MongoServerError: unknown operator: $search Custom operator not on the allowlist Add $search to ALLOWED_OPERATORS if it is a legitimate MongoDB Atlas Search operator, then document the decision
Prisma $queryRaw tagged template returns wrong row count Prisma interprets BigInt row counts; comparison with === 1 fails Cast with Number(result[0].count) or use $executeRaw for DML statements
ORM migrations contain raw SQL and Semgrep flags them Migration files contain ALTER TABLE statements, not user input Add the migrations directory to .semgrepignore after confirming migrations never interpolate external values

Common Implementation Mistakes

Frequently Asked Questions

Can parameterized queries prevent all forms of SQL and NoSQL injection?

Parameterization eliminates the data/code boundary violation at query execution time, but it does not prevent second-order injection (stored payloads replayed into unparameterized queries), logic flaws, or identifier manipulation. Complete coverage requires strict input validation, least-privilege database roles, output encoding, and — for legacy endpoints — runtime application self-protection (RASP) or a WAF injection ruleset as a compensating control.

How do I handle dynamic column names or table references safely?

Parameters cannot bind SQL identifiers. Mandate strict server-side allowlists defined as TypeScript const arrays or enums, so the set of valid identifiers is determined at compile time and any invalid value fails validation before reaching the database.

Does using an ORM eliminate the need for manual parameterization?

No. ORMs abstract parameterization for standard model operations but expose raw-query methods that bypass those safeguards. Every use of Prisma’s $queryRawUnsafe, TypeORM’s query(), or a Mongoose $where clause must be treated as a high-risk boundary requiring explicit review and SAST coverage.

What is the performance overhead of prepared statements in high-throughput systems?

Negligible when paired with connection pooling and server-side query-plan caching. The database reuses the parsed and optimised execution plan for every subsequent execution with different parameters. Disabling caching increases CPU utilisation and parse latency. For extreme throughput, investigate batch inserts, read replicas, and query-plan tuning rather than trading away injection protection.