Server Side Template Injection in Sails with Cockroachdb

Server Side Template Injection (SSTI) occurs when an attacker can inject template expressions that are subsequently evaluated by the server-side rendering engine. In Sails.js, views are often rendered using templates (e.g., EJS) where dynamic data is interpolated. If user-controlled input is embedded into these templates without proper escaping or validation and then passed to database queries, SSTI can lead to unintended behavior, including data exposure or injection into the query layer.

When Sails applications use Cockroachdb as the backend database, the risk pattern typically involves dynamic query construction where template variables are directly interpolated into SQL strings or passed as parameters without sanitization. Cockroachdb, while PostgreSQL-wire compatible, still interprets injected SQL fragments if the application builds queries by concatenating strings. For example, consider a controller that renders a user profile page and passes a template variable into a raw query:

const userQuery = `SELECT * FROM users WHERE id = '${req.param('userId')}';

If req.param('userId') contains template-like syntax such as ${process.env.DATABASE_URL} or malicious SQL, and the view engine later evaluates expressions within the request lifecycle, the application may inadvertently expose sensitive environment variables or alter query logic. This is especially relevant when using Sails blueprint APIs or custom Waterline ORM queries that dynamically build SQL fragments based on template context.

Additionally, SSTI in Sails can amplify issues when combined with unsafe view helpers or custom template tags that execute logic. An attacker might supply input that manipulates template rendering to trigger database calls with elevated permissions or leak query metadata. Because Cockroachdb returns structured results, injected template expressions that resolve to SQL fragments can modify WHERE clauses, JOIN logic, or RETURNING clauses, leading to unauthorized data access.

The interaction between Sails’ view layer and Cockroachdb’s SQL engine creates a chain where unsanitized template input can propagate into query construction. This is particularly dangerous when developers assume ORM safeguards fully isolate template variables from raw SQL. In practice, any dynamic string assembly that includes user input should be treated as a potential injection vector, regardless of the database backend.

To mitigate SSTI when using Cockroachdb with Sails, ensure that user input is never directly interpolated into SQL strings or template expressions that influence query building. Use parameterized queries or the Waterline ORM’s built-in escaping mechanisms to separate data from commands.

Instead of string concatenation, use parameterized queries with placeholders. For example, with the node-postgres client commonly used with Cockroachdb in Sails services:

const { Client } = require('pg');
const client = new Client({ connectionString: process.env.COCKROACHDB_URI });
await client.connect();

// Safe parameterized query
const result = await client.query(
  'SELECT * FROM users WHERE id = $1',
  [req.param('userId')]
);
await client.end();

If using Waterline ORM, leverage its built-in query methods that avoid raw SQL assembly. For instance, to find a user by ID safely:

const user = await User.findOne(req.param('userId'));
// Waterline translates this to a parameterized query against Cockroachdb

For complex queries, use the sendNativeQuery method with bound parameters instead of interpolating values:

const users = await sails.getDatastore().sendNativeQuery(
  'SELECT email, role FROM users WHERE status = $1 AND created_at > $2',
  ['active', new Date('2023-01-01')]
);
// Parameters are passed separately, preventing template injection

Additionally, validate and sanitize all inputs that could reach templates. Use libraries like validator to check data formats before they are used in views or query construction. Ensure that any custom template helpers in Sails do not evaluate or execute injected strings as code.

Finally, apply principle of least privilege to database roles used by Sails. Even if injection occurs, restricted permissions can prevent destructive operations on Cockroachdb, limiting the impact of a potential SSTI exploit.