Java JDBC PreparedStatement

A PreparedStatement is a SQL template with ? placeholders where the values go. You set the values separately by index, and the driver sends the template and the data on different channels — so a value can never be parsed as SQL. This is the single most important habit in JDBC: it makes injection structurally impossible and lets the database reuse the query plan. Prefer it over Statement for essentially everything.

Creating, binding, executing

String sql = "INSERT INTO users (name, age) VALUES (?, ?)";
try (Connection conn = DriverManager.getConnection(url, user, pw);
     PreparedStatement ps = conn.prepareStatement(sql)) {
  ps.setString(1, name);   // bind by 1-based index
  ps.setInt(2, age);
  int rows = ps.executeUpdate();
}

The placeholders are numbered from 1, not 0 — a constant source of off-by-one mistakes. Each setXxx matches the column's type: setString, setInt, setBigDecimal, setTimestamp, and so on.

Why it defeats injection

With a Statement, the value is part of the SQL text, so a quote in the value can terminate the literal and inject new commands. With a PreparedStatement, the SQL is fixed and parsed before any value is bound; the value is then transmitted as a typed parameter. There is no string for an attacker's quote to break out of — the dangerous value from the previous chapter simply becomes a literal name.

Binding NULL and special types

You cannot pass a Java null to setInt (it takes a primitive), and setString(i, null) is ambiguous for some types. The explicit form is setNull(index, sqlType), naming the column's java.sql.Types:

ps.setNull(3, java.sql.Types.VARCHAR);

Reusing a prepared statement

A prepared statement is built to be executed many times with different values — set, execute, clear, repeat. The database parses and plans the SQL once and reuses it, which is why prepared statements are also faster in a loop than rebuilding a Statement string each time. (Combine this with batching — a later chapter — for bulk inserts.)

A worked example: anatomy of a template and its bindings

This program treats the SQL template as data: it counts the placeholders, walks the values you would bind to them (including the malicious string that broke the Statement), and shows the setNull type code — all the moving parts of parameter binding, without a live database.

What to take from the run:

• The template has three ? placeholders, and the program counts them — that count is exactly how many setXxx calls you must make. A mismatch (binding parameter 4 of a 3-placeholder query) throws at execution.
• Bindings are 1-based: parameter 1 is the first ?. The loop prints bind 1, bind 2, bind 3 to drive that home — the most common beginner bug is starting at 0.
• The first value is the same x'; DROP TABLE users;-- string that hijacked the Statement last chapter. Here it is just data bound to parameter 1; the driver stores it verbatim as a name. The injection is neutralised by construction, not by escaping.
• The null at parameter 3 is why setNull(index, Types.VARCHAR) exists. JDBC needs the SQL type to tell the database which kind of NULL it is — you name it with a java.sql.Types constant.
• Each value carries an implied type — String, int, NULL-of-VARCHAR — which is why there is a setXxx per type rather than one stringly-typed setter. Matching the setter to the column type is the discipline that keeps prepared statements both safe and correct.

Practice