SQL injection - Code

Need

Implementation of proper input validation and the use of parameterized statements or stored procedures to prevent SQL injection attacks.

Context

• Usage of TypeScript for type-checking and compiling JavaScript code
• Usage of Express for building web applications and APIs
• Usage of MySQL for database connectivity and querying

Description

Non compliant code

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

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

app.get('/users', (req, res) => {
  const username = req.query.username;
  const query = `SELECT * FROM users WHERE username = '${username}'`;

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

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

The vulnerability in this code is a SQL injection vulnerability. It occurs because the code constructs a SQL query string by directly concatenating the user-supplied input (username) into the query without proper validation or sanitization.

In the code, the username value is obtained from the request query parameters (req.query.username). It is then directly used in the SQL query string without any validation or sanitization. This means that an attacker can manipulate the username parameter to inject arbitrary SQL code into the query.

For example, an attacker can pass a value like ' OR '1'='1' -- as the username parameter. This would result in the following SQL query being executed:

SELECT * FROM users WHERE username = '' OR '1'='1' --'

The injected SQL code ' OR '1'='1' -- is a common technique used to bypass the authentication mechanism and retrieve all the records from the users table.

This vulnerability allows an attacker to execute arbitrary SQL statements, potentially leading to unauthorized access, data leakage, or even data manipulation.

To fix this vulnerability, the code should use parameterized statements or prepared statements to ensure that user input is properly sanitized and treated as data rather than executable code.

Steps

• Use parameterized statements or prepared statements instead of concatenating user input directly into the SQL query
• Sanitize and validate user input before using it in the SQL query
• Implement proper error handling and logging for database queries

Compliant code

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

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

app.get('/users', (req, res) => {
  const username = req.query.username;
  const query = 'SELECT * FROM users WHERE username = ?';
  const values = [username];

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

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

The fixed code is written in TypeScript and addresses the SQL injection vulnerability by using parameterized statements. Here's an explanation of the code:

1. The code imports the necessary modules, including Express and MySQL.

2. An Express application is created using the express() function.

3. A MySQL connection is established using the mysql.createConnection() function. The connection details, such as the host, username, password, and database name, are provided.

4. An endpoint /users is defined using the app.get() method. This endpoint handles GET requests and expects a query parameter username.

5. Inside the request handler function, the username value is extracted from the request query.

6. A SQL query is defined as a string, selecting all columns from the users table where the username column matches the provided value. The query uses a placeholder ? for the value.

7. An array values is created, containing the username value.

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

9. In the callback function, any error that occurred during the query execution is logged to the console, and an appropriate error response is sent if an error occurred. Otherwise, the query results are sent as a JSON response.

10. The Express application is set to listen on port 3000 using the app.listen() method. A log message is printed to the console once the server starts.

By using parameterized statements (? placeholder) and passing the values separately, the fixed code ensures that user input is properly sanitized and prevents SQL injection attacks.

References

• 297.SQL injection - Code