Using PreparedStatement is one of the most effective ways to prevent SQL injection in Java. It works by separating the SQL query structure from the data, ensuring that user input is treated strictly as data and never as part of the executable SQL command.
Here is how you use it:
1. The Key Concept: Placeholders
Instead of concatenating strings (which is where the danger lies), you use a question mark (?) as a placeholder for every dynamic value.
2. Implementation Example
package org.kodejava.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SecureQueryExample {
public void getUserDetails(String username) {
// 1. Define SQL with placeholders (?)
String sql = "SELECT id, email, status FROM users WHERE username = ?";
try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "user", "pass");
// 2. Prepare the statement
PreparedStatement pstmt = conn.prepareStatement(sql)) {
// 3. Bind the values (index starts at 1)
pstmt.setString(1, username);
// 4. Execute the query
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
System.out.println("User ID: " + rs.getInt("id"));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Why this prevents SQL Injection
Imagine a malicious user provides this as a “username”: ' OR '1'='1.
- Vulnerable (String Concatenation):
SELECT * FROM users WHERE username = '' OR '1'='1'— This changes the logic to return all users. - Secure (PreparedStatement):
The database receives the query structure first. When the input is sent, the database looks literally for a user whose name is the string' OR '1'='1. Since no such user exists, the attack fails, and the query remains safe.
Best Practices
- Use
setXXXmethods: Always use the specific setter for your data type (e.g.,setInt(),setString(),setTimestamp()). This adds an extra layer of type validation. - Never Concatenate: Even if you use a
PreparedStatement, if you build the SQL string using+orStringBuilderbefore passing it toprepareStatement(), you are still vulnerable. - Try-with-resources: As shown above, use try-with-resources to ensure the
ConnectionandPreparedStatementare closed automatically, preventing resource leaks.
