How can I search issues with SQL in comments?

[philippos]

I have a Jira installation with thousands of issues. Some of them contain in comments SQL commands.

I would like to collect all those SQL commands, along with corresponsing Jira ID.

Is there any such method? Simple querying for issues with "SELECT" or "INSERT" into comments returns many irrelevant records.

Thank you.

[HarryH · Accepted solution]

so now I think the workaround is to run:

comment ~ "insert" AND comment ~ "into"

[HarryH]

Please use "Issue Advanced Search", and then use JQL:

comment ~ "insert" OR comment ~ "update"

[philippos]

Thanks, this will get all comments with "insert" or "update", correct?

Can I get all comments with "Select ___ from" where ____ any single word?

[HarryH]

yes, the JQL I pasted search all comments contains "insert" or "update", you can change "OR" to "AND", if you want to use "and" relationship.

Also you can add more conditions after it, like: OR comment ~ "select* from"

* and ? is also support in JQL.

To perform a single character wildcard search use the "?" symbol.

To perform a multiple character wildcard search use the "*" symbol.

Please note that there is a space after *, I have tested on my jira, it works fine.

If you have any problem, please let me know.

[philippos]

Thank you.

I get results where the words "insert" and "into" are dispersed in the comment.

Is it possible to force the results to contain them in exact sequence "insert into"?

[Dan Garthwaite]

I just did this with a proximate query:

status = "DONE" AND comment ~ '"SELECT FROM WHERE"~100'

Pay special attention to the spacing around the first tilde and also the single quotes around the proximate expression.

[philippos]

Therefore I see my only choices is to search comments for words like "select", "delete", "into", "from" etc.

[philippos]

Therefore I see my only choices is to search comments for words like "select", "delete", "into", "from" etc.

Thanks anyway.

[HarryH]

To search the exact sequence, you can search with

comment ~ "\"insert into\""

But I have tried on my machine, it doesn't work, I think it's a bug of jira, because the documention suggests the usage.

• Find issues where a Comment contains the exact phrase "My PC is quite old":

  comment ~ "\"My PC is quite old\""

Please refer to the doc:

https://confluence.atlassian.com/display/JIRA052/Advanced+Searching#AdvancedSearching-Comments

[Peter Van de Voorde]

Hi philippos,

JQL doesn't allow wildcards so that might be a bit difficult.

You could ofcourse try something like this :

comment ~ 'Select' and comment ~ 'from' and comment ~ 'where'

Best Regards,

Peter

[Udo Brand]

Peters solution is as close as you can get. Even so it is perfect since there could be three comments one containing 'select', the second 'from' and the third 'where'.

[HarryH]

JQL supports wildcards, please see my example in previous comment, also please check https://confluence.atlassian.com/display/JIRA/Performing+Text+Searches

[Peter Van de Voorde]

I stand corrected, I must have misread it somewhere.

Thank you for pointing this out Harry.