SQL Injection Prevention

Parameterised queries in TypeScript with pg

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

// VULNERABLE: string interpolation
async function findUserVulnerable(email: string) {
  // If email = "' OR '1'='1" this returns all users
  const result = await pool.query(
    "SELECT * FROM users WHERE email = '" + email + "'"
  );
  return result.rows;
}

// SAFE: parameterised query - $1 is a placeholder, not string interpolation
async function findUser(email: string) {
  const result = await pool.query(
    'SELECT id, email, name FROM users WHERE email = $1 AND active = true',
    [email]
  );
  return result.rows[0] ?? null;
}

// SAFE: multiple parameters
async function searchOrders(customerId: string, status: string, limit: number) {
  const result = await pool.query(
    'SELECT id, total, status FROM orders WHERE customer_id = $1 AND status = $2 LIMIT $3',
    [customerId, status, limit]
  );
  return result.rows;
}

Parameterised queries send the SQL and data in separate protocol messages; the database parses the SQL structure before seeing the data, making injection structurally impossible. The $1, $2 placeholders are PostgreSQL syntax; MySQL uses ? positional placeholders.

Safe dynamic queries with Arel in Ruby on Rails

class OrderQuery
  def initialize(scope = Order.all)
    @scope = scope
  end

  # SAFE: ActiveRecord parameterisation
  def by_customer(customer_id)
    @scope = @scope.where(customer_id: customer_id)
    self
  end

  # SAFE: hash syntax prevents injection
  def with_status(status)
    allowed = %w[pending confirmed shipped cancelled]
    raise ArgumentError, "Invalid status: #{status}" unless allowed.include?(status.to_s)
    @scope = @scope.where(status: status)
    self
  end

  # SAFE: Arel for dynamic column sorting (never interpolate column names)
  def sorted_by(column, direction = 'asc')
    allowed_columns = %w[created_at total status]
    allowed_directions = %w[asc desc]
    raise ArgumentError unless allowed_columns.include?(column.to_s)
    raise ArgumentError unless allowed_directions.include?(direction.to_s)
    @scope = @scope.order(Order.arel_table[column].send(direction))
    self
  end

  def results = @scope
end

orders = OrderQuery.new
  .by_customer(params[:customer_id])
  .with_status('confirmed')
  .sorted_by('created_at', 'desc')
  .results

Column names and sort directions cannot be parameterised; they must be validated against an allowlist. ActiveRecord's hash syntax (where(status: value)) automatically parameterises the value. Never use where("status = #{value}") with string interpolation.