What ways does JPA give you to bind values into a JPQL query, and why should a value never be concatenated into the query string?
answer
- :name and ?1 — both via setParameter
- Name passed without the colon
- Concatenation = injection + plan churn
- Wildcards belong in the bound value
- Structure (sort column) needs a whitelist
basics
~10 sNamed parameters (:name) and positional ones (?1), both bound with setParameter. Concatenating values instead allows injection through the entity model, defeats JDBC prepared-statement reuse, and creates a new query plan per distinct value.
solid answer
~50 sJPQL supports **named parameters** — `where b.title = :title`, bound with `setParameter("title", value)` — and **positional parameters** — `?1`, `?2`, bound by index (1-based). Named is the strong default: order-independent, self-documenting, and reusable if the same value appears twice. Why never concatenate: 1. **Injection.** JPQL is parsed by the provider, but attacker-controlled text can still alter the query's logic (`' or 1=1 --` style), leaking or destroying data through the entity model. Native queries make it a straight SQL injection. 2. **Plan and statement reuse.** Bound parameters produce one JPQL parse tree and one prepared statement per query shape; concatenation produces a distinct statement per value, blowing the query-plan cache on both provider and database side. 3. **Type handling.** `setParameter` runs the value through the mapped type, handling enums, dates, UUIDs and null correctly instead of relying on string formatting. Some things cannot be parameters at all — entity names, attribute paths, sort direction. Those must be validated against a whitelist, never interpolated blindly.
code
java · 12 linesvar byAuthor = em.createQuery(
"select b from Book b where b.author.name = :name and b.status in :statuses",
Book.class)
.setParameter("name", name)
.setParameter("statuses", List.of(Status.ACTIVE, Status.DRAFT))
.getResultList();
var byRange = em.createQuery(
"select b from Book b where b.price between ?1 and ?2", Book.class)
.setParameter(1, low)
.setParameter(2, high)
.getResultList();go deeper
Recall the two syntaxes, that setParameter binds values, and that concatenating user input is unsafe.
Explain plan and statement reuse, collection parameters for IN, wildcard handling and type conversion, and that structure cannot be bound.
Cover injection through the entity model, plan-cache churn from IN-list expansion, and whitelisting for dynamic sorting or filtering.
Set the policy: constant query text, bound values, metamodel or whitelist for structure, and concatenation near createQuery treated as a review-blocking finding.
## The two parameter styles **Named parameters** are the JPA-idiomatic form: ```java TypedQuery<Book> q = em.createQuery( "select b from Book b where b.author.name = :author and b.price <= :max", Book.class); q.setParameter("author", author); q.setParameter("max", max); ``` The colon prefix appears in the query text; `setParameter` takes the name **without** the colon. A name may appear several times in one query and is bound once. **Positional parameters** use `?1`, `?2`, numbered from 1: `where b.price between ?1 and ?2`, bound with `setParameter(1, low)`. They are legal but fragile — inserting a condition renumbers everything after it. Note that JPQL's positional parameters are *numbered*, unlike JDBC's bare `?`; Hibernate's legacy bare-`?` HQL form was removed in Hibernate 6. ## Special cases - **Collections for IN**: `where b.status in :statuses` with a `List` or `Set` bound to `statuses`. Hibernate expands it into the right number of placeholders; because the placeholder count varies, this is a known source of plan-cache churn, which newer Hibernate versions mitigate by padding the list to power-of-two sizes (`hibernate.query.in_clause_parameter_padding`). - **LIKE wildcards** are part of the *value*, not the syntax: bind `"%" + term + "%"`, and remember to escape `%` and `_` inside user input if a literal match is intended. - **Entities as parameters**: `where b.author = :author` accepts the entity instance; the provider compares identifiers. - **Enums** bind directly; the mapped `EnumType` decides ordinal or string. - **Temporal types**: modern `java.time` types bind directly. Legacy `java.util.Date`/`Calendar` needed a `TemporalType` argument, which is why old code carries `setParameter("d", date, TemporalType.TIMESTAMP)`. - **Nulls**: `setParameter("x", null)` produces `= null`, which is never true. Use `is null` explicitly, or build the predicate conditionally. ## Why concatenation is a defect **Security.** The reflex "JPQL isn't SQL, so injection doesn't apply" is wrong. If user input reaches the query text, an attacker can close a literal and append conditions in JPQL itself — `' or 1=1 or ''='` widens a result set; a crafted subquery can exfiltrate other entities' data through a query the application then serialises. And any JPQL injection becomes an SQL injection the moment the query is native or the concatenation happens in a native query. **Performance.** Providers cache the parsed form of query strings, and the database caches execution plans per statement text. One shape with bound values means one parse and one plan; a thousand concatenated values mean a thousand of each, evicting everything useful and burning CPU on parsing. On some databases this is the single biggest cause of library-cache contention. **Correctness.** String formatting has to reproduce the database's literal syntax for dates, decimals, booleans, quoting and escaping — under the current locale. `setParameter` delegates to the mapped type and the JDBC driver instead, which is both simpler and right. ## What cannot be parameterised Parameters replace *values*, not *structure*. You cannot bind an entity name, an attribute path, a join, `ASC`/`DESC`, or the column in an `ORDER BY`. Dynamic sorting and dynamic filters therefore need one of: - a strict whitelist mapping an external token to a fixed, code-owned fragment; - the Criteria API, which builds structure from typed objects instead of strings; - or, for sorting, framework-level abstractions that validate the property against the metamodel. A whitelist is not optional decoration — it is the control that replaces binding for structural elements. ## Practical habits - Prefer named parameters everywhere; keep positional ones for very short queries or generated code. - Build the query string from constants only. If a fragment is conditional, append a *constant* fragment and bind its value. - Never interpolate even "safe-looking" values such as numeric ids parsed from a request — the parse can be bypassed by a refactor, and the plan-cache argument applies regardless. - Review any string concatenation near `createQuery` as a security finding by default.
- How do you support a user-selectable sort column if it cannot be a query parameter?Map the external token to a fixed fragment your code owns — a switch or an immutable map from "price" to "b.price" — and reject anything unmatched. Alternatively build the query with the Criteria API, where the ordering is expressed as a metamodel attribute rather than text. Interpolating the raw token into the query string is an injection vector even when it looks harmless.
- Why can binding a collection to an IN clause hurt the database's plan cache, and what mitigates it?The provider expands the collection into one placeholder per element, so a list of 3 and a list of 4 produce different statement texts and therefore different cached plans. Hibernate can pad the list to the next power of two so that many sizes share a statement shape, which collapses dozens of variants into a handful.
saying these in an interview costs you the question
- Claiming JPQL cannot be injected because it is not SQL
- Interpolating 'safe' values such as parsed numeric ids into the query string
- Passing the colon along with the name to setParameter
- Putting LIKE wildcards in the query text instead of the bound value
- Trying to bind a sort column or entity name as a parameter