Node.js SQL Injection
Guide: Examples
and Prevention

stackhawk

StackHawk|September 6, 2021

We will briefly examine what Node.js SQL injection is and what a SQL injection attack looks like. We'll also discuss measures to prevent them.

Node.js is a fantastic runtime platform for developing accessible and uncomplicated applications and services, offering all the flexibility and benefits of working with JavaScript. The fact that its source is both open and supported by Microsoft, Google, and IBM provides this decade-old technology a lot of credibility. Yet it is not invulnerable to attacks like SQL injection. 

Of course, no platform is perfect. Moreover, vulnerabilities like these are mainly introduced into systems by poor development practices. Which is why developers must nowadays be aware of their impact and mitigate them appropriately. For that purpose, this article will serve as an introduction to SQL injection attacks for beginners and a refresher for more seasoned developers. 

We will briefly examine what SQL injection is and what a SQL injection attack looks like. We'll also discuss measures to prevent them. If you have never worked on Node.js or JavaScript, I recommend taking some time to familiarize yourself with them. Nevertheless, the concepts of SQL injection are pretty consistent across platforms and languages.

Ready to Test Your App

What is SQL Injection?

First, let's briefly explain what SQL injection is. 

SQL injection is an attack that takes advantage of poor database integration infrastructure and lackluster user input validation. Malicious SQL instructions injected directly into the system's SQL database through user-facing input fields can take over a system. The main goal of a SQL injection attack is to manipulate the data in the database, force the system to present its data, or both. 

Given that these attacks target the system database and, when successful, can provide access to the database, the potential impact is evident. One could be excused for believing that these attacks are very complex and are used only rarely. However, most SQL injection attacks are not particularly sophisticated or rare. In fact, the opposite is true.

Node.js SQL Injection Guide: Examples and Prevention image

SQL Injection Example

Let's look at a simple SQL injection attack that could target any system that allows user input. 

Imagine you have code in your model layer or database integration layer where you retrieve user information by formatting the following SQL query command: 

query = 'SELECT * FROM Users WHERE Email = "' + USERNAME + '" AND Pass = "' + PASSWORD + '";'

This simple query command would typically search through the users table and retrieve the user who intends to log in. However, a hacker could take advantage of the lack of validation from user inputs by inputting values that the developer did not foresee a valid user would use.  

For example, let's say you input something like the following:

" or ""="

The system would then return all users in the table. 

As you can see, there is little complexity or intricacy in this attack. It lives and dies on simple input validation. However, this doesn't mean that SQL injection attacks can't be complex or be part of a more powerful, more sophisticated attack.

If you want to read more about SQL injection, you can read this post where we go into more details about it.

What SQL Injection Attacks Look Like in Node.js

How can you spot a SQL injection vulnerability in your Node.js code? 

It's not hard when you know how it works, and the database layer is where the most glaring problems usually are. 

Take this code, for example:

app.post("/records", (request, response) => {
const data = request.body;
const query = `SELECT * FROM health_records WHERE id = (${data.id})`;
connection.query(query, (err, rows) => {
if(err) throw err;
response.json({data:rows});
});
});

This will process a post request to the records endpoint and find user records matching the IDs provided.  

Now can you spot the issues with this implementation? 

As we saw in the previous section, allowing unescaped and unsanitized input into the query command must be avoided at all costs. 

Common SQL Injection Attacks

Besides the ""="" attacks that we explored already, a few more forms of injection attacks are widespread. Hackers can use them to successfully target a vulnerable system if they have enough knowledge of the database structure after some trial and error.

  • Always true (or 1=1) attacks 

    • SELECT * FROM Users WHERE UserId = 105 OR 1=1;

  • Query stacking attacks 

    • SELECT * FROM products WHERE id = 10; DROP members--

  • Data exfiltration (or query comment) attacks 

    • SELECT * FROM health_records WHERE date = '22/04/1999'; -- ' AND id = 33

If you want to learn more and see examples of these attacks, you can find some here and here.

Preventive Measures for SQL Injection Attacks

Now that you have a basic understanding of how SQL injection attacks take advantage of our systems, let's look at how we can prevent them or mitigate their impact. 

First, we need to address the user input validation implemented in our user-facing front-end code. This validation would be our first layer of defense against bad actors and serve as a responsive interaction mechanism for users struggling with interface intuitiveness.

Node.js SQL Injection Guide: Examples and Prevention image

Make sure that the values provided by the user are scoped and sanitized for each field accordingly. That means, for example, that if an input field is intended to receive emails, it does not allow the user to submit the form if an invalid email address—or no value at all—is set. 

A simple example would look like this:

function validateForm() {
let x = document.forms["form"]["email"].value;
const re = /^(([^<>()[\]\\.,;:\s@"]+(\.[^<>()[\]\\.,;:\s@"]+)*)|(".+"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/;

if (x == "") {
alert("Email must be filled out");

return false;
} else if (re.test(String(x).toLowerCase()) == false) {
alert("Email must be valid");

return false;
}
return true;
}

Of course, this approach can be expanded with input masks and responsive form styling to tell the user that the value provided is not valid and to inform the user what is expected.

Second, input validation can be implemented at the control level, where most of the calculations are done. This strategy could be as simple as revalidating and, when appropriate, sanitizing the user input before it reaches the model layer. Additionally, adding a third-party library like "node-mysql" that implements escaping automatically also helps.

Data Layer Protection

Finally, once the issues at the top level are addressed, we can then secure the database layer. To do that, all we need to do is implement what are known as query placeholders or name placeholders. These placeholders, indicated here with the ? symbol, tell the interfacing layer to automatically escape the input passed to it and validate its type and format so that it complies with the database structure. This means that if a string is given to a column expecting an integer, the query aborts, throwing an exception. 

If we expand on the previous example, we can address the issue with a simple change to the code. 

app.post("/records", (request, response) => {
const data = request.body;
connection.query('SELECT * FROM health_records where id = ?', [data.id], (err, rows) => {
if(err) throw err;
response.json({data:rows});
});
});

It's a subtle change, but it has a significant impact on the security of our code.

Add Automated Security Testing to Your Pipeline for Free

Bottom Line

We've explored the risks that involve SQL injection attacks and the various ways to counter them, and here's a recap of what you should do: 

  • Implement input validation and field masking at the view level.

  • Ensure that your model layer properly uses placeholders. 

  • Avoid insecure packages that have access to the database.

  • Make use of application security monitoring features.

  • Enforce security policies and best practices with your team.

In the end, complying with proper SQL injection prevention practices is relatively straightforward. Depending on the size and complexity of the codebase, however, your mileage might vary. Nevertheless, the time investment that this protection requires will pay dividends for years to come. 

This post was written by Juan Reyes. Juan is an engineer by profession and a dreamer by heart who crossed the seas to reach Japan following the promise of opportunity and challenge. While trying to find himself and build a meaningful life in the east, Juan borrows wisdom from his experiences as an entrepreneur, artist, hustler, father figure, husband, and friend to start writing about passion, meaning, self-development, leadership, relationships, and mental health. His many years of struggle and self-discovery have inspired him and drive to embark on a journey for wisdom.


StackHawk  |  September 6, 2021