0

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.

ThunderM
  • 41
  • 1
  • 7

1 Answers1

0

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'.

SebastianBrandt
  • 439
  • 2
  • 10
  • Sorry I should have mentioned the Camel version. The current version is 2.17 and the problem therefore is not that the poll enrich does not have access to the original exchange I believe/hope. I believe the sql is already executed before applying the pollEnrich and I have not correctly specified the parameter at that point, but I do not know how to. – ThunderM Jun 19 '17 at 14:04
  • Could you try enrich just to make sure its not something weird about pollEnrich? Could you also try :#${header.brand} in your query? – SebastianBrandt Jun 19 '17 at 14:18
  • Nope, as I already suspected it does not work (I may even already have tried that or at least something similar). Though I did hope it would work this time. – ThunderM Jun 20 '17 at 17:11