Skip to main content

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 Java for building robust and scalable applications
  • Usage of java.sql.Connection for establishing a connection to a database in Java
  • Usage of java.sql.DriverManager for managing database connections in Java
  • Usage of java.sql.Statement for executing SQL statements in Java
  • Usage of javax.servlet.http.HttpServlet for handling HTTP requests in Java Servlet applications
  • Usage of javax.servlet.http.HttpServletRequest for handling HTTP requests in Java Servlet applications
  • Usage of HttpServletResponse for handling HTTP responses in Java Servlets

Description​

Non compliant code​

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class VulnerableServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) {
        String user = request.getParameter("user");
        String pass = request.getParameter("pass");

        try {
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/testdb", "user", "password");
            Statement statement = connection.createStatement();
            String query = "SELECT * FROM Users WHERE username='" + user + "' AND password='" + pass + "'";
            statement.executeQuery(query);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

The above code represents a servlet which accepts a username and password from an HTTP POST request and uses these values to form a SQL query. This query is then executed to retrieve user data from a database.

The vulnerability lies in the formation of the SQL query. The code directly concatenates the user-provided input into the query, which opens up the possibility for a SQL Injection attack. An attacker could provide a specially crafted input such as username'; DROP TABLE Users; -- which would lead to the deletion of the entire Users table.

This is because the crafted input ends the original SQL statement after the username and then starts a new one to drop the table. The -- at the end is a SQL comment which causes the rest of the original query to be ignored.

The code does not perform any data validation or sanitization on the user input before using it to form the SQL query. It also does not use parameterized statements or stored procedures which would prevent this kind of injection attack.

Steps​

  • Use parameterized statements or prepared statements to execute SQL queries.
  • Validate and sanitize user input before using it in SQL queries.
  • Implement proper access control and authentication mechanisms to prevent unauthorized access to the database.
  • Implement least privilege principle by granting only necessary permissions to database users.
  • Regularly update and patch the database management system to fix any known vulnerabilities.
  • Implement input validation to ensure that only expected data types and formats are accepted.
  • Implement proper error handling and logging to detect and respond to potential SQL injection attacks.
  • Educate developers about secure coding practices and the risks associated with SQL injection vulnerabilities.

Compliant code​

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class SecureServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) {
        String user = request.getParameter("user");
        String pass = request.getParameter("pass");

        // Validate and sanitize user input
        if (isValid(user) && isValid(pass)) {
            try {
                Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/testdb", "user", "password");
                String query = "SELECT * FROM Users WHERE username= ? AND password= ?";
                PreparedStatement preparedStatement = connection.prepareStatement(query);
                preparedStatement.setString(1, user);
                preparedStatement.setString(2, pass);
                preparedStatement.executeQuery();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    // Implement input validation
    private boolean isValid(String input) {
        // Add validation logic here
        return true;
    }
}

The above code fixes the SQL Injection vulnerability by using parameterized queries (also known as prepared statements) instead of concatenating user input directly into the SQL query. This ensures that user input is always treated as literal data, not part of the SQL command, effectively eliminating the risk of SQL Injection.

The isValid function is a placeholder for input validation logic. This function should be implemented to check the user input for any unexpected data types, formats, or values. This is an important step in preventing not only SQL Injection, but also other types of injection attacks.

In addition to these code changes, it's important to follow secure coding practices and principles such as least privilege, regular patching and updates, proper access control and authentication, and developer education to prevent SQL Injection and other vulnerabilities.

References​

Last updated on