PreparedStatement

A PreparedStatement is a JDBC statement whose SQL text contains ? placeholders, with the values supplied separately through typed setters such as setString, setLong or setObject. The SQL structure is fixed when the statement is created; only the parameter values change between executions. That separation gives three benefits at once: values can never be interpreted as SQL, so injection through values is impossible; types are converted correctly, for example a BigDecimal to numeric or an OffsetDateTime to timestamptz; and the same statement can be executed repeatedly, letting the driver and server skip parsing and reuse plans. Spring's JdbcClient and JPA named parameters build on exactly this mechanism.

Because the SQL text and the values travel separately and are never combined into one string. With PostgreSQL's extended protocol, the driver sends the statement with $1, $2 placeholders in a Parse message, and the server fixes the statement's structure at that point. The values arrive later in a Bind message and are treated strictly as typed data. So an input like x' OR '1'='1 is simply compared with the email column as a literal string, quotes included, and matches nothing. Concatenation fails because the input becomes part of the code the parser reads. Note the limits: binding protects values only, not identifiers, ORDER BY columns or SQL fragments built from input.

No. Placeholders can stand only for values, because the server must know the full structure of the statement, including which tables and columns it touches, at parse and planning time, before any values arrive. Writing ORDER BY ? binds a constant string, so the query sorts by a literal and the ordering is silently ignored; FROM ? is a syntax error. When the user must choose a sort column or a table, map their input onto a fixed whitelist, ideally an enum whose constants carry the SQL fragment, and reject anything else. Never escape and concatenate the raw input. Query builders such as jOOQ give the same safety through generated, compile-time-checked column references.

Bind the whole list as one array parameter and write WHERE product_id = ANY(?), passing a Long[] through setArray or, in Spring JdbcClient, param with an array. The SQL text is then identical whatever the list size. Generating IN (?, ?, ?) with a different number of placeholders creates a distinct statement for each list length, so a lookup with 1 to 500 ids becomes 500 statements that pollute the driver's statement cache and the server's plan cache and force re-parsing. Very long IN lists can also hit limits; PostgreSQL allows 65,535 bind parameters per statement. For tens of thousands of ids, consider a join against a temporary table or unnest of an array.

Reuse lets both the driver and the server skip repeated work. PgJDBC switches to a named server-side prepared statement after prepareThreshold executions, five by default, so later executions send only Bind and Execute and the server skips parsing and analysis. After several executions PostgreSQL may also switch to a cached generic plan, skipping planning, which matters for complex joins where planning can be a large share of latency. The driver's per-connection statement cache, 256 queries by default, means the same SQL text on a pooled connection reuses that statement. Add batching, which sends many parameter sets per round trip, and you remove most per-statement overhead. Concatenated SQL gets none of this.

It is a PgJDBC connection property that makes the driver rewrite a JDBC batch of single-row INSERT statements into multi-row INSERT ... VALUES (...), (...), (...) statements before sending them. Without it, the driver still sends the batch efficiently, but PostgreSQL executes each row as a separate statement with its own bind and execute overhead. With it, the server inserts many rows per statement, which in this note's example cut 10,000 rows from 0.9 seconds to 0.35 seconds. It only affects INSERTs; updates and deletes are unchanged. A side effect is that executeBatch returns SUCCESS_NO_INFO instead of a per-row count, so code must not rely on individual update counts.

Server-side prepared statements belong to one physical server connection. In PgBouncer transaction pooling, each transaction may run on a different server connection, so a statement that PgJDBC prepared as S_1 on one backend does not exist on the next, and the application gets errors such as prepared statement "S_1" does not exist, or already exists. The old workaround was prepareThreshold=0, which disables server-side preparation and loses its parse and plan savings. Since PgBouncer 1.21, setting max_prepared_statements lets the pooler track protocol-level prepared statements and re-prepare them transparently on whichever server connection it uses, so you get transaction pooling and preparation together.

I make the safe path the only path and enforce it with tooling, not good intentions. The engineering standard says all SQL, JPQL and native queries use bound parameters; static analysis, such as SpotBugs or Sonar SQL-injection rules and Semgrep, fails the build on concatenated or String.format-built SQL. The platform team provides shared building blocks for what binding cannot cover: enum whitelists for sorting and identifiers, array parameters for lists, a LIKE-escaping utility, and query builders like jOOQ or the Criteria API for dynamic search. Code review checklists cover the remaining cases. Finally, defence in depth: each service connects with a least-privilege role, so a missed bug cannot read or drop other data.