Crlf Injection in Koa with Cockroachdb

Crlf Injection occurs when an attacker can inject a carriage return (CR, \r) and line feed (\n) sequence into a header or query value, causing the application to split and falsify headers, cache entries, or SQL statements. In a Koa application that builds SQL for Cockroachdb using string concatenation or interpolation, injecting \r\n into user-controlled input can change the intent of a query or smuggle additional statements.

Consider a Koa route that constructs a Cockroachdb query from a request parameter without sanitization:

// Unsafe: directly interpolating user input into SQL string
app.use(async (ctx) => {
  const user = ctx.request.query.name; // e.g., admin\r\n--
  const sql = `SELECT * FROM users WHERE name = '${user}'`;
  const result = await pool.query(sql);
  ctx.body = result.rows;
});

If the input is admin\r\n--, the resulting SQL sent to Cockroachdb becomes:

SELECT * FROM users WHERE name = 'admin'
--';

The comment sequence -- (note the leading space after \r\n) can cause the rest of the query to be ignored, potentially bypassing authentication or data isolation. Cockroachdb adheres to standard SQL behavior for line comments, so the injected line break and comment successfully alter query logic.

Another scenario involves header manipulation in Koa middleware before issuing a Cockroachdb query. For example, if a header value derived from user input is forwarded to the database or logged in a way that affects query construction, an injected \r\n can inject a new header or alter an existing one:

// Unsafe: setting a response header from user input
const sort = ctx.request.query.sort; // e.g., name\r\nSet-Cookie: session=steal
ctx.set('X-Sort-By', sort);
// If later used to dynamically form SQL, the injected CRLF may split logic

In this case, the injected CRLF can create an additional header, which may not directly execute SQL but can be chained in a broader attack flow when combined with other vectors (e.g., cache poisoning or header smuggling). When such manipulated values are later used to construct dynamic SQL for Cockroachdb, the injected newline and comment can again change query semantics.

Key points specific to this combination:

• Koa does not inherently sanitize query parameters or header values; developers must treat all external input as untrusted.
• Cockroachdb follows SQL comment rules, so \r\n-- effectively comments out the remainder of the statement.
• String-based query construction in JavaScript/TypeScript is prone to these issues; prefer parameterized patterns to avoid inadvertent line-break injection.

Remediation focuses on preventing CRLF characters from affecting SQL construction and ensuring that input is handled safely before being sent to Cockroachdb. The primary defense is to avoid string interpolation for SQL and instead use parameterized queries or prepared statements that keep data separate from commands.

Example: safe query using placeholders with the pg client (which Cockroachdb supports):

// Safe: using parameterized query to prevent CRLF injection
app.use(async (ctx) => {
  const user = ctx.request.query.name;
  const result = await pool.query('SELECT * FROM users WHERE name = $1', [user]);
  ctx.body = result.rows;
});

This approach ensures that the input is treated strictly as a parameter value. Even if the value contains \r\n--, it cannot alter the structure of the SQL command because the query and data are sent separately to Cockroachdb.

If you must construct dynamic SQL, explicitly disallow or escape CRLF characters in user input:

// Sanitize input by rejecting or replacing CRLF characters
function sanitize(str) {
  if (str == null) return str;
  return str.replace(/[\r\n]+/g, '');
}

app.use(async (ctx) => {
  const raw = ctx.request.query.name;
  const safeName = sanitize(raw);
  const result = await pool.query('SELECT * FROM users WHERE name = $1', [safeName]);
  ctx.body = result.rows;
});

Additionally, validate input format to enforce expectations (e.g., alphanumeric usernames) and reduce the attack surface:

// Validate input format strictly
function isValidName(name) {
  return /^[A-Za-z0-9_]+$/.test(name);
}

app.use(async (ctx) => {
  const candidate = ctx.request.query.name;
  if (!candidate || !isValidName(candidate)) {
    ctx.status = 400;
    ctx.body = { error: 'Invalid name' };
    return;
  }
  const result = await pool.query('SELECT * FROM users WHERE name = $1', [candidate]);
  ctx.body = result.rows;
});

When integrating with middleware, ensure that any header-derived values used for database decisions are also sanitized:

// Safe handling of header-derived values before using with Cockroachdb
const sortHeader = ctx.request.get('X-Sort-By') || 'id';
const safeSort = sortHeader.replace(/[\r\n]/g, '');
const validColumns = new Set(['name', 'created_at', 'id']);
const orderBy = validColumns.has(safeSort) ? safeSort : 'id';
const result = await pool.query('SELECT * FROM users ORDER BY $1 ASC LIMIT 10', [orderBy]);
ctx.body = result.rows;

These patterns reduce the risk of CRLF injection by removing or strictly controlling line-break characters and by leveraging Cockroachdb’s support for parameterized queries. They also align with secure coding practices for SQL interfaces in Node.js environments.