How to get data from a database with a constructed query?

[dave-csc]

Hello,

I need to retrieve data from a database with a SELECT query, but some parts of that query need to be parametrized (I mean entire conditions, not just single values).

I tried with the Table Input transform, but it seems you can at most define queries with prepared-statement-like syntax, adding an entire condition like AND MY_FLAG = 1 doesn't seem to work.

I also tried the Dynamic SQL Row transform, but compared to the table input it requires many other transforms before (to get the parameters, convert the query segments, and include them in the final query) and after (to strip the extra fields kept by the Dynamic SQL Row). I also got issues when I moved all those components in a sub-pipeline to clean up the code a bit, it all stopped to work.

So, what is the best way to achieve the result? I would like to avoid using directly variables to inject the SQL: variables are somewhat public and this could lead to actual SQL injection attacks.

Thanks in advance for answering :)

[KBoersch]

I'm doing this exactly the way you don't like. I build these conditions and set them as variable or depending on the requirements just as parameter.

You could work with a sub-pipeline receiving just the complete sql as parameter instead of seting it as variable. The subpipeline then uses the received param in a table input step. At least you're not seting any global variables then.

That's the "easy way".

If you don't want to do this, imho there is no way around a more complicated approach like dynamic sql row.

Another idea that comes to my mind is using something like a temp-table you're producing via SQL Script transform/action that creates a table with the data you wanna receieve based on your condition. After success you can then use table input with a normal select xy from z on this table with the prefiltered data. I did something similar before, when I didn't want to use hop to directly read out the data. But I'm not sure if it works with your scenario.

Hope my answer was somehow helpful.

Greetings
Kai

[hansva]

This is where you hit the limits of what is possible.
You could indeed compose the query insert it in a Hop variable and use that variable to execute the query, but as you have stated this would allow any part of the query to be modified.
Prepared statements on the other hand are a safe way to inject values into a query but they can't be used to modify the statement on runtime.

What I have done in the past if the goal is to enable/disable specific filters in the where clause is adding a void condition

Select * from my_table
where
(field = ? or 1=?)

By providing a 0 or 1 in the second position you essentially enable or disable that condition

[bamaer]

adding Thad's reply from the mailing lists for completeness: https://lists.apache.org/thread/1xspxgkf5tyqf1ps920lb0lz7sjto0sq

[thadguidry]

We'll, I can retype it here and give some more background also:

A great way to handle this is to use additional technology like DB2Rest, as an example, to
automatically create an API endpoint for your database and then work with
the URL parameters. I can attest that it’s already being used by large
companies to migrate legacy databases and enable fast frontend
development. The bonus is that it mitigates SQL Injections because it uses
prepared statements as well as becomes an automatic API gateway for your
database.

Of course, there are other "automatic API" type middleware, but
we found they were hard to use, and not performant, or lacking features,
so we built DB2Rest to make it simple for DBA's and frontend engineers, both.

https://db2rest.com

[thadguidry]

And for Hop, you'd interact with DB2Rest's created endpoint for your DB, just as you would with any other API, through scripts/code, Hop's REST client plugin, etc.

[dave-csc]

Hello,

actually @hansva's solution was very clever and sufficient for my needs, so I implemented it. It works and requires only two extra components (one to get the variables, and the other to convert them to numbers and values) before the Table Input.

Thanks to all for your support :)