Skip to content

CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') - Java

Overview

SQL Injection occurs when an application builds a SQL query out of untrusted data rather than parameterizing it. An attacker who controls part of the query text can bypass authentication, read or modify data the query was never meant to reach, or run administrative operations.

Primary Defence: Use PreparedStatement with parameterized queries (JDBC), JPA/Hibernate named parameters, or query builder frameworks that automatically parameterize values.

Common Vulnerable Patterns

String Concatenation

String userId = request.getParameter("id");
String query = "SELECT * FROM users WHERE id = " + userId;
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query); // VULNERABLE

Why this is vulnerable:

  • String concatenation incorporates user input directly into SQL queries without escaping, allowing attackers to inject SQL code (e.g., 1 OR 1=1, 1; DROP TABLE users--) that bypasses authentication or executes unauthorized database operations.

String.format()

String username = request.getParameter("username");
String query = String.format("SELECT * FROM users WHERE username = '%s'", username);
stmt.executeQuery(query); // VULNERABLE

Why this is vulnerable:

  • String formatting embeds user input directly into the SQL string.
  • Quotes and SQL operators can break out of the intended literal.
  • Attackers can inject additional predicates or statements.

Secure Patterns

PreparedStatement (JDBC)

String userId = request.getParameter("id");
String query = "SELECT * FROM users WHERE id = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, userId); // Parameterized value
ResultSet rs = pstmt.executeQuery(); // SECURE - the value was bound as a parameter

Why this works:

  • PreparedStatement sends the query structure and parameter values separately to the database.
  • The database treats parameter values as pure data, not executable SQL, preventing injection regardless of special characters in the input.

Named Parameters (JPA/Hibernate)

String username = request.getParameter("username");
String jpql = "SELECT u FROM User u WHERE u.username = :username";
TypedQuery<User> query = entityManager.createQuery(jpql, User.class);
query.setParameter("username", username); // SECURE - bound as a named parameter
List<User> users = query.getResultList();

Why this works:

  • JPA named parameters (:username) are placeholders the persistence provider binds at execution.
  • The framework ensures the value is sent as a parameter, not concatenated into the JPQL/SQL.

Criteria API (Type-Safe)

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<User> cq = cb.createQuery(User.class);
Root<User> user = cq.from(User.class);
cq.select(user).where(cb.equal(user.get("username"), username)); // SECURE - the Criteria API parameterizes the comparison
List<User> users = entityManager.createQuery(cq).getResultList();

Why this works:

  • The Criteria API uses a type-safe, object-oriented approach to query building.
  • The cb.equal() method automatically creates a parameterized condition, ensuring the username value is treated as data, not SQL code.

Framework-Specific Guidance

Spring Data JPA

// Repository interface - completely safe
public interface UserRepository extends JpaRepository<User, Long> {
    // SECURE - method name query
    User findByUsername(String username);

    // SECURE - @Query with named parameters
    @Query("SELECT u FROM User u WHERE u.email = :email")
    User findByEmail(@Param("email") String email);
}

Why this works:

  • Spring Data JPA translates method names like findByUsername into parameterized queries automatically.
  • The @Query annotation with :email creates named parameters.
  • Both approaches ensure values are sent as parameters, not embedded in SQL.

MyBatis

public interface UserMapper {
    User getUserById(@Param("userId") String userId);
}

<!-- SECURE - XML mapper with a parameterized query -->
<select id="getUserById" resultType="User">
    SELECT * FROM users WHERE id = #{userId}
</select>

// SECURE - Java usage of the mapper
User user = userMapper.getUserById(userId);

Why this works:

  • MyBatis uses #{userId} as a parameterized placeholder in XML mappers when the mapper method exposes that name, for example through @Param("userId").
  • The framework automatically creates a PreparedStatement and binds the userId parameter.
  • Avoid ${userId} for untrusted values; ${} performs text substitution and can create SQL injection.

Data Type Handling

Numeric Parameters

String idParam = request.getParameter("id");
try {
    int id = Integer.parseInt(idParam); // Validate type first
    String query = "SELECT * FROM users WHERE id = ?";
    PreparedStatement pstmt = connection.prepareStatement(query);
    pstmt.setInt(1, id); // Type-safe
    // ...
} catch (NumberFormatException e) {
    // Handle invalid input
}

IN Clauses (Multiple Values)

// Vulnerable - don't build dynamic IN clause
String ids = request.getParameter("ids"); // "1,2,3"
String query = "SELECT * FROM users WHERE id IN (" + ids + ")"; // VULNERABLE

List<Integer> userIds = Arrays.asList(1, 2, 3, 4, 5);

// SECURE - Spring Data JPA handles IN clauses
List<User> users = userRepository.findByIdIn(userIds);

// SECURE - or with the @Query annotation
@Query("SELECT u FROM User u WHERE u.id IN :ids")
List<User> findByIds(@Param("ids") List<Integer> ids);

Why this works:

  • Spring Data JPA recognizes collection-type parameters and automatically generates parameterized IN clauses.
  • Each value in the list becomes a separate parameter with its own ? placeholder, maintaining injection protection for multiple values.
List<Integer> userIds = Arrays.asList(1, 2, 3, 4, 5);

// SECURE - JPA expands collections
String jpql = "SELECT u FROM User u WHERE u.id IN :userIds";
TypedQuery<User> query = entityManager.createQuery(jpql, User.class);
query.setParameter("userIds", userIds);
List<User> users = query.getResultList();

// Generated SQL: SELECT * FROM users WHERE id IN (?, ?, ?, ?, ?)

Why this works:

  • JPA automatically expands collection parameters into multiple placeholders.
  • When you pass a List to setParameter(), JPA binds each element as its own parameter.

JDBC PreparedStatement, the manual equivalent

// Build placeholders dynamically, but set each parameter safely
List<Integer> ids = parseIds(request.getParameter("ids"));
String placeholders = String.join(",", Collections.nCopies(ids.size(), "?"));
String query = "SELECT * FROM users WHERE id IN (" + placeholders + ")";
PreparedStatement pstmt = connection.prepareStatement(query);
for (int i = 0; i < ids.size(); i++) {
    pstmt.setInt(i + 1, ids.get(i));
}
ResultSet rs = pstmt.executeQuery(); // SECURE - each value is parameterized

Why this works:

  • While the placeholder string is concatenated (e.g., "?, ?, ?"), the actual values are bound using setInt(), making each one a proper parameter.
  • Only the query structure is dynamic, not the data, preventing injection.

Migration Considerations

  1. Identify all SQL construction: Search for Statement, createQuery, string concatenation with SQL keywords
  2. Replace with PreparedStatement: connection.createStatement() becomes connection.prepareStatement(query)
  3. Parameterize inputs: Replace concatenated values with ? placeholders
  4. Set parameters: Use setString(), setInt(), etc. for each placeholder
  5. Test: Verify functionality and try injection payloads

Database Permission Hardening (Defense in Depth)

Even with parameterized queries, restrict database permissions to limit SQL injection impact:

MySQL/MariaDB Permission Examples

Create restricted application user:

-- Create user with limited permissions

CREATE USER 'app_user'@'app_server_ip' IDENTIFIED BY 'strong_password';

-- Grant specific table access only

GRANT SELECT, INSERT, UPDATE ON myapp.users TO 'app_user'@'app_server_ip';
GRANT SELECT, INSERT, UPDATE ON myapp.orders TO 'app_user'@'app_server_ip';
GRANT SELECT ON myapp.products TO 'app_user'@'app_server_ip';  -- Read-only

-- Explicitly deny dangerous operations

REVOKE FILE, PROCESS, SUPER, SHUTDOWN, CREATE USER ON *.* FROM 'app_user'@'app_server_ip';
REVOKE DROP, CREATE, ALTER, TRUNCATE ON myapp.* FROM 'app_user'@'app_server_ip';

-- Flush privileges

FLUSH PRIVILEGES;

What this prevents in SQLi attacks:

  • '; DROP TABLE users; -- - No DROP permission
  • ' UNION SELECT * FROM admin_users -- - No access to admin tables
  • '; LOAD DATA INFILE '/etc/passwd' -- - No FILE permission
  • '; EXEC xp_cmdshell 'rm -rf /' -- - No system command execution

Still possible (use prepared statements!):

  • Data exfiltration from permitted tables
  • Unauthorized data modification within granted permissions

Key principle: Least privilege reduces blast radius but does NOT eliminate SQL injection risk. Always use prepared statements.

Java connection string with restricted user:

String url = "jdbc:mysql://localhost:3306/myapp";
String username = "app_user";  // Restricted user, not root!
String password = System.getenv("DB_PASSWORD");

Connection conn = DriverManager.getConnection(url, username, password);

Considerations

Placeholders do not work for identifiers. A prepared statement binds values, so a table name, a column name, or the direction in an ORDER BY cannot be parameterised - the driver would quote it as a string and the query would fail. This is why a query that looks parameterised can still be injectable: the values are bound and the identifier is concatenated.

Map identifiers through an allowlist, and prefer removing the need. Where a caller must influence an identifier, treat their input as a key into a server-side map of permitted names and reject anything absent from it, rather than validating the input and using it directly. Better still, ask whether the identifier needs to be caller-controlled at all - a sort parameter that accepts created_at or total is usually a fixed set of queries wearing a dynamic costume, and enumerating them removes the question entirely.

Where the framework can resolve the name for you, let it do that too. On the JPA and Hibernate side an identifier does not have to reach the query text as a string: JPA Criteria's root.get(name) and Hibernate's org.hibernate.query.Order resolve a property name against the entity metamodel, so anything that is not a mapped attribute raises PathElementException rather than being parsed. That is Hibernate's exception type; Jakarta Persistence specifies IllegalArgumentException for an unknown attribute, and Hibernate's is a subclass of it, so catching IllegalArgumentException holds whichever provider is underneath. That guarantee is narrower than it first looks, which is why it supplements the allowlist instead of replacing it: it proves the name is an attribute, not that this caller may sort by it, and Order.asc(User.class, "password") will sort by the password column quite happily - ordering alone is enough to extract it a row at a time. CWE-564 covers the Hibernate case in full.

Common Pitfalls

  • Using MyBatis ${} substitution for a value that "seems like" it needs raw text, such as a LIKE pattern or an ORDER BY column, instead of #{}. ${} performs plain text substitution, not parameter binding, so any untrusted value passed through it is injectable even though #{} is used correctly elsewhere in the same mapper.
  • Building a Spring Data JPA @Query by concatenating a dynamic part of the query, such as "SELECT u FROM User u WHERE u." + field + " = :value". The value half of the query is genuinely parameterized, which can create false confidence, but the concatenated field name is still an injectable identifier.
  • Parameterizing a PreparedStatement's values correctly with ? while building the base query text by concatenating a table or schema name derived from a multi-tenant request parameter, without allowlist validation of that identifier.
  • Using Statement instead of PreparedStatement for a query considered "internal only" (an admin report, a scheduled job), then later reusing that same query with a parameter sourced from user-facing code without revisiting whether the trust assumption still holds.

Additional Resources