SQL Injection - Headers

Need

Prevention of SQL injection in the idClient header and application fields

Context

• Usage of Node.js v14.17.0 for server-side JavaScript development
• Usage of Express for building web applications and APIs
• Usage of MySQL for database connectivity and query execution

Description

Non compliant code

import express from 'express';
import mysql from 'mysql';

const app = express();
const port = 3000;

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydatabase'
});

app.get('/users', (req, res) => {
  const idClient = req.headers['idclient'];
  const application = req.headers['application'];

  const query = `SELECT * FROM users WHERE idClient = '${idClient}' AND application = '${application}'`;

  connection.query(query, (error, results) => {
    if (error) {
      res.status(500).json({ error: 'Internal Server Error' });
    } else {
      res.json(results);
    }
  });
});

app.listen(port, () => {
  console.log(`Server is running on port ${port}`);
});

The vulnerability in this code is a SQL Injection vulnerability. It occurs because the code directly concatenates user-controlled input (idClient and application) into the SQL query string without proper sanitization or validation.

In the vulnerable code, the idClient and application values are obtained from the request headers without any validation. Then, these values are directly used in the SQL query string without any form of parameterization or escaping.

An attacker can exploit this vulnerability by manipulating the idClient and application headers to inject malicious SQL statements. For example, an attacker can set the idClient header to ' OR 1=1 -- which would result in the following query:

SELECT * FROM users WHERE idClient = '' OR 1=1 --' AND application = '...'

This injected SQL statement would always evaluate to true (1=1), bypassing any intended authentication or authorization checks. The attacker can then retrieve or modify sensitive information in the database.

To mitigate this vulnerability, it is recommended to use parameterized statements or stored procedures. Parameterized statements separate the SQL query logic from the user input, preventing the injection of malicious code.

Steps

• Sanitize and validate the idClient and application headers before using them in the SQL query
• Use parameterized statements or prepared statements to execute the SQL query
• Handle errors properly and provide appropriate error messages

Compliant code

import express from 'express';
import mysql from 'mysql';

const app = express();
const port = 3000;

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydatabase'
});

app.get('/users', (req, res) => {
  const idClient = req.headers['idclient'];
  const application = req.headers['application'];

  const query = 'SELECT * FROM users WHERE idClient = ? AND application = ?';
  const values = [idClient, application];

  connection.query(query, values, (error, results) => {
    if (error) {
      res.status(500).json({ error: 'Internal Server Error' });
    } else {
      res.json(results);
    }
  });
});

app.listen(port, () => {
  console.log(`Server is running on port ${port}`);
});

The fixed code addresses the SQL Injection vulnerability by using parameterized statements to perform database queries. Here's an explanation of the changes made:

1. The code imports the necessary modules, including 'express' for creating the server and 'mysql' for connecting to the MySQL database.

2. A connection to the MySQL database is established using the mysql.createConnection() method. The connection details, such as the host, user, password, and database name, are provided.

3. The code defines an Express app and sets the port to 3000.

4. In the route handler for the '/users' endpoint, the code retrieves the 'idClient' and 'application' values from the request headers using req.headers['idclient'] and req.headers['application'], respectively.

5. The code constructs a SQL query string with placeholders for the 'idClient' and 'application' values. The query string is 'SELECT * FROM users WHERE idClient = ? AND application = ?'.

6. An array of values is created with the 'idClient' and 'application' variables. This array, values, will be used to replace the placeholders in the query.

7. The connection.query() method is used to execute the SQL query with the provided values. This method takes the query string, the values array, and a callback function as parameters.

8. In the callback function, the code checks if an error occurred during the query execution. If an error is present, the server responds with a 500 status code and an error message. Otherwise, the server responds with the query results in JSON format.

9. The app listens on the specified port, and a console log message is printed to indicate that the server is running.

By using parameterized statements, the code ensures that the user input (idClient and application) is treated as data rather than executable code. This prevents SQL Injection attacks by escaping special characters and separating the SQL logic from the user input.

References

• 155.SQL Injection - Headers