This is an automated email from the ASF dual-hosted git repository.
jamesnetherton pushed a commit to branch camel-quarkus-main
in repository https://gitbox.apache.org/repos/asf/camel-quarkus-examples.git
The following commit(s) were added to refs/heads/camel-quarkus-main by this
push:
new a373d68e Build the jdbc-datasource INSERT with statement parameters
a373d68e is described below
commit a373d68e55aecdbd772e755e6fb2c11890c2e75e
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
