Pentaho : how to make SQL SELECT query passing by PDI / Kettle datasource?

Question

I just create a button that make an SQL SELECT string. And I want to use a registered PDI Kettle transformation as datasource to execute it.

QUESTION : how to pass my SQL string parameter to my simple PDI Ketle transformation registered as datasource, execute in ajax and get the result in json callback.

My Pentaho PDI / Kettle transformation is here and registered in Pentaho CDE : kettle and datasource

That’s the button and the begininng of the function : js button, js button in CDE, and the code beginning : js button code

Answer 1

Short answer: DON’T.

Long answer: the query will be accessible in the Javascript layer of your app, and can be viewed/edited by a malicious user.

If you pass the whole query directly (which you can, by the way), you open a large security whole in that a malicious user can replace the query quite easily using the browser’s console and inject any SQL statement into your application, including a Drop statement.

The query should be parametrized, and only the parameter values are passed to the SQL query. The parameters are sanitized by CDA to some extent, so malicious characters such as single quotes are removed.

But the whole query? Just don’t.