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:
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.
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:
username = "username = '' OR '1'='1'"
Your final query looks like this:
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:
username = "a';DROP TABLE users;"
The final query looks like this:
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.