PreparedStatement & SQL Injection Prevention

PreparedStatement compiles the SQL once and accepts typed bind parameters at runtime. This eliminates SQL injection, the most common web application vulnerability, and also lets the database reuse the cached query execution plan for faster repeated queries. This article shows every setter type, the SQL injection exploit and its fix, batch inserts, and returning generated keys.

Statement vs PreparedStatement

The fundamental difference is how SQL is sent to the database:

Feature	Statement	PreparedStatement
SQL compilation	Every call	Once at `prepareStatement()`
User input	String concatenation: unsafe	Bind parameters: safe
SQL injection	Vulnerable	Not possible via parameters
Performance (repeated)	Parse overhead each time	Plan cache reused
Batch support	Possible but unusual	First-class `addBatch()`

Default rule: always use PreparedStatement for any SQL that involves application data. Use Statement only for static DDL scripts where no user data is involved.

The SQL Injection Attack

Consider a login query built with string concatenation:

Java

// ❌ DANGEROUS — never do this
String username = request.getParameter("username");  // user input
String sql = "SELECT * FROM users WHERE username = '" + username + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);

If the attacker sends username = ' OR '1'='1, the SQL becomes:

SQL

SELECT * FROM users WHERE username = '' OR '1'='1'
-- Returns ALL rows — authentication bypassed entirely

A more destructive payload like ' ; DROP TABLE users; -- can destroy data. PreparedStatement prevents this entirely: the parameter value is never interpreted as SQL syntax.

Basic PreparedStatement: SELECT with Parameters

Replace concatenated values with ? placeholders and set them via typed setters:

Java

String sql = "SELECT id, name, email FROM users WHERE email = ? AND active = ?";

try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
     PreparedStatement ps = conn.prepareStatement(sql)) {

    ps.setString(1, "alice@example.com");  // 1-based index
    ps.setBoolean(2, true);

    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getLong("id") + " " + rs.getString("name"));
        }
    }
}

PreparedStatement Setter Reference

Use the setter that matches the SQL column type:

Java Type	Setter
`String`	`setString(index, value)`
`int`	`setInt(index, value)`
`long`	`setLong(index, value)`
`double`	`setDouble(index, value)`
`BigDecimal`	`setBigDecimal(index, value)`
`boolean`	`setBoolean(index, value)`
`java.sql.Date`	`setDate(index, value)`
`java.sql.Timestamp`	`setTimestamp(index, value)`
`byte[]`	`setBytes(index, value)`
SQL NULL	`setNull(index, Types.VARCHAR)`
Any object	`setObject(index, value)`: driver infers type

INSERT and Retrieving Generated Keys

Java

String sql = "INSERT INTO users (name, email, created_at) VALUES (?, ?, ?)";

try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
     PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

    ps.setString(1, "Charlie");
    ps.setString(2, "charlie@example.com");
    ps.setTimestamp(3, Timestamp.from(Instant.now()));

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

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

Batch INSERT with PreparedStatement

Sending multiple rows in one round-trip is dramatically faster than individual inserts. Call addBatch() after each row, then executeBatch() once:

Java

List<User> users = loadUsersFromCsv();  // large list

String sql = "INSERT INTO users (name, email) VALUES (?, ?)";

try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
     PreparedStatement ps = conn.prepareStatement(sql)) {

    conn.setAutoCommit(false);  // wrap in a transaction for atomicity

    int batchSize = 500;
    int count = 0;

    for (User u : users) {
        ps.setString(1, u.getName());
        ps.setString(2, u.getEmail());
        ps.addBatch();

        if (++count % batchSize == 0) {
            ps.executeBatch();  // flush every 500 rows
        }
    }
    ps.executeBatch();   // flush remainder
    conn.commit();
    System.out.println("Inserted " + count + " users");

} catch (SQLException ex) {
    conn.rollback();
    throw ex;
}

PostgreSQL reWriteBatchedInserts=true in the JDBC URL converts individual batch entries into a single multi-row INSERT statement, giving another significant speed boost.

Reusing a PreparedStatement Across Multiple Calls

A single PreparedStatement can be re-executed with different parameter values without re-parsing. Call clearParameters() (optional) between uses if you want to reset all parameters first:

Java

String sql = "SELECT * FROM orders WHERE customer_id = ?";

try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
     PreparedStatement ps = conn.prepareStatement(sql)) {

    long[] customerIds = {1L, 42L, 99L};

    for (long customerId : customerIds) {
        ps.setLong(1, customerId);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                System.out.println("Order: " + rs.getLong("id"));
            }
        }
        // ps is reused — SQL is already compiled
    }
}

Statement vs PreparedStatement

The SQL Injection Attack

Basic PreparedStatement: SELECT with Parameters

PreparedStatement Setter Reference

INSERT and Retrieving Generated Keys

Batch INSERT with PreparedStatement

Reusing a PreparedStatement Across Multiple Calls

Related articles