Product: SonarQube Enterprise 9.5.0.56709
Rule: RSPEC-3649
Multiple SQL injections were found in the application by manual testing, but these were not detected by a Sonar scan.
I cannot share the full source code of the application, but here are the relevant parts for the code flow for the SQL injection in the key GET parameter.
backend/services/database.js
const oracledb = require('oracledb');
const dbConfig = require('../config/db.js');
async function initialize() {
const pool = await oracledb.createPool(dbConfig.motherBox);
}
module.exports.initialize = initialize;
async function close() {
await oracledb.getPool().close();
}
module.exports.close = close;
function simpleExecute(statement, binds = [], opts = {}) {
return new Promise(async (resolve, reject) => {
let conn;
opts.outFormat = oracledb.OBJECT;
opts.autoCommit = true;
oracledb.fetchAsString = [ oracledb.CLOB , oracledb.DATE ];
try {
conn = await oracledb.getConnection();
const result = await conn.execute(statement, binds, opts);
resolve(result);
} catch (err) {
reject(err);
throw err;
} finally {
if (conn) { // conn assignment worked, need to close
try {
await conn.close();
} catch (err) {
throw err;
}
}
}
});
}
module.exports.simpleExecute = simpleExecute;
backend/models/team.js
// Get Single team details from ID on specific field
getByKeyword: async function (strSelect = "*", strTeamKey) {
try {
var strSQl =
"SELECT " +
strSelect +
" FROM teams WHERE TEAM_KEY = '" +
strTeamKey +
"'";
const result = await database.simpleExecute(strSQl);
return result.rows;
} catch (error) {
console.log(
"[Error] File: " + fileName + ", Method: " + helper.getFunctionName(),
error
);
throw error;
}
},
...
backend/controllers/team.js (controller definition)
// Fetch Team's component from given key
exports.singleComponentFetch = async (req, res, next) => {
try {
strTeamKey = req.query.key;
const result = await Team.getByKeyword("ID", strTeamKey);
if (result.length == 0) {
console.log("WRONG Teams Key Supplied " + strTeamKey);
var arr = {
message: "Team Key not found",
component: []
};
return res.status(200).json(arr);
package.json
...
"dependencies": {
...
"jquery": "^3.6.0",
"oracledb": "^5.2.0",
"util": "^0.12.4",
...
},
"devDependencies": {
...
"@types/node": "^16.9.4",
...
"ts-node": "10.2.1",
"tslint": "~6.1.0",
"typescript": "^4.4.4"
}
Relevant snippets from the scanner output with sonar.verbose=true:
It seems the scanner couldn’t parse the oracledb dependency, therefore it has no clue that it should be considered a sink for this rule. Did I misconfigure something? The rule is obviously enabled.
What does this line supposed to mean?
(Exclude oracledb) Omitting dependency /usr/src/node_modules/oracledb/index.js since it is not in the list of simulated dependencies = undefined
8:52:43.868 INFO: SonarScanner 4.6.2.2472
18:52:43.870 INFO: Java 11.0.14 Alpine (64-bit)
18:52:43.870 INFO: Linux 5.10.104-linuxkit amd64
18:52:44.035 DEBUG: keyStore is :
18:52:44.035 DEBUG: keyStore type is : pkcs12
18:52:44.035 DEBUG: keyStore provider is :
18:52:44.036 DEBUG: init keystore
18:52:44.036 DEBUG: init keymanager of type SunX509
18:52:44.888 DEBUG: Create: /opt/sonar-scanner/.sonar/cache
18:52:44.889 INFO: User cache: /opt/sonar-scanner/.sonar/cache
18:52:44.889 DEBUG: Create: /opt/sonar-scanner/.sonar/cache/_tmp
18:52:44.893 DEBUG: Extract sonar-scanner-api-batch in temp...
18:52:44.897 DEBUG: Get bootstrap index...
18:52:53.964 INFO: Scanner configuration file: /opt/sonar-scanner/conf/sonar-scanner.properties
18:52:53.965 INFO: Project root configuration file: NONE
18:52:53.966 DEBUG: Execution getVersion
18:52:53.996 INFO: Analyzing on SonarQube server 9.5.0.56709
18:52:53.997 INFO: Default locale: "en_US", source code encoding: "UTF-8" (analysis is platform dependent)
18:52:53.998 DEBUG: Work directory: /usr/src/.scannerwork
18:52:54.001 DEBUG: Execution execute
18:52:54.436 DEBUG: Enterprise 9.5.0.56709
...
18:53:44.023 INFO: Load/download plugins (done) | time=48960ms
18:53:44.606 DEBUG: Plugins:
18:53:44.606 DEBUG: * Sonar Text Plugin 1.1.6 (communitytext)
18:53:44.606 DEBUG: * IaC Code Quality and Security 1.7.0.2012 (iac)
18:53:44.607 DEBUG: * PL/SQL Code Quality and Security 3.7.0.4372 (plsql)
18:53:44.607 DEBUG: * Scala Code Quality and Security 1.9.0.3429 (sonarscala)
18:53:44.607 DEBUG: * C# Code Quality and Security 8.40.0.48530 (csharp)
18:53:44.607 DEBUG: * Vulnerability Analysis 9.5.1.16463 (security)
18:53:44.608 DEBUG: * Java Code Quality and Security 7.12.0.29739 (java)
18:53:44.608 DEBUG: * HTML Code Quality and Security 3.6.0.3106 (web)
18:53:44.608 DEBUG: * Flex Code Quality and Security 2.7.0.2865 (flex)
18:53:44.609 DEBUG: * XML Code Quality and Security 2.5.0.3376 (xml)
18:53:44.609 DEBUG: * Text file Code Quality and Security 1.1.0.282 (text)
18:53:44.610 DEBUG: * VB.NET Code Quality and Security 8.40.0.48530 (vbnet)
18:53:44.610 DEBUG: * Swift Code Quality and Security 4.6.0.5406 (swift)
18:53:44.610 DEBUG: * CFamily Code Quality and Security 6.34.0.48468 (cpp)
18:53:44.610 DEBUG: * Python Code Quality and Security 3.13.0.9611 (python)
18:53:44.610 DEBUG: * Dataflow Bug Detection 1.2.2.1720 (dbd)
18:53:44.611 DEBUG: * Go Code Quality and Security 1.9.0.3429 (go)
18:53:44.612 DEBUG: * JaCoCo 1.1.1.1157 (jacoco)
18:53:44.612 DEBUG: * Kotlin Code Quality and Security 2.9.0.1147 (kotlin)
18:53:44.612 DEBUG: * RPG Code Quality 3.2.0.3034 (rpg)
18:53:44.612 DEBUG: * Dataflow Bug Detection Rules for Java 1.2.2.1720 (dbdjavafrontend)
18:53:44.613 DEBUG: * PL/I Code Quality and Security 1.11.1.2727 (pli)
18:53:44.613 DEBUG: * T-SQL Code Quality and Security 1.6.0.4844 (tsql)
18:53:44.613 DEBUG: * VB6 Code Quality and Security 2.8.0.3021 (vb)
18:53:44.613 DEBUG: * Apex Code Quality and Security 1.9.0.3429 (sonarapex)
18:53:44.613 DEBUG: * JavaScript/TypeScript/CSS Code Quality and Security 9.3.0.18033 (javascript)
18:53:44.614 DEBUG: * Ruby Code Quality and Security 1.9.0.3429 (ruby)
18:53:44.614 DEBUG: * Vulnerability Rules for C# 9.5.1.16463 (securitycsharpfrontend)
18:53:44.614 DEBUG: * Vulnerability Rules for Java 9.5.1.16463 (securityjavafrontend)
18:53:44.614 DEBUG: * Vulnerability Rules for JS 9.5.1.16463 (securityjsfrontend)
18:53:44.614 DEBUG: * COBOL Code Quality 5.0.0.5433 (cobol)
18:53:44.615 DEBUG: * Vulnerability Rules for Python 9.5.1.16463 (securitypythonfrontend)
18:53:44.615 DEBUG: * PHP Code Quality and Security 3.23.1.8766 (php)
18:53:44.615 DEBUG: * ABAP Code Quality and Security 3.10.0.3628 (abap)
18:53:44.615 DEBUG: * Configuration detection fot Code Quality and Security 1.2.0.267 (config)
18:53:44.616 DEBUG: * Vulnerability Rules for PHP 9.5.1.16463 (securityphpfrontend)
18:53:44.720 INFO: Loaded core extensions: developer-scanner
...
18:54:07.718 DEBUG: Average line length for backend/controllers/team.js is 32
18:54:07.719 DEBUG: 'backend/controllers/team.js' indexed with language 'js'
18:54:08.017 DEBUG: Average line length for backend/models/team.js is 34
18:54:08.017 DEBUG: 'backend/models/team.js' indexed with language 'js'
18:54:08.157 DEBUG: Average line length for backend/services/database.js is 22
18:54:08.157 DEBUG: 'backend/services/database.js' indexed with language 'js'
...
18:58:47.850 DEBUG: 'backend/models/team.js' generated metadata with charset 'UTF-8'
18:58:47.874 DEBUG: Resolved '../services/database.js' as '/usr/src/backend/services/database.js'
18:58:47.875 DEBUG: Resolved '../services/database.js' as '/usr/src/backend/services/database.js'
18:58:47.875 DEBUG: ... '../services/database.js' was imported in '/usr/src/backend/models/team.js'
18:58:47.886 DEBUG: Resolved '../lib/helpers' as '/usr/src/backend/lib/helpers.js'
18:58:47.886 DEBUG: Resolved '../lib/helpers' as '/usr/src/backend/lib/helpers.js'
18:58:47.886 DEBUG: ... '../lib/helpers' was imported in '/usr/src/backend/models/team.js'
18:58:48.072 DEBUG: Saving issue for rule no-var on line 8
18:58:48.074 DEBUG: Saving issue for rule default-param-last on line 39
18:58:48.076 DEBUG: Saving issue for rule no-var on line 98
18:58:48.078 DEBUG: Saving issue for rule no-var on line 114
18:58:48.080 DEBUG: Saving issue for rule no-var on line 127
...
18:58:53.591 DEBUG: 'backend/services/database.js' generated metadata with charset 'UTF-8'
18:58:53.615 DEBUG: Resolved 'oracledb' as '/usr/src/node_modules/oracledb/index.js'
18:58:53.615 DEBUG: (Resolved oracledb in node_modules): resolvedName 'oracledb' as '/usr/src/node_modules/oracledb/index.js'
18:58:53.615 DEBUG: (Exclude oracledb) Omitting dependency /usr/src/node_modules/oracledb/index.js since it is not in the list of simulated dependencies = undefined
18:58:53.619 DEBUG: Failed to resolve 'oracledb.ts'
18:58:53.619 DEBUG: Resolved 'oracledb' as 'undefined'
18:58:53.619 DEBUG: ... 'oracledb' was imported in '/usr/src/backend/services/database.js'
18:58:53.622 DEBUG: Resolved '../config/db.js' as '/usr/src/backend/config/db.js'
18:58:53.622 DEBUG: Resolved '../config/db.js' as '/usr/src/backend/config/db.js'
18:58:53.622 DEBUG: ... '../config/db.js' was imported in '/usr/src/backend/services/database.js'
18:58:53.695 DEBUG: Saving issue for rule sonar-no-unused-vars on line 5
18:58:53.699 DEBUG: Saving issue for rule no-dead-store on line 5
18:58:53.701 DEBUG: Saving issue for rule no-useless-catch on line 38
18:58:53.704 DEBUG: Saving issue for rule no-unsafe-finally on line 39
18:58:53.716 DEBUG: 'backend/db/team.js' generated metadata with charset 'UTF-8'
18:58:53.924 DEBUG: Saving issue for rule no-empty-function on line 2
...
18:59:16.530 DEBUG: 'backend/controllers/team.js' generated metadata with charset 'UTF-8'
18:59:16.546 DEBUG: Resolved '../models/team' as '/usr/src/backend/models/team.js'
18:59:16.546 DEBUG: Resolved '../models/team' as '/usr/src/backend/models/team.js'
18:59:16.546 DEBUG: ... '../models/team' was imported in '/usr/src/backend/controllers/team.js'
18:59:16.550 DEBUG: Resolved '../lib/helpers' as '/usr/src/backend/lib/helpers.js'
18:59:16.550 DEBUG: Resolved '../lib/helpers' as '/usr/src/backend/lib/helpers.js'
18:59:16.550 DEBUG: ... '../lib/helpers' was imported in '/usr/src/backend/controllers/team.js'
18:59:16.679 DEBUG: Saving issue for rule no-unused-function-argument on line 5
18:59:16.682 DEBUG: Saving issue for rule no-var on line 11
18:59:16.684 DEBUG: Saving issue for rule no-unused-function-argument on line 31
18:59:16.687 DEBUG: Saving issue for rule no-implicit-global on line 33
18:59:16.689 DEBUG: Saving issue for rule no-var on line 39
18:59:16.691 DEBUG: Saving issue for rule no-unused-function-argument on line 51
18:59:16.694 DEBUG: Saving issue for rule no-var on line 59
18:59:16.698 DEBUG: Saving issue for rule sonar-block-scoped-var on line 59
18:59:16.702 DEBUG: Saving issue for rule no-var on line 67
18:59:16.705 DEBUG: Saving issue for rule no-redeclare on line 67
18:59:16.707 DEBUG: Saving issue for rule prefer-for-of on line 73
18:59:16.710 DEBUG: Saving issue for rule no-var on line 92
18:59:16.712 DEBUG: Saving issue for rule no-redeclare on line 92
...