---
title: "How to run SQL queries securely without page edit restrictions"
canonical: "https://support.appfire.com/space/SUPPORT/89139508/How%20to%20run%20SQL%20queries%20securely%20without%20page%20edit%20restrictions"
format: markdown
---
> Macro (div)
> 
> ### <span style="color: #000000">On this page</span>
> 
> > Macro (toc)

## <span style="color: #000000">Description</span>

<span style="color: #000000">The</span> *<span style="color: #000000">SQL</span>*<span style="color: #000000"> 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</span> [Macro Security for Confluence](https://appfire.atlassian.net/wiki/spaces/CMSP)<span style="color: #000000">. This discusses new ways to provide similar capabilities while minimizing the inconveniences introduced by the security controls. </span>

<span style="color: #000000">Two solutions are discussed in this article; one not requiring macro security and the other using</span> [Macro Security for Confluence](https://appfire.atlassian.net/wiki/spaces/CMSP)<span style="color: #000000">. </span>

## <span style="color: #000000">Steps - without macro security</span>

1. <span style="color: #000000">Install </span>*<span style="color: #000000">SQL for Confluence</span>*<span style="color: #000000"> using UPM. App version 5.6 or higher is required.</span>
2. <span style="color: #000000">Immediately disable the </span>*<span style="color: #000000">SQL</span>*<span style="color: #000000">, </span>*<span style="color: #000000">SQL Query</span>*<span style="color: #000000">, </span>*<span style="color: #000000">SQL Legacy</span>*<span style="color: #000000">, and </span>*<span style="color: #000000">SQL Query Legacy</span>*<span style="color: #000000"> macros </span>[using standard UPM features](https://answers.atlassian.com/questions/15292700)<span style="color: #000000">. Leave </span>*<span style="color: #000000">SQL File</span>*<span style="color: #000000"> and </span>*<span style="color: #000000">SQL File Legacy</span>*<span style="color: #000000"> macros enabled. </span>
3. <span style="color: #000000">Add an example SQL script to the </span>***<span style="color: #000000"><confluence-home>/script</span>***<span style="color: #000000"> directory on the Confluence server. </span>*<span style="color: #000000">Only scripts in this directory or subdirectories are accessible</span>*<span style="color: #000000">.</span>
4. <span style="color: #000000">On a page, add the </span>*<span style="color: #000000">SQL File</span>*<span style="color: #000000"> macro and specify the SQL script file used in step 3.</span>
5. <span style="color: #000000">Preview to see the results of the query.</span>

> ✅ **Secure the script directory**
> ✅ 
> ✅ <span style="color: #000000">Ensure only authorized persons can add or change scripts in the </span>***<span style="color: #000000">scripts</span>***<span style="color: #000000"> directory on the Confluence server. Only add scripts that are okay to run by anyone from Confluence.</span>

> ✅ **Use parameter markers**
> ✅ 
> ✅ <span style="color: #000000"> 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</span> [Wikipedia: SQL injection](http://en.wikipedia.org/wiki/SQL_injection)<span style="color: #000000">.</span>

## <span style="color: #000000">Steps - with macro security</span>

1. <span style="color: #000000">Install </span>*<span style="color: #000000">SQL for Confluence</span>*<span style="color: #000000"> using UPM. App version 5.6 or higher is required.</span>
2. <span style="color: #000000">Update the properties file used to load the macro security configuration by adding something like the following:</span>
3. <span style="color: #000000">In UPM, find </span>*<span style="color: #000000">Macro Security for Confluence</span>*<span style="color: #000000"> and click </span>**<span style="color: #000000">Configure</span>**<span style="color: #000000">.</span>
4. <span style="color: #000000">Load the updated properties file from step 2.</span>
5. <span style="color: #000000">Add an example SQL script to the </span>***<span style="color: #000000"><confluence-home>/script</span>***<span style="color: #000000"> directory on the Confluence server. </span>*<span style="color: #000000">Only scripts in this directory or subdirectories are accessible.</span>*
6. <span style="color: #000000">On a page, add the </span>*<span style="color: #000000">SQL File</span>*<span style="color: #000000"> macro </span><span style="color: #000000">and specify the SQL script file</span><span style="color: #000000"> used in step 5.</span>
7. <span style="color: #000000">Preview to see the results of the query. Note that no page edit restrictions are required.</span>