Can I dynamically generate reports by sending MySQL statements from my application?

myDBR Forums » General discussion

Can I dynamically generate reports by sending MySQL statements from my application?

(22 posts) (2 voices)

Started by shem
Latest reply from myDBR Team

Tags:

shem, Member

When using myDBR, I usually create a report in the myDBR portal and then embed a URL into my application, and the report displays in my webpage.
Is there a way to prepare a MySQL statement inside my application and send it to myDBR such that myDBR will dynamically generate a report (without first going in to the myDBR portal and creating a report)?
myDBR Team, Key Master

You can pass a SQL statement as a parameter and use dynamic SQL in the report. Although, that is usually not how reports are created.

If you do that, consider calling the report as HTTP POST (use parameter '&http_method=post') if your query is a long one. The user might be using an old browser with limits for the URL length.

What would be the reason for passing the SQL statement from the app? Then you would have to decide where potential myDBR commands would come from if you need to format the report. Either you pass the commands from your app, or you already know the query and keep the myDBR commands in the report.

--
myDBR Team
shem, Member

Thank you very much for the reply, and yes I tested it and it works.
My follow up question to you is Should I be concerned about MySQL injection?, i.e. that with a direct URL to the myDBR report template containing a complete SQL statement, that someone could get hold of the URL string and change the parameter to a dangerous one?
myDBR Team, Key Master

If you pass the parameter as a protected parameter (p-parameter), it is protected by the hash parameter.

Why would you need to send a SQL-query as a parameter?

—
myDBR Team
shem, Member

Thanks again for the reply.
I am guessing there is more you need to tell me about using the "p" parameter.
When I changed the "u" parameter to a "p" parameter in my PHP code i.e. from
'http://localhost/mydbr/report.php?r=8&m=1&h=XXXYYYZZZ078e04c770f15b44ec7f3f639909&i=1&u1=' . $client_dbname . ' &u2=' . $sql_urlencoded;
to
'http://localhost/mydbr/report.php?r=8&m=1&h=XXXYYYZZZ078e04c770f15b44ec7f3f639909&i=1&p1=' . $client_dbname . ' &p2=' . $sql_urlencoded;
it no longer generated output for the report.
So is there something else I need to do? For example:
1) Is there something I have to change in the stored procedure itself? Are the parameters declared differently?
2) I saw something in your documentation about "calculate the URL hash by yourself". Do I need to do this and if so how?

Also, can you explain further how using a "p" parameter protects me from SQL injection?

p.s. The reason we want to send an SQL-query as a parameter, is because we want to pre-processing before sending the query.
Just some examples which I am making up on the spot.
Maybe based on who the user is we want to display the order of the columns differently.
Or maybe based on what time-zone the user is in we might want to hide a column.
myDBR Team, Key Master

Hi,
myDBR has user-defined parameters, protected parameters, and automatic parameters.

For example, if we have a report showing the user's projects from a given timeframe and the user can click on a project and get further details on it. We have then at least three parameters (user login/ID, the timeframe and the chosen project ID for the linked report.

An automatic parameter would be the user login (see Automatic parameters). The URL does not show automatic parameters; myDBR will handle them internally.

The timeframe (from-to dates) would be user-defined parameters, which the user can freely change. The URL shows these parameters as 'u' or 'e' parameters.

When the project list is shown and the URL is generated for the detail report, we do not want the user to be able to change the project ID in the URL (to see projects that do not belong to the user). For this, myDBR will generate a protected parameter for the Project ID. See the documentation. Protected parameters are included in the hash parameter calculation; 'h'-parameter in the URL). The hash seed comes from the Environment settings and is unique for your installation.

--
myDBR Team
shem, Member

The above information is helpful so thank you for that.
However, it only partially covers what I am looking for.

Your above example is about a "linked report", and if I understood it correctly, when the user clicks on a specific project then the URL generated for the resulting page contains a protected project_id parameter.
I am guessing that the URL will show (for a project_id of 4) &p1=4 (or if its the third parameter, then maybe p3=4)

However, I am not asking about a linked report.
I am asking about the initial report which I call via a Direct URL which I have embedded in my PHP application.
For example I have created a report which accepts an sql statement as a parameter
http://servername/mydbr/report.php?r=8&m=1&h=aaabbbccc078e04c770f15b44ec7f3f639909&i=1&p1=' . $client_dbname . ' &p2=' . $sql_urlencoded;
where the variable $sql_urlencoded might contain
"select * from customers"
My stored procedure
My question is: what is to prevent my customer who uses my PHP application, and doing "View Page source". He will see
http://servername/mydbr/report.php?r=8&m=1&h=aaabbbccc078e04c770f15b44ec7f3f639909&i=1&p1=CocaCola&p2=select * from customers
Then he will copy and paste this to his browser, except he will make the change below which I have bolded
http://servername/mydbr/report.php?r=8&m=1&h=aaabbbccc078e04c770f15b44ec7f3f639909&i=1&p1=CocaCola&p2=DELETE customers
In other words what protects the initial report from SQL injection?
myDBR Team, Key Master

Shem,
When you have parameters like '&p1=CocaCola&p2=select%20%2A%20from%20customers', both parameters (p1 and p2) are protected. The URL hash value (h) is calculated based on these values (see Direct URL access to reports).

If the user takes the URL, changes the p1 or p2 parameter, and tries to run the report, the URL hash no longer matches the parameters, and myDBR refuses to run the report.

P.S, You should only give myDBR read access to the actual data unless your want to make reports editable.
--
myDBR Team
shem, Member

Actually in the myDBR report I created it was u1 and u2 that were generated as part of the Direct URL.
So my mistake. (I had copied the direct URL to my PHP code and changed "u" to "p" hoping that my embedded report would still work, while also providing protection - but of course the report did not work after that)

So then how does one cause the Direct URL to contain p1 and p2 (as opposed to u1 and u2)?
Does that only happen with "linked reports"?
Is it something in my stored procedure that causes it?
Do I need to go to the "Edit" link of the myDBR report and set something there?
myDBR Team, Key Master

So then how does one cause the Direct URL to contain p1 and p2 (as opposed to u1 and u2)?

if you create the URL by yourself, you use the p1 and p2 in the URL, and calculate the hash (h-parameter) for the URL according to documentation.

Does that only happen with "linked reports"?

Whatever creates the URL (you, myDBR, trird party app), they can use protected parameters. myDBR will automatically use protected parameters for parameters that are not user changeable parameters.

Is it something in my stored procedure that causes it?

No. The stored procedure is eventually called once myDBR parses the URL, getting the procedure and the parameters.

Do I need to go to the "Edit" link of the myDBR report and set something there?

No, a parameter can be a user-changeable parameter or a protected parameter depending on how the URL is being generated.

--
myDBR Team
shem, Member

Thank you, that is definitely helpful.

Just a few loose end questions now:

1) How can I see the the URL hash seed value used in the installation of myDBR? Is it in one of the config files? If so, which one?

2) I understand that I can generate a direct URL which has the "p" parameter if I follow the instructions from the documentation in the section entitled "Generating a direct URL".

I also seem to understand that when when I create a "linked report" that the parameters passed to the second stored procedure become protected (and the resulting URL has a "p"), correct?
Is there any other way that myDBR would cause a "p" parameter to be generated?
myDBR Team, Key Master

1) How can I see the the URL hash seed value used in the installation of myDBR? Is it in one of the config files? If so, which one?

You can find the seed value at Environment settings → Authentication / security → URL hash seed. The value is stored in the database.

2) I understand that I can generate a direct URL which has the "p" parameter if I follow the instructions from the documentation in the section entitled "Generating a direct URL".

I also seem to understand that when when I create a "linked report" that the parameters passed to the second stored procedure become protected (and the resulting URL has a "p"), correct?
Is there any other way that myDBR would cause a "p" parameter to be generated?

In general, myDBR uses protected parameters for those parameters that are not user changeable. User changeable parameters are the ones that you ask from the user or ortherwise allow user to change. You decide which parameters are user changeable, the rest will be protected.

--
myDBR Team
shem, Member

Thank you again
you write "find the seed value at Environment settings → Authentication / security → URL hash seed"

When I go there it shows an empty value.
I see that there a "Randomize" button. Am I suppose to click it to generate the first value for the seed?

You also write that "the value is stored in the database"
Which table?
myDBR Team, Key Master

When I go there it shows an empty value.
I see that there a "Randomize" button. Am I suppose to click it to generate the first value for the seed?

You can click the Randomize button or type one of your own.

You also write that "the value is stored in the database"
Which table?

It's stored in mydbr_options under name url_hash

--
myDBR Team
shem, Member

Thank you for the information.

I was successfully able to follow the instructions in your documentation, on creating a hash and building a direct url which I embedded in my PHP application.
I was trying to execute report_id=8 which is defined in my myDBR portal.

The good news is when I rendered the direct url from my application it does not give me a Security Hash error (nor any other error)

The bad news is that it does not seem to be executing report_id 8
In the stored procedure, I put a very simple select statement at the very beginning.
My PHP application is not showing even the output of that simple select statement.

Am I missing something?
Is there something that connects the Direct URL I generated in my PHP application, with the myDBR report created on the portal, other than the report_id?
myDBR Team, Key Master

Try to narrow the problem down. Does your generated URL run the report when you run it directly in the browser (not as embedded report from your app)?

The direct URL is as any myDBR report URL. The report being run is defined by the report ID (r-parameter in the URL).

--
myDBR Team
shem, Member

Does your generated URL run the report when you run it directly in the browser (not as embedded report from your app)?

No, when I do a var_export of the direct URL from PHP, and copy it to the browser, then I do get the error
Report security hash does not match. Report execution aborted

But I am guessing that that error is something related to url encoding/decoding when pasting into the address bar of the browser.
My PHP code works regular mydbr reports where I copy the URL from the myDBR portal,
and I think (not sure) for those reports if I copied the wrong hash from the myDBR portal, my PHP application would show the "security hash" error.

The following is the var_export of the direct URL I generated from within the php application for the policy list report
http://localhost/mydbr/report.php?r=8&m=1&h=41022a72cd2eb692e98953291e1d67c467383eae&i=1&u1=`clearfacto`&p2=SELECT+%2A+FROM+%60clearfacto%60.bf_policy+LIMIT+20

p.s. When encoding the p2 parameter for the hash, was I supposed to use the php urlencode or the php rawurlencode function.
I noticed in the example in your documentation that you used rawurlencode to generate the hash (hence a space was a %20), while you used urlencode in the URL you show for the p2 parameter itself (where a space became a "+" character).

p.s. The original unprotected URL from the mydBR portal was
http://localhost/mydbr/report.php?r=8&m=1&h=b33176f34509078e04c770f15b44ec7f3f639909&i=1&u1=`clearfacto`&u2=SELECT+%2A+FROM+%60clearfacto%60.bf_policy+LIMIT+20
and that still works, both from the PHP application, and when pasting it into the browser
myDBR Team, Key Master
OK,
let's break it down.

You have a report with ID 8, in folder 1 and one protected parameter p2 with value "SELECT * FROM `clearfacto`.bf_policy LIMIT 20". Assume your hash seed is "secret".

You concatenate the following values
- report id "8"
- protected parameter URL variable "p2"
- the urlencoded value for the parameter value "SELECT%20%2A%20FROM%20%60clearfacto%60.bf_policy%20LIMIT%2020"
- the optional m + folder ID "m1"
- the hash seed "secret"
Hence the string would be "8p2SELECT%20%2A%20FROM%20%60clearfacto%60.bf_policy%20LIMIT%2020m1secret". The sha1 value from that would be e0035bbcd10a96b8c65d811f2b66c3478b8b2b0a.

So, your URL would be:

http://localhost/mydbr/report.php?r=8&u1=%60clearfacto%60&p2=SELECT%20%2A%20FROM%20%60clearfacto%60.bf_policy%20LIMIT%2020&m=1&h=e0035bbcd10a96b8c65d811f2b66c3478b8b2b0a&i=1

The user parameter u1 needs to be urlencoded as user is free to change it, it does not affect the hash value.

--
myDBR Team
shem, Member

you wrote
the hash seed "secret"

No.
Because in my settings and in the mydbr options table, there was no value for the hash, so I just set the hash seed to an empty string
myDBR Team, Key Master

Do not use an empty string as a hash seed value. Set an unique value for it.

--
myDBR Team
shem, Member

IT WORKED - Thank you so much for your help.

Just a side question on something you mentioned.
You wrote
"The user parameter u1 needs to be urlencoded as user is free to change it, it does not affect the hash value"

Are you saying that always the "u" parameters need to be urlencoded? Even if I copy the URL from the myDBR portal and paste it into my PHP application?
Or does that only apply if I am generating the hash inside my PHP code?

Also, what do you mean by "as user is free to change it"?
Do you mean if a hacker(user) obtained the url and pasted it into his browser while changing the parameter?
If its a hacker, then he could simply first decode it and then paste it in to a browser?
OR do you mean that our PHP application might sometimes pass the parameter value with characters that the browser does not handle, so it is advisable to be urlencoded?

Also, when you say urlencoded
Do you mean the PHP urlencode function (which replaces a space with %20)
or do you mean the PHP rawurlencode function (where a space became a "+" character)?
I noticed in the example in your documentation that you used %20 to generate the hash while you used "+" character when showing the "p2" parameter in the full URL.
myDBR Team, Key Master

"The user parameter u1 needs to be urlencoded as user is free to change it, it does not affect the hash value"

Are you saying that always the "u" parameters need to be urlencoded? Even if I copy the URL from the myDBR portal and paste it into my PHP application?
Or does that only apply if I am generating the hash inside my PHP code?

You are generating an URL, so all URL components needs to be urlencoded (use rawurlencode). Otherwise you will not have a valid URL.

Also, what do you mean by "as user is free to change it"?
Do you mean if a hacker(user) obtained the url and pasted it into his browser while changing the parameter?
If its a hacker, then he could simply first decode it and then paste it in to a browser?
OR do you mean that our PHP application might sometimes pass the parameter value with characters that the browser does not handle, so it is advisable to be urlencoded?

An u-parameter means that user is free to give any value for it. An example of an u-parameter would be that you ask user timeframe (start time and end time) that the report should fetch the data from. User is free to choose the dates for him/her liking.

If you do not want anyone to be able to change the parameter value, use protected parameters.

User parameters and protected parameters need to be urlencoded as they are part of an URL.

Also, when you say urlencoded
Do you mean the PHP urlencode function (which replaces a space with %20)
or do you mean the PHP rawurlencode function (where a space became a "+" character)?

When you urlencode in PHP use the rawurlencode function. It will replace the space with %20. PHP's urlencode function is used for legacy applications.

--
myDBR Team

Reply

You must log in to post.