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.
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.
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.
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.
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
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 before it is inserted into the query.
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.
Type Checking
While using query parameters is a best practice to avoid SQL injection attacks, it is not fool-proof. Malicious inputs sent to your application can take advantage of the flexibility in how some Node libraries handle type conversions of query parameters.
For example, take a look at this code example written for the express framework that handles login authentication. This example comes from the article Finding an unseen SQL Injection by bypassing escape functions in mysqljs/mysql
...
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) {
...
}
);
}
});
...This code seems secure. However, a request with the following payload would allow a hacker to gain access to the application without a password:
body: "username=admin&password[password]=1"In this example, the attacker is passing in a value that gets evaluated as an Object instead of a String value, and results in the following SQL query:
SELECT * FROM accounts WHERE username = 'admin' AND password = `password` = 1The password = `password` = 1 part evaluates to TRUE and is a 1=1 attack.
To protect yourself from this type of attack, it's best to check that the variables you are passing to query parameters are of the data type that you expect. The following code snippet adds some type checking to ensure both values are strings, and will prevent the 1=1 vulnerability:
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) {
...
}
);
}
});Validating that your query parameters are of the correct data type as well as doing input validation gives you the best protection against SQL injection.
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.
Check the data types of input parameters.
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.
