JDBC & Databases

PreparedStatement & SQL Injection

15 min Lesson 4 of 13

PreparedStatement & SQL Injection

In the previous lesson you sent SQL strings built by simple concatenation. That approach works — until someone supplies malicious input. This lesson covers PreparedStatement, the standard JDBC mechanism for parameterized queries, and explains exactly why parameterization eliminates SQL injection rather than merely reducing it.

What SQL Injection Actually Is

SQL injection happens when user-supplied data is interpreted as SQL syntax rather than as a value. Consider a login query built with string concatenation:

// DANGEROUS — never do this
String username = request.getParam("username");
String password = request.getParam("password");

String sql = "SELECT * FROM users WHERE username = '" + username
           + "' AND password = '" + password + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);

If a user supplies the username ' OR '1'='1 and any password, the resulting query becomes:

SELECT * FROM users
WHERE username = '' OR '1'='1'
AND password = 'anything'

The condition '1'='1' is always true, so the query returns every user row — a complete authentication bypass. More destructive payloads can drop tables, exfiltrate data, or write files to disk depending on database permissions.

Never build SQL strings from user input. Even simple-looking inputs like a numeric ID can be injected if you do not control the data source. A disciplined rule — always use parameterized queries — is safer than trying to validate or escape individual inputs.

PreparedStatement: Pre-compilation and Parameters

A PreparedStatement sends the SQL template to the database server first, before any values are known. The server parses and compiles the query plan at that point. Values are then sent separately as typed parameters — they can never alter the query's structure because the structure is already locked.

The placeholder character is a question mark ?. Parameters are bound by position (starting at 1) using typed setter methods:

String sql = "SELECT * FROM users WHERE username = ? AND password = ?";

try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, username);   // parameter index 1
    ps.setString(2, password);   // parameter index 2

    try (ResultSet rs = ps.executeQuery()) {
        if (rs.next()) {
            System.out.println("Authenticated: " + rs.getString("username"));
        } else {
            System.out.println("Invalid credentials");
        }
    }
}

If the attacker again passes ' OR '1'='1 as the username, the database treats that entire string as a literal value to compare against the username column — it is never parsed as SQL syntax. No row matches, so no bypass occurs.

Why this works at the protocol level: Most JDBC drivers implement the binary (wire) protocol for prepared statements. The SQL template and the parameter values travel as separate network packets. The server's SQL parser only ever sees the template — it has already finished parsing before the values arrive.

Setter Methods and Type Safety

PreparedStatement provides setters for every SQL type. Using the correct setter lets the driver perform proper type conversion and ensures column comparisons use the right type semantics:

PreparedStatement ps = conn.prepareStatement(
    "INSERT INTO orders (customer_id, amount, placed_at, active) VALUES (?, ?, ?, ?)"
);

ps.setInt(1, 42);                                  // INT column
ps.setBigDecimal(2, new BigDecimal("199.95"));     // DECIMAL column
ps.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now())); // TIMESTAMP column
ps.setBoolean(4, true);                            // BOOLEAN / TINYINT(1) column

int rowsInserted = ps.executeUpdate();
System.out.println("Inserted: " + rowsInserted);
ps.close();

Common setters: setString, setInt, setLong, setDouble, setBigDecimal, setBoolean, setDate, setTimestamp, setNull. For nullable columns, always use setNull(index, Types.VARCHAR) rather than passing a Java null to setString — the behaviour of passing null is driver-dependent.

Reusing a PreparedStatement

One of the performance benefits of prepared statements is that the same compiled plan can be reused for many executions with different parameters. This is especially useful for bulk operations:

String insertSql = "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)";

List<Product> products = loadProductsFromFile(); // hypothetical

try (PreparedStatement ps = conn.prepareStatement(insertSql)) {
    for (Product p : products) {
        ps.setString(1, p.name());
        ps.setBigDecimal(2, p.price());
        ps.setInt(3, p.stock());
        ps.executeUpdate();   // re-uses the compiled plan each iteration
    }
}

Prepare once, execute many times. Calling prepareStatement has an up-front cost (a round-trip to compile the plan). If you are inserting or updating many rows in a loop, prepare the statement outside the loop and bind new parameters inside it. For very large batches, combine this with batch execution (covered in a later lesson).

Retrieving Auto-Generated Keys

When you insert a row into a table with an auto-increment primary key, you often need the generated ID immediately. Pass the flag Statement.RETURN_GENERATED_KEYS to prepareStatement, then retrieve it via getGeneratedKeys():

String sql = "INSERT INTO articles (title, body) VALUES (?, ?)";

try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    ps.setString(1, "JDBC Deep Dive");
    ps.setString(2, "PreparedStatement prevents SQL injection by...");
    ps.executeUpdate();

    try (ResultSet keys = ps.getGeneratedKeys()) {
        if (keys.next()) {
            long newId = keys.getLong(1);
            System.out.println("New article ID: " + newId);
        }
    }
}

What PreparedStatement Does Not Protect Against

Parameterization protects values — the data that fills placeholders. It does not protect dynamic structural elements such as table names, column names, or ORDER BY directions, because those cannot be sent as parameters. If you must make these dynamic, use an allowlist:

// Allowlist for dynamic ORDER BY — never interpolate raw user input here
private static final Set<String> ALLOWED_COLUMNS =
    Set.of("name", "price", "created_at");

public List<Product> findAll(String orderBy) {
    if (!ALLOWED_COLUMNS.contains(orderBy)) {
        throw new IllegalArgumentException("Invalid sort column: " + orderBy);
    }
    // safe to embed — we control the value
    String sql = "SELECT * FROM products ORDER BY " + orderBy;
    // ...
}

Summary

SQL injection exploits the failure to separate code from data. PreparedStatement enforces that separation at the protocol level: the SQL template is compiled before any user data is sent, making it structurally impossible for a parameter value to alter the query's intent. Use the correct typed setter for each column, prepare outside loops, retrieve generated keys when needed, and guard dynamic structural fragments with allowlists.