Versions Compared

Old Version 3

changes.mady.by.user Former user

Saved on

compared with

New Version 4

changes.mady.by.user Smita Nair

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


Div
idProductTOC

On this page

Table of Contents
maxLevel2
minLevel2
excludeOn this page
typeflat
separatorpipe

Description

The The SQL macros provide powerful capabilities to bring important data into the collaborative environment Confluence offers. However, on sites that have untrusted users, the macros must be restricted using using CMSP. This discusses new ways to provide similar capabilities while minimizing the inconveniences introduced by the security controls. 

Two solutions are discussed in this article, one not requiring macro security and the other using using CMSP. 

Steps - without Macro Security

  1. Install SQL Install SQL for Confluence using UPM. Add-on App version 5.6 or higher is required.
  2. Immediately disable the SQL, SQL Query, SQL Legacy, and SQL Query Legacy macros using standard UPM features. Leave SQL File and SQL File Legacy macros enabled. 

  3. Add an example SQL script to the <confluence-home>/script directory on the Confluence server. Only scripts in this directory or subdirectories are accessible.

  4. On a page, add the SQL File macro specifying the file to be the name of the file used in step 3.
  5. Preview to see the results of the query.
Tip
titleSecure the script directory

Ensure only authorized persons can add or change scripts in the scripts directory on the Confluence server. Only add scripts that are ok okay to run by anyone from Confluence.


Tip
titleUse parameter markers

 Use SQL parameter markers to parameterize your scripts. This makes the scripts reusable and prevents SQL injection attacks when the SQL statements are partially constructed from user input. See See Wikipedia: SQL injection.

Steps - with Macro Security

  1. Install SQL for Confluence using UPM. Add-on App version 5.6 or higher is required.

  2. Update the properties file used to load the macro security configuration by adding something like the following:

    No Format
    sql-file=*ANY
sql-file.datasource.*=*ANY
sql-file.file.example.sql=*ANY


  3. In UPM, find Macro Security for Confluence and press the click Configure button.
  4. Load the updated properties file from step 2.

  5. Add an example SQL script to the <confluence-home>/script directory on the Confluence server. Only scripts in this directory or subdirectories are accessible.

  6. On a page, add the SQL File macro specifying the file to be the name of the file used in step 5.
  7. Preview to see the results of the query. Note that no page edit restrictions are required.