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.

Environment

Application	Confluence
Macros	Run Self-Service Reports for Confluence - Run macro SQL for Confluence - SQL macro Scripting for Confluence - Groovy macro Macro security for confluence

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:
Image Modified

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

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("&#39;", "'")

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("#", "'")

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("&#39;", "'").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.

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

...

1. administrator user account.

...

1. Select Cog wheel

...

1. Image Added > Manage apps.
2. Select the Macro security for confluence app.
3. Expand the app and click Configure to start using it.

...

1. Add sql.

...

1. disableAntiXss and Macro security to *

...

1. ANY as shown below

...

1. :
  Image Modified

Versions Compared

Old Version 22

New Version Current

Key

Environment

Instructions