JAVA / PLATFORM, BUILDS AND TESTING
JDBC, prepared statements and injection safety
Replace concatenated SQL with parameterized PreparedStatements, explain why binding stops injection, and cover what placeholders cannot protect.
What you will learn
- Bind every user-supplied value with setString or setInt instead of concatenating it
- Explain injection as the parser losing track of where your literal ends
- Generate one ? per IN element and pick identifiers from a fixed allow-list
- Reuse one PreparedStatement across executions instead of creating one per row
Understanding JDBC, prepared statements and injection safety
A Statement hands the database one finished string, and the server parses that whole string as SQL before it has any idea which parts you thought were data. If you build the string by concatenation, the closing quote that ends your value comes from the user's text rather than from your code, so the user decides where the literal stops and where the grammar resumes. That is all SQL injection is: a parsing boundary you accidentally handed over, not a list of dangerous characters.
prepareStatement splits the work in two. The SQL text, with a ? wherever a value belongs, is parsed and planned first, at a moment when your values do not exist yet; then setString, setInt and friends deliver values into fixed slots of that finished plan. Because the parse already happened, a value containing a quote, a -- comment or the words DROP TABLE cannot turn into grammar, it can only be the contents of its slot. The setters also carry type information, so setInt(1, 42) sends an integer rather than text that would need quoting at all.
The same property explains the limit: a placeholder can stand for a value, never for a table name, a column name, a keyword, or the number of elements in an IN list, because those decide the plan's shape and the shape is fixed before values arrive. When the structure genuinely has to vary, choose it in Java from a hard-coded allow-list, and build IN (?, ?, ?) by generating one marker per element and binding each one. Hand-rolled escaping is the wrong direction: you would be reimplementing one dialect's literal rules, and it protects nothing in numeric contexts where there are no quotes to escape.
import java.sql.*;
public class LoginLookup {
public static void main(String[] args) throws SQLException {
String attack = "' OR '1'='1";
try (Connection c = DriverManager.getConnection("jdbc:h2:mem:demo", "sa", "")) {
try (Statement s = c.createStatement()) {
s.execute("CREATE TABLE account (id INT PRIMARY KEY, username VARCHAR(40), secret VARCHAR(40))");
s.execute("INSERT INTO account VALUES (1, 'alice', 'hunter2'), (2, 'bob', 'letmein'), (3, 'carol', 'qwerty')");
}
String glued = "SELECT id, username FROM account WHERE username = 'alice' AND secret = '"
+ attack + "' ORDER BY id";
System.out.println("server parses: " + glued);
try (Statement s = c.createStatement(); ResultSet rs = s.executeQuery(glued)) {
while (rs.next()) {
System.out.println(" leaked row: " + rs.getInt("id") + " " + rs.getString("username"));
}
}
String bound = "SELECT id, username FROM account WHERE username = ? AND secret = ?";
try (PreparedStatement ps = c.prepareStatement(bound)) {
ps.setString(1, "alice");
ps.setString(2, attack);
try (ResultSet rs = ps.executeQuery()) {
System.out.println("bound attack rows: " + (rs.next() ? 1 : 0));
}
ps.setString(2, "hunter2");
try (ResultSet rs = ps.executeQuery()) {
System.out.println("bound real login: " + (rs.next() ? rs.getString("username") : "denied"));
}
}
}
}
}A prepared statement is parsed before your values exist, so a bound value can only ever be data, never grammar.
Worked examples
A payload survives as plain text
Shows that a bound value carrying SQL syntax is stored character for character instead of being executed or mangled.
import java.sql.*;
public class BoundPayload {
public static void main(String[] args) throws SQLException {
String payload = "x'); DROP TABLE note; --";
try (Connection c = DriverManager.getConnection("jdbc:h2:mem:notes", "sa", "")) {
try (Statement s = c.createStatement()) {
s.execute("CREATE TABLE note (body VARCHAR(80))");
}
try (PreparedStatement ps = c.prepareStatement("INSERT INTO note (body) VALUES (?)")) {
ps.setString(1, payload);
System.out.println("rows inserted: " + ps.executeUpdate());
}
try (Statement s = c.createStatement();
ResultSet rs = s.executeQuery("SELECT body, LENGTH(body) FROM note")) {
rs.next();
System.out.println("stored: " + rs.getString(1));
System.out.println("length: " + rs.getInt(2));
System.out.println("identical: " + payload.equals(rs.getString(1)));
}
}
}
}Example explained
Line 1setString(1, payload) delivers the text into the value slot, so the parser never inspects it as SQL.
Line 2executeUpdate returns 1, and the note table is still there afterwards, so the DROP TABLE fragment was never a statement.
Line 3LENGTH(body) is 24, the exact character count of the payload, proving nothing was stripped or escaped away.
Line 4The plain Statement in the second block is acceptable only because its SQL contains no external input.
LIKE wildcards are not injection
Shows that binding keeps a value out of the grammar but percent and underscore still act as wildcards inside a pattern.
import java.sql.*;
public class LikeSearch {
public static void main(String[] args) throws SQLException {
String typed = "%";
try (Connection c = DriverManager.getConnection("jdbc:h2:mem:search", "sa", "")) {
try (Statement s = c.createStatement()) {
s.execute("CREATE TABLE person (name VARCHAR(20))");
s.execute("INSERT INTO person VALUES ('ann'), ('bob'), ('a%b')");
}
try (PreparedStatement ps = c.prepareStatement(
"SELECT COUNT(*) FROM person WHERE name LIKE ?")) {
ps.setString(1, "%" + typed + "%");
try (ResultSet rs = ps.executeQuery()) {
rs.next();
System.out.println("naive pattern matched: " + rs.getInt(1));
}
}
String literal = typed.replace("!", "!!").replace("%", "!%").replace("_", "!_");
try (PreparedStatement ps = c.prepareStatement(
"SELECT COUNT(*) FROM person WHERE name LIKE ? ESCAPE '!'")) {
ps.setString(1, "%" + literal + "%");
try (ResultSet rs = ps.executeQuery()) {
rs.next();
System.out.println("escaped pattern matched: " + rs.getInt(1));
}
}
}
}
}Example explained
Line 1The first bound value is the pattern %%% , which is a legitimate pattern matching every row, so all three come back.
Line 2That is not an injection: the statement's shape never changed, only the meaning of the value inside LIKE.
Line 3ESCAPE '!' declares an escape character, and the replace chain turns the user's % into !% so it matches a literal percent sign.
Line 4Only 'a%b' contains a literal percent sign, hence the count of 1.
Important notes
The examples use H2's in-memory URL because it needs no server; since JDBC 4.0 the driver registers itself from the classpath, so no Class.forName call is needed, and the database disappears when the last connection closes.
Binding stops injection, not meaning: a correctly bound id can still belong to another user, so the authorization check remains your code's job.
Common mistakes
Stripping or doubling quotes while still concatenating: a numeric slot such as WHERE id = has no quotes at all, so 5 OR 1=1 passes the filter untouched and returns the whole table.
Parameterizing the values but pasting one fragment in, typically the ORDER BY column or the table name, which leaves the statement injectable through exactly that fragment.
Calling prepareStatement inside a per-row loop and never closing it, so every row re-parses the SQL and leaks a database-side statement handle until the connection fails.
Try it yourself
Change, predict, then run
Create an in-memory table with three accounts, then write one PreparedStatement that selects by username and secret and execute it twice, once with a real pair and once with ' OR '1'='1 as the secret. Print the row count each time and confirm you get 1 then 0.
Open the Java workspaceCheck your understanding
A query is built as "SELECT * FROM account WHERE id = " + input, where input is expected to be a number, and the code rejects any input containing a single quote. Why is it still injectable?
- Rejecting single quotes should be replaced by doubling them, which is what a driver does internally
- A Statement bypasses the server-side parser, so any input reaches the execution plan directly
- The value lands in an unquoted numeric slot, so a payload like 1 OR 1=1 never needs a quote
- String concatenation in Java loses the character encoding that the server expects
Show answer
In id = <input> there is no string delimiter to break out of, so 1 OR 1=1 is already valid SQL and the quote filter finds nothing to reject while the WHERE clause changes shape. Option 0 is tempting because doubling quotes is the real rule for string literals, but escaping only ever protects a delimited context and protects nothing where the value is not delimited; binding the value with setInt is what actually fixes it, because then the plan is parsed before the value exists.