Custom Report - use macro to make SQL dynamic

Larry Lim asked on August 30, 2018 21:53

I have been trying to create a dynamic custom report that displays a search query based on a bizForm field value. I want to use a URL variable reference and use the QueryString function to place it in the custom report 'query'..

What I created:

Custom table = 'RetailStoreLocator' --> StoreName, State
Reporting > Custom Report > created report named: 'findStore'
Custom Report > Parameter > 'test'
• DEFAULT value == {%QueryString.GetValue("myValue") #%}
So it gets the value from the URL like: www.site.com?myValue=OH <-- (location = Ohio)
query that has:
- QUERY in the 'table' selection named: 'findStore':
  Select StoreName, State from customtable_RetailStoreLocator WHERE '{%test%}' IN (StoreName, State)
So far it does not allow me to use any 'QueryString' function in the Reporting-Parameters area.

The flow for this follows:
1. BizForm with all states
2. Select a state
3. Triggers a page with a Report Webpart
4. Shows the stores that are based on the user's selection
5. User has the option to export the data into an 'Excel' or 'PDF' format (KEY needed function)
Any suggestions?

Correct Answer

Zach Perry answered on September 4, 2018 16:13

If you click the caret and just type in the macro it should work.

Image Text

This will just populate the form, it will still have to be submitted.

And as far as security, it's not just the table you are writing too, passing a querystring directly into a query can potentially allow anyone to do anything to your database under the Kentico's security context, which should be close to dboowner.

0 votesVote for this answer Unmark Correct answer

Recent Answers

Zach Perry answered on August 31, 2018 17:07

Did you click the little caret next to default value to open the new window and enter your macro?

Also, becareful passing values from a querystring directly into a sql query, very high risk for sql injection.

0 votesVote for this answer Mark as a Correct answer

Larry Lim answered on August 31, 2018 17:54

Hi Zach, I did try the carrot, but did not find the querystring macro listed (its a pretty long list to iterate through)... I used the querystring macro on the page I am calling the 'report' webpart... and it works with no problem. When it is called in the Report > Parameters Tab > Default Value > {%QueryString%} macro is not interpreted from my tests.

The data-table is basic information that would not have any security impacts- I always worry about injection situations...

Still investigating a remedy...

0 votesVote for this answer Mark as a Correct answer

Larry Lim answered on September 10, 2018 16:31 (last edited on September 10, 2018 16:32)

Hi Zach, I will try this again and see what happens...

I added security for user-account access that detects user position and such so that will help baffle things a little

Will enact changing the query to a 'stored' procedure to help limit injections...

More shortly...
-Larry

0 votesVote for this answer Mark as a Correct answer

Larry Lim answered on September 10, 2018 22:57 (last edited on December 10, 2019 02:31)

Hi Zach, Checking back in- I was able to finally add the 'QueryString.GetValue' to the report Parameter section. Not sure why it did not take, but I slowly went through the inteli-display as I typed in the macro and it finally worked.

Additionally- my implementation of Kentico locks down all users to specific job-types, titles, and unique viewing privileges so the user-base of the 'store' data is a tiny group of need-to-know staff only. However, I am working on handling a stored procedure for this to help increase protection from injection attacks.

Few things:

I used a custom form that would prevent data from being written to the form db-table

Form's General tab uses:
{% CurrentDocument.NodeAliasPath |(identity)GlobalAdministrator%} ... to point back to itself and makes it transposable.

Appreciate the follow-up... the biggest part of the puzzle was working on the Report Parameters section. After that was figured out it got a lot easier!

Best,
-Larry Lim

0 votesVote for this answer Mark as a Correct answer