JDBI - What's the difference between @define and @bind in JDBI?

Question

Are both functions sanitized / safe against SQL injection? For example, consider the following:

@SqlUpdate("INSERT INTO <tableName> (<columns>) VALUES (<values>)")
    public abstract void addRowToDataset(@Define("tableName") String tableName, @Define("columns") String columns, @BindIn("values") Collection<Object> values);

My current understanding is that @define literally inserts the string as is into the query, but @bind does sanitization. So if we control the columns and tableName parameters and only the values parameter is user input, then we should be fine.

Answer 1

I came across the same question and found the answer surprisingly difficult to find. A colleague tells me it was sort of just common knowledge a programming-generation ago, so maybe its documentation was forgotten as software evolved.

In fact, Oracle's Binding and Defining at first seems promising, but is completely misleading, as its "Defining" refers to setting up references to retrieve results, which is not at all what it means in JDBI.

Here is what they actually mean, if my colleague is correct:

• @Define is for constants, and interpolates the format <field>.
• @Bind is for variables, and interpolates the format :field (a "named parameter"). You are correct: a side-effect of binding is that sanitization occurs.
• @BindIn is for lists, and interpolates the format (element IN (<field>)). The elements are also sanitized.

My mistake was using the <field> format for a @Bind parameter, which resulted in the interpolation always being an empty string.