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