Need

Prevention of time-based SQL injection attacks

Context

• Usage of TypeScript for statically typed 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 connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydatabase'
});

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

  connection.query(query, (error, results) => {
    if (error) throw error;
    res.json(results);
  });
});

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

The vulnerability in the given code is a Time-based SQL Injection.

In the vulnerable code, the user input id is directly concatenated into the SQL query without any sanitization or validation. This allows an attacker to manipulate the id parameter and inject malicious SQL code.

For example, an attacker can pass a value like 1 OR SLEEP(5) as the id parameter. This would result in the following SQL query being executed: SELECT * FROM users WHERE id = 1 OR SLEEP(5). If the database takes 5 seconds to respond to this query, it indicates that the vulnerability exists.

By leveraging time delays, an attacker can extract sensitive information from the database or interfere with the application's queries. They can also use techniques like UNION-based SQL Injection to retrieve data from other database tables.

This vulnerability can have severe consequences, including unauthorized access to data, data leakage, and potential manipulation of the application's authentication and authorization mechanisms.

To mitigate this vulnerability, it is recommended to use prepared statements (parameterized queries) or stored procedures. These techniques ensure that user input is properly sanitized and separated from the SQL query, preventing any possibility of SQL injection attacks. Additionally, enforcing the principle of least privilege can help limit the potential impact of an attack.

Steps

• Use parameterized queries instead of concatenating user input directly into the SQL query.
• Modify the code to use prepared statements to prevent SQL injection attacks.
• Sanitize and validate user input before using it in the SQL query.
• Implement proper error handling and logging to handle any potential errors.
• Consider implementing a web application firewall (WAF) to provide an additional layer of protection against SQL injection attacks.
• Regularly update and patch the dependencies used in the application to ensure any known vulnerabilities are addressed.
• Follow the principle of least privilege and ensure that the database user used by the application has only the necessary permissions.

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/:id', (req, res) => {
  const id = req.params.id;
  const query = 'SELECT * FROM users WHERE id = ?';
  const values = [id];

  connection.query(query, values, (error, results) => {
    if (error) throw error;
    res.json(results);
  });
});

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

The fixed code addresses the vulnerability by implementing several best practices to prevent Time-based SQL Injection attacks.

1. Prepared Statements: The code uses prepared statements by using parameterized queries. In the query, the placeholder "?" is used to represent the value that will be provided later. This ensures that user input is treated as data and not as part of the SQL query itself, preventing any potential SQL injection.

2. Input Sanitization: The code retrieves the user input from the request parameters and assigns it to the "id" variable. This ensures that the input is properly sanitized and validated before being used in the SQL query. By using parameterized queries, any malicious SQL code entered as the "id" parameter will be treated as data and not executed as part of the query.

3. Error Handling: The code includes error handling for the database query. If an error occurs during the execution of the query, it will be caught and an error response will be sent. This prevents any potential leakage of sensitive information or unintended behavior due to unhandled errors.

4. Least Privilege: Although not explicitly shown in the code, it is recommended to enforce the principle of least privilege when configuring the database user. The user should have the minimum necessary privileges required to perform the required operations, reducing the potential impact of any successful attack.

By implementing these measures, the fixed code ensures that user input is properly handled and prevents any possibility of Time-based SQL Injection attacks.

References

• 154. Time-based SQL Injection