Parameterized SQL Queries in Mirth

This doesn't seem to be an often discussed topic, but in the interest of preventing SQL injection it should be an item of importance with developers. (At least in the back of your mind)

Using the provided code snippet from Mirth's reference list:


var dbConn;
var result;

try {
dbConn = DatabaseConnectionFactory.createDatabaseConnection('driver', 'address', 'username', 'password');
result = dbConn.executeCachedQuery('expression', paramList);
} finally {
if (dbConn) {
dbConn.close();
}
}

or

var dbConn;
var result;

try {
dbConn = DatabaseConnectionFactory.createDatabaseConnection('driver', 'address', 'username', 'password');
result = dbConn.executeUpdate('expression', paramList);
} finally {
if (dbConn) {
dbConn.close();
}
}

is somewhat tedious and error prone.
If you want to use much more straightforward and less error prone, a simple way to do that is to use something like:


importPackage(java.sql);
var dbConn = DriverManager.getConnection('dbUrlString','username','password);

var query = dbConn.prepareStatement("Select id from sampleDB where Firstname=? and Lastname=?");

query.setString(1,"John");
query.setString(2,"Doe");

result = query.executeQuery();

If your queries are consistent you may even consider creating a library in the Global Deploy Scripts with a variety of pre-built query strings that can be easily reused.