Server Side Template Injection in Cockroachdb

Server Side Template Injection (SSTI) in Cockroachdb environments typically occurs when user input is improperly handled in database queries or template rendering contexts. Unlike traditional web application SSTI, Cockroachdb-specific vulnerabilities often emerge through improper handling of JSONB data, template functions in stored procedures, or dynamic SQL generation.

One common pattern involves Cockroachdb's JSONB operators where template syntax gets embedded in JSON structures. For example:

SELECT * FROM users WHERE preferences @> '{"theme": "{{user_input}}"}'

If the application layer passes unsanitized user input into the JSONB template, an attacker could inject template directives that get executed by the database engine. This becomes particularly dangerous when combined with Cockroachdb's support for JavaScript UDFs (User-Defined Functions).

Cockroachdb's stored procedures can also be vulnerable when they use template-like syntax for dynamic query construction. Consider this vulnerable pattern:

CREATE OR REPLACE FUNCTION get_user_data(user_id TEXT) RETURNS TABLE AS $$
DECLARE
  query TEXT;
BEGIN
  query := 'SELECT * FROM users WHERE id = ''' || user_id || '''';
  RETURN QUERY EXECUTE query;
END;
$$ LANGUAGE plpgsql;

An attacker could inject template syntax through the user_id parameter, potentially causing the database to execute unintended operations. This is especially problematic when Cockroachdb's template engine processes the query before execution.

Another Cockroachdb-specific manifestation occurs with the database's support for template functions in views. Consider:

CREATE VIEW user_data AS
SELECT
  id,
  name,
  CASE WHEN role = '{{malicious}}' THEN 'admin' ELSE role END AS role
FROM users;

If the application layer substitutes template variables without proper validation, this could lead to privilege escalation or data exposure.

Detecting SSTI in Cockroachdb requires understanding both the database's template processing capabilities and common injection patterns. middleBrick's scanner specifically targets these Cockroachdb-specific vulnerabilities through several detection methods.

The scanner first identifies template syntax patterns within SQL queries and JSONB structures. It looks for common template delimiters like {{ }}, <% %>, and #{} that might be improperly embedded in database operations. For Cockroachdb specifically, it also checks for JavaScript UDF usage patterns that could be exploited for template injection.

middleBrick tests for SSTI by injecting template payloads into query parameters and observing the database's response. For example, it might test:

SELECT * FROM products WHERE category = '{{7*7}}'

If the database returns 49 instead of the literal string '{{7*7}}', this indicates template processing is occurring at the database level rather than being properly escaped by the application layer.

The scanner also examines stored procedures and functions for template-like string concatenation patterns. It specifically looks for:

• Dynamic SQL construction using user input
• JSONB operations with template syntax
• JavaScript UDF definitions that might process template data
• View definitions with conditional logic using template variables

middleBrick's Cockroachdb-specific checks include testing for template injection in JSONB operations, which are particularly common in modern applications using Cockroachdb's JSON capabilities. It verifies whether template syntax in JSONB queries gets processed or properly escaped.

The scanner also checks for template injection in Cockroachdb's vector and array operations, where template syntax might be embedded in complex data structures. This is especially relevant for applications using Cockroachdb's advanced data types.

Remediating SSTI in Cockroachdb environments requires a multi-layered approach that addresses both the database configuration and application code patterns. The first step is implementing proper input validation and escaping at the application layer before data reaches the database.

For JSONB operations in Cockroachdb, use parameterized queries instead of string concatenation:

-- Vulnerable pattern
SELECT * FROM users WHERE preferences @> '{