User Macro with sql-query failed after Confluence upgrade to 7.7 version

Problem Statement

Existing user macros with SQL query are failed after confluence upgrade from 7.6.x to 7.7.x version if variables/queries inside macro contain special characters.

Error Message

sql: org.postgresql.util.PSQLException: ERROR: syntax error at or near "&" Position: 14

Log Entries

2022-01-26 06:20:15,156 ERROR [http-nio-8090-exec-12] [swift.confluence.sql.SqlDataHelper] process Error in executing SQL statements : SELECT id AS &quot;JI_ID&quot; FROM jiraissue LIMIT 1;, Exception : {}
– url: <URL> | page: <page ID> | traceId: <traceid> | userName: <user-name> | referer: <page URL> | action: viewpage
org.postgresql.util.PSQLException: ERROR: syntax error at or near "&"
Position: 14
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153)

Environment

Application	Confluence
Version	7.7.x or later
App Version	SQL for confluence 10.4.2
Database Type	PostgreSQL

Cause

Confluence performs htmlencoding for every variable that is used as output, except the name of the variable ends with "Html"

Solution

Append Html in the variable names. E.g- Variable “sqlQuery” as “sqlQueryHtml”.
Example: User macro code snippet in the earlier version of confluence 7.6.x
## @param ReportTitle:title=Report Title|type=string|required=false #set($paramReportTitle="Resource Planning Report - " + $content.currentDate) #set($doubleQuote = '"' ) #set($singleQuote = "'" ) #set($sqlQuery = "SELECT id AS ${doubleQuote}JI_ID${doubleQuote} FROM jiraissue LIMIT 1;") $action.getHelper().renderConfluenceMacro("{sql:datasource=basutest|output=html|columnLabel=true|table=true|convertNull=true|autoTotal=false|autoNumber=false} $sqlQuery {sql}")

User macro code snippet in the later version of confluence 7.7.x

## @param ReportTitle:title=Report Title|type=string|required=false
#set($paramReportTitle="Resource Planning Report - " + $content.currentDate)

#set($doubleQuoteHtml = '"' )
#set($singleQuoteHtml = "'" )

#set($sqlQueryHtml = "SELECT id AS ${doubleQuoteHtml}JI ID${doubleQuoteHtml} FROM jiraissue LIMIT 1;")

$action.getHelper().renderConfluenceMacro("{sql:datasource=basutest|output=html|columnLabel=true|table=true|convertNull=true|autoTotal=false|autoNumber=false}

$sqlQueryHtml

{sql}")