JQL input sanitization

[Theo Gravity]

I'm looking into creating a set of REST endpoints that call the Jira cloud API using JQL, specifically this API:

https://developer.atlassian.com/cloud/jira/platform/rest/v3/#api-api-3-search-post

I'm using JQL to search issues because I have custom fields that I need to query against and do not intend on using the issue id(s) at all as part of the search.

// javascipt
const jql = `project = PROJ AND "Some field" ~ ":input_from_user"`

// :input_from_user needs to be sanitized so things like
// "some value AND" cannot be used for the value

Usually with this kind of access, when you allow custom parameters to be sent by the end-user, you have to be careful with SQL-like injection. 

Is there anything I can use to build JQL queries in a secure fashion in this scenario? In most database libraries, you can create prepared statements w/ parameter binding.

Is there a good list of operators / keywords / escape items that can be referenced against to possibly build my own sanitization function?

Or is it recommended that JQL-based APIs only be used for internal purposes only?

I'd rather not resort to having to connect to the database that Jira connects to and write SQL directly against it (but at least the SQL drivers / libraries have the capability of preventing injection attacks), so was curious.

[Nic Brough -Adaptavist-]

JQL is not SQL, and you can't inject anything dangerous into it.  JQL does nothing but search and return a list of issues.  It doesn't do anything else, it really is just a list of matching issues.

  The worst attack someone can make is an attempt at a denial of service - a JQL string that would return a lot of issues, and even that won't do a lot because the service is throttled so it can continue to serve real users.

On top of all of that, to use JQL, you have to be authenticated (unless you have allowed "anonymous" access).

[Tom Lister]

Hi @Theo Gravity

a you can safely search using JQL over REST as you will still have to provide credentials and pass Jira persmission checks.

See the docs here 

https://developer.atlassian.com/server/jira/platform/jira-rest-api-examples/

For examples of searching for issues.

[Theo Gravity]

I don't think both answers understood my question.

Here's a hypothetical setup:

Public API:

/getIssuesByStatus/:status

Untrusted user - [sends :status] -> Public API - [Calls Jira Cloud Issue Search API using :status under JIRA user with project read/write permissions for issues] -> JIRA API  

My query looks like this:

project = PROJ AND status = ":status"

Ideally, the user should only be sending values that fall under the status.

Because I lack the ability to sanitize the :status param, the user could do something like

/getIssuesByStatus/closed%22%20AND%20id%20%3D%20%22123 // closed" AND id = "123

This gets translated in the Public API to:

project = PROJ AND status = "closed" AND id = "123"

Which should not be sent to the JIRA endpoint because only status values should be sent.

I really want to know if there is a recommended escaping / sanitization solution specific for dealing with JQL queries here to prevent the above issue. 

[Nic Brough -Adaptavist-]

I think we've understood you fine.

What we're telling you is that there is no problem with this.  If the user sends a nonsense query, or even one that makes sense but returns nothing because they're not authorised to see them, so what?  Who cares that they get nothing back?

If you want to do this level of "sanitisation", then you need to (re)write your external application in a way that hides all of this from the user, and does all the work of finding out what might be valid and limits them to those values.

It's a lot of work compared with telling the user "send valid data if you want a useful response"

[Theo Gravity]

What if they get something back and that something isn't meant for them? That is possible if they are able to guess the right query.

Limiting the values is a good idea, however. Although in some of the cases, they're not straight enumerations, something like a string pattern can be used to ensure the input only matches it.

Thanks.

[Nic Brough -Adaptavist-]

>What if they get something back and that something isn't meant for them? That is possible if they are able to guess the right query.

It is only possible if you have given them the permission to see the "thing that isn't meant for them" in Jira.  External calls respect the permissions in Jira.

[theogravity]

Can you point to me the right place where I can set the permission of the Jira API user that I call with to restrict it so that it can only execute  a specific query as mentioned above (where the :status still can be a dynamic value) with no modifiers like order by, etc (anything that a user could possibly prepend or append to)?

[Nic Brough -Adaptavist-]

You do not limit the queries, you limit the issues returned to what the user can see.

[theogravity]

That's not going to work for my use case.

Given the responses so far:

- There are no sanitation tools

- Use SQL prepared statements with parameter binding instead 

[Nic Brough -Adaptavist-]

There are no "sanitation tools" because there is nothing to sanitise.

You need to get your permissions right for the users.

Do not try to use SQL, this is madness given that the Jira database is not designed for any form of reporting, you will end up with hideously complex queries that fail to account for security and give you performance issues.

[theogravity]

I completely disagree. I've provided an example above of something that needs sanitization for my particular use case and the solutions presented completely skirts around the issue and doesn't support the use case at all.

It's ok to say that my use case is not compatible with how JQL should be used.

[Nic Brough -Adaptavist-]

I think you might be misunderstanding the point, but it's hard to explain.

Can we go back to first principles?  What are you trying to achieve, and why do you think you need to "sanitise" the inputs?  And what do you think "sanitise" really means and how it is of benefit to your process?

[Tom Lister]

Hi 

the most insecure JQL you could create is to say nothing I.e an empty JQL statement. This effectively asks JIRA to show me everything I’m permitted to see. Everything else you add is reducing that list to something more meaningful. I guess that’s why Jira reports ended up being called filters.

if your users are connecting as Jira users they cannot return any more information via Rest than they could while logged in to Jira normally. Although that would seem a wasted exercise.

if you are allowing non Jira users to access via a proxy user, you will be working with the issues the proxy user can see. Nothing you do to the JQL will change that or increase its scope.

Issue security is covered in many articles

https://confluence.atlassian.com/jirakb/how-to-limit-user-to-only-browse-issues-assigned-to-or-reported-by-them-779160753.html

https://confluence.atlassian.com/jirakb/limit-restrict-user-s-project-access-280069544.html

[Pradeep Das]

The issue is that the `superuser` is executing the queries. So every entity is visible to the query and, in this case, the service that's making the query has no way to ascertain on whose behalf the queries are executed. 

[Nic Brough -Adaptavist-]

That's wrong.  A "superuser" may well have complete admin rights, but that is admin rights.  I've looked after Jira systems where I can only see a fraction of 1% of the issues in it, despite being a "superuser".

The statement "the `superuser` is executing the queries. So every entity is visible to the query" is completely wrong.  The query will get the results that the user running it has permissions to see.

Which completely solves the "who should see what" problem.

[Pradeep Das]

Got it... Thanks!! 
As you said:
`The query will get the results that the user running it has permissions to see.`

The use case here is that a special service-user is running the query and that special service-user has permission to see everything. The fact that the service is constructing the JQL, it has already performed the necessary steps and checks outside of the JIRA setup to form the right JQL query. The query, in theory, would result in returning the correct visible or browsable set of issues if it is not altered by some other means.

So repeating the original question: how can this service that's constructing the JQL be sure that the query is not altered using some form of injection by the client? 
Can the client pass a well-crafted query in the values that would eventually lead to altering the query? If so then your point on "who can see what" does not solve this problem.   

[Nic Brough -Adaptavist-]

Repeating the original question, which has already been answered, does not help explain what you are looking for

Please could you explain what you think an "injection" might do?  As explained already, JQL is not SQL, and not susceptible to what security systems call "SQL injection" attacks.  To try to rephrase it again, even if you made the most heinous "Johnny drop tables;" attack,  the response would be one of two types:

• The data you asked for, limited to what the user can see
• A "bad request" because your query is not understandable.

Which are not injection attacks, because that idea is nonsense.