Versions Compared

Old Version 5

changes.mady.by.user Smita Nair

Saved on

compared with

New Version Current

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

Steps - without

...

macro security

  1. Install SQL for Confluence using UPM. 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 and specify the SQL script file used in step 3.
  5. Preview to see the results of the query.

...

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 Wikipedia: SQL injection.

Steps - with

...

macro security

  1. Install SQL for Confluence using UPM. 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 click Configure.
  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 macro and specify the SQL script file used in step 5.
  7. Preview to see the results of the query. Note that no page edit restrictions are required.