Does PocketQuery work with dynamic table names?

I would like to use a parameter as part of a table name.  Is it possible to do this with PocketQuery?   The database I am sending queries to has one set of tables for each customer of ours such as ABC, DEF, GHI.

If I need to run the query for customer ABC, the table would be ABC.EMPLOYEES.

If I need to run the query for customer DEF, the table would be DEF.EMPLOYEES.

Can I do this something like :customer.EMPLOYEES?

2 answers

1 accepted

2 votes
Accepted answer

Hi Jeremy,

Thank you for your interest in PocketQuery! This doesn't work with the latest release of PQ on the marketplace, but it should work with PocketQuery 2.1 which is not released yet. Could you install our current release candidate version 2.1-m7? With this one I got my example work...

Query:

SELECT Name, Population
FROM :Table
WHERE Continent = :Continent

The only thing to take care: in the field Parameter Types you must enter YourTableParameter=Constant so in my case I put Table=Constant.

PocketQuery 2.1 will be released very soon!

Regards, Felix (Scandio)

It seems to work, but I had an issue in that I cannot give the table an alias such as in the example below, I alias the table as apl and use it in other places such as:

SELECT * FROM ABCOBJ.TAPRRRPF utm inner join abcobj.tapdefpf uex on utm.UTID = uex.UTID inner join :Table apl on apl.applicationnumber = utm.applicationnumber and apl.employeeid = utm.employeeid and utm.CLIENTLIBRARY = 'xxx' where utm.UTID = '0105200015117555'

 

The queries in PocketQuery only seem to work if you put :Table everywhere such as:

SELECT * FROM ABCOBJ.TAPRRRPF utm inner join abcobj.tapdefpf uex on utm.UTID = uex.UTID inner join :Table on :Table.applicationnumber = utm.applicationnumber and :Table.employeeid = utm.employeeid and utm.CLIENTLIBRARY = 'xxx' where utm.UTID = '0105200015117555'

 

Another thing that would be needed is to let the result set have horizontal scrolling:

resultset.png
How the result set displays is simply unreadable when there are many columns needing to be returned.

Hi Jeremy,

This seems to be a matter of replacement order of the query template with the Spring library we are using under the hood. I don't see a straight forward solution to this. Most probably you'll have to do it as you wrote in your second example.

Regarding the styles, you will have to create your own layout with CSS in a <style> tag in the PocketQuery template. We cannot deal with possible custom layouts in the macro. A good starting point could be: https://css-tricks.com/fixing-tables-long-strings/

Regards, Felix

Felix, do you know if the text entered for the :Table is sanitized before running?

Hmm. What do you mean by "sanitized"?

My developer gave me this info:

Validated.  Determined not to be exploitative.  This only matters if they are using string concatenation.

If the query was "select * from users where :username"

If a user typed: "test'; drop table users;--" into the username field in the confluence page, would the code build the query string as something similar to: "select * from users where username = 'test'; drop table users;--"?

Hi Jeremy,

I understand. Fear not:

  • PocketQuery cannot run multiple statements in one.
  • Query parameters will be inserted in the statement as strings, surrounded by single quotes. You can change that behaviour as a PocketQuery admin setting parameter types (see docs). But also then it must still be one statement and no manipulating clauses will ever work in your statement.

That said, I still recommend to use a read-only DB user for the PocketQuery connection. PocketQuery is designed to be a read-only tool and assigning a user with privileges for DB manipulation is unclean.

Regards, Felix

Jeremy, do you have further questions or will you "accept"? smile

I accept.  I will create a new thread to ask about the possibility of allowing a query to be run against multiple servers instead of just one.   If more than one server is available, then the user can select which one instead of an auto-load occurring (a default could be specified if auto-load was selected).

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Oct 24, 2018 in Confluence

Atlassian Research opportunity with Confluence templates

Do you use templates with Confluence? Take part in a remote 1-hr workshop. You'll receive USD $100 for your time!   We're looking for people to participate in a   remote 1-hr workshop...

1,523 views 25 14
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you