SQL injection is one of the most destructive ways an application can be attacked. This kind of attack is targeted toward the application database, which can result in consequences that are irreversible, lead to loss of money, and reduce user trust in your company. There are far too many application data breaches happening every day, usually when a malicious agent attacks the database. The most common way to attack is by tricking the application to run well-crafted malicious code at the database level (SQL injection). causing data to be exposed, corrupted, or even lost permanently. In this tutorial, we will demonstrate how to use automated tests to check our application entry points for database injection attacks.

Prerequisites

To follow this tutorial, a few things are required:

  1. Basic knowledge of Javascript
  2. Node.js installed on your system (version >= 11.0)
  3. A CircleCI account
  4. A GitHub account

Cloning the demo project

To begin the exercise, you will need to clone the demo project. The project is a simple Node.js user accounts API application with the following endpoints:

  • GET : /users/fetch is an endpoint for fetching all users
  • POST : /users/fetch is a POST endpoint that takes a user id and uses it to fetch a single user
  • POST : /users/create is a POST endpoint that takes the parameters name and email to create a new user record

This project runs on a SQLite database that stores data in memory (for demo purposes) and contains a single table, named users.

Clone the project by running the following command:

git clone --single-branch --branch base-project https://github.com/CIRCLECI-GWP/sql-injection-testing.git

Once the cloning process is complete, go into the root of the project and install the dependencies by running the commands below:

cd sql-injection-testing
npm install

Next, run the application with this command:

npm run dev

The application will start listening on a default port of 3000. Open up Postman and add some users using the http://localhost:3000/users/create endpoint.

Get Users - Postman

You can also send an id parameter for the first user by calling the POST endpoint of /users/fetch to retrieve the user.

Get User - Postman

Adding tests to check for sensitive data exposure threat

The endpoints POST : /users/fetch and POST : /users/create are two entry points into the application. They take in data that the application processes to give results. The handlers for these two endpoints can be found in user.js at the root of the project.

....

function getUser(req, res) {

    let sql = `SELECT * FROM users WHERE id='${req.body.id}'`;

    db.all(sql, function (err, data) {
        if(err) throw err

        res.json({
            status : 200,
            data,
            message : "User record retrieved"
        })
    })

}

function createUser(req, res) {

    let sql = `INSERT INTO users(email, name) VALUES ('${req.body.email}', '${req.body.name}')`;

    db.run(sql, function (err) {
        if(err) throw err

        res.json({
            status : 200,
            message : "User successfully created"
        })
    })
}

Both handlers run SQL queries that directly take in user data from the request object. We can attack one of these endpoints to expose its SQL injection vulnerability.

We would be testing the POST : /users/fetch endpoint, which should take the id of a specific user and return just that user in a data array. Using a well-crafted SQL injection attack will cause this endpoint to expose the data for all users in the database.

To begin, install jest (test runner) and supertest (for testing API endpoints) using the following command:

npm install -D jest supertest

Once this installation is done, create a folder __tests__ and within this folder, create the file injection.test.js. Add the following code:

const supertest = require("supertest");
const app = require("../app");
const request = supertest(app);

const db = require("../db");

let createTableSQL =
  "CREATE TABLE IF NOT EXISTS `users` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , `email` VARCHAR(100) NOT NULL , `name` VARCHAR(240) NOT NULL)";

let insert =
  "INSERT INTO users (name, email) VALUES ('user1@test.com','User 1'), ('user2@test.com','User 2'), ('user3@test.com','User 3')";

test("Test malicious data exposure", (done) => {
  db.run(createTableSQL, function (err) {
    db.run(insert, function () {
      let maliciousUserId = `1' OR 1=1;--`;

      request
        .post("/users/fetch/")
        .send({ id: maliciousUserId })
        .set("Accept", "application/json")
        .expect(200)
        .expect("Content-Type", /json/)
        .end(function (err, res) {
          if (err) return done(err);

          //Should not return more than one record
          expect(res.body.data.length).toBeLessThan(2);
          done();
        });
    });
  });
});

This test case begins by creating the users table and seeding it with 3 test users. It then crafts a malicious user id that intercepts the SQL query and injects a condition that will always be true. This is an alternative to the condition that restricts the result to just the user with the id. The result is that the query returns all the users in the users table.

We can test result of the API call to make sure it does not contain more than one record. With 3 users already in the table, if the request returns more than 1 result, it will fail, which indicates that the attack was successful.

To complete the test setup, add a test script to package.json:

"scripts": {
    ...
    "test": "jest"
},

Now you can run the test by running the following code at the root of the project:

npm run test

Once the test runs, you will get a failed result.

Test Failed - CLI

Instead of returning a single value or none at all if there is no match, the injection attack was able to successfully force the database to expose all 3 users contained in the table. This kind of attack is a dangerous way for malicious users to expose sensitive or private data in your database tables like phone numbers, account numbers, and credit card information.

Fixing the injection loophole

So, how can this be fixed? SQL injection is usually fixed by tweaking your code to check for conditions to validate data coming from users accessing your application. In the above case for example, we can try checking that the value submitted by the user it is an integer, because ids are stored as integers. Any malicious SQL will not pass that check because they are strings and not integers.

However, even though the above strategy works to fix the loophole in the POST : /users/fetch endpoint, there is another recommended best practise for processing SQL statements in our applications: the SQL command in the getUser handler.

let sql = `SELECT * FROM users WHERE id='${req.body.id}'`;

It is considered generally unsafe to directly pass user-inputed values directly into our SQL queries. We can instead use placeholders in our queries that map to a set of values defined in an array or object. Most database drivers and Object Relational Mappers (ORMs) provide this functionality. In the case for our endpoint, replace the getUser handler code with the following piece of code:

function getUser(req, res) {
  let sql = `SELECT * FROM users WHERE id=?`;

  db.all(sql, [req.body.id], function (err, data) {
    if (err) throw err;

    res.json({
      status: 200,
      data,
      message: "User record retrieved"
    });
  });
}

The updated handler uses the placeholder ? in our query, then passes an array as the second argument to db.all(), which contains the id for the user. When you rerun the test you will get a passing result.

Test Passed - CLI

Our test now passes because the malicious code is converted into a non-harmful string that does not match any user id. No user is returned, keeping our data safe from unwanted exposure.

Automating the testing process

The aim of this tutorial is not only to run SQL injection tests. We want to automate the entire process so that anytime updates are pushed to the code, these tests run to ensure that our data is fully protected.

To automate the running of the SQL injection tests, the first step is to push the project to GitHub.

Now, go to the Projects page on the CircleCI dashboard to add the project.

Add Project - CircleCI

Click Set Up Project.

Add Config - CircleCI

On the setup page, click Use Existing Config to instruct CircleCI that we want to add a configuration file manually instead of using the sample. Next, you are prompted to either download a configuration file for the pipeline or start building.

Build Prompt - CircleCI

Click Start Building. This build will fail because we have not set up our configuration file yet, which will be our next step.

Create a folder named .circleci at the root of the project and add a configuration file named config.yml inside the folder you just created. In this file, enter the following code:

version: 2.1
jobs:
  build:
    working_directory: ~/repo
    docker:
      - image: circleci/node:11
    steps:
      - checkout
      - run:
          name: Update NPM
          command: "sudo npm install -g npm"
      - restore_cache:
          key: dependency-cache-{{ checksum "package-lock.json" }}
      - run:
          name: Install Dependencies
          command: npm install
      - save_cache:
          key: dependency-cache-{{ checksum "package-lock.json" }}
          paths:
            - ./node_modules
      - run:
          name: Run tests
          command: npm run test

In this configuration, we begin by pulling in the appropriate Node.js image, then update npm and install the project dependencies.

Once the dependencies are installed and cached for faster subsequent builds, the tests are run.

Commit all changes and push to the remote repository to run the build pipeline. You should get a successful build.

Build Successful - CircleCI

And by clicking the build, you can review the test details.

Build Details - CircleCI

The tests have passed as expected and we now have an automated pipeline for testing SQL injection attacks.

Note: Avoid running injection tests on a production database; always run these tests in a staging environment.

Conclusion

The database is the heart and soul of all data-driven applications (almost all of them these days). Any attack on the database cannot be tolerated, and thoroughly testing the application code for SQL injection attacks is a requirement. In this tutorial, we have demonstrated creating a simple injection test for an exposed endpoint and automating the testing process.

Happy coding!


Fikayo Adepoju is a Full-stack developer, technical writer, and tech content creator proficient in Web and Mobile technologies and DevOps with over 10 years experience developing scalable distributed applications. With over 40 articles written for CircleCI, Twilio, Auth0, and The New Stack blogs and also on his personal Medium page, he loves to share his knowledge to as many developers as would benefit from it. You can also check out his video courses on Udemy.