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, you can use the replace function.

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