mulesoft - How to add a dynamic parameter in Mule4 Select Query

Question

I have a SELECT query for which the entire WHERE condition is coming from a Java class. How can I use the entire WHERE condition in the SELECT query? This is what I tried:

SELECT * FROM EMPLOYEE_TABLE WHERE 1=1 #[payload]

Here payload is a WHERE clause coming from a Java class: NAME = VKP AND STATE = PA AND CITY = KOP

I am getting an error message like " parameter null was not bound for query select…", but I am able to see the payload value coming out from the Java class and in the logs.

question from:https://stackoverflow.com/questions/65908796/how-to-add-a-dynamic-parameter-in-mule4-select-query

Answer 1

You should not use an expression directly in the query. Instead assign the query in an expression to a variable, then use the variable as the query.

For example something like:

    <set-variable value="#['SELECT * FROM EMPLOYEE_TABLE WHERE 1=1 ' ++ payload]" name="query" />
    <db:select ...>
        <db:sql>#[vars.query]</db:sql>
    </db:select>

Note that you risk having SQL injections vulnerabilities doing this.