Fix SQL Injection (Legacy & Modern) in Sails

Sails.js and its Waterline ORM provide a layer of protection by default, but developers often bypass this for 'performance' or 'complex joins' using raw SQL. Whether you are on legacy Sails (<1.0) using .query() or modern Sails (1.0+) using .sendNativeQuery(), string concatenation is a death sentence. To secure your app, you must enforce parameterization to separate the SQL logic from the untrusted data.

The Vulnerable Pattern

// UNSAFE: String interpolation allows an attacker to break out of the query context
// Payload example: id = '1; DROP TABLE users;--'
const userId = req.param('id');
const rawSql = `SELECT * FROM users WHERE id = ${userId}`;
// This is a direct path to RCE or total data exfiltration
await sails.getDatastore().sendNativeQuery(rawSql);

The Secure Implementation

The vulnerability exists because the database engine cannot distinguish between the developer's commands and the attacker's data when they are concatenated into a single string. By using placeholders ($1, $2 or ?) and passing a separate array of values, you utilize the database's native parameter binding. This ensures that the input is never executed as code. In modern Sails, always use .getDatastore().sendNativeQuery(sql, [values]) and avoid template literals for any variable that originates from a request object.

// SAFE: Positional parameterization (PostgreSQL/MySQL style)
const userId = req.param('id');
const sql = 'SELECT * FROM users WHERE id = $1';
const values = [userId];
// The database driver handles escaping, treating input strictly as a literal
const result = await sails.getDatastore().sendNativeQuery(sql, values);
// LEGACY (Sails <1.0) SAFE VERSION:
// Pet.query(‘SELECT * FROM pet WHERE name = ?’, [‘Fido’], (err, results) => { … });