SQL injection through string concatenation

Description

Concatenating or formatting request values into SQL, JPQL, or HQL text removes the boundary between data and query syntax. An attacker can inject conditions or additional syntax to change what the query means. Even PreparedStatement is unsafe if an already contaminated SQL string is passed to prepareStatement. Define the query template on the server and bind external values separately to placeholders.

Potential impact

Unauthorized data retrieval or bypass of authentication and authorization checks.
Insertion, modification, or deletion of data.
Reduced availability through expensive queries.
The actual impact depends on the executed statement and the database account's permissions.

Remediation

Define SQL, JPQL, and HQL text on the server. Bind data values through PreparedStatement, JdbcTemplate, JdbcClient.param, or JPA/Hibernate setParameter.
For column names or sort directions that cannot use placeholders, map input to server-defined constants through an enum, switch, or an immutable finite allow-list. Do not concatenate the original request string merely because it passed a check.
Do not substitute SQL escaping, format validation, or a helper whose name merely suggests sanitization for parameter binding.
Give the database account only the permissions the application needs.

Examples

JDBC

Before

java

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import jakarta.servlet.http.HttpServletRequest;

public final class UserRepositoryUnsafe {
    public boolean exists(HttpServletRequest request, Connection connection)
            throws Exception {
        String username = request.getParameter("username");
        String requestedSort = request.getParameter("sort");
        String sql = "SELECT id FROM users WHERE username = '" + username
                + "' ORDER BY " + requestedSort;

        try (Statement statement = connection.createStatement();
                ResultSet result = statement.executeQuery(sql)) {
            return result.next();
        }
    }
}

username is inserted as a value and requestedSort as an identifier, so either input can change the query structure.

After

java

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import jakarta.servlet.http.HttpServletRequest;

public final class UserRepositorySafe {
    public boolean exists(HttpServletRequest request, Connection connection)
            throws Exception {
        String username = request.getParameter("username");
        String requestedSort = request.getParameter("sort");

        String sortColumn = switch (requestedSort) {
            case "created" -> "created_at";
            case "lastLogin" -> "last_login";
            default -> "created_at";
        };
        String sql = "SELECT id FROM users WHERE username = ? ORDER BY "
                + sortColumn;

        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, username);
            try (ResultSet result = statement.executeQuery()) {
                return result.next();
            }
        }
    }
}

Values are bound to ?. Column names that cannot be bound are mapped to server-defined constants instead of reusing the request string.

Spring JdbcClient

JdbcClient, recommended from Spring Framework 6.1 onward, also accepts query text through sql(String). Using a modern API does not make dynamically concatenated SQL safe.

Before

java

import jakarta.servlet.http.HttpServletRequest;
import org.springframework.jdbc.core.simple.JdbcClient;

public final class SpringUserRepositoryUnsafe {
    private final JdbcClient client;

    public SpringUserRepositoryUnsafe(JdbcClient client) {
        this.client = client;
    }

    public void find(HttpServletRequest request) {
        String name = request.getParameter("name");
        client.sql("SELECT * FROM users WHERE name = '" + name + "'")
                .query()
                .listOfRows();
    }
}

After

java

import jakarta.servlet.http.HttpServletRequest;
import org.springframework.jdbc.core.simple.JdbcClient;

public final class SpringUserRepositorySafe {
    private final JdbcClient client;

    public SpringUserRepositorySafe(JdbcClient client) {
        this.client = client;
    }

    public void find(HttpServletRequest request) {
        String name = request.getParameter("name");
        client.sql("SELECT * FROM users WHERE name = :name")
                .param("name", name)
                .query()
                .listOfRows();
    }
}

JdbcTemplate uses ? placeholders with value arguments, while NamedParameterJdbcTemplate uses :name placeholders and a parameter map in the same way.

JPA/Hibernate

java

import jakarta.persistence.EntityManager;
import jakarta.servlet.http.HttpServletRequest;

public final class JpaUserRepositorySafe {
    private final EntityManager entityManager;

    public JpaUserRepositorySafe(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public void find(HttpServletRequest request) {
        String name = request.getParameter("name");
        entityManager.createQuery(
                        "select u.id, u.name from User u where u.name = :name",
                        Object[].class)
                .setParameter("name", name)
                .getResultList();
    }
}

For Hibernate Session.createSelectionQuery, createMutationQuery, and createNativeMutationQuery, bind external values with setParameter rather than concatenating them into HQL/SQL. Named queries also prevent this issue only when their query text is fixed and external values are bound.

SQL injection through string concatenation

Description

Potential impact

Remediation

Examples

JDBC

Before

After

Spring JdbcClient

Before

After

JPA/Hibernate

References