Link Search Menu Expand Document

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

OWASP - SQL Injection

OWASP - SQL Injection Prevention Cheat Sheet