Package com.google.mu.safesql

Class SafeQuery

java.lang.Object
com.google.mu.safesql.SafeQuery
@RequiresGuava @Immutable @CheckReturnValue public final class SafeQuery extends Object
A piece of provably-safe (from SQL injection) query string constructed by the combination of a compile-time string constant, other SafeQuery, safe literal values (booleans, enum constant names, numbers etc.), and/or mandatorily-quoted, auto-escaped string values.

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

  • Field Details

    • EMPTY

      public static final SafeQuery 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 with args.

    • when

      @TemplateFormatMethod public static SafeQuery when(boolean condition, @TemplateString @CompileTimeConstant String query, Object... args)
      An optional query that's only rendered if condition is true; otherwise returns EMPTY. 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 if arg is present; otherwise returns EMPTY. 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

      public static StringFormat.Template<SafeQuery> template(@CompileTimeConstant String formatString)
      Creates a template with the placeholders to be filled by subsequent StringFormat.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, passing asList("foo", "bar") as the value for placeholder '{ids}' will result in WHERE 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 use SafeQuery.of(tmpl, a, b) instead.

    • and

      public static Collector<SafeQuery,?,SafeQuery> and()
      A collector that joins boolean query snippets using AND 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

      public static Collector<SafeQuery,?,SafeQuery> or()
      A collector that joins boolean query snippets using OR 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

      public static Collector<SafeQuery,?,SafeQuery> joining(@CompileTimeConstant String delim)
      Returns a collector that can join SafeQuery objects using delim as the delimiter.

      Empty SafeQuery elements are ignored and not joined.

    • toString

      public String toString()
      Returns the encapsulated SQL query.
      Overrides:
      toString in class Object

    • equals

      public boolean equals(Object obj)
      Overrides:
      equals in class Object

    • hashCode

      public int hashCode()
      Overrides:
      hashCode in class Object