Page Comparison

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.

...

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 functioncheck for the below two methods.

1. By updating the groovy script as shown in the below example will help to display the special characters as it is.
   
   

   1. 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("'", "'")':

      Code Block
      theme Midnight
      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("#", "'")'
      

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

      
      

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

      Code Block
      theme Midnight
      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

      
      

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

      
      

2. If the Macro Security for Confluence is installed in the confluence instance enable/add the sql.disableAntiXss to*ANY in the Macro security for confluence at configuration level to avoid such inconsistencies:
   Follow the below steps to enable the sql.disableAntiXss:

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