Class SafeQuery
It's best practice for your db layer API to require SafeQuery instead of
String as the parameter to ensure safety. Internally, you can use
toString()
to access the query string.
This class supports generating SQL based on a string template in the syntax
of TemplateString
, and with the same compile-time protection. To
prevent SQL injection errors, special characters of string values are
automatically escaped; and negative numbers are automatically enclosed by
parenthesis.
Compared to SafeSql
, SafeQuery is independent of the underlying DB engine.
It simply wraps a sanitized query string, using auto-escaping and unicode encoding to prevent SQL
injection. It can be used for databases that don't support JDBC parameterization, or if you
prefer dynamic SQL over parameterization for debugging reasons etc.
This class is Android compatible.
- Since:
- 7.0
-
Nested Class Summary
Modifier and TypeClassDescriptionstatic class
An SPI class for subclasses to provide additional translation from placeholder values to safe query strings. -
Field Summary
-
Method Summary
Modifier and TypeMethodDescriptionand()
A collector that joins boolean query snippets usingAND
operator.boolean
int
hashCode()
Returns a collector that can joinSafeQuery
objects usingdelim
as the delimiter.static SafeQuery
Returns a query using a constant query template filled withargs
.static SafeQuery
optionally
(String query, Optional<?> arg) An optional query that's only rendered ifarg
is present; otherwise returnsEMPTY
.or()
A collector that joins boolean query snippets usingOR
operator.Ifthis
query is empty (likely from a call tooptionally(java.lang.String, java.util.Optional<?>)
orwhen(boolean, java.lang.String, java.lang.Object...)
), returns thefallback
query.Ifthis
query is empty (likely from a call tooptionally(java.lang.String, java.util.Optional<?>)
orwhen(boolean, java.lang.String, java.lang.Object...)
), returns the SafeQuery produced from thefallback
template andargs
.Ifthis
query is empty (likely from a call tooptionally(java.lang.String, java.util.Optional<?>)
orwhen(boolean, java.lang.String, java.lang.Object...)
), returns the query produced by thefallback
supplier.static StringFormat.Template
<SafeQuery> Creates a template with the placeholders to be filled by subsequentStringFormat.Template.with(java.lang.Object...)
calls.toString()
Returns the encapsulated SQL query.static SafeQuery
An optional query that's only rendered ifcondition
is true; otherwise returnsEMPTY
.
-
Field Details
-
EMPTY
An empty query string.- Since:
- 8.1
-
-
Method Details
-
of
@TemplateFormatMethod public static SafeQuery of(@CompileTimeConstant @TemplateString String query, Object... args) Returns a query using a constant query template filled withargs
. -
when
@TemplateFormatMethod public static SafeQuery when(boolean condition, @TemplateString @CompileTimeConstant String query, Object... args) 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:SafeQuery query = SafeQuery.of( "SELECT job_id, start_timestamp {user_email} FROM jobs", SafeQuery.when(isSuperUser, ", user_email"));
- Since:
- 8.1
-
optionally
@TemplateFormatMethod public static SafeQuery optionally(@TemplateString @CompileTimeConstant String query, Optional<?> arg) An optional query that's only rendered ifarg
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:SafeQuery query = SafeQuery.of( "SELECT * FROM jobs {where}", SafeQuery.optionally("WHERE {filter}", getOptionalFilter()));
- Since:
- 8.1
-
template
Creates a template with the placeholders to be filled by subsequentStringFormat.Template.with(java.lang.Object...)
calls. For example:private static final Template<SafeQuery> FIND_CASE_BY_ID = SafeQuery .template("SELECT * FROM `{project_id}.{dataset}.Cases` WHERE CaseId = '{case_id}'"); // ... SafeQuery query = FIND_CASE_BY_ID.with(projectId, dataset, caseId);
Placeholders must follow the following rules:
- String placeholder values can only be used when the corresponding
placeholder is quoted in the template string (such as '{case_id}').
- If quoted by single quotes (') or double quotes ("), the placeholder value will be auto-escaped.
- If quoted by backticks (`), the placeholder value cannot contain backtick, quotes and other special characters.
- Unquoted placeholders are assumed to be symbols or subqueries. Strings
(CharSequence) and characters cannot be used as placeholder value. If you
have to pass a string, wrap it inside a
TrustedSqlString
(you can configure the type you trust by setting the "com.google.mu.safesql.SafeQuery.trusted_sql_type" system property). - An Iterable's elements are auto expanded and joined by a comma and space (", ").
- If the iterable's placeholder is quoted as in
WHERE id IN ('{ids}')
, every expanded element is quoted (and auto-escaped). For example, passingasList("foo", "bar")
as the value for placeholder'{ids}'
will result inWHERE id IN ('foo', 'bar')
. - If the Iterable's placeholder is backtick-quoted, every expanded element
is backtick-quoted. For example, passing
asList("col1", "col2")
as the value for placeholder`{cols}`
will result in`col`, `col2`
. - Subqueries can be composed by using
SafeQuery
as the placeholder value.
The placeholder values passed through
StringFormat.Template.with(java.lang.Object...)
are checked by ErrorProne to ensure the correct number of arguments are passed, and in the expected order.Supported expression types:
- null (NULL)
- Boolean
- Numeric (negative numbers will be enclosed in parenthesis to avoid semantic change)
- Enum
- String (must be quoted in the template)
TrustedSqlString
SafeQuery
- Iterable
If you are trying to create SafeQuery inline like
SafeQuery.template(tmpl).with(a, b)
, please useSafeQuery.of(tmpl, a, b)
instead. - String placeholder values can only be used when the corresponding
placeholder is quoted in the template string (such as '{case_id}').
-
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 "TRUE".Empty SafeQuery elements are ignored and not joined.
- Since:
- 7.2
-
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 "FALSE".Empty SafeQuery elements are ignored and not joined.
- Since:
- 7.2
-
joining
Returns a collector that can joinSafeQuery
objects usingdelim
as the delimiter.Empty SafeQuery elements are ignored and not joined.
-
orElse
@TemplateFormatMethod public SafeQuery orElse(@TemplateString @CompileTimeConstant String fallback, Object... args) Ifthis
query is empty (likely from a call tooptionally(java.lang.String, java.util.Optional<?>)
orwhen(boolean, java.lang.String, java.lang.Object...)
), returns the SafeQuery produced from thefallback
template andargs
.Using this method, you can create a chain of optional queries like:
import static c.g.common.labs.base.Optionals.nonEmpty; SafeQuery.of( """ CREATE TABLE ... {cluster_by} """, optionally("CLUSTER BY (`{cluster_columns}`)", nonEmpty(clusterColumns)) .orElse("-- no cluster"));
- Since:
- 8.3
-
orElse
Ifthis
query is empty (likely from a call tooptionally(java.lang.String, java.util.Optional<?>)
orwhen(boolean, java.lang.String, java.lang.Object...)
), returns thefallback
query.- Since:
- 8.3
-
orElse
Ifthis
query is empty (likely from a call tooptionally(java.lang.String, java.util.Optional<?>)
orwhen(boolean, java.lang.String, java.lang.Object...)
), returns the query produced by thefallback
supplier.- Since:
- 8.3
-
toString
Returns the encapsulated SQL query. -
equals
-
hashCode
public int hashCode()
-