Package com.google.mu.safesql

Class SafeQuery

java.lang.Object
com.google.mu.safesql.SafeQuery
@RequiresGuava @Immutable 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.

This class is Android compatible.

Since:
7.0

  • 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.

    • template

      public static StringFormat.Template<SafeQuery> template(@CompileTimeConstant String formatString)
      Creates a template with the placeholders to be filled by subsequent StringFormat.To.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.To.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.

    • with

      @TemplateFormatMethod public static SafeQuery with(@CompileTimeConstant String subqueryName, SafeQuery subquery, @TemplateString @CompileTimeConstant String template, Object... args)
      Creates a SafeQuery with a single CTE (Common Table Expression). That is, the query in template refers to subquery by subqueryName.

      For example: 

      
 SafeQuery.with(
     "jobs", SafeQuery.of("SELECT * FROM INFO_SCHEMA.JOBS WHERE id = {project}", projectId),
     "SELECT `{columns}` FROM jobs", columns);
      is equivalent to: 
      
 WITH jobs AS (
   SELECT * FROM INFO_SCHEMA.JOBS WHERE id = ...
 )
 SELECT ... FROM jobs
      Since:
      8.1

    • with

      @TemplateFormatMethod public static SafeQuery with(@CompileTimeConstant String name1, SafeQuery subquery1, @CompileTimeConstant String name2, SafeQuery subquery2, @TemplateString @CompileTimeConstant String template, Object... args)
      Creates a SafeQuery with two CTEs (Common Table Expression). That is, the query in template refers to subquery1 by name1 and subquery2 by name2.

      For example: 

      
 SafeQuery.with(
     "jobs", SafeQuery.of("SELECT * FROM INFO_SCHEMA.JOBS WHERE id = {project}", projectId),
     "days", SafeQuery.of("SELECT ..."),
     "SELECT `{columns}` FROM jobs, days", columns);
      is equivalent to: 
      
 WITH jobs AS (
   SELECT * FROM INFO_SCHEMA.JOBS WHERE id = ...
 ),
 days AS (
   SELECT ...
 )
 SELECT ... FROM jobs, days
      Since:
      8.1

    • with

      @TemplateFormatMethod public static SafeQuery with(@CompileTimeConstant String name1, SafeQuery subquery1, @CompileTimeConstant String name2, SafeQuery subquery2, @CompileTimeConstant String name3, SafeQuery subquery3, @TemplateString @CompileTimeConstant String template, Object... args)
      Creates a SafeQuery with 3 CTEs (Common Table Expression). That is, the query in template refers to subquery1 by name1, subquery2 by name2 and subquery3 by name3.

      For example: 

      
 SafeQuery.with(
     "jobs", SafeQuery.of("SELECT * FROM INFO_SCHEMA.JOBS WHERE id = {project}", projectId),
     "days", SafeQuery.of("SELECT ..."),
     "editions", SafeQuery.of("SELECT ..."),
     "SELECT `{columns}` FROM jobs, days, editions", columns);
      is equivalent to: 
      
 WITH jobs AS (
   SELECT * FROM INFO_SCHEMA.JOBS WHERE id = ...
 ),
 days AS (
   SELECT ...
 ),
 editions AS (
   SELECT ...
 )
 SELECT ... FROM jobs, days, editions
      Since:
      8.1

    • 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".
      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".
      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.

    • 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