Class SafeSql
create
PreparedStatement
.
This class is intended to work with JDBC Connection
and PreparedStatement
API
with parameters set through the setObject()
method.
The main use case though, is to be able to compose subqueries and leaf-level parameters with a
consistent templating API.
For trivial parameterization, you can use:
SafeSql sql = SafeSql.of(
"""
select id from Employees
where firstName = {first_name} and lastName = {last_name}
""",
firstName, lastName);
try (var statement = sql.prepareStatement(connection),
var resultSet = statement.executeQuery()) {
...
}
The code internally uses the JDBC '?'
placeholder in the SQL text, and calls
PreparedStatement.setObject()
to
set all the parameter values for you so you don't have to keep track of the parameter
indices or risk forgetting to set a parameter value.
The templating engine uses compile-time checks to guard against accidental use of
untrusted strings in the SQL, ensuring that they can only be sent as parameters:
try to use a dynamically generated String as the SQL and you'll get a compilation error.
In addition, the same set of compile-time guardrails from the StringFormat
class
are in effect to make sure that you don't pass lastName
in the place of
first_name
, for example.
That said, the main benefit of this library lies in flexible and dynamic query composition. By composing smaller SafeSql objects that encapsulate subqueries, you can parameterize by table name, by column names or by arbitrary sub-queries that may be computed dynamically.
For example, the following code builds sql to query the Users table with flexible
number of columns and a flexible WHERE clause depending on the UserCriteria
object's state:
import static com.google.mu.safesql.SafeSql.optionally;
class UserCriteria {
Optional<String> userId();
Optional<String> firstName();
...
}
SafeSql queryUsers(UserCriteria criteria, @CompileTimeConstant String... columns) {
SafeSql sql = SafeSql.of(
"select `{columns}` from Users where {criteria}",
asList(columns),
Stream.of(
optionally("id = {id}", criteria.userId()),
optionally("firstName LIKE '%{first_name}%'", criteria.firstName()))
.collect(SafeSql.and()));
}
SafeSql usersQuery = queryUsers(userCriteria, "firstName", "lastName");
If UserCriteria
has specified firstName()
but userId()
is
unspecified (empty), the resulting SQL will look like:
select `firstName`, `lastName` from Users where firstName LIKE ?
And when you call usersQuery.prepareStatement(connection)
,
statement.setObject(1, "%" + criteria.firstName().get() + "%")
will be called
to populate the PreparedStatement.
Sometimes you may wish to parameterize by table names, column names etc.
for which JDBC has no support.
If the identifiers can come from compile-time literals or enum values, prefer to wrap
them using SafeSql.of(identifier)
which can then be composed as subqueries.
But what if the identifier string is loaded from a resource file, or is specified by a request field? While such strings are inherently dynamic and untrusted, you can still parameterize them if you backtick-quote the placeholder in the SQL template. For example:
SafeSql.of("select `{columns}` from Users", request.getColumns())
The backticks tell SafeSql that the string is supposed to be an identifier (or a list of
identifiers). SafeSql will sanity-check the string(s) to make sure injection isn't possible.
In the above example, if getColumns()
returns ["id", "age"]
, the genereated
SQL will be select `id`, `age` from Users
. That is, each individual string will
be backtick-quoted and then joined by ", ".
Note that with straight JDBC, if you try to use the LIKE operator to match a user-provided
substring, i.e. using LIKE '%foo%'
to search for "foo", this seemingly intuitive
syntax is actually incorect:
String searchBy = ...;
PreparedStatement statement =
connection.prepareStatement("select * from Users where firstName LIKE '%?%'");
statement.setString(1, searchBy);
JDBC considers the quoted question mark as a literal so the setString()
call will fail. You'll need to use the following workaround:
PreparedStatement statement =
connection.prepareStatement("select * from Users where firstName LIKE ?");
statement.setString(1, "%" + searchBy + "%");
And even then, if the searchTerm
includes special characters like '%' or backslash ('\'),
they'll be interepreted as wildcards and escape characters, opening it up to a form of minor
SQL injection despite already using the parameterized SQL.
The SafeSql template protects you from this caveat. The most intuitive syntax does exactly what you'd expect (and it escapes special characters too):
String searchBy = ...;
SafeSql sql = SafeSql.of(
"select * from Users where firstName LIKE '%{search_term}%'", searchTerm);
try (PreparedStatement statement = sql.prepareStatement(connection)) {
...
}
And even when you don't use LIKE operator or the percent sign (%), it may still be more readable
to quote the string parameters just so the SQL template explicitly tells readers that
the parameter is a string. The following template works with or without the quotes:
// Reads more clearly that the {id} is a string
SafeSql sql = SafeSql.of("select * from Users where id = '{id}'", userId);
A useful tip: the compile-time check tries to be helpful and checks that if you use the same parameter name more than once in the template, the same value must be used for it. So for example, if you are trying to generate a SQL that looks like:
SELECT u.firstName, p.profileId
FROM (select firstName FROM Users where id = 'foo') u,
(select profileId FROM Profiles where userId = 'foo') p
It'll be important to use the same user id for both subqueries. And you can use the following
template to make sure of it at compile time:
SafeSql sql = SafeSql.of(
"""
SELECT u.firstName, p.profileId
FROM (select firstName FROM Users where id = '{user_id}') u,
(select profileId FROM Profiles where userId = '{user_id}') p
""",
userId, userId);
If someone mistakenly passes in inconsistent ids, they'll get a compilation error.
This class serves a different purpose than SafeQuery
, which is to directly escape
string parameters when the SQL backend has no native support for parameterized queries.
- Since:
- 8.2
-
Field Summary
-
Method Summary
Modifier and TypeMethodDescriptionand()
A collector that joins boolean query snippets usingAND
operator.boolean
List
<?> Returns the parameter values in the order they occur in the SQL.int
hashCode()
Returns a collector that joins SafeSql elements usingdelimiter
.static SafeSql
static SafeSql
Convenience method equivalent toof("{param}", param)
, which is translated to a single question mark ('?') withparam
being the value.static SafeSql
optionally
(String query, Optional<?> param) An optional query that's only rendered ifparam
is present; otherwise returnsEMPTY
.or()
A collector that joins boolean query snippets usingOR
operator.prepareCall
(Connection connection) Returns aCallableStatement
with the encapsulated sql and parameters.prepareStatement
(Connection connection) Returns aPreparedStatement
with the encapsulated sql and parameters.static StringFormat.Template
<SafeSql> Returns a template ofSafeSql
based on thetemplate
string.toString()
Returns the SQL text with'?'
as the placeholders.static SafeSql
An optional query that's only rendered ifcondition
is true; otherwise returnsEMPTY
.
-
Field Details
-
EMPTY
An empty SQL
-
-
Method Details
-
of
@TemplateFormatMethod public static SafeSql of(@TemplateString @CompileTimeConstant String template, Object... params) ReturnsSafeSql
usingtemplate
andparams
.For example:
PreparedStatement statement = SafeSql.of("select * from JOBS where id = {id}", jobId) .prepareStatement(connection);
- Parameters:
template
- the sql templateparams
- The template parameters. Parameters that are themselvesSafeSql
are considered trusted subqueries and are appended directly. Other types are passed through JDBCPreparedStatement.setObject(int, java.lang.Object, int)
, with one exception: when the corresponding placeholder is quoted by backticks like`{columns}`
, its string parameter value (orIterable<String>
parameter value) are directly appended (quotes, backticks, backslash and other special characters are disallowed). This makes it easy to parameterize by table names, column names etc.
-
ofParam
Convenience method equivalent toof("{param}", param)
, which is translated to a single question mark ('?') withparam
being the value.If you have a list of candidate ids that need to be passed to the IN opertor, you can use:
List<Long> userIds = ...; SafeSql.of( "SELECT * FROM Users WHERE id IN ({user_ids})", userIds.stream().map(SafeSql::ofParam).toList())
-
optionally
@TemplateFormatMethod public static SafeSql optionally(@TemplateString @CompileTimeConstant String query, Optional<?> param) An optional query that's only rendered ifparam
is present; otherwise returnsEMPTY
. It's for use cases where a subquery is only added when present, for example the following query will add the WHERE clause if the filter is present:SafeSql query = SafeSql.of( "SELECT * FROM jobs {where}", SafeSql.optionally("WHERE {filter}", getOptionalWhereClause()));
-
when
@TemplateFormatMethod public static SafeSql when(boolean condition, @TemplateString @CompileTimeConstant String template, Object... params) An optional query that's only rendered ifcondition
is true; otherwise returnsEMPTY
. It's for use cases where a subquery is only conditionally added, for example the following query will only include the userEmail column under super user mode:SafeSql query = SafeSql.of( "SELECT job_id, start_timestamp {user_email} FROM jobs", SafeSql.when(isSuperUser, ", user_email"));
- Parameters:
condition
- the guard condition to determine iftemplate
should be renderdtemplate
- the template to render ifcondition
is trueparams
- seeof(String, Object...)
for discussion on the template arguments
-
template
Returns a template ofSafeSql
based on thetemplate
string.For example:
private static final Template<SafeSql> GET_JOB_IDS_BY_QUERY = SafeSql.template( """ SELECT job_id from Jobs WHERE query LIKE '%{keyword}%' """); PreparedStatement stmt = GET_JOB_IDS_BY_QUERY.with("sensitive word").prepareStatement(conn);
See
of(String, Object...)
for discussion on the template arguments. -
and
A collector that joins boolean query snippets usingAND
operator. The AND'ed sub-queries will be enclosed in pairs of parenthesis to avoid ambiguity. If the input is empty, the result will be "(1 = 1)".Empty SafeSql elements are ignored and not joined.
-
or
A collector that joins boolean query snippets usingOR
operator. The OR'ed sub-queries will be enclosed in pairs of parenthesis to avoid ambiguity. If the input is empty, the result will be "(1 = 0)".Empty SafeSql elements are ignored and not joined.
-
joining
Returns a collector that joins SafeSql elements usingdelimiter
.Empty SafeSql elements are ignored and not joined.
-
prepareStatement
Returns aPreparedStatement
with the encapsulated sql and parameters.- Throws:
UncheckedSqlException
- wrapsSQLException
if failed
-
prepareCall
Returns aCallableStatement
with the encapsulated sql and parameters.- Throws:
UncheckedSqlException
- wrapsSQLException
if failed
-
getParameters
Returns the parameter values in the order they occur in the SQL. They are used by methods likeprepareStatement(java.sql.Connection)
andprepareCall(java.sql.Connection)
to create and populate the returnedPreparedStatement
-
toString
Returns the SQL text with'?'
as the placeholders. It's used by methods likeprepareStatement(java.sql.Connection)
andprepareCall(java.sql.Connection)
to create and populate the returnedPreparedStatement
. -
hashCode
public int hashCode() -
equals
-