Never concatenate strings with jOOQ – Java, SQL, and jOOQ.

[ad_1]

jOOQ supports a large amount of out-of-the-box SQL syntax. As such, most users won’t think of resorting to string concatenation like they used to when writing dynamic SQL with JDBC. But every once in a while a vendor specific feature is not supported by jOOQ (yes it does happen). In this case, jOOQ supports a variety of “Simple SQL” API, which can be used to build almost all types of jOOQ API elements, such as:



// Static import is implied, as always
import static org.jooq.impl.DSL.*;

// Column expressions
Field<String> f = field("cool_function(1, 2, 3)", String.class);

// Predicates
Condition c = condition("col1 <fancy operator> col2");

// Tables
Table<?> t = table("wicked_table_valued_function(x, y)");


But then, sometimes you need to pass an argument to such a function dynamically, such as another column expression. And you want to do this in a type safe way, because the jOOQ code generator has already produced type safe column expressions. So you might be inclined to concatenate, however:



field("cool_function(1, " + MY_TABLE.MY_COLUMN + ", 3)");


Never do that!

For these reasons:

  1. Although jOOQ is generally very safe for SQL injections, this is where you can still introduce a simple SQL injection vulnerability. Not in this case, because the column is generated code, but maybe you will concatenate the user input. Note that in order to increase protection against SQL injections, the use of raw SQL can be prevented globally and only allowed locally when needed by adding our PlainSQL verifier, using the verification framework or Google ErrorProne.
  2. As always with string concatenation, you are prone to SQL syntax errors. In this case, the generated SQL is not specific to any dialect, because MY_TABLE.MY_COLUMN.toString() is called, without any contextual information, like the SQLDialect and all other configuration indicators.

Instead, use the jOOQ raw SQL models mini-language, which allows model placeholders like {0}, {1}, {2}:



field("cool_function(1, {0}, 3)", MY_TABLE.MY_COLUMN);


And if you do it more often, you can include this call in your own mini DSL:



public static Field<String> coolFunction(Field<?> field) {
    field("cool_function(1, {0}, 3)", field);
}


And now call it like this:



coolFunction(MY_TABLE.MY_COLUMN)


Generally :

With jOOQ you should never need to resort to SQL string concatenation

You can still use either:

  • The jOOQ DSL API secure type
  • The simple SQL modeling API (and ideally hide such use behind your own type-safe DSL API)

[ad_2]

Source link