Skip to content

How NearForm approaches SQL Injection Prevention

What are SQL injections and how nearForm fights with them?

Preventing SQL Injection Attacks

Modern day application security is a complex and multifaceted problem.

At NearForm, we create modern, best practice security tools and processes all the way through the delivery cycle; from design to development to infrastructure, through to continuous automated penetration testing.

There are far too many points to cover in one post, so today we're just going to focus on a small module (@nearform/SQL) that we've just open sourced to help node.js developers mitigate against SQL Injection attacks.

There are many different attack methods and any of them can lead to losing data.

In the chart you can see SQL Injection attacks are the top security risk:

Source: Web Hacking Incident Database (WHID) SQL Injection is at the top of OWASP Top 10 list from 2010 , 2013 , and the latest, 2017 .

It's marked as an easy attack, with severe impact. That makes SQL Injection consistently the number 1 since the data has been collected.

What is SQL injection?

A SQL injection attack consists of insertion or "injection" of a SQL query via the input data from the client to the application.

A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system.

SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to effect the execution of predefined SQL commands.

Source: SQL Injection

Examples of SQL injections

Here is an example of the SQL Injection: js statement = "SELECT * FROM users WHERE name = '" + username + "';" This is a simple SQL query for fetching users from the user's table by name.

It works ok when you send a username - you get the user. But what happens if you send something like this:

Plain Text 
username = "username = '' OR '1'='1'"

Your final query looks like this:

Plain Text 
SELECT * FROM users WHERE name = '' OR '1'='1';

This query is giving you all users from the user's table. However, an attacker can also send this:

Plain Text 
username = "a';DROP TABLE users;"

The final query looks like this:

Plain Text 
SELECT * FROM users WHERE name = 'a';DROP TABLE users;

And your users table is gone!

Parameterised Queries to the Rescue

Parameterised queries are the best way to protect from SQL Injection attacks.

The SQL statement you pass is parsed and compiled by the database.

By specifying parameters, you tell the database engine where you want to put the variables. It then takes the compiled statement and the variables combines them and executes the query.

The important thing here is that the parameter values are combined with the compiled statement, and not as a string.

SQL Injection works by tricking the script into including malicious strings when it creates SQL to send to the database; so by sending the actual SQL separately from the parameters, you limit the risk of nefarious SQL code being injected.

Any parameters you send when using a prepared statement are just treated as strings. So from the SQL Injection example above, if you pass username = "a';DROP TABLE users; as a parameter, your user's table is filtered by username = "a';DROP TABLE users; , and it won't remove everything from it.

Another benefit of using prepared statements is that if you execute the same statement many times in the same session it is only parsed and compiled once, giving you some speed gains.

Why Did We Develop @nearform/sql Module?

We were looking for an easy but powerful way to protect from SQL Injections.

We wanted something that's fast and easy to use. In other words, it doesn't require a lot of changes in the code and boilerplate code which is very safe.

That is why we created @nearform/sql module.

It uses parameter queries, it's small, fast, fully tested and is battle-hardened in several production systems.

It's also very easy to integrate into any new or existing project.

nearForm SQL module

@nearForm/sql is a very small NPM module that helps prevent SQL Injections by making sure that concatenating tilde strings in Node.js gets translated into a safe prepared statement.

It has no dependencies, and it's very fast (check the benchmark)! It currently supports PostgreSQL and we are working on support for other SQL databases.

@nearForm/sql module is open-sourced ( https://github.com/nearform/sql , and published on NPM ( https://www.npmjs.com/package/@nearform/sql .

How do I Install it?

Plain Text 
npm install @nearform/sql

How do I Use it?

It's really simple to use.

SQL module exports ES6 template strings tag that does all the magic!

Plain Text 
const SQL = require('@nearform/sql')</p><p>const db = connectDB() // your db instance</p><p>const username = 'user'
const email = 'user@email.com'
const password = 'Password1'</p><p>const sql = SQL`INSERT INTO users (username, email, password) VALUES (${username},${email},${password})` // generate SQL query</p><p>db.query(sql) // execute query

Import SQL module, connect to your DB and prepend SQL to your template strings.

It's really easy to use if you already have some queries; simply prepend SQL module and you are secured.

How Does it Work?

The SQL template string tag parses the query and returns an object that's understandable by PostgreSQL Node.js client :

Plain Text 
const username = 'user'
const email = 'user@email.com'
const password = 'Password1'</p><p>const sql = SQL`INSERT INTO users (username, email, password) VALUES (${username},${email},${password})` // generate SQL query
sql.text // INSERT INTO users (username, email, password) VALUES ($1 , $2 , $3)
sql.values // ['user, 'user@email.com', 'Password1']

With PostgreSQL Node.js client , you can pass text and values separately. This is the most efficient way of preventing SQL injection attacks.

Testing for SQL Injections

Testing your application for SQL Injection is critical.

One of our favourite penetration testing tools for testing SQL Injection attacks is sqlmap .

Coming up with a good test plan and tools such as sqlmap is a good starting point.

For reference, here's a comprehensive example of how we've tested Udaru for SQL injection attacks.

Testing in @nearForm/SQL

There are two levels of testing in the @nearForm/SQL module:

  1. unit tests that confirm that functionality works
  2. security tests which test for SQL Injection

We have implemented sqlmap testing and it confirms that our code is fully secured.

How does sqlmap testing work?

  • We run a PostgreSQL database and a users table.
  • We run a Hapi.js server and expose POST and GET endpoints to do CRUD operations to a users table.
  • We then run sqlmap and try to attack the app through those endpoints.

It fails to do SQL Injection - our build is green.

This is automated through CircleCI and you can also run it locally: bash npm run test:security

Conclusion

We created @nearForm/SQL Injection protection module to help developers protect their apps from SQL Injections.

It's small, fast and uses modern ES6 syntax without much boilerplate code.

Next time you start to write a SQL query, think about installing @nearform/sql module!

Don't miss a beat

Need help building a fast and secure web application for your business? Contact us today!

Insight, imagination and expertly engineered solutions to accelerate and sustain progress.

Contact