New Method for Concatenating Groovy Strings / Stricter Validation

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. See the Tips and Considerations for more details about what is supported and what is not supported.

IMPORTANT: In addition, the system has tightened the validation for scripts using StringBuilder or StringBuffer in the createPreparedStatement. A provided property is responsible for enabling the stricter validation and is turned on by default. Scripts using one of these techniques will not compile. The property is com.oracle.ouaf.groovy.strictConcatenationValidation . With this property set to "true", you can identify the scripts that need to change by running the Compile All Groovy Scripts (F1-CAGVY) batch.  If for some reason you need additional time to fix the scripts that fail this compilation, you can override the property and set it to "false" while you fix your scripts.

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

By default, script validation is tightened to disallow concatenation unless the new method is used. Refer to the description for information about the property and overriding it while you fix any scripts that do not compile.

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, this previously supported string concatenation is not allowed by default.  You should review all your scripts in this release and change them to use the new method.  Here is an example:

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');