New Method for Concatenating Groovy Strings

There is a string size limitation in Groovy of 65,535 when using createPreparedStatement and as a result, script writers have used the StringBuilder or String Buffer method to build more complex queries. 

In this release, a new Groovy method is provided: concatenateGroovyStrings(String... args). This allows script writers to concatenate strings to build a query for createPreparedStatement that exceeds the maximum string size. The new method accepts a variable number of strings (varargs of strings) to concatenate. This method validates that the arguments are only strings. The createPreparedStatement method is enhanced to accept the new method as its first argument or to accept a variable that was created using that method.

IMPORTANT: In a future release, using StringBuilder or StringBuffer in the createPreparedStatement will no longer be supported. The system will validate that if the argument used in the createPreparedStatement is either a string itself, a concatenation of strings using "+" or created using the concatenateGroovyStrings method.

You should identify custom scripts that are using a concatenation technique for building a query and refactor them to use the concatenateGroovyStrings method.

The new Groovy method validates that only strings are used in concatenation, ensuring no chance of SQL injection.

Steps to Enable

You don't need to do anything to enable this feature.

Tips And Considerations

Scripts continue to work in this release. However, in the next release validation is tightening up so you should plan to identify custom scripts that are using a concatenation technique for building a query and refactor them to use the concatenateGroovyStrings method before the next release.

Here are some examples of valid syntax using the new method:

// Example 1 - use the new method directly in the createPreparedStatement to concatenate strings
def query = createPreparedStatement(concatenateGroovyStrings("SELECT * ", "FROM ", "TABLE_NAME"), 'My Query');

// Example 2 - define a variable using the new method and refer to that variable as the first argument in the createPreparedStatement
def concatStr = concatenateGroovyStrings("SELECT * ", "FROM ", "TABLE_NAME");
def query = createPreparedStatement(concatStr, 'My Query');

// Example 3 - support example 2, but also with reassignment
def concatStr = concatenateGroovyStrings("SELECT * ", "FROM ", "TABLE_NAME");
def concatStr2 = concatStr;
def query = createPreparedStatement(concatStr2, 'My Query');

// Example 4 - support a combination of variables and strings in the prepared statement - as long as the variables were created using the new method
def concatStr = concatenateGroovyStrings("SELECT * ", "FROM ", "TABLE_NAME");
def query = createPreparedStatement(concatStr + " WHERE FIELD_NAME = 'VALUE'", 'My Query');

The createPreparedStatement will not accept any variable that is not created directly from the new method with literal strings.  Examples of syntax that is not allowed.

// Example 1 

def concatStr = concatenateGroovyStrings("SELECT * ", "FROM ", "TABLE_NAME");
def concatStr2 = concatStr + " WHERE FLD_NAME = 'A'";
def query = createPreparedStatement(concatStr2, 'My Query');

// Example 2 
def concatStr = concatenateGroovyStrings("SELECT * ", "FROM ", "TABLE_NAME");
def concatStr2 = (new StringBuffer(concatStr)).toString();
def query = createPreparedStatement(concatStr2, 'My Query');

// Example 3 
def concatStr = concatenateGroovyStrings("SELECT * ", "FROM ", "TABLE_NAME");
def concatStr2 = new String(concatStr);
def query = createPreparedStatement(concatStr2, 'My Query');

In this release, the system continues to support previously supported string concatenation.  However, this will not be allowed in the future.  You should review all your scripts in this release and change them to use the new method.  Here is an example of what will not be allowed in the future.

def query = createPreparedStatement(new StringBuffer("SELECT * FROM TABLE_NAME").
      append(" WHERE FLD_A = 'X'").
      append(" AND FLD_B = 'Y'").
      append(" ORDER BY FLD_C").toString(), 'My Query');

Note that simple concatenation with "+" of literal strings directly in createPreparedStatement is supported and will continue to be supported in the future.

def query = createPreparedStatement("SELECT * " + "FROM " + "TABLE_NAME"), 'My Query')