Preventing SQL injection attacks with automated testing

This tutorial covers:

1. What SQL injection is and why it is so destructive
2. Adding and automating tests for exposure threat
3. Fixing the injection loophole

A SQL injection attack targets an application’s 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. SQL injection is one of the most destructive ways an application can be attacked. The attack tricks the application into running well-crafted malicious code at the database level. A SQL injection attack can cause data to be exposed, corrupted, or even lost permanently.

In this tutorial, I will demonstrate how to use automated tests to check vulnerable 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

Our tutorials are platform-agnostic, but use CircleCI as an example. If you don’t have a CircleCI account, sign up for a free one here.

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 Postman and add some users using the http://localhost:3000/users/create endpoint. ( You can only create a single user at a time.)

{
  "email": "john.doe@gmail.com",
  "name": "John Doe"
}

Fetch the users by making a GET call to the http://localhost:3000/users/fetch endpoint.

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

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 : http://localhost:3000/users/fetch endpoint which is supposed to take the id of a specific user and return just that user in a data array. We would use a well crafted SQL injection attack that will attack this endpoint causing it 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.

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 more than 2 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 strategy works to fix the loophole in the POST : http://localhost:3000/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. For our endpoint, replace the getUser code handler in the users.js file with this 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.

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, click the Setup Project button on the CircleCI dashboard.

By defining a configuration file named .circleci/config.yml (you may need to specify the branch), CircleCI automatically detects it. You can click Setup Project to start the build. This build will fail because you have not set up your configuration file yet, this is 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 this code:

version: 2.1
orbs:
  node: circleci/node@5.0.2
jobs:
  build-and-test:
    executor:
      name: node/default
    steps:
      - checkout
      - node/install-packages
      - run:
          command: npm run test
workflows:
  build-and-test:
    jobs:
      - build-and-test

This configuration starts with defining the Node.js orb. The Node.js orb contains a set of prepackaged CircleCI configurations you can use to easily install Node.js and its package managers (npm, yarn).

In the build-and-test job, we have steps to checkout/pull the latest code changes, install packages and run the test suite.

Note: The node/install-packages step is predefined in the Node.js orb.

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

By clicking the build, you can review the test details.

You can click an individual step to get more details.

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, so 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. It will be well worth your time to apply what you have learned to other projects your team is working on.

Happy coding!

---

Fikayo Adepoju is a LinkedIn Learning (Lynda.com) Author, 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.

Read more posts by Fikayo Adepoju