# SQL & NoSQL Injection

SQL and NoSQL Injection are attacks in which Code is injected into database queries in order to change their outcome.

A03:2021

# Background

# Context

Injection attacks are possible if database queries are created based on user input. Consequently, the user gains control over the behavior of the query through the added code or commands. The best known types of such an injection are SQL injections, which take place on relational databases such as MySQL, and NoSQL injections which can happen in a variety of databases such as MongoDB or Redis.

The following mechanisms were defined to inject code into databases:

User input
Cookies
Server variables
Second-order injections (execution of injection is postponed)

# SQL Injection

In SQL injections attackers inject data into SQL queries to change their outcome. An example can be seen in the following:

connection.query(
    "SELECT * from user WHERE name = '" + "' OR 1=1",
     function(err, rows, fields) {
    ...
}

The user input (2. String) is concatenated to the SQL query without further escaping and therefore changes the query's outcome. The induced behaviour causes the WHERE clause to always be true and thus always return all users.

In the literature different types of SQL injections could be elaborated:

Type	Example
Tautologies	OR 1 = 1
Union queries	; UNION SELECT * FROM SecretTable
Piggyback queries	; DROP SecretTable
Incorrect queries	OR OR OR OR
Extending queries	sqlcmd

# NoSQL Injection

A NoSQL injection functions similar to a SQL injection. An example can be seen in the following:

collection.find({title: `{"$ne": " "}`})

The query searches for all titles within its collection which are not equal to an empty string. The resulting dataset contains all titles.

Different types of NoSQL injections exist. However, these are again similar to the SQL injection types:

Type	Example
Operator injections (creates e.g. tautologies)	{$ne: {''}}
JavaScript injections (creates e.g. tautologies)	$where: "..."
Piggyback queries	"$or"

# Issues

Data could be stolen through attacks which use tautologies or piggyback injections
Data could also be lost or changed to the advantage of the attacker (elevation of privilege)
Successful attacks can not only attack databases but also their underlying systems and infrastructure

# Solutions

Parameterized queries (e.g. via ORMs)
Usage of secure APIs
Escaping or sanitization (OWASP SQL Injection Cheat Sheet (opens new window))
Using of client-directed queries (GraphQL)

# Technology

To connect a framework with a database, the usage of a library is most often indispensable. These libraries will be evaluated in the following.

Your favorite framework/library/database is missing? Add it yourself! (opens new window)

# Express

# MySQL database

mysql (opens new window)

The library mysql can be used to connect an Express server with and MySQL database. The following shows recommended querying of data:

connection.query(
    "SELECT * FROM user WHERE name = " + connection.escape(input), 
    function(err, rows, fields) {
    ...
  });

connection.query(
    "SELECT * from user WHERE name = ?",[input], 
    function(err, rows, fields) {
    ...
  });

TIP

The escape() Methode escapes potentially dangerous code
- NO_BACKSLASH_ESCAPE needs to be disabled, otherwise the backslash character cannot be used for escaping
? are placeholder for user input which is escaped before its added to the SQL query

Sequelize (opens new window)

Sequelize is one of many ORMs (opens new window) which are available on npmjs (opens new window). ORMs function through the definition of a model which represents data inside of the database. Therefore, parameters, which are passed to the database, can be given a datatype to which the parameter is parsed to.

As a result, queries are secure.

const User = sequelize.define('User', {
    name: {type: DataTypes.STRING},
    lastName: {type: DataTypes.STRING}
}, { ... } 
)

app.get('/getUser', function(req, res, next) {
    User.findAll({where: {name: "' OR 1=1 --"}})
    .then(result => {
        ...
    })    
})

# MongoDB database

When working with MongoDB, the most common libraries are mongodb (opens new window) and mongoose (opens new window) (ORM).

DANGER

The usage is insecure if no sanitization library is used. Attacks possible through operator injections.

Solution: The usage of sanitization libraries such as express-mongo-sanitize (opens new window) or mongo-sanitize (opens new window). The former prior library is the preferred choice since it functions with Express' middleware and adds therefore more Security By Default.

# Spring Boot

# MySQL database

In Java, different ways of querying data from a MySQL database exist. Both Java's mysql-connector-java and Spring's spring-boot-starter-data-jdbc offer secure data querying with PreparedStatement's or NamedParameterJdbcTemplate's. However, queries can also be created insecurely. In contrast, spring-boot-starter-data-jpa can be used as ORM by defining Entity classes making the queries secure.

# MongoDB database

When using MongoDB as underlying database, a Spring Boot developer will most likely use spring-boot-starter-data-jpa. While testing and researching for potential security issues, none were found.

← General Information Cross-Site Scripting (XSS) →