How to avoid inconsistent select-field values with special characters (&, #) while using Groovy script

When you use Groovy script to fetch database values to populate a select-list field, you might observe that the values with special characters are not displayed as expected. This article explains how to correct the Groovy script to avoid such inconsistencies between database values and the values you see in the select-list of a Confluence page.

Environment

Instructions

When you populate values with special characters like & or # into a select-field using Groovy script, you see that an 'amp' value is appended to the select-field value on the page. To remove these additional characters, check for the below two methods:

Update the Groovy script - By updating the groovy script as shown in the example below, you can display the special characters as they are:

• Update the groovy script in the getUserChoice function. For example, if your field value contains '&', then you need to update the return choice with  'replace("'", "'")'

  def getUserChoice() {
      def sql = """select * from simple order by name;"""
  
      def userChoice = "None:Select an author:" + getChoice(sql, 'testDS')
      return userChoice.replace("'", "'")

  Similarly, if your field value contains '#', use 'replace("#", "'")'
  

  def getUserChoice() {
       def sql = """select * from simple order by name;"""
  
      def userChoice = "None:Select an author:" + getChoice(sql, 'testDS')
      return userChoice.replace("#", "'")

• The following is the sample groovy script, which contains run, SQL with the groovy script:

  import com.atlassian.renderer.v2.RenderMode
  def getUserChoice() {
       def sql = """select * from simple order by name;"""
  
      def userChoice = "None:Select an author:" + getChoice(sql, 'testDS')
      return userChoice.replace("'", "'").replace("#", "'")
  }
  
  def getUserCodesChoice() {
  	def sql = """SELECT CONCAT('#', code, '(', description, '#):#', code, '(', description, ')#:') from mysimple;"""
  	def userCodesChoice = getChoice(sql, 'testDS')
  	return userCodesChoice.replace("#", "'")
  }
  
  def getChoice(final sql, final dataSource) {
   
      def choiceMacro = "{sql-query:datasource=${dataSource}|table=false} ${sql} {sql-query}"
      def choices = subRenderer.render(choiceMacro, context, RenderMode.suppress(RenderMode.F_FIRST_PARA))
   
      if (choices.contains('div class="error"')) {
          println "Error rendering macro: ${choiceMacro}"
          println "Error: ${choices}"
          return "ERROR"
      }
      return choices
  }
  
  def runMacro = """
  {run:id=dynamic|
  	autorun=false|
  	heading=Create a new user list registry entry|
    	replace=author:author::select::${userChoice},
              user_code:User Code::select::${userCodesChoice},
  			title::Title::Text,
  			customer_name::Customer Name:text,
  			description::Description:text|
  	keepRequestIds=title, author, user_code, customer_name|
  	titleReset=Cancel|
  	titleRun=Create a new user list|
  	showReset=true}
  
  	{sql:dataSource=testDS} INSERT INTO simpletest1 (customer_
  name, author, user_code, title) VALUES ('\$customer_name', '\$author', '\$user_code', '\$title'); {sql}
  
  	{cli:profile=AM|confluence|showCommand-true|macros=true}
  		--action copyPage 
  			--space "SRAV"
  			--title "Test" 
  			--newTitle "L{sql-query:dataSource=testDS|table=false}SELECT type FROM simple ORDER BY id DESC LIMIT 1;{sql-query} - \$title"
  			--parent "Test" 
  			--replace --findReplace "place_holder_for_isotek_number:{sql-query:dataSource=testDS|table=false}SELECT type FROM simple ORDER BY id DESC LIMIT 1;{sql-query},place_holder_for_customer_name:'\$customer_name',place_holder_for_author:'\$author',place_holder_for_user_code:'\$user_code',place_holder_for_description:'\$description'" 
  
  	{cli}
  
  {run}
  """
   
  out.println runMacro

  • It is recommended to implement the mentioned solution on a test instance before running them in a production environment. 
  • Modify the query, table names to match your database or profile information.

Use Macro Security for Confluence: If the Macro Security for Confluence app is also installed in your Confluence instance, you can add the sql.disableAntiXss to*ANY in the Macro Security for Confluence Configuration screen. This helps avoiding inconsistencies with  select-field values in the database and UI. Follow the steps below to enable the sql.disableAntiXss restriction:

• 1. Log into the Confluence instance with an administrator user account.
  2. Select Cog wheel  > Manage apps.  
  3. Select the Macro security for confluence app.
  4. Expand the app and click Configure to start using it.
  5. Add sql.disableAntiXss and Macro security to *ANY as shown below: