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:
- Basic knowledge of Javascript
- Node.js installed on your system (version >= 11.0)
- A CircleCI account
- 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 usersPOST : /users/fetch
is aPOST
endpoint that takes a userid
and uses it to fetch a single userPOST : /users/create
is aPOST
endpoint that takes the parametersname
andemail
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 id
s 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.