Introducing SafeSql – Enforced Injection Safety – ██FR█████ █INTELL███████████

This content originally appeared on DEV Community and was authored by Dash One

SQL Injection

In Java, JDBC PreparedStatement is the vital defense against SQL Injection (SQLi).

However when developers need to build dynamic SQL (imagine if you need to parameterize by table name, column names, or have complex subqueries to reuse), PreparedStatement‘s parameterization support becomes insufficient and they usually have to fall back to raw string concatenation, opening the door to injection risks.

NotPetya (2017) – Up to $10 Billion: This ransomware attack, which exploited initial vulnerabilities that could include web application flaws, caused unprecedented global disruption and staggering financial losses for giants like Maersk and FedEx.
Equifax (2017) – Over $1.4 Billion: A failure to patch a known web application vulnerability exposed sensitive data for 147 million people, leading to massive settlements, fines, and enduring reputational damage.

When large, complex systems rely on programmer caution and code reviews for dynamic SQL string concatenation, the risk is a timed bomb. Human errors, vast codebase, developer turnover, and rushed reviews make it impossible to manually prevent every subtle SQLi vulnerability.

Humans make mistake, they always do.

Existing Mitigations

Hardening the SQL String

Google internally uses the TrustedSqlString class. By taking advantage of ErrorProne’s compile-time check, it ensures that only trusted sql snippets (compile-time string constants, flag values etc.) are allowed to be concatenated.

The Spanner library builds on top of TrustedSqlString, by supporting bind("id", id) calls for named parameters like @id.

It works, but when you have complex subqueries to be composed together, each with their own parameters, you’ll have to manage and merge the parameters yourself, making sure the parameter names don’t clash etc.

This can still result in fragmented SQL and complex parameter managing code.

SQL Template With Escape Hatch

MyBatis offers the #{} syntax for filling in template parameters and passing them as PreparedStatement parameters.

For dynamic SQL (table names etc.), the ${} syntax is used to allow developers to fill in arbitrary string, an escape hatch and a “be careful, you are on your own” warning.

The ${} escape hatch is not only a door to SQLi, it can also be confusing to developers about which is what, adding human errors to the attackers’ ammunition.

Java DSL

Another mitigation is to switch to what I call “DSL-first” approach (as opposed to “SQL-first”, where you write real SQL.

So instead of select id, name from users where name like '%foo%', you write:

select(ID, NAME).from(USERS).where(NAME.like("%" + name + "%"))

The upside is that the DSL won’t accept arbitrary concatenated string. And because the DSL result is a Java object, you can compose them, with both the subquery SQL and its parameters.

The downsides however are also pretty glaring:

You write in Java DSL, NOT SQL. You can’t easily copy and paste between your Java source code and your DB console to debug and test your SQL.
You can’t easily parameterize by table name or column names, unless you make an escape hatch, which then leaves the door to injection widely open.
While IDE auto-completion feels nice for Java devs, when the SQL grows long and complex, it’s more mentally taxing to have to first translate the DSL into SQL in your head before you can reason about it (it’s leaky abstraction).
Quirks like operator precedence divergence (a.or(b).and(c) reads like a OR b AND c but is different) can be confusing and dangerous-they result in silent behavior bug and may cause data corruption.

`SafeSql`

Before we dive into details, here’s an intuitive example:

SafeSql sql = SafeSql.of(
    "select id, name from users where name like '%{name}%'");
List<User> users = sql.query(dataSource, User.class);

How Does `SafeSql` Prevent SQLi?

SafeSql is heavily influenced by Google’s TrustedSqlString technique: it uses ErrorProne to ensure only compile-time constants can be used as the SQL template.

But it takes one step further by encapsulating the parameters with the sql template as a higher-level SafeSql object that can be passed around and composed, kinda like JEP 459‘s SQL processor.

SafeSql then enforces that all value parameters are passed through PreparedStatement‘s parameterization.

The only types of parameters that are directly embedded in the SQL string are:

Another SafeSql object, which is already validated to be safe from inection.
backtick-quoted or double-quoted placeholders like "{user_table}" are validated to only include characters that can be safely used as an identifier.

There is simply no way for an unsuspecting developer to accidentally inject malicious code. If SafeSql compiles and runs, it’s provably safe from SQLi.

Next let’s look at a few reasl world examples

Dynamic `IN` Clauses with Collections

When using an IN clause, most frameworks require you to manually construct the right number of placeholders, which can be cumbersome and error-prone.

Example (MyBatis XML):

<!-- Using MyBatis <foreach> to handle dynamic IN lists -->
<select id="findByDepartmentIds" resultType="User">
  SELECT * FROM Sales
  WHERE department_id IN
  <foreach item="id" collection="departmentIds" open="(" separator="," close=")">
    #{id}
  </foreach>
</select>

// Called with:
sqlSession.selectList("findByDepartmentIds", Map.of("departmentIds", List.of(1, 2, 3)));

It’s safe from SQL injection (if you correctly used #{}, not ${}). But it requires the special <foreach> XML syntax, which adds visual clutter and complexity compared to writing plain SQL.

With SafeSql:

List<Integer> departmentIds = List.of(1, 2, 3);
SafeSql.of(
    "SELECT * FROM Sales WHERE department_id IN ({department_ids})",
    departmentIds);

SafeSql automatically expands the list parameter {department_ids} to the correct number of placeholders and binds the values, eliminating manual string work and preventing common mistakes.

Conditional Query Fragments

Adding optional filters or groupings usually means string concatenation or awkward branching in your code.

Example (with manual concatenation):

String sql = "SELECT * FROM Sales";
if (groupByDay) {
    sql += " GROUP BY department_id, date";
} else {
    sql += " GROUP BY department_id";
}

Manually stitching together query fragments can quickly become hard to read and maintain, and makes errors or injection bugs more likely.

With SafeSql:

SafeSql.of(
    """
    SELECT department_id {group_by_day -> , date}, COUNT(*) AS cnt
    FROM Sales
    WHERE department_id IN ({department_ids})
    GROUP BY department_id {group_by_day -> , date},
    """,
    groupByDay, departmentIds, groupByDay);

The -> guard in {group_by_day -> , date} tells SafeSql to conditionally include , date wherever needed based on the value of groupByDay. This keeps dynamic queries clean and makes logic explicit at the SQL level, not hidden in string operations.

Injecting Identifiers like Column or Table Names

Most frameworks only allow safe parameterization for values, because the JDBC PreparedStatement only supports value parameterization. When it comes to parameterizing SQL identifiers, such as table names or column names, developers are forced to fall back to direct string concatenation.

Example (in MyBatis XML):

<select id="findMetrics" resultType="Map">
  SELECT department_id, date, ${metricColumns}
  FROM Sales
</select>

// When called as:
sqlSession.selectList("findMetrics", Map.of("metricColumns", "price, currency, discount"));
// Generates:
SELECT department_id, date, price, currency, discount FROM Sales

In MyBatis, #{} is for safe value parameterization—these become JDBC parameters and are properly escaped.
${} is direct string substitution—raw and unescaped.
Using ${metricColumns} means whatever string is supplied is injected as-is into the SQL.
- This creates a serious SQL injection risk if any part of the input comes from user data.
The similarity between #{} (JDBC parameterized) and ${} (please inject me!) is a frequent source of confusion and mistakes.

With SafeSql:

Simply backtick-quote (or double-quote, whichever your DB uses to quote identifiers) the placeholder and SafeSql will recognize the intention to use it as an identifier:

List<String> metricColumns = List.of("price", "currency", "discount");
SafeSql.of("SELECT department_id, date, `{metric_columns}` FROM Sales", metricColumns);

SafeSql expands {metric_columns} into a comma-separated, properly quoted, and validated list of identifiers. Unsafe or invalid names are rejected, which removes a whole class of injection risks and makes the query easy to audit.

Proper Escaping for LIKE Patterns

When user input is used in LIKE queries, forgetting to escape % or _ can cause accidental matches or security issues.

Example (with manual escaping):

String term = userInput.replace("%", "\%").replace("_", "\_");
String sql = "SELECT * FROM users WHERE name LIKE ?";
jdbcTemplate.query(sql, term);

Manual escaping is repetitive and easy to forget, leading to unpredictable results or vulnerabilities.

With SafeSql:

SafeSql.of("SELECT * FROM users WHERE name LIKE '%{name}%'", userName);

SafeSql escapes any special characters in parameters used within LIKE expressions automatically. You don’t have to think about escaping rules or risk mistakes—user input is always treated literally.

Query Composition and Parameter Isolation

Composing queries from reusable fragments often leads to parameter name clashes or incorrect bindings, especially as code grows.

With manual tracking, you have to carefully manage names and argument lists, often by inventing your own conventions, which makes code hard to read and can break during refactorings.

It’s easy to accidentally overwrite or mismatch parameters, leading to subtle bugs or unexpected results.

With SafeSql:

SafeSql computation = SafeSql.of("CASE ... END as computed, {from_time}", fromTime);
SafeSql whereClause = ...;
SafeSql.of(
    """
    SELECT department_id, {computation}
    FROM Sales
    WHERE {where_clause}
    """,
    computation, whereClause);

Each fragment’s parameters are managed in isolation by SafeSql, so you can safely compose and reuse query parts without worrying about accidental overwrites or binding mistakes. They are guaranteed to be eventually sent through PreparedStatement in the correct order.

Parameter Name and Order Checking

In traditional frameworks, parameter order or name mismatches can slip by the compiler and only fail at runtime—or worse, silently produce incorrect results.

Example (with JDBC):

String sql = "SELECT * FROM users WHERE id = ? AND name = ?";
preparedStatement.setInt(1, userName); // mistake: wrong order
preparedStatement.setString(2, userId);

Example (with Spring NamedParameterJdbcTemplate):

new NamedParameterJdbcTemplate(dataSource)
    .queryForList(
        "SELECT * FROM users WHERE id = :id AND name = :name",
        Map.of("nmae", userName, "id", userId));  // typo!

This kind of error won’t always be caught during development, and can be difficult to debug.

With SafeSql:
SafeSql integrates with ErrorProne to check at compile time that the template placeholders and the template arguments match. If you get the order or number of arguments wrong, you’ll get a clear compilation error.

For example, the following code will fail to compile because the order of the two arguments is wrong:

SafeSql.of(
    """
    select id, name, age from users
    where id = {id} OR name LIKE '%{name}%'
    """,
    userName, userId);

Such compile-time check is very useful when your SQL is large and you need to change or move a snippet around, which can cause the placeholders no longer matching the template arguments.

Sometimes you may run into a placeholder name that doesn’t match the corresponding argument expression. If renaming the placeholder isn’t an option, consider using an explicit comment like the following:

SafeSql.of(
    """
    select id, name, age
    from users
    where id = {user_id} OR name LIKE '%{user_name}%'
    """,
    /* user_id */ userRequest.getUuid(), userRequest.getUserName());

The explicit /* user_id */ confirms your intention to both SafeSql and to your readers that you do mean to use the uuid as the user_id.

Type Safety

SafeSql doesn’t know your database schema so cannot check for column types or column name typos.

But typos and superficial type mismatch (like using int for the name column) are the easy kind of errors:

If you test your SQL (and you should), the DB will instantly catch these errors for you.
- In fact, it’s best if you’ve copied the SQL from the DB console where the correctness is already verified.
- And you should have automated tests to ensure the SQL not only type checks, but gives you the right result.
Most db types are numbers and strings. So the type checking can only provide marginal protection anyways.
False sense of security is dangerous. If you ever wish to skip testing your SQL because it “type checks”, then it’s a liability.

The main safety advantages of SafeSql are in its zero-backdoor SQL injection prevention, compile-time semantic check such that you can’t pass user.name() in the place of {ssn} despite both being string, as well as automatic parameter wiring, and convenient dynamic query construction—areas where mistakes are easy to make and hard to find otherwise.

Summary

In a nutshell, use SafeSql if any of the following applies to you:

You are a large enterprise. Relying on developer vigilance to avoid SQL injection isn’t an option. Instead, you need systematically enforced safety.
You prefer to write actual SQL, and appreciate the ability to directly copy-paste queries between your code and the database console for easy debugging and testing.
A low learning curve and a what-you-see-is-what-you-get (WYSIWYG) approach are important to you. No Domain Specific Language (DSL) to learn.
You need to parameterize by table names, column names, all while preventing injection risks.
You have dynamic and complex subqueries to compose. And you find it error prone managing the subquery parameters manually.
You value compile-time semantic error prevention so you won’t accidentally use user.name() in a place intended for {ssn}, even if both are strings.

This content originally appeared on DEV Community and was authored by Dash One

Introducing SafeSql – Enforced Injection Safety

SQL Injection

Existing Mitigations

Hardening the SQL String

SQL Template With Escape Hatch

Java DSL

SafeSql

How Does SafeSql Prevent SQLi?

Dynamic IN Clauses with Collections

Conditional Query Fragments

Injecting Identifiers like Column or Table Names

Proper Escaping for LIKE Patterns

Query Composition and Parameter Isolation

Parameter Name and Order Checking

Type Safety

Summary

`SafeSql`

How Does `SafeSql` Prevent SQLi?

Dynamic `IN` Clauses with Collections