Executing SQL queries is security-sensitive. It has led in the past to the following vulnerabilities:
SQL injection is still one of the top 10 security vulnerabilities. Applications that execute SQL commands should neutralize any externally-provided values used in those commands. Failure to do so could allow an attacker to include input that changes the query so that unintended commands are executed, or sensitive data is exposed. Instead of trying to sanitize data by hand, SQL binding mechanisms should be used; they can be relied on to automatically perform a full sanitization.
This rule flags the execution of SQL queries which might be susceptible to SQL injection. The goal is to guide security code reviews.
This rule raises an issue when any of the following NodeJS APIs is used:
mysql.query() mysql2.query() pg.query() sequelize.query() You are at risk if you answered yes to this question.
You can also reduce the impact of an attack by using a database account with low privileges.
const sql = 'SELECT * FROM users WHERE id = ' + userinput;
// === MySQL ===
const mysql = require('mysql');
const mycon = mysql.createConnection({ host: host, user: user, password: pass, database: db });
mycon.connect(function(err) {
mycon.query(sql, (err, res) => {}); // Noncompliant
});
// === PostgreSQL ===
const pg = require('pg');
const pgcon = new pg.Client({ host: host, user: user, password: pass, database: db });
pgcon.connect();
pgcon.query(sql, (err, res) => {}); // Noncompliant
// === MySQL ===
const sql = 'SELECT name FROM users WHERE id = ?';
const mysql = require('mysql');
const mycon = mysql.createConnection({ host: host, user: user, password: pass, database: db });
mycon.connect(function(err) {
mycon.query(sql, [userinput], (err, res) => {});
});
// === PostgreSQL ===
const sql = 'SELECT name FROM users WHERE id = $1';
const pg = require('pg');
const pgcon = new pg.Client({ host: host, user: user, password: pass, database: db });
pgcon.connect();
pgcon.query(sql, [userinput], (err, res) => {});
No issue will be raised if the database command is defined by a hard-coded string (no concatenation).
pgcon.query('SELECT name FROM users WHERE id = 42', (err, res) => {});