Macros

• SQL Macro
• SQL-query Macro - performance optimized for read-only SQL statements
• SQL-file Macro - locked to just run administrator defined queries

Usage

{sql-query:dataSource=TestDS|output=wiki}
select * from test
{sql-query}

There are *{sql-query:dataSource=TestDS|table=false} select count(*) from test01 {sql-query}* rows in table test01

{sql-query:dataSource=TestDS|p1=%test%|showsql=true}
select * from test01 where a1 like ?
{sql-query}

Example chart from Site statistics:

{chart:title=Confluence Content|type=pie|displayData=true|width=500|height=300
|dataOrientation=vertical|pieSectionLabel=%0% - %1%|legend=false|
tables=content|attachment=Confluence content.png}

{sql:dataSource=confluenceDS|id=content}
  select contenttype as Type, count(distinct CONTENT.title) as "Count" 
    from CONTENT
    group by contenttype
    having count(distinct CONTENT.title) > 0
  union
  select contenttype as Type, count(*) as "Count"
    from CONTENT 
	where contenttype='COMMENT'
    group by contenttype 
	having count(*) > 0
  union
  select 'ATTACHMENTS' as Type, count(distinct title) as "Count" 
    from ATTACHMENTS 
  order by "Count" DESC
{sql}

{chart}

Confluence 3.x wiki macros

Duplicate macros are available for Confluence 3.x style wiki markup for recursive use.

• sql1 - duplicate of sql for recursive use
• sql2 - duplicate of sql for recursive use
• sql3 - duplicate of sql for recursive use
• sql-query1 - duplicate of sql-query for recursive use
• sql-query2 - duplicate of sql-query for recursive use
• sql-query3 - duplicate of sql-query for recursive use

Security

1. This macro and the dataSource parameter can be restricted to trusted users by configuring security using Macro Security for Confluence.
   
   • The sql and sql-query macros are controlled separately. The duplicate macros (sql1, sql2, sql-query1, etc...) cannot be configured separately as they inherit from the macro they duplicate.
2. Only some databases/JDBC drivers support read only connections to be established without restrictions on the database permission level. The sql-query macro uses this support to make it more convenient by only requiring a single datasource configuration. If you database/JDBC driver does not prevent updates when using the sql-query macro, then configure an additional datasource that only has read-only permission on your database. Use macro security at the datasource level to control access. For example, PostgreSQL supports this, but it is not supported on MS Sequel Server (SQL-52).
3. On untrusted sites, prevent SQL injection attacks by using parameter markers. This in only necessary when the SQL statements are partially constructed from user input. See Wikipedia: SQL injection. Parameter markers are supported by SQL for Confluence.
4. Users requiring Anti-XSS support must upgrade to 4.1.0 or higher.
5. The disableAntiXss parameter on the sql and sql-query macros can be enabled by configuring security using Macro Security for Confluence.