SQL Injection in NodeJS
Vulnerable example
The following Express.js middleware implements a simple HTTP basic authentication mechanism backed by a SQLite database:
function auth_filter(req, res, next) {
const user = auth(req) || {};
const sql = `SELECT COUNT(*) AS authorized
FROM user
WHERE username = '${user.name}' AND
password = '${user.pass}'`;
// use SQL to authorize the resource
private_db.get(sql, function (err, row) {
if (err || !row.authorized) {
res.writeHead(401, {'WWW-Authenticate': 'Basic realm="Private"'});
res.end('Unauthorized');
} else {
next();
}
});
}
Since the SQL query is built by concatenating the user.name and user.pass input values in the string, an attacker could manipulate the query to return at least one record and bypass the authentication mechanism.
For example, by injecting ' OR 1 OR ' in the username and any random character in the password fields, the query becomes:
SELECT COUNT(*) AS authorized
FROM user
WHERE username = '' OR 1 OR '' AND
password = 'xxx'
The manipulated query returns the total record count in the table in the authorized variable, which (unless it is 0) allows the authorization condition to be bypassed: !row.authorized.
Prevention
Most database adaptors provide APIs to perform parametrized queries.
Examples
node-sqlite3
const sql = `SELECT COUNT(*) AS authorized
FROM user
WHERE username = ? AND
password = ?`;
// use SQL to authorize the resource
private_db.get(sql, [user.name, user.pass], /*...*/);
References
CWE - CWE-89: Improper Neutralization of Special Elements used in an SQL Command