CWE-89: SQL Injection - Java

Overview

SQL Injection occurs when an application incorporates untrusted data into a SQL query without proper validation or parameterization. Attackers can manipulate queries to bypass authentication, access unauthorized data, modify or delete records, or execute 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(); // SAFE

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); // SAFE
List<User> users = query.getResultList();

Why this works:

JPA named parameters (:username) are placeholders that get replaced with parameterized values by the persistence provider.
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)); // SAFE
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> {
    // Method name query - SAFE
    User findByUsername(String username);

    // @Query with named parameters - SAFE
    @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

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

// Java usage - SAFE
User user = sqlSession.selectOne("getUserById", userId);

Why this works:

MyBatis uses #{userId} as a parameterized placeholder in XML mappers.
The framework automatically creates a PreparedStatement and binds the userId parameter, preventing SQL injection through automatic parameterization.

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

Recommended: Spring Data JPA (Simplest)

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

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

// Or with @Query annotation - SAFE
@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 (@p0, @p1, etc.), maintaining injection protection for multiple values.

Recommended: JPA/Hibernate with Named Parameters (Simple)

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

// JPA automatically expands collections - SAFE
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 creates individual parameterized values for each element, generating safe SQL with proper parameter binding.

JDBC PreparedStatement (More Manual)

// 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(); // SAFE - 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 Strategy

Identify all SQL construction: Search for Statement, createQuery, string concatenation with SQL keywords
Replace with PreparedStatement: Change Statement to PreparedStatement
Parameterize inputs: Replace concatenated values with ? placeholders
Set parameters: Use setString(), setInt(), etc. for each placeholder
Test thoroughly: 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);

Remediation Steps

Locate the Finding

Review the data flow in the security scan report:

Source: Where user input enters (request.getParameter(), HttpServletRequest, @PathVariable, @RequestParam, @RequestBody)
Sink: The SQL execution call (executeQuery(), executeUpdate(), createQuery(), createNativeQuery(), selectOne())
Note any frames between source and sink

Understand the Data Flow

Trace how data moves from source to sink:

Identify if any validation or sanitization exists
Look for string concatenation or String.format() in SQL queries
Check if PreparedStatement or parameterized queries are used
Verify if JPA/Hibernate query methods are used with parameters

Identify the Pattern

Match the code to vulnerable patterns:

String concatenation → Pattern: "SELECT * FROM users WHERE id = " + userId
String.format() → Pattern: String.format("SELECT * FROM users WHERE name = '%s'", name)
Statement instead of PreparedStatement → Pattern: Statement stmt = connection.createStatement()
MyBatis ${} substitution → Pattern: SELECT * FROM users WHERE email = '${email}'

Apply the Fix

Choose the appropriate remediation approach based on your architecture:

Option 1: Use Spring Data JPA repository methods (Recommended - automatic parameterization)

Define repository interfaces extending JpaRepository
Use method name queries like findByEmail(), findByUsername()
Use @Query annotation with named parameters (:paramName)
Spring Data automatically generates parameterized queries from method signatures

Option 2: Use JPQL or Criteria API with parameters (Type-safe JPA approach)

Use named parameters with :parameterName syntax in JPQL
Call setParameter() to bind values safely
Use Criteria API for compile-time type safety
Avoid string concatenation in JPQL/HQL queries

Option 3: Use JDBC PreparedStatement (Direct JDBC access)

Replace Statement with PreparedStatement
Use ? placeholders for all user-controlled values
Call setString(), setInt(), etc. for each placeholder
Avoid concatenating user input into SQL strings

Option 4: For dynamic table/column names, use allowlist validation (Special cases)

Parameterized queries cannot be used for identifiers (table/column names)
Create allowlist of valid table/column names
Validate user input against allowlist before using in query
Consider redesigning to avoid user-controlled identifiers

See the Secure Patterns section for detailed implementation examples of each approach.

CWE-89: SQL Injection - Java

Overview

Common Vulnerable Patterns

String Concatenation

String.format()

Secure Patterns

PreparedStatement (JDBC)

Named Parameters (JPA/Hibernate)

Criteria API (Type-Safe)

Framework-Specific Guidance

Spring Data JPA

MyBatis

Data Type Handling

Numeric Parameters

IN Clauses (Multiple Values)

Migration Strategy

Database Permission Hardening (Defense in Depth)

MySQL/MariaDB Permission Examples

Remediation Steps

Locate the Finding

Understand the Data Flow

Identify the Pattern

Apply the Fix

Additional Resources