Application Integration & Schema Evolution

SQL Injection and Query Safety

SQL injection occurs when untrusted data changes SQL structure. Bind every value, allowlist the few dynamic structural choices, minimize database authority, and treat stored data as untrusted again whenever it later enters a command.

Binding is the primary value defense.
Identifiers need structural validation.
Sort grammar must be closed.
Variable lists need generated placeholders.
Second-order injection crosses time.
Least privilege limits blast radius.

Safe construction by input kind
Input	Unsafe	Safe
Value	`... email = '` + input	`email = ?` plus bind
Column	`ORDER BY` + column	token → constant fragment map
Direction	append request text	closed enum → `ASC` or `DESC`
IN list	join raw strings	N placeholders / array / staging table
Stored text	concatenate value read from DB	bind again at the new SQL boundary

Values, list, identifier, and direction kept separate

List<Long> ids = requireAtMost(requestedIds, 100);
if (ids.isEmpty()) return List.of();
String sort = switch (request.sort()) {
  case NEWEST -> "o.placed_at DESC, o.order_id DESC";
  case OLDEST -> "o.placed_at ASC, o.order_id ASC";
};
String marks = String.join(",", Collections.nCopies(ids.size(), "?"));
String sql = "SELECT o.order_id, o.status FROM orders o "
           + "WHERE o.customer_id = ? AND o.order_id IN (" + marks + ") "
           + "ORDER BY " + sort;
// Bind customerId, then each validated Long. No request text enters SQL.

Second-order risk

1. A customer name is safely bound and stored as:  Acme"; DROP ...
2. A later export job reads that name.
3. The job concatenates it into a dynamically generated SQL statement.
4. Stored data becomes syntax at the second interpreter.

Fix step 3 with binding/structural APIs; “it came from our database” is not trust.