Skip to main content

SQL injection

Need​

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

Context​

  • Usage of Java for building robust and scalable applications
  • Usage of java.sql for database connectivity and SQL operations in Java
  • Usage of javax.servlet for Java web application development

Description​

Non compliant code​

import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.http.*;

public class VulnerableServlet extends HttpServlet {
    private Connection connection;

    public void init() throws ServletException {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost/testDB", "user", "password");
        } catch (Exception e) {
            throw new ServletException(e);
        }
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            String id = request.getParameter("id");
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM Users WHERE id = " + id);
            // process the result
        } catch (SQLException e) {
            throw new ServletException(e);
        }
    }
}

The above code is a simple servlet that connects to a MySQL database and fetches a user based on the provided ID. The ID is taken directly from the request parameter and concatenated into the SQL query. This is a classic example of an SQL injection vulnerability.

The problem arises in the doGet method. Here, the id is directly taken from the request parameters and used in the SQL query without any validation or sanitization. This allows an attacker to manipulate the SQL query by sending a specially crafted id parameter.

For instance, an attacker could send 1 OR 1=1 as the id, which would result in the query SELECT * FROM Users WHERE id = 1 OR 1=1. Since 1=1 is always true, this would return all users, potentially leaking sensitive information.

Even worse, an attacker could send 1; DROP TABLE Users; -- as the id, which would result in the query SELECT * FROM Users WHERE id = 1; DROP TABLE Users; --. This would delete the entire Users table from the database.

To fix this vulnerability, never build SQL queries by concatenating user inputs. Instead, use prepared statements or stored procedures, which can safely parameterize inputs.

Steps​

  • Use parameterized statements or prepared statements to execute SQL queries.
  • Validate and sanitize user input before using it in SQL queries.
  • Implement proper input validation to ensure only valid data is used in SQL queries.
  • Implement proper error handling to handle any SQL exceptions that may occur.

Compliant code​

import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.http.*;

public class SecureServlet extends HttpServlet {
    private Connection connection;

    public void init() throws ServletException {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost/testDB", "user", "password");
        } catch (Exception e) {
            throw new ServletException(e);
        }
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            String id = request.getParameter("id");
            // Validate and sanitize user input
            if (id != null && id.matches("\\\\d+")) {
                PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM Users WHERE id = ?");
                preparedStatement.setInt(1, Integer.parseInt(id));
                ResultSet resultSet = preparedStatement.executeQuery();
                // process the result
            } else {
                // handle invalid input
            }
        } catch (SQLException e) {
            throw new ServletException(e);
        }
    }
}

The above code fixes the SQL Injection vulnerability by using a PreparedStatement instead of a Statement. A PreparedStatement allows us to use placeholders (?) for our parameters, which are then filled in by the PreparedStatement object in a safe manner, preventing SQL Injection attacks.

The user input is also validated and sanitized before it is used in the SQL query. The matches("\\\\d+") method checks if the input is a number. If the input is not a number, the code does not execute the SQL query, preventing potential SQL Injection attacks.

The code also implements proper error handling. If a SQLException occurs, it is caught and a ServletException is thrown. This allows the application to handle the error in a controlled manner, preventing potential information leakage to the attacker.

References​

Last updated on