Security Restrictions on SQL Query | Elements Connect

To protect your databases, Elements Connect avoids pasting dependency values directly into the text of a SQL query. Each value inserted with FreeMarker is sent to the database as a SQL parameter: the database treats it as data, never as SQL.

Without this, a user who can edit an issue could type a value that changes the query, and read tables the query was never meant to access.

As a consequence, values can’t be used to build the query itself, and on PostgreSQL they must be converted to the type of the column they are compared to.

What is concerned

These restrictions apply to connected fields using a Database data source with Use FreeMarker to enable dynamic query logic enabled, in:

the Query
the Live query

They apply to every value inserted with ${...}, for example:

${userInput}
${issue.customfield_10001}, ${issue.summary}…
${currentUser.displayName}, ${reporter.displayName}…
${currentCustomfield.ids?join(',')}…

What is not allowed

1. A value used as a column, table or sort order

A value can’t provide a column name, a table name, an alias, or the column of an ORDER BY / GROUP BY.

Template	Result
`SELECT ${issue.customfield_10001} FROM contacts`	❌ Database error
`SELECT name FROM ${issue.customfield_10001}`	❌ Database error
`ORDER BY ${issue.customfield_10001}`	⚠️ Sort order ignored, or database error depending on the database

What to do: use FreeMarker logic to choose between fixed column names:

ORDER BY <#if issue.customfield_10001 == "date">created_date<#else>name</#if>

2. A value used as an operator or SQL keyword

A value can’t provide an operator (=, >, LIKE…) or a keyword (AND, DESC…).

Template	Result
`WHERE price ${issue.customfield_10001} 100`	❌ Database error

What to do: same as above, write the operator in the template and use <#if> to choose it.

3. Turning off escaping in a FreeMarker query

The following are refused in SQL queries:

the ?no_esc built-in, for example ${userInput?no_esc}
the <#noautoesc> directive
changing the output format, for example with <#outputformat> or <#ftl auto_esc=false>

The query is refused as soon as it is read, and the field shows the error:

Disabling auto-escaping or changing the output format is not allowed in a SQL query (line X, column Y): interpolated values must stay bound as SQL parameters.

What to do: remove these constructs from your query and live query.

4. A value inside a SQL comment

A value inserted inside a comment (-- …, /* … */, or # … on MySQL / MariaDB) is removed from the query. This has no effect on the results, since a comment is not executed.

5. Comparing a value to a non-text column on PostgreSQL

Every value is sent as a text parameter. PostgreSQL does not convert text to other types on its own, so comparing a value to a number, date, timestamp or boolean column fails, with or without quotes around the value.

MySQL / MariaDB, SQL Server and Oracle convert the value automatically and are not affected.

Template (PostgreSQL)	Result
`WHERE customer_id = ${userInput}`	❌ operator does not exist: integer = character varying
`WHERE customer_id = '${userInput}'`	❌ Same error
`WHERE customer_id IN (${ids?join(',')})`	❌ Same error
`WHERE rental_date > '${userInput}'`	❌ operator does not exist: timestamp without time zone > character varying
`WHERE create_date = '${userInput}'`	❌ operator does not exist: date = character varying
`WHERE activebool = '${userInput}'`	❌ operator does not exist: boolean = character varying
`WHERE customer_id = CAST(${userInput} AS INTEGER)`	✅ Works
`WHERE customer_id = ${userInput}::int`	✅ Works
`WHERE rental_date > CAST(${userInput} AS TIMESTAMP)`	✅ Works

What to do: convert the value to the type of the column with CAST(${...} AS <type>), or ${...}::<type>.

For a list, convert each element with <#list> instead of ?join:

WHERE customer_id IN (<#list ids as id>CAST(${id} AS INTEGER)<#sep>,</#list>)

Limitations

`?join` with a variable or several arguments

?join is supported with one fixed separator only (?join(','), ?join("','")). With a separator stored in a variable, or with several arguments, the whole list is sent as one single value, and the query usually returns nothing.

Template	Result
`IN (${ids?join(',')})`	✅ Works (on PostgreSQL, only for a text column, see section 5)
`IN (${ids?join(sep)})`	⚠️ No result
`IN (${ids?join(",", "none")})`	⚠️ No result

`?join` inside a directive or a string

Do not use ${...?join(...)} inside a FreeMarker directive or a FreeMarker string. The template then fails with a FreeMarker error.

Template	Result
`<#assign s="${ids?join(',')}">` then `${s}`	❌ Error: evaluated to null or missing
`<#if "${ids?join(',')}"?has_content>`	❌ Same error
`${"a${ids?join(',')}b"}`	❌ The template does not parse

What to do: use ?join directly in the query (IN (${ids?join(',')})), and test the list itself in conditions: <#if ids?has_content>.

Empty lists

When the list is empty, IN (${ids?join(',')}) produces IN (), which is invalid SQL. Protect it with a condition:

<#if ids?has_content>WHERE id IN (${ids?join(',')})</#if>

Database-specific quoting

Some database-specific ways of writing strings or names are not recognized when they contain a value: PostgreSQL $$…$$ or E'…' strings, MySQL \' escapes inside a string, SQL Server [...] names. The query may be refused with the error:

Unable to safely parameterize the FreeMarker SQL query (unresolved value placeholder)

or fail on the database side.

What to do: use standard single quotes ('…') around values, and double a quote to escape it ('').