Insecure Use of SQL Queries

Fixing Insecure Use of SQL Queries

About SQL Injection (SQLi)

What is SQL injection (SQLi)?

SQL injection (SQLi) is a type of security vulnerability that allows attackers to exploit an application's interaction with a SQL database.

This occurs when an application fails to properly sanitize user input and includes untrusted data in an SQL query, allowing attackers to inject malicious code or modify the intended behavior of the query.

SQL injection can affect various types of applications that interact with SQL databases, such as web applications, content management systems, and e-commerce platforms.

Attackers can use various techniques, such as crafted queries or filter values, to inject malicious code or entities into the application.

Check out this video for a high-level explanation:

What is the impact of SQL injections?

SQL injection can lead to various security threats and risks, such as:

Information disclosure: SQL injection can expose sensitive information, such as user data, login credentials, or other types of confidential information, to unauthorized parties.
Unauthorized access: SQL injection can allow attackers to gain unauthorized access to databases, perform unauthorized actions, or modify data.
Denial-of-service: SQL injection can cause denial-of-service (DoS) attacks by injecting malicious queries or values that consume system resources, such as CPU or memory, leading to system crashes or slowdowns.
Arbitrary code execution: SQL injection can execute arbitrary code on the database server by injecting malicious queries or values that exploit vulnerabilities in the application or the underlying operating system.

SQL injections are very common and have been the cause of many high-profile breaches.

How to prevent SQL injections?

To prevent SQL injection, it is important to follow security best practices and implement appropriate security measures, such as:

Use parameterized queries: Use parameterized queries and other secure coding practices to ensure that user input is properly sanitized and not used to construct SQL queries directly.
Use input validation: Validate user input to ensure that it is safe and does not contain malicious code or characters that can be used for injection attacks.
Limit user access: Limit user access to databases to authorized users and restrict access to sensitive information and actions.
Use secure coding practices: Follow secure coding practices, such as code reviews, vulnerability scanning and testing, and threat modeling, to ensure that the source code is free of vulnerabilities and that SQL injection vulnerabilities are addressed.
Use a web application firewall: Use a web application firewall (WAF) that can help prevent SQL injection by filtering and blocking malicious traffic.

References

Taxonomies

Explanation & Prevention

CVE Details: CWE-89

Training

Secure Code Warrior: SQL Injection

SQL injection

Rule-specific references:

Sequelize documentation

Option A: Use ORM libraries

Install an ORM like sequelize
Go through the issues that GuardRails identified in the PR/MR

Replace the code that looks like:

"SELECT * from users where email = " + req.params.email + ";";

with:

sequelize
.query("SELECT * FROM user WHERE email = :email ", {
  raw: true,
  replacements: { email: req.body.email }
})
.then(projects => {
  // Do your stuff
});

Test it
Ship it 🚢 and relax 🌴

Option B: Validate input with Joi

Install JOI
Go through the issues that GuardRails identified in the PR/MR

Replace the code that looks like:

"SELECT * from users where email = " + req.params.email + ";";

with:

const Joi = require("joi");

const user_schema = Joi.object().keys({
  email: Joi.string().email();
});
Joi.validate({ email: req.params.email }, user_schema, function(error, val) {
  if (error == null) {
    "SELECT * from users where email = " + req.params.email + ";";
    //do stuff
  } else {
    //catch validation error
  }
});

Test it
Ship it 🚢 and relax 🌴

SQL Injection (MySQL)

Concatenating or inserting any untrusted data with raw SQL queries without validating, sanitizing, and filtering can introduce SQL injection.

Option A: Validate, sanitize, filter untrusted data and parameterize queries

Before untrusted data gets anywhere near your database queries, it should be validated, filtered and sanitized. Following that, using prepared statements (parameterized queries) is highly recommended.

Locate one of the following vulnerable patterns:

Inserting untrusted data to template literal expression without validation, sanitization, and filtering:

import express from 'express';
import mysql from 'mysql';
const app = express();

app.post('/records', (request, response) => {
  const data = request.body.id;
  // data is untrusted.
  const query = `SELECT * FROM health_records WHERE id = ${data} AND height = 200`;
  connection.query(query, (err, rows) => {
    if(err) throw err;
    // Do something with parameters.
  });
});

Replace it with one of the following patterns:

Escape query value with ? in the array of replacements:

import express from 'express';
import mysql from 'mysql';
const app = express();

app.post('/records', (request, response) => {
  const data = request.body.id;
  // data is untrusted.
  const query = 'SELECT * FROM health_records WHERE id = ? AND height = ?';
  connection.query(query, [data, 200], (err, rows) => {
    if(err) throw err;
    // Do something with parameters.
  });
});

Escape query value using sql string and values array within an options object:

import express from 'express';
import mysql from 'mysql';
const app = express();

app.post("/records", (request, response) => {
  connection.query({
      sql: 'SELECT * FROM health_records WHERE id = ? AND height = ?',
      timeout: 40000, // 40s
      values: [request.body.id, 200]
    }, (err, rows) => {
      if(err) throw err;
      // Do something with parameters.
    }
  );
});

Escape query value with the second argument directly to query:

import express from 'express';
import mysql from 'mysql';
const app = express();

app.post("/records", (request, response) => {
  // If the query only has a single replacement character (?),
  // and the value is not null, undefined, or an array,
  // it can be passed directly as the second argument to .query:
  connection.query(
    'SELECT * FROM health_records WHERE id = ?',
    request.body.id,
    (err, rows) => {
      if(err) throw err;
      // Do something with parameters.
    }
  );
});

Escape query values directly with mysql.escape(), connection.escape() or pool.escape():

import express from 'express';
import mysql from 'mysql';
const app = express();

app.post("/records", (request, response) => {
  connection.query(
    'SELECT * FROM health_records WHERE id = ' + mysql.escape(request.body.id),
    (err, rows) => {
      if(err) throw err;
      // Do something with parameters.
    }
  );
});

Escape query values based on the shape of the passed in JavaScript value:

import express from 'express';
import mysql from 'mysql';
const app = express();

app.post("/records", (request, response) => {
  const replacements  = {id: request.body.id, height: 200};
  const query = connection.query('SELECT * FROM health_records WHERE ?', replacements, (err, rows) => {
    if (err) throw err;
    // Do something with parameters.
  });
  console.log(query.sql); // SELECT * FROM health_records WHERE `id` = 1, `height` = 200
});

Test it
Ship it 🚢 and relax 🌴

SQL Injection (MongoDB)

Using untrusted data in MongoDB queries without validation, filtering and sanitization can cause NoSQL injection.

A defense-in-depth approach is your best means for mitigating NoSQL injection attacks. Before untrusted data gets anywhere near your datastore queries, it should be validated, filtered, and sanitized for the particular query use case you are intending on using the data within.

Also, consider the database permissions that queries have.

Once the untrusted data is validated (using an allow-list) and filtered, you should also sanitize it. The following patterns are just some of the techniques for sanitization of untrusted data.

At least the following MongoDB collection methods are vulnerable to NoSQL injection:

aggregate
and
bulkWrite
count
deleteMany
deleteOne
distinct
drop
find
findAndModify
findByIdAndUpdate
findOne
findOneAndDelete
findOneAndRemove
findOneAndReplace
findOneAndUpdate
insert
insertMany
insertOne
or
remove
rename
replaceOne
update
updateMany
updateOne
where

GuardRails provides MongoDB coverage for the following NPM packages: mongodb, mongoose and mongojs

Rule-specific references:

OWASP Testing for NoSQL Injection

Option A: Sanitize untrusted data by converting it to string

Sanitizing by converting untrusted data to a string.

Locate one of the following vulnerable patterns:

The intention of this code is for req.query.password to be a string. However, if it is { "$ne": "not_the_password" } for example, the query will succeed without the user knowing the password:

const MongoClient = require('mongodb').MongoClient;
const app = express();
const url = 'mongodb://localhost:27017';
const dbName = 'myProject';
const client = new MongoClient(url);

app.get('/users', async (req, res) => {
  let users;

  try {
    client.connect("mongodb://someHost:somePort/", (err, db) => {
      if (err) throw err;         

      db.collection("someCollection").find({ password: req.query.password }).toArray((err, result) => {
        if (err) throw err;
        users = result;
        client.close();
      });
    });

    res.send(users);
  } catch (err) {
    console.error(err);
    res.status(500).send({ error: 'An error occurred' });
  }
});

Replace the query line with one of the following patterns:
By converting the value of the password property to a string before MongoDB processes the query, any MongoDB operators are no longer processed as operators.
Sanitize using the template literal construct:
```
db.collection("someCollection").find({ password: `${req.query.password}` }).toArray((err, result) => {
  if (err) throw err;
  users = result;
  client.close();
});
```
Sanitize using the [type].prototype.toString() method:
```
db.collection("someCollection").find({ password: req.query.password.toString() }).toArray((err, result) => {
  if (err) throw err;
  users = result;
  client.close();
});
```
The mongo-sanitize NPM package can also be used to neutralize/remove any object entries with property names that begin with $.
Test it
Ship it 🚢 and relax 🌴

Option B: Sanitize untrusted data by converting it to JSON

Sanitizing by converting untrusted data to JSON.

Locate one of the following vulnerable patterns:

Similar to the previous pattern, except one of the untrusted values is nested:

import { MongoClient } from 'mongodb';

const app = express();
const url = 'mongodb://localhost:27017';
const dbName = 'myProject';
const client = new MongoClient(url);

app.get('/users', async (req, res) => {
  try {
    await client.connect();
    const db = client.db(dbName);
    const collection = db.collection('users');

    const users = await collection.find({ user: req.query.user, city: { $ne: req.query.city } }, {/* projection */}, {/* options */}).toArray();

    res.send(users);
    client.close();
  } catch (err) {
    console.error(err);
    res.status(500).send({ error: 'An error occurred' });
  }
});

Replace the query line with a pattern similar to the following:

By converting user and city to JSON vai JSON.stringify, any MongoDB operators are now strings and therefore sanitized.

const users = await collection.find({ user: JSON.stringify(req.query.user), city: { $ne: JSON.stringify(req.query.city) } }, {/* projection */}, {/* options */}).toArray();

Test it
Ship it 🚢 and relax 🌴

Option C: `$where` operator requires parameterization

Converting untrusted data to strings does not work for every case though. The $where operator requires a string.

The $function operator is also of concern, although not quite as dangerous as $where.

Locate one of the following vulnerable patterns:

The value of the $where operator is a string containing a JavaScript expression or a full JavaScript function to the query system, so is especially dangerous.

import { MongoClient } from 'mongodb';

const app = express();
const url = 'mongodb://localhost:27017';
const dbName = 'myProject';
const client = new MongoClient(url);

app.get('/users', async (req, res) => {
  try {
    await client.connect();
    const db = client.db(dbName);
    const collection = db.collection('users');

    const user = req.query.user;
    const query = { $where: `this.user == '${user}'` }
    // Vulnerable:
    const users1 = await collection.find(query, {}).toArray();

    // Vulnerable:
    const users2 = await collection.find({ $where: req.body.query }, {});
    // Vulnerable:
    const users3 = await collection.find({ $where: "name = " + req.body.name });
    // Vulnerable:
    const users4 = await collection.find({ $where: req.body.query.toString() }, {});
    // Vulnerable:
    const users5 = await collection.find({ $where: String(req.body.query) }, {});
    // Vulnerable:
    const users6 = await collection.find({ $where: JSON.stringify(req.body.query) }, {});

    res.send(users1 || users2 || users3 || users4 || users5 || users6);
    client.close();
  } catch (err) {
    console.error(err);
    res.status(500).send({ error: 'An error occurred' });
  }
});

The value of the $where operator needs to be carefully constructed. As mentioned previously, each untrusted value should be validated and filtered based on what you expect it to be. For example, consider using an allow-list for each value you are intending on using within the query. Split the untrusted data up into as small of pieces as possible to help you validate each one. Consider validating each untrusted value against constraints such as length, and domain type (Ex: are you expecting an email address, IP address, person's name, etc).
Test it
Ship it 🚢 and relax 🌴

Fixing Insecure Use of SQL Queries​

What is SQL injection (SQLi)?​

What is the impact of SQL injections?​

How to prevent SQL injections?​

References​

Taxonomies​

Explanation & Prevention​

Related CVEs​

Training​

SQL injection​

Option A: Use ORM libraries​

Option B: Validate input with Joi​

SQL Injection (MySQL)​

Option A: Validate, sanitize, filter untrusted data and parameterize queries​

SQL Injection (MongoDB)​

Option A: Sanitize untrusted data by converting it to string​

Option B: Sanitize untrusted data by converting it to JSON​

Option C: $where operator requires parameterization​

Fixing Insecure Use of SQL Queries

What is SQL injection (SQLi)?

What is the impact of SQL injections?

How to prevent SQL injections?

References

Taxonomies

Explanation & Prevention

Related CVEs

Training

SQL injection

Option A: Use ORM libraries

Option B: Validate input with Joi

SQL Injection (MySQL)

Option A: Validate, sanitize, filter untrusted data and parameterize queries

SQL Injection (MongoDB)

Option A: Sanitize untrusted data by converting it to string

Option B: Sanitize untrusted data by converting it to JSON

Option C: `$where` operator requires parameterization