Package com.google.mu.safesql

Class SafeSql

java.lang.Object
com.google.mu.safesql.SafeSql
@RequiresGuava @ThreadSafe @CheckReturnValue public final class SafeSql extends Object
An injection-safe dynamic SQL, constructed using compile-time enforced templates.

This class is intended to work with JDBC Connection 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 an intuitive templating API.

The IN Operator
A common dynamic SQL use case is to use the IN SQL operator: 

   SafeSql sql = SafeSql.of(
       """
       SELECT id FROM Users
       WHERE firstName = {first_name} AND lastName IN ({last_names})
       """,
       firstName, lastNamesList);
   List<Long> ids = sql.query(connection, row -> row.getLong("id"));
In the above example if firstName is "Emma" and lastNamesList is ["Watson", "Lin"], the generated SQL will be: 

   SELECT id FROM Employees
   WHERE firstName = ? AND lastName IN (?, ?)
And the parameters will be set as: 

   statement.setObject(1, "Emma");
   statement.setObject(2, "Watson");
   statement.setObject(3, "Lin");
Compile-time Protection

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 of PreparedStatement: try to use a dynamically generated String as the SQL template 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.

To enable the compile-time plugin, copy the <annotationProcessorPaths> in the "maven-compiler-plugin" section from the following pom.xml file snippet: 


 <build>
   <pluginManagement>
     <plugins>
       <plugin>
         <artifactId>maven-compiler-plugin</artifactId>
         <configuration>
           <annotationProcessorPaths>
             <path>
               <groupId>com.google.errorprone</groupId>
               <artifactId>error_prone_core</artifactId>
               <version>2.23.0</version>
             </path>
             <path>
               <groupId>com.google.mug</groupId>
               <artifactId>mug-errorprone</artifactId>
               <version>8.2</version>
             </path>
           </annotationProcessorPaths>
         </configuration>
       </plugin>
     </plugins>
   </pluginManagement>
 </build>
Conditional Subqueries
By composing SafeSql objects that encapsulate subqueries, you can also parameterize by arbitrary sub-queries that are 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 usersQuery(UserCriteria criteria, @CompileTimeConstant String... columns) {
     return 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()));
   }

   List<User> users = usersQuery(userCriteria, "email", "lastName")
       .query(connection, row -> new User(row.getString("email"), row.getString("lastName")));
If UserCriteria has specified firstName() but userId() is unspecified (empty), the resulting SQL will look like: 

   SELECT `email`, `lastName` FROM Users WHERE firstName LIKE ?

And when you call usersQuery.prepareStatement(connection) or one of the similar convenience methods, statement.setObject(1, "%" + criteria.firstName().get() + "%") will be called to populate the PreparedStatement.

Parameterize by Column Names or Table Names
Sometimes you may wish to parameterize by table names, column names etc. for which JDBC parameterization has no support.

If the identifiers are compile-time string literals, you can wrap them using SafeSql.of(COLUMN_NAME), 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?

Passing the string directly as a template parameter will only generate the JDBC '?' parameter in its place, which won't work (PreparedStatement can't parameterize identifiers); SafeSql.of(theString) will fail to compile because such strings are inherently dynamic and untrusted.

The safe way to parameterize dynamic strings as identifiers is to backtick-quote their placeholders 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 ensure injection safety.

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

The LIKE Operator

Note that with straight JDBC API, 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 searchTerm = ...;
   PreparedStatement statement =
       connection.prepareStatement("SELECT id FROM Users WHERE firstName LIKE '%?%'");
   statement.setString(1, searchTerm);
JDBC PreparedStatement 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 id FROM Users WHERE firstName LIKE ?");
   statement.setString(1, "%" + searchTerm + "%");
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 searchTerm = ...;
   SafeSql sql = SafeSql.of(
       "SELECT id FROM Users WHERE firstName LIKE '%{search_term}%'", searchTerm);
   List<Long> ids = sql.query(connection, row -> row.getLong("id"));
Quote String Placeholders
Even when you don't use the LIKE operator or the percent sign (%), it may still be more readable to quote the string placeholders just so the SQL template explicitly tells readers that the parameter is a string. The following template works with or without the quotes around the {id} placeholder: 

   // Reads more clearly that the {id} is a string
   SafeSql sql = SafeSql.of("SELECT * FROM Users WHERE id = '{id}'", userId);
Enforce Identical Parameter

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.

Immutable if the template parameters you pass to it are immutable.

This class serves a different purpose than SafeQuery. The latter is to directly escape string parameters when the SQL backend has no native support for parameterized queries.

Since:
8.2

  • Field Details

    • EMPTY

      public static final SafeSql EMPTY
      An empty SQL

  • Method Details

    • of

      @TemplateFormatMethod public static SafeSql of(@TemplateString @CompileTimeConstant String template, Object... params)
      Returns SafeSql using template and params.

      For example: 

      
 List<Long> jobIds = SafeSql.of(
         "SELECT id FROM Jobs WHERE timestamp BETWEEN {start} AND {end}",
         startTime, endTime)
     .query(connection, row -> row.getLong("id"));

      Note that if you plan to create a PreparedStatement and use it multiple times with different sets of parameters, it's more efficient to use prepareToQuery() or prepareToUpdate(), which will reuse the same PreparedStatement for multiple calls. The returned StringFormat.Templates are protected at compile-time against incorrect varargs.

      Parameters:
      template - the sql template
      params - The template parameters. Parameters that are themselves SafeSql are considered trusted subqueries and are appended directly. Other types are passed through JDBC PreparedStatement.setObject(int, java.lang.Object, int), with one exception: when the corresponding placeholder is quoted by backticks like `{columns}`, its string parameter value (or Iterable<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.

    • nonNegativeLiteral

      public static SafeSql nonNegativeLiteral(int number)
      Wraps non-negative number as a literal SQL snippet in a SafeSql object.

      For example, the following SQL Server query allows parameterization by the TOP n number: 

      
   SafeSql.of("SELECT TOP {page_size} UserId FROM Users", nonNegativeLiteral(pageSize))

      Needed because the SQL Server JDBC driver doesn't support parameterizing the TOP number through PreparedStatement API.

      Throws:
      IllegalArgumentException - if number is negative

    • optionally

      @TemplateFormatMethod public static SafeSql optionally(@TemplateString @CompileTimeConstant String query, Optional<?> param)
      An optional query that's only rendered if param 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: 
      
 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 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: 
      
 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 if template should be renderd
      template - the template to render if condition is true
      params - see of(String, Object...) for discussion on the template arguments

    • template

      public static StringFormat.Template<SafeSql> template(@CompileTimeConstant String template)
      Returns a StringFormat.Template of SafeSql based on the template string. Useful for creating a constant to be reused with different parameters.

      For example: 

      
 private static final Template<SafeSql> FIND_USERS_BY_NAME =
     SafeSql.template("SELECT `{columns}` FROM Users WHERE name LIKE '%{name}%'");

 String searchBy = ...;
 List<User> userIds = FIND_USERS_BY_NAME.with(asList("id", "name"), searchBy)
     .query(connection, row -> new User(row.getLong("id"), row.getString("name")));

      If you don't need a reusable template, consider using of(java.lang.String, java.lang.Object...) instead, which is simpler.

      The template arguments follow the same rules as discussed in of(String, Object...) and receives the same compile-time protection against mismatch or out-of-order human mistakes, so it's safe to use the template as a constant.

      The returned template is immutable and thread safe.

    • and

      public static Collector<SafeSql,?,SafeSql> and()
      A collector that joins boolean query snippet 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 "(1 = 1)".

      Empty SafeSql elements are ignored and not joined.

    • or

      public static Collector<SafeSql,?,SafeSql> or()
      A collector that joins boolean query snippet 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 "(1 = 0)".

      Empty SafeSql elements are ignored and not joined.

    • joining

      public static Collector<SafeSql,?,SafeSql> joining(@CompileTimeConstant String delimiter)
      Returns a collector that joins SafeSql elements using delimiter.

      Empty SafeSql elements are ignored and not joined.

    • orElse

      @TemplateFormatMethod public SafeSql orElse(@TemplateString @CompileTimeConstant String fallback, Object... args)
      If this query is empty (likely from a call to optionally(java.lang.String, java.util.Optional<?>) or when(boolean, java.lang.String, java.lang.Object...)), returns the SafeSql produced from the fallback template and args.

      Using this method, you can create a chain of optional queries like: 

      
 import static ....Optionals.nonEmpty;

 SafeSql.of(
     """
     CREATE TABLE ...
     {cluster_by}
     """,
     optionally("CLUSTER BY (`{cluster_columns}`)", nonEmpty(clusterColumns))
         .orElse("-- no cluster"));
      Since:
      8.3

    • orElse

      public SafeSql orElse(SafeSql fallback)
      If this query is empty (likely from a call to optionally(java.lang.String, java.util.Optional<?>) or when(boolean, java.lang.String, java.lang.Object...)), returns the fallback query.
      Since:
      8.3

    • orElse

      public SafeSql orElse(Supplier<SafeSql> fallback)
      If this query is empty (likely from a call to optionally(java.lang.String, java.util.Optional<?>) or when(boolean, java.lang.String, java.lang.Object...)), returns the query produced by the fallback supplier.
      Since:
      8.3

    • query

      public <T> List<T> query(Connection connection, SqlFunction<? super ResultSet,? extends T> rowMapper)
      Executes the encapsulated SQL as a query against connection. The ResultSet will be consumed, transformed by rowMapper and then closed before returning.

      For example: 

      
 List<Long> ids = SafeSql.of("SELECT id FROM Users WHERE name LIKE '%{name}%'", name)
     .query(connection, row -> row.getLong("id"));

      Internally it delegates to PreparedStatement.executeQuery().

      Throws:
      UncheckedSqlException - wraps SQLException if failed

    • update

      @CanIgnoreReturnValue public int update(Connection connection)
      Executes the encapsulated DML (create, update, delete statements) against connection and returns the number of affected rows.

      For example: 

      
 SafeSql.of("INSERT INTO Users(id, name) VALUES({id}, '{name}')", id, name)
     .update(connection);

      Internally it delegates to PreparedStatement.executeUpdate().

      Throws:
      UncheckedSqlException - wraps SQLException if failed

    • prepareStatement

      @MustBeClosed public PreparedStatement prepareStatement(Connection connection)
      Returns a PreparedStatement with the encapsulated sql and parameters.

      It's often more convenient to use query(java.sql.Connection, com.google.mu.safesql.SqlFunction<? super java.sql.ResultSet, ? extends T>) or update(java.sql.Connection) unless you need to directly operate on the PreparedStatement.

      Throws:
      UncheckedSqlException - wraps SQLException if failed

    • prepareToQuery

      public static <T> StringFormat.Template<List<T>> prepareToQuery(Connection connection, @CompileTimeConstant String template, SqlFunction<? super ResultSet,? extends T> rowMapper)
      Returns a query template that will reuse the same cached PreparedStatement for repeated calls of StringFormat.Template.with(java.lang.Object...) using different parameters.

      Allows callers to take advantage of the performance benefit of PreparedStatement without having to re-create the statement for each call. For example in: 

      
   try (var connection = ...) {
     var queryByName = SafeSql.prepareToQuery(
         connection, "SELECT id FROM Users WHERE name LIKE '%{name}%'",
         row -> row.getLong("id"));
     for (String name : names) {
       for (long id : queryByName.with(name))) {
         ...
       }
     }
   }
      Each time queryByName.with(name) is called, it executes the same query template against the connection, but with a different name parameter. Internally it reuses the cached PreparedStatement object and just calls PreparedStatement.setObject(int, Object) with the new set of parameters before calling PreparedStatement.executeQuery().

      The template arguments follow the same rules as discussed in of(String, Object...) and receives the same compile-time protection against mismatch or out-of-order human mistakes.

      The returned Template is not thread safe.

      The caller is expected to close the connection after done, which will close the cached PreparedStatement.

    • prepareToUpdate

      public static StringFormat.Template<Integer> prepareToUpdate(Connection connection, @CompileTimeConstant String template)
      Returns a DML (create, update, delete) template that will reuse the same cached PreparedStatement for repeated calls of StringFormat.Template.with(java.lang.Object...) using different parameters.

      Allows callers to take advantage of the performance benefit of PreparedStatement without having to re-create the statement for each call. For example in: 

      
   try (var connection = ...) {
     var insertUser = SafeSql.prepareToUpdate(
         connection, "INSERT INTO Users(id, name) VALUES({id}, '{name}')");
     int totalRowsAffected = insertUser.with(1, "Tom") + insertUser.with(2, "Emma");
   }
      Each time insertUser.with(...) is called, it executes the same DML template against the connection, but with different id and name parameters. Internally it reuses the cached PreparedStatement object and just calls PreparedStatement.setObject(int, Object) with the new set of parameters before calling PreparedStatement.executeUpdate().

      The template arguments follow the same rules as discussed in of(String, Object...) and receives the same compile-time protection against mismatch or out-of-order human mistakes.

      The returned Template is not thread safe.

      The caller is expected to close the connection after done, which will close the cached PreparedStatement.

    • toString

      public String toString()
      Returns the SQL text with the template parameters translated to the JDBC '?' placeholders.
      Overrides:
      toString in class Object

    • hashCode

      public int hashCode()
      Overrides:
      hashCode in class Object

    • equals

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