My name is Maxwell "ꓘ" Dulin and I am a Senior Security Engineer who attempts to hack everything under the sun; from web applications to garage doors. Recently, an article about a SQL injection technique that exists solely in the main MySQL NodeJS library was released. This technique works by injecting a JSON object into a parameterized query that expects a string. While looking at the payload for this exploit, I wondered two things:

• Why does this attack work with a nested object?
• Is my website using NodeJs vulnerable to this attack?

In this post, we will answer both of these questions. First, we will find the root cause of this bug within the mysqljs library. Second, we will walk through the exploitation of this vulnerability on my personal blog (this site!). NOTE: Please do not attack my site, as the vulnerability has already been patched.

Bypassing Escape Functions in mysqljs

The authors of the article did a wonderful job at explaining the attack and including a live demo. I will only be giving a quick summary of the initial part of the article; but, I encourage you to read the article and test out this bug yourself at the live demo from the post.

The article begins with a very simple NodeJs Express setup for a login endpoint; an image of this is shown in Figure 1. On lines 3 and 4, the username and password fields are put into local variables to be used in a query later. On lines 6-8, there is a classic SQL query that validates the user attempting to login matches the password on lines. This uses a parameterized query to add the request supplied username and password to prevent a SQL Injection.

The author then pulls out an exploit payload that looks nonsensical:

data = {
  "username": "admin",
  "password": {
    "password": 1
  }
};

The JSON object has two fields that are used: username and password. The object does not have a string for the password field though; instead, it has another object inside of the field. With the code snippet shown above and mysqljs as the library of choice for querying, the object shown above will completely bypass the authentication mechanism! To me, this seemed impossible since the code is using parameterized queries to prevent SQL injection. Therefore, I had to understand why this exploit works the way it does.

Why Does This Happen?

After going through the demo website for this, the reason for this bypass occurring still did not make sense to me. All I understood was that putting a very specific object bypassed the authentication flow. Why does putting a JSON object with very specific parameters bypass the authentication? In particular, what is the query parsing doing here? Since I was not satisfied with just knowing about the vulnerability, I decided to open up the source code to truly understand why this occurs.

Using connection.query()

There are several functions in the library that can be used for SQL queries but they all follow the same flow for following this vulnerability though. For the sake of example, we are going to choose connection.query(). This function takes in two main parameters:

• SQL query: The templated SQL query used for getting or altering data in the database. An example of this is SELECT * FROM accounts WHERE `username` = ?. The question mark (?) represents the values to be subbed in later.
• Inputs: The supplied input for the query to use. This is an array of as many objects as there are question marks in the query.

An example usage of this function is shown in Figure 1 above. Below is the source code for connection.query() within the library.

The purpose of using the question mark (?) and an array of separate values is to prevent the meaning of the query from being altered; the technique of changing the query is commonly known as SQL Injection (SQLi). The mysqljs library will take the inputs and escape them to ensure that the query meaning is not modified by our inputs.

The code for the query is shown in Figure 2. First, the function creates a query object at line 181. After that, on line 189, it passes both the SQL query and the values of our query to a function called format. Finally, it adds the query to the queue since this is an asynchronous function at line at 198. Since line 189 has the code for combining the query string and values, we want to evaluate this function. This turns out to be a wrapper around the library SqlString. So, down the rabbit hole we go!

SqlString

The function format is what we are looking for and is easy to find. This function works as follows:

1. On line 91, iterate through each expected input for the query. This is done by finding all question marks (?) in the query string.
2. Lines 98-100 escape the value to ensure that it cannot affect the query (SQLi protection) by calling the SqlString.escape() function. Depending on the data type, the operation is different. We will be coming back to this in the next section.
3. On line 102, the placeholder (question mark - ?) in the query is replaced with the proper value, after it has been escaped. Now, the query can be made without the fear of SQL injection attacks (or so we thought).

Escaping in SqlString

Once inside of the escape() function at line 34, the escaping depends on the type of the value being passed in. For instance, a string is passed into the function escapeString() in order to escape all malicious characters and a number is simply converted into a string and sent back.

When the datatype is an object, such as our attack above, the function SqlString.objectToValues() is called at line 54. ObjectToValues is fairly simple and can be summed up in one line (shown below). This line of code is performed for each key-value pair in the object:

data = SqlString.escapeId(key)
      + ' = '
      + SqlString.escape(val, true, timeZone);

The function SqlString.escapeId() escapes the value as if it is a literal identifier in SQL, such as a column name. In practice, this escapes single/double quotes and adds backticks (`) around the text. Then, an equals sign ( = ) is added in after the literal identifier for a comparison operation. Finally, the value of the object is escaped of all malicious characters by calling the SqlString.escape() function again. If we put this altogether, we end up with `key` = value being inserted into the query. In practice, this code allows for the addition of a user inputted column to compare a value against. Although this is useful functionality, it crosses over the dangerous line of modifying the query, making it potentially susceptible to exploitation in a SQL injection-like fashion.

The Great Mistake

The backtick (`) character is used in MySQL to delimit literals that represent identifiers (not strings), such as columns. Although this does not look helpful at first glance for altering a query, there is a lot we can do with this. To demonstrate this, we will use the standard login query, such as the one shown in the beginning of the post. If we insert an object into the SQL statement, the query will look like this: ... password = `<key>` = <value>. Since the key in the backticks (`) is going to be the literal column name, things get real weird.

Now, we are going to use the original exploit payload (detailed above) to pwn this. What if we set the key to be password as well? Well, password = `password` will evaluate to true! This is because password = `password` is literally comparing data coming from the same column as being equal while it iterates over each password in the database. Obviously, comparing the same data with itself is going to be true. Finally, if we set the value to be 1, then the whole statement will evaluate to true. This happens since true = 1 is true, which is a simplification from the password = `password` statement already being true. Putting everything together, the following query will be executed:

SELECT * 
FROM accounts 
WHERE username = "admin" AND
password = `password` = 1

This injection gives us the ability to make any simple statement become true when used in a WHERE clause. In the original exploit above, this is used in order to make the password of any username evaluate to true. By doing so, we can login as any user we please. Compared to a standard SQLi, this is equivalent to ' OR 1=1 -- evaluating to true on any username. Damn, that's a powerful primitive for simply not validating the type of a variable being used for a query.

Further Exploitation

Using the technique within a WHERE block is interesting for many reasons. First, query statements can be made true on a SELECT statement to return more data. Additionally, it could be used in a DELETE statement to remove all of the data from a database. The primitive being true or false seems small, but can be used in many places for breaking the logic, as shown above with the login example. From my testing in the MySQL REPL in different blocks, the item in the identifier will be interpreted as a variable, column or other things. Sadly, nothing interesting came of this; I was only able to create interesting boolean operation comparisons from this within WHERE blocks. But, maybe there is more interesting research to be done into this!

Exploiting this bug on my site

Website Architecture

My personal website was built in 2017 as a junior in college. It uses NodeJS with an Express server and a combination of MySQL and DynamoDB for different functionality on the backend. To add/edit/remove posts and resources on the blog, I built a login portal for myself. These credentials are stored in a MySQL database. As you would expect from the attacks mentioned above, the backend uses the mysqljs library for interfacing with a locally hosted MySQL database.

Login Flow

The endpoint for logging in is at /api/auth, which accepts a JSON POST request. This requires two parameters: username and password. Once the data is sent to the backend, the password is hashed using SHA512 and a static 100 byte salt. In retrospect, this should be using a slower hashing function such as bcrypt or scrypt to make cracking hashes more difficult. This is something I will likely change soon to get up-to-date with industry standards.

Once the password is hashed, a SQL query is made via pool.query(). The code for this is shown below:

The query (SELECT * FROM Users where username = ? AND password = ?) is checking to see if the user and hashed password are a match. If this is true, then it will return a result. Otherwise, it will return nothing, indicating that there was a mismatch. When there is a match, a session cookie is created, stored in the database and sent back to the user via the Set-Cookie header. Otherwise, authorization is not granted.

Exploitation

As a disclaimer, the vulnerability has been patched on this website by validating the type at the beginning of the request; please do not attempt to attack my website without permission :) On my website, there was no validation on the object shape. As a result, objects, arrays or any other type will be passed directly into the value section of pool.query(). In the example from the exploit post explaining the technique, they are not hashing their passwords. Since I hash my passwords, the technique cannot be used on the password field on this website, since it will attempt to hash the object.

After reading the exploit post above, I thought "I hash my passwords. This attack should not work on my website." After pondering this for a little while I thought about the other field in the login flow: username. Then, I had an idea: if we can trick the query to return true for any username with a matching hash, then we can login as that user! This is the inverse of the attack above: with a known password, we can login as any user with a chosen password. On a large website, such as Facebook, you could use this attack to target users with simple passwords. For instance, some user on Facebook is likely to have the password "Monkey123". By using this vulnerability, you could login as that user, simply by knowing a common password. Although this cannot be used as a targeted attack on specific users, it is still useful for logging in as random users by using simple passwords, as some user on the website is bound to have "Monkey123" as their password.

To get this to work, we will create a similar JSON object as before. Except, the attack object will be in the username field instead of the password. The object that would need to be sent to the /api/auth endpoint is shown below:

{
 "username": {
   "username" : 1
 },

 "password":"<some_password>"
}

When using the object shown above, we get the query SELECT * From Users WHERE username = `username` = 1 AND password = <some-hash> after all of the parsing. Since the username will evaluate to true and we assume that some user on the website has this password, we will login as the first user with that password. Wow, that's a real crazy exploit! A more conceptional view of this is shown in Figure 5.

Fortunately for myself, there is only a single user on this website. So, if you knew the password for the account, then you would just login with that username anyway. As a result, the real damage of the vulnerability to this site was minimal, but definitely scary nonetheless.

Conclusion

I personally never considered the shape of an input for a SQL query to be a security issue if the backend used parameterized queries. Even as a professional security engineer, some novel attacks slip through the cracks. If anything, this demonstrates how difficult writing secure software really is. I still cannot believe this attack is possible!

From a remediation perspective, people can blame the developers for not sanitizing the shape of objects while using the mysqljs library. To me, the fault on the vulnerability should go to the library developers instead of the users of the library. Libraries should be secure by default or have explicit documentation on security bugs that can arise if the user is not careful.

SqlString being able to add a column and value into a query is a scary functionality, since it is directly affecting the SQL query itself. But, this functionality does have a use case. On the other hand, allowing objects to be passed into SqlString from mysqljs seems a little unnecessary. My suggestion is to set the configuration setting stringifyObjects as true by default in order to fix this problem. This way, the functionality is there when necessary for developers to use, but this vulnerability does not occur by default.

Overall, I had a wonderful time diving into the weeds of why this vulnerability occurs within mysqljs and finding the issue in my own personal website. Along the way, I learned a ton about MySQL, how parameterized queries work (or are these parameterized queries?) and many other little things that will be helpful some day. Thanks to Flatt Security for the original post and my friends Jay Angra and Nathan Kirkland for reviewing this post prior to publishing. Feel free to reach out to me (contact information is in the footer) if you have any questions or comments about this article or anything else. Cheers from Maxwell "ꓘ" Dulin.