How to run SQL queries securely without page edit restrictions

Owned by
Former user (Deleted)
Last updated: Feb 28, 2020 by
Smita Nair
2 min read

On this page

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 Macro Security for Confluence. 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 Macro Security for Confluence. 

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

Secure 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 okay to run by anyone from Confluence.

Use 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:

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