Camel Blueprint specify parameter for prepared sql statement

Question

I have a poll enrich which enriches a POJO with the result of an SQL query (from a MySQL database). It currently gets the brand from the POJO and then gets the name from the order matching the brand. I had to add quotes around the ${body.getBrand}, else the query would look for a column with the brand name instead of using the value. Currently it looks like this:

<pollEnrich id="_enrich1" strategyRef="merge" timeout="5000">
    <simple>sql:SELECT name FROM orders WHERE brand= '${body.getBrand}'</simple>
</pollEnrich>

I want to change it because I'll probably need to create more sql queries and the current version does not work if the value contains quotes and thus is vulnerable to sql injection.

I thought prepared statements would do the trick and wanted to use a named parameter but I do not seem to be able to set the value of the parameter.

I have tried many different things like for example setting a header and change the query to have a named parameter:

<setHeader headerName="brand" id="brand">
    <simple>${body.getBrand}</simple>
</setHeader>
<pollEnrich id="_enrich1" strategyRef="merge" timeout="5000">
    <simple>sql:SELECT name FROM orders WHERE brand= :#brand</simple>
</pollEnrich>

but I keep getting

PreparedStatementCallback; bad SQL grammar [SELECT name FROM orders WHERE brand= ?]; nested exception is java.sql.SQLException: No value specified for parameter 1

I have also tried setting the useMessageBodyForSql option to true (since this seemed like something that might help?) but nothing I have tried seemed to work.

I have seen a lot of examples/solutions for people setting the routes with java, but I assume there must also be a solution for the blueprint xml?

If anyone got any suggestion or example that would be great.

Answer 1

In Camel version < 2.16, pollEnrich doesn't have access to the original exchange and therefore cannot read your header, hence the exception. This is documented here: http://camel.apache.org/content-enricher.html Guessing from your example, a normal enrich should work too and it has access to the original exchange. Try changing 'pollEnrich' to 'enrich'.