Does PocketQuery work with dynamic table names?

[Jeremy Soronen]

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?

[Felix Grund (Scandio) · Accepted solution]

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)

[Jeremy Soronen]

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.

[Felix Grund (Scandio)]

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

[Jeremy Soronen]

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

[Felix Grund (Scandio)]

Hmm. What do you mean by "sanitized"?

[Jeremy Soronen]

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;--"?

[Felix Grund (Scandio)]

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

[Felix Grund (Scandio)]

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

[Jeremy Soronen]

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).