This is an automated email from the ASF dual-hosted git repository. JiriOndrusek pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/camel-quarkus-examples.git
commit 2ff635c30ff1580d4a784f14c00b0b4a534a686a Author: James Netherton <[email protected]> AuthorDate: Fri Aug 7 07:11:42 2026 +0100 Build the jdbc-datasource INSERT with statement parameters The ETL route built its INSERT by interpolating row values into the statement text. The values come from the source database rather than an end user, so the example is not exploitable as shipped, but it is the canonical SQL injection shape in the one example a reader consults for "how do I do JDBC with Camel". It also breaks on ordinary data - a hotel name containing an apostrophe produces a syntax error and the row is silently dropped. Switch to useHeadersAsParameters=true with :?name placeholders so the values are bound as JDBC statement parameters. The statement text is now static, so setBody() takes a constant() rather than a Simple expression. Also wrap the JDBC access in JdbcService in try-with-resources; it previously leaked a pooled connection per request. Add O'Brien's Hotel to the seed data as a regression test: it fails on the old route and passes on the new one. Co-authored-by: Claude Opus 5 (1M context) <[email protected]> --- jdbc-datasource/README.adoc | 2 ++ jdbc-datasource/src/main/java/org/acme/jdbc/JdbcRoutes.java | 11 ++++++++--- jdbc-datasource/src/main/java/org/acme/jdbc/JdbcService.java | 11 ++++++++--- jdbc-datasource/src/test/java/org/acme/jdbc/JdbcTest.java | 3 ++- jdbc-datasource/src/test/resources/init-source-db.sql | 3 ++- 5 files changed, 22 insertions(+), 8 deletions(-) diff --git a/jdbc-datasource/README.adoc b/jdbc-datasource/README.adoc index e57fe29b..4efde859 100644 --- a/jdbc-datasource/README.adoc +++ b/jdbc-datasource/README.adoc @@ -66,6 +66,8 @@ Extract, Transform and Load related logs should be output as below: 2023-11-14 15:12:55,897 INFO [route17] (Camel (camel-9) thread #9 - timer://insertCamel) -> Loading transformed data in target database 2023-11-14 15:12:55,904 INFO [route17] (Camel (camel-9) thread #9 - timer://insertCamel) -> Transforming review for hotel 'Small Hotel' 2023-11-14 15:12:55,909 INFO [route17] (Camel (camel-9) thread #9 - timer://insertCamel) -> Loading transformed data in target database +2023-11-14 15:12:55,915 INFO [route17] (Camel (camel-9) thread #9 - timer://insertCamel) -> Transforming review for hotel 'O'Brien's Hotel' +2023-11-14 15:12:55,919 INFO [route17] (Camel (camel-9) thread #9 - timer://insertCamel) -> Loading transformed data in target database ---- === Packaging and running the application diff --git a/jdbc-datasource/src/main/java/org/acme/jdbc/JdbcRoutes.java b/jdbc-datasource/src/main/java/org/acme/jdbc/JdbcRoutes.java index 27e2fe9c..75d7d10c 100644 --- a/jdbc-datasource/src/main/java/org/acme/jdbc/JdbcRoutes.java +++ b/jdbc-datasource/src/main/java/org/acme/jdbc/JdbcRoutes.java @@ -42,11 +42,16 @@ public class JdbcRoutes extends RouteBuilder { String review = (String) sourceData.get("review"); int mappedReview = reviewMapping.getOrDefault(review, 0); sourceData.put("review", mappedReview); + + exchange.getIn().setHeader("id", sourceData.get("id")); + exchange.getIn().setHeader("hotel_name", sourceData.get("hotel_name")); + exchange.getIn().setHeader("price", sourceData.get("price")); + exchange.getIn().setHeader("review", mappedReview); }) .log("-> Transforming review for hotel '${body[hotel_name]}'") - .setBody() - .simple("INSERT INTO Target (id, hotel_name, price, review) VALUES(${body[id]}, '${body[hotel_name]}', ${body[price]}, ${body[review]})") - .to("jdbc:target_db") + .setBody(constant( + "INSERT INTO Target (id, hotel_name, price, review) VALUES(:?id, :?hotel_name, :?price, :?review)")) + .to("jdbc:target_db?useHeadersAsParameters=true") .log("-> Loading transformed data in target database"); } } diff --git a/jdbc-datasource/src/main/java/org/acme/jdbc/JdbcService.java b/jdbc-datasource/src/main/java/org/acme/jdbc/JdbcService.java index 9aa2caee..4d1ecd34 100644 --- a/jdbc-datasource/src/main/java/org/acme/jdbc/JdbcService.java +++ b/jdbc-datasource/src/main/java/org/acme/jdbc/JdbcService.java @@ -16,8 +16,10 @@ */ package org.acme.jdbc; +import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; +import java.sql.Statement; import io.agroal.api.AgroalDataSource; import io.quarkus.agroal.DataSource; @@ -39,10 +41,13 @@ public class JdbcService { StringBuilder sb = new StringBuilder(); - ResultSet rs = targetDb.getConnection().createStatement().executeQuery("SELECT (hotel_name, review) FROM Target"); + try (Connection connection = targetDb.getConnection(); + Statement statement = connection.createStatement(); + ResultSet rs = statement.executeQuery("SELECT (hotel_name, review) FROM Target")) { - while (rs.next()) { - sb.append(rs.getString(1)); + while (rs.next()) { + sb.append(rs.getString(1)); + } } return sb.toString(); diff --git a/jdbc-datasource/src/test/java/org/acme/jdbc/JdbcTest.java b/jdbc-datasource/src/test/java/org/acme/jdbc/JdbcTest.java index 18b3de7e..43ba70d9 100644 --- a/jdbc-datasource/src/test/java/org/acme/jdbc/JdbcTest.java +++ b/jdbc-datasource/src/test/java/org/acme/jdbc/JdbcTest.java @@ -38,7 +38,8 @@ public class JdbcTest { .then() .extract().asString(); - return "(\"Grand Hotel\",1)(\"Middle Hotel\",0)(\"Small Hotel\",-1)".equals(hotelReviews); + return "(\"Grand Hotel\",1)(\"Middle Hotel\",0)(\"Small Hotel\",-1)(\"O'Brien's Hotel\",0)" + .equals(hotelReviews); }); } } diff --git a/jdbc-datasource/src/test/resources/init-source-db.sql b/jdbc-datasource/src/test/resources/init-source-db.sql index 0b0c7cbb..9cba1706 100644 --- a/jdbc-datasource/src/test/resources/init-source-db.sql +++ b/jdbc-datasource/src/test/resources/init-source-db.sql @@ -20,4 +20,5 @@ CREATE TABLE IF NOT EXISTS Source (id SERIAL PRIMARY KEY, hotel_name VARCHAR(255 INSERT INTO Source (id, hotel_name, price, review) VALUES (1, 'Grand Hotel', 100, 'best'), (2, 'Middle Hotel', 20, 'good'), -(3, 'Small Hotel', 17, 'worst'); \ No newline at end of file +(3, 'Small Hotel', 17, 'worst'), +(4, 'O''Brien''s Hotel', 90, 'good'); \ No newline at end of file
