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

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

Owned by Basudev Behera (Unlicensed), created
Last updated: Feb 28, 2022
1 min read

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

  1. 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}")

 

Related content