Finding and Fixing SQL Injection
Vulnerabilities in Node (Express)
with StackHawk

In today's rapidly evolving digital landscape, the security of web applications and APIs is more crucial than ever. With cyber threats becoming increasingly sophisticated, protecting sensitive data against unauthorized access is a top priority for developers and businesses. Among the numerous security vulnerabilities that frequently plague web applications and APIs, SQL Injection stands out as one of the most dangerous and prevalent.

In this blog, we delve into the world of web application security by exploring SQL Injection vulnerabilities. First, we will build a simple API with Node and Express, then configure StackHawk and use HawkScan through the Hawk CLI to identify any vulnerabilities. This hands-on approach will highlight how SQL injection vulnerabilities are introduced and demonstrate practical steps to fix them. After addressing the SQL injection vulnerability in our code, we'll retest with StackHawk to ensure the security issue is resolved. Let’s start by looking at the basics of StackHawk and SQL injection.

What is StackHawk?

StackHawk is a modern, powerful DAST tool designed for developers to integrate application security testing seamlessly into their software development lifecycle. It is not just another security tool; it's a developer-first platform emphasizing automation, ease of use, and integration into existing development workflows.

At its core, StackHawk is built around empowering developers to take the lead in application security. It provides a suite of tools that make it easy to find, understand, and fix security vulnerabilities before they make it to production. One of the critical components of StackHawk is HawkScan, a dynamic application security testing (DAST) tool that scans running applications and APIs to identify security vulnerabilities.

With StackHawk, developers and security teams can:

• Automate Security Testing: Integrate security testing into CI/CD pipelines, ensuring every build is automatically scanned for vulnerabilities.

• Identify and Fix Vulnerabilities: Receive detailed, actionable information about each vulnerability, including where it is in the code and how to fix it.

• Test in All Environments: Use StackHawk in various environments, including development, staging, and production, to catch security issues at any stage of the development process.

• Customize Scans: Tailor scanning configurations to suit specific applications and environments, ensuring more relevant and accurate results.

By focusing on a developer-first approach to security testing and integrating smoothly into existing dev tools and practices, StackHawk demystifies application security, making it a more approachable and manageable part of the software development lifecycle.

What is SQL Injection?

SQL Injection (SQLi) is a type of security vulnerability that occurs in the database layer of an application. It allows attackers to interfere with the queries that an application makes to its database. This can lead to various harmful outcomes, including unauthorized viewing of data, deletion of data, and, in some cases, complete control over the database

The vulnerability arises when an application uses user input in its SQL queries without proper validation or escaping. Attackers can exploit this by injecting malicious SQL code through the application's input channels (like forms, URLs, API endpoints, etc.), which can unintentionally manipulate the database.

For example, consider an API that uses a URl parameter to retrieve user information from a database. If this input is inserted into a SQL query without proper safeguards, an attacker could input SQL commands that the database will execute. This could lead to unauthorized access to sensitive data, such as passwords, personal information, etc.

SQL Injection can be classified into different types, such as:

• In-band SQLi: The most common and straightforward kind, where the attacker uses the same communication channel to launch the attack and gather results.

• Inferential SQLi: The attacker sends data payloads to the server and observes the response and behavior of the server to learn about its structure.

• Out-of-band SQLi: Used when the attacker can't use the same channel to launch the attack and gather information, often relying on server capabilities to send data to the attacker.

SQL Injection is a high-severity vulnerability because it can compromise sensitive data and take over database systems. Fortunately, with the right tools and practices, it can be detected and remediated effectively, which we will explore in this blog with the help of StackHawk and HawkScan.

How can SQL injection impact application security?

SQL Injection can have a profound and detrimental impact on application security, with consequences ranging from data breaches to complete system compromise. Here’s how SQL Injection can affect application security:

• Data Breach: The most immediate threat posed by SQL Injection is unauthorized access to sensitive data. Attackers can exploit vulnerable inputs to extract data from the database, exposing personal information, financial details, and other confidential data.

• Data Loss and Corruption: SQL Injection attacks can also be used to delete or modify data in the database. This can result in data loss, data integrity corruption, and application functionality disruption.

• Unauthorized System Access: In severe cases, SQL Injection can lead to a complete system takeover. Attackers can use advanced SQL Injection techniques to escalate their privileges within the database, allowing them to execute administrative operations and potentially gain access to the underlying server.

• Legal and Compliance Issues: A successful SQL Injection attack can lead to violations of data protection regulations like GDPR, HIPAA, etc. This can result in hefty fines, legal action, and damage to the organization's reputation.

• Loss of Trust: Customers and users lose trust in applications and businesses that fail to protect their data. A single SQL Injection vulnerability that leads to a data breach can have long-lasting repercussions on an organization's credibility and customer loyalty.

• Financial Costs: Dealing with the aftermath of a SQL Injection attack can be costly. Expenses can include legal fees, regulatory fines, costs associated with rectifying the breach, and investments in upgrading security measures.

Preventing SQL Injection requires a multi-faceted approach, including proper input validation, use of prepared statements, regular security testing, and awareness training for developers. Tools like StackHawk play a crucial role in identifying potential SQL Injection vulnerabilities, enabling teams to address these issues before they lead to security incidents proactively.

Finding and Fixing SQL Injection in an Express API

Now, we will understand SQL injection a bit more deeply by creating a simple API with an endpoint containing a SQL injection vulnerability. In this step-by-step guide, we will create an API using Node and Express, then set up StackHawk to test the application through HawkScan and the Hawk CLI. Once the vulnerability is reported, we will fix the injection flaw and retest to ensure it’s indeed fixed.

Creating an API with NodeJS and Express

In this part of the guide, we will create our base project and its corresponding API. For this, we will rely on node and express. To follow along, you will need to ensure that you:

• Have node and npm installed

• Have an IDE where you can edit text

• Optionally, have a tool that you can send API requests through to test the endpoint, such as Postman or Insomnia

With the prerequisites above, you can proceed with each step below.

Step 1: Create and Initialize the Project

First, create a new directory for your project and navigate into it. If you do this through a terminal, you can run the command below.

Next, with the terminal pointed to the root directory, Initialize a new Node.js project with npm using the following command.

Finally, before we write any code, we will Install the following dependencies:

• Express: for setting up the server.

• Body-parser: for parsing incoming request bodies.

• SQLite3: for the database (chosen for simplicity; no separate DB server needed).

To do this, using the same terminal pointed to the project root, run the following:

Step 2: Create the Application Code

Now, we will begin to create our API application. In the root directory of the project, create a file named app.js. This file will contain your Express server and the API endpoint that contains the SQL injection vulnerability.

After creating and opening the app.js file, add the following code:

const express = require('express');
const bodyParser = require('body-parser');
const sqlite3 = require('sqlite3').verbose();


// Initialize Express app
const app = express();
app.use(bodyParser.json());


// Initialize SQLite database
const db = new sqlite3.Database(':memory:');


db.serialize(() => {
 db.run("CREATE TABLE users (id INT, name TEXT)");


 // Insert example users
 const stmt = db.prepare("INSERT INTO users (id, name) VALUES (?, ?)");
 stmt.run(1, "John Doe");
 stmt.run(2, "Jane Smith");
 stmt.run(3, "Alice Johnson");
 stmt.finalize();
});


// Insecure SQL query endpoint without using parameterized query
app.get('/users/:id', (req, res) => {
 let userId = req.params.id;
 // Directly concatenating user input into the SQL query string
 let sql = `SELECT * FROM users WHERE id = ${userId}`;
 db.all(sql, [], (err, rows) => {
   if (err) {
     res.status(500).send("Error in database operation");
   } else {
     res.json(rows);
   }
 });
});


// Start server
const PORT = 4000;
app.listen(PORT, () => {
 console.log(`Server running on port ${PORT}`);
});

For those wondering about the code above, this Node.js application, built using the Express framework, establishes a simple web API hooked up to a SQLite database. It initializes an Express server and configures it to parse JSON request bodies. The SQLite database, stored in memory, is set up with a `users` table and pre-populated with example data. The application features an API endpoint (`/users`) that contains a SQL injection vulnerability. Finally, once started, the server will listen for incoming API requests on port 4000.

Step 3: OpenAPI Specification

For this particular API, we will also include an OpenAPI Spec. To do this, we will add an endpoint, /api-spec, that will automatically generate and respond with an OpenAPI Spec. To do this, we will first add the express-oas-generator import to our code (it was already installed earlier in the npm install we ran).

Then, we will initialize the spec generator just under where we define the express app.

With that, the code at the top of our app.js file will now look like this:

const express = require('express');
const bodyParser = require('body-parser');
const sqlite3 = require('sqlite3').verbose();
const expressOasGenerator = require('express-oas-generator');


// Initialize Express app
const app = express();
expressOasGenerator.init(app, {});
app.use(bodyParser.json());

Now, make sure to Save all of the files you’ve updated so that we can run our API service in the next step.

Step 4: Running the Application

Finally, let’s start the Node.js application by running the following command in a terminal (pointed at the root directory of the project):

Next, let’s quickly check to make sure that our endpoint is working as intended. As you can see In the provided Node.js Express application, the vulnerability lies in the /users endpoint. The endpoint concatenates user input directly into an SQL query without sanitization or parameterization. This makes it susceptible to SQL injection.

In Postman, you can send a simple request to the endpoint. For instance, suppose you want to query the user with ID 1. For this, you could use the following GET request:

In a malicious query, an attacker could exploit the SQL injection vulnerability by providing an SQL snippet in the id parameter. For example, they could use the following amended GET request:

This query would cause the SQL command to return all rows in the users' table because ‘1=1’ is always true. What happens in this scenario is that the SQL query executed by the server turns into something like this:

Since 1=1 is always true, this condition bypasses the intended filter and can potentially expose all the data in the users' table. In the case above, you’ll get the returned data from all 3 default users we added in the code instead of just a single user.

Configure StackHawk

Now that our code is set up and our API is running, let’s get StackHawk to identify this vulnerability for us automatically. To do this, you’ll need to make sure that you have a StackHawk account. If you need one, you can sign up for a trial account or log into an existing account.

If you’re logging into an existing StackHawk account, from the applications page you’ll click Add an App -> Create Custom App

If you’re new to StackHawk, you’ll be automatically brought into the Add an App flow. On the Scanner screen, you’ll see the instructions for installing the StackHawk CLI. Since we will be running our testing locally, we will use this option. Once the hawk init command is executed successfully, click the Next button.

On the next screen, you will fill out an Application Name, Environment Name, and Host. Feel free to copy the default values I’ve added in the screenshot below for our purposes. Once filled out, click Next.

Since we will be testing a RESTful API, on the next page, we will choose our Application Type as “API”, the API Type as “REST / OpenAPI”, and point to our OpenAPI Specification file by selecting URL Path and adding in our endpoint, /api-spec/, into the textbox. Once complete, click Next.

Lastly, we will need to add a stackhawk.yml file to the root of our project. Once the file is added, copy the screen's contents, paste it into the file, and save it. Once complete, we can run the “hawk scan” command in a terminal pointing to the root of our project. Lastly, we can click the Finish button.

Run HawkScan

After running the “hawk scan” command in the previous step, you should see the tests beginning to execute in the terminal.

This will run tests against our API based on the OpenAPI spec using the OpenAPI Spider provided by StackHawk.

Explore the initial findings

Once the tests are complete, the terminal will contain some information about any vulnerabilities found. Below, we can see that it has identified the SQL injection vulnerability that we introduced in the code. To explore this further, we will click on the test link that is provided at the bottom of the output. This will take us into the StackHawk platform to explore further.

After clicking on the link, we can now see the test results nicely formatted. Next, we will click on the SQL Injection entry.

Within this entry, we can see an Overview and Resources that can help us with fixing this vulnerability as well as the Request and Response that the API returned. Just above this, you will also see a Validate button, which will display a cURL command with the exact API request used to expose the vulnerability.

Fixing the SQL Injection vulnerability

Now that we have identified the vulnerability, it’s time to fix it. In this case, to prevent SQL injection in the Node.js Express application, the easiest way to fix this is to modify the code to use parameterized queries. This approach ensures that user input is treated as data, not as part of the SQL command. Doing so prevents attackers from injecting malicious SQL code through user input.

Here's how you can modify the vulnerable endpoint in your app.js file to use parameterized queries:

const express = require('express');
const bodyParser = require('body-parser');
const sqlite3 = require('sqlite3').verbose();
const expressOasGenerator = require('express-oas-generator');


// Initialize Express app
const app = express();
expressOasGenerator.init(app, {});
app.use(bodyParser.json());


// Initialize SQLite database
const db = new sqlite3.Database(':memory:');


db.serialize(() => {
 db.run("CREATE TABLE users (id INT, name TEXT)");


 // Insert example users
 const stmt = db.prepare("INSERT INTO users (id, name) VALUES (?, ?)");
 stmt.run(1, "John Doe");
 stmt.run(2, "Jane Smith");
 stmt.run(3, "Alice Johnson");
 stmt.finalize();
});


// Secure SQL query endpoint using parameterized query with URI param
app.get('/users/:id', (req, res) => {
 let userId = req.params.id;
 db.all("SELECT * FROM users WHERE id = ?", [userId], (err, rows) => {
   if (err) {
     res.status(500).send("Error in database operation");
   } else {
     res.json(rows);
   }
 });
});


// Start server
const PORT = 4000;
app.listen(PORT, () => {
 console.log(`Server running on port ${PORT}`);
});

Let’s recap the changes made in the code to alleviate this vulnerability. First, instead of concatenating the userId directly into the SQL statement, the query is parameterized. The ? in the query "SELECT * FROM users WHERE id = ?" is a placeholder for the `userId`. This placeholder is then safely replaced by the userId value provided in the request. Then, we also use the `db.all` method with an array ([userId]) that safely inserts the `userId` into the query. This ensures that user input is always treated as a value and not executable code, thus preventing SQL injection.

The endpoint is now secured against SQL injection attacks by making these changes. The application treats user inputs as parameters, not as parts of the SQL statement, which is a critical practice in developing secure applications. It’s important always to validate and sanitize user inputs and use parameterized queries to handle data in SQL statements to mitigate potential injection attempts.

Lastly, save the code and restart the application to apply the changes.

Confirm the fix!

With the fix in place, let’s confirm it is working as intended. To do this, back in StackHawk, we will click the Rescan Findings button.

Then, we will see a modal containing the “hawk rescan” command that includes the correct Scan ID. You’ll run this command in the same terminal where you ran the initial set of tests.

In the output, you will again see any vulnerabilities found in the scan. In this case, you’ll see that the SQL injection vulnerability is no longer showing. Clicking on the link at the bottom of the terminal output, you can confirm that the SQL injection vulnerability has now been added to the Fixed Findings from Previous Scan, confirming that the vulnerability has been successfully fixed and has passed any vulnerability tests.

Try it out!

With that, we've successfully used StackHawk to scan an API, identify a SQL injection issue, and confirm that our fix worked as intended. Are you looking to make your applications more secure and put the power of security testing into the hands of developers? With StackHawk, “shift-left” the responsibility of security and empower developers to build secure APIs and applications with ease. Want to give it a try for yourself? Sign up for StackHawk today for a free 14-day trial.