Sql Injection in Postgresql

SQL injection in PostgreSQL occurs when user-supplied data is concatenated directly into SQL queries without proper sanitization. PostgreSQL's specific features create unique injection vectors that don't exist in other databases.

The most common PostgreSQL-specific vulnerability involves unescaped dollar-quoted strings. While dollar-quoting ($$...$$) provides convenience for embedding quotes, it creates injection risks when user input is interpolated:

const name = req.query.name; // User input: hello$$); DROP TABLE users; --
const query = `SELECT * FROM users WHERE name = $$${name}$$`;
// Resulting query:
// SELECT * FROM users WHERE name = $$hello$$); DROP TABLE users; --$$

PostgreSQL's array syntax ($$...$$) and dollar-quoted strings create injection patterns that standard SQL injection detection misses. The dollar-quoting allows attackers to break out of the intended string context and inject arbitrary SQL.

Another PostgreSQL-specific vector involves the EXECUTE statement with dynamic SQL. When combined with user input, this becomes dangerous:

const schema = req.query.schema; // User input: public; DROP TABLE sensitive_data;
const query = `SELECT * FROM ${schema}.users`;
// Resulting query:
// SELECT * FROM public; DROP TABLE sensitive_data; .users

PostgreSQL's support for custom functions and procedural language (PL/pgSQL) creates additional injection surfaces. User-controlled function names or parameters can lead to arbitrary code execution:

const funcName = req.query.func; // User input: malicious_func; DROP TABLE users;
const query = `SELECT * FROM ${funcName}(123)`;
// Resulting query:
// SELECT * FROM malicious_func; DROP TABLE users; (123)

PostgreSQL's COPY command for bulk data loading is another critical injection point. When file paths or data are user-controlled, attackers can read arbitrary files or inject data:

const filePath = req.query.file; // User input: /etc/passwd; SELECT * FROM users;
const query = `COPY users FROM '${filePath}'`;
// Resulting query:
// COPY users FROM '/etc/passwd; SELECT * FROM users;'

Detecting SQL injection in PostgreSQL requires understanding both general injection patterns and PostgreSQL-specific syntax. Static analysis tools must recognize dollar-quoted strings, array constructors, and PostgreSQL-specific functions.

middleBrick's PostgreSQL detection engine scans for these specific patterns:

// Detecting dollar-quoted string injection
/\$[^$]*\$[^$]*\$[^$]*\$/ // Pattern: $$...$$ with nested quotes

// Detecting dynamic SQL with EXECUTE
/EXECUTE\s+['"`].*?\$[^$]*\$.*?['"`]/i

// Detecting unsafe array constructors
/ARRAY\s*\[.*?\]/i

// Detecting COPY command injection
/COPY\s+\w+\s+FROM\s+['"`].*?['"`]/i

Runtime detection focuses on PostgreSQL's query execution patterns. The key indicators are:

• Dynamic SQL construction using string concatenation
• User input appearing in SQL without parameterization
• Use of EXECUTE with user-controlled variables
• Direct interpolation in dollar-quoted strings
• User-controlled schema or table names
• Unsafe COPY command usage

middleBrick's black-box scanning specifically tests PostgreSQL endpoints for these vulnerabilities. The scanner sends payloads designed to trigger PostgreSQL-specific error messages, confirming the database type and version:

Payload: ' OR 1=1; SELECT version(); --
Expected PostgreSQL response: PostgreSQL version string in error message

Payload: ' OR 1=1; SELECT current_database(); --
Expected PostgreSQL response: Current database name exposed

The scanner also tests for PostgreSQL-specific functions that might be exposed through injection:

Payload: ' OR 1=1; SELECT pg_sleep(5); --
If response delayed by ~5 seconds: PostgreSQL function accessible

For API endpoints, middleBrick analyzes request parameters and body data for injection patterns, then sends crafted payloads to observe database-specific error responses and timing characteristics unique to PostgreSQL.

PostgreSQL provides several native mechanisms for preventing SQL injection. The most fundamental is parameterized queries using prepared statements, which separate SQL logic from data:

// Vulnerable: String concatenation
const name = req.query.name;
const query = `SELECT * FROM users WHERE name = '${name}'`;

// Secure: Parameterized query
const query = {
  text: 'SELECT * FROM users WHERE name = $1',
  values: [req.query.name]
};

// Using node-postgres
await client.query(query);

// Using pg-promise
const data = await db.any('SELECT * FROM users WHERE name = $1', [req.query.name]);

For PostgreSQL-specific features like array handling, use proper array constructors rather than string concatenation:

// Vulnerable
const ids = req.query.ids; // User input: 1,2); DROP TABLE users; --
const query = `SELECT * FROM users WHERE id = ANY(ARRAY[${ids}])`;

// Secure
const ids = req.query.ids.split(',').map(Number);
const query = {
  text: 'SELECT * FROM users WHERE id = ANY($1::int[])',
  values: [ids]
};

When dynamic SQL is necessary (e.g., for dynamic table names), validate against a whitelist of allowed values:

const allowedSchemas = ['public', 'sales', 'hr'];
const schema = req.query.schema;
if (!allowedSchemas.includes(schema)) {
  throw new Error('Invalid schema');
}

// Schema is now safe to interpolate
const query = `SELECT * FROM ${schema}.users WHERE active = $1`;
const result = await client.query(query, [true]);

For COPY commands and bulk operations, use parameterized file paths and validate file existence:

// Vulnerable
const filePath = req.query.file;
const query = `COPY users FROM '${filePath}'`;

// Secure
const basePath = '/var/lib/postgresql/data/uploads/';
const filePath = path.join(basePath, req.query.filename);
if (!filePath.startsWith(basePath)) {
  throw new Error('Invalid file path');
}
const query = {
  text: 'COPY users FROM $1',
  values: [filePath]
};

PostgreSQL's row-level security (RLS) provides an additional defense layer by restricting data access at the database level:

// Enable RLS on users table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

// Create policy that restricts access based on user ID
CREATE POLICY user_access ON users
  FOR SELECT USING (user_id = current_setting('app.current_user_id')::int);

// In application code
process.env.PGTZ = 'UTC';
await client.query('SET app.current_user_id = $1', [req.user.id]);

For PostgreSQL functions, use SECURITY DEFINER with caution and validate all inputs:

CREATE OR REPLACE FUNCTION get_user_data(p_user_id int)
RETURNS TABLE(id int, name text) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name FROM users WHERE id = p_user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;