search_path Hijacking: The PostgreSQL Attack You've Never Heard Of

Source: Dev.to

Most developers know about SQL injection. Few know about search_path hijacking.

It’s just as dangerous.

What is search_path?

PostgreSQL’s search_path determines which schema to look in when you reference an unqualified table name.

-- With search_path = public, these are equivalent:
SELECT * FROM users;
SELECT * FROM public.users;

The Attack

If an attacker can control the search_path, they can redirect your queries to malicious tables:

// ❌ Dynamic search_path from user input
const schema = req.query.tenant; // Attacker controls this
await client.query(`SET search_path TO ${schema}`);
await client.query('SELECT * FROM users'); // Now queries attacker's schema

How it works

1. Attacker creates a schema with a malicious users table.
2. Attacker sets search_path to their schema.
3. Your query returns the fake data.

Why This Matters

The Correct Pattern

// ✅ Static search_path
await client.query(`SET search_path TO tenant_${tenantId}`);

// ✅ Validated against allowlist
const ALLOWED_SCHEMAS = ['tenant_1', 'tenant_2', 'tenant_3'];
if (!ALLOWED_SCHEMAS.includes(schema)) {
  throw new Error('Invalid schema');
}
await client.query(`SET search_path TO ${schema}`);

// ✅ Fully qualified table names
await client.query('SELECT * FROM public.users'); // Explicit schema

Let ESLint Catch This

npm install --save-dev eslint-plugin-pg

import pg from 'eslint-plugin-pg';
export default [pg.configs.recommended];

Dynamic search_path is caught:

src/tenants.ts
  8:15  error  🔒 CWE-426 | Dynamic search_path detected
               Fix: Use static schema name or validate against allowlist

Multi‑Tenant Pattern

// ✅ Safe multi-tenant with validated schema
async function queryTenant(tenantId, sql, params) {
  // Validate tenant exists
  const tenant = await getTenant(tenantId);
  if (!tenant) throw new Error('Unknown tenant');

  const client = await pool.connect();
  try {
    // Schema name from trusted source, not user input
    await client.query(`SET search_path TO tenant_${tenant.id}`);
    return await client.query(sql, params);
  } finally {
    // Reset search_path
    await client.query('SET search_path TO public');
    client.release();
  }
}

Quick Install

npm install --save-dev eslint-plugin-pg

import pg from 'eslint-plugin-pg';
export default [pg.configs.recommended];

Don’t let attackers hijack your queries.

• 📦 npm: eslint-plugin-pg
• 📖 Rule docs: no-unsafe-search-path
• ⭐ Star on GitHub