Need

Implementation of secure coding practices to prevent 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 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}'`; // Vulnerable code

  connection.query(query, (error, results) => {
    if (error) {
      res.status(500).json({ error: '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 in the line where the SQL query is constructed:

const query = SELECT * FROM users WHERE username = '${username}';

In this line, the value of the username variable is directly concatenated into the SQL query string without any validation or sanitization. This means that an attacker can manipulate the value of the username parameter in the request URL to inject malicious SQL code.

For example, if an attacker sets the username parameter to ' OR '1'='1, the resulting SQL query would be:

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

This would cause the query to return all rows from the users table, effectively bypassing any authentication or authorization checks.

The impact of this vulnerability is that an attacker can execute arbitrary SQL statements, potentially gaining unauthorized access to sensitive information, modifying or deleting data, or even executing commands on the underlying database server.

To fix this vulnerability, the code should use parameterized queries or prepared statements instead of directly concatenating user input into the SQL query. This would ensure that the user input is properly escaped and treated as data, preventing any malicious SQL code from being executed.

Steps

• Use parameterized queries or prepared statements instead of concatenating user input directly into the SQL query.
• Sanitize and validate user input before using it in the query.
• Escape special characters in user input to prevent SQL injection attacks.
• Implement proper error handling to handle any potential errors during the query execution.

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 = ?';

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

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

The fixed code addresses the SQL injection vulnerability by implementing parameterized 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. The code creates an instance of the express application and establishes a connection to the MySQL database using the 'mysql.createConnection' method. The connection details, such as the host, username, password, and database name, are provided.

3. The code defines a route for handling GET requests to '/users'. Inside the route handler, it retrieves the value of the 'username' query parameter from the request object.

4. Instead of directly concatenating the 'username' value into the SQL query, the code uses a parameterized query approach. It defines the SQL query string with a placeholder '?' for the 'username' value.

5. The 'connection.query' method is used to execute the SQL query. The 'username' value is passed as an array in the second argument of the method, which will be safely inserted into the query as a parameter.

6. The callback function for 'connection.query' handles the response from the database. If there is an error, it sends a 500 Internal Server Error response. Otherwise, it sends the query results as a JSON response.

7. The code starts the server and listens on port 3000. A console log message is printed to indicate that the server is running.

By using parameterized queries, the fixed code ensures that user-supplied input is properly sanitized and prevents SQL injection attacks.

References

• 146. SQL injection