Generate where clause using MessageFormat

Question

I want to generate SQL using MessageFormat so that same string can be used by many users and they just have to pass where clause arguments.

e.g. I want select * from user where name='John' and age=15 and area='JStreet'

I can do it using MessageFormat.format(select * from user where {0}={1} and {2}={3} and {4}={5} ,"name","'John'", "age","15","area","'JStreet'")

But I want it dynamic. Means here I am bounded till {0}-{5} what if I need to add more AND conditions. How can I do this ?

Answer 1

Do not let the user specify the column names as strings. That makes your code easy to break, and it opens you to a very common and dangerous security vulnerability known as SQL injection. I know you said it’s only “for internal use,” but employees/students can be hackers and it’s always possible one will wish to cause harm.

Instead, represent the columns as enum values. I assume the columns of the user table are fixed, so you can hard-code them in the enum:

public enum UserField {
    NAME,
    AGE,
    AREA
}

As others have mentioned, always use a PreparedStatement when making use of values from end users or from unknown code. You can now use the enums to build that PreparedStatement:

public PreparedStatement createStatement(Map<UserField, ?> values,
                                         Connection conn)
throws SQLException {

    Collection<String> tests = new ArrayList<>(values.size());
    for (UserField field : values.keySet()) {
        tests.add(field.name().toLowerCase() + "=?");
    }

    String sql;
    if (tests.isEmpty()) {
        sql = "select * from user";
    } else {
        sql = "select * from user where " + String.join(" and ", tests);
    }

    PreparedStatement statement = conn.prepareStatement(sql);

    int index = 0;
    for (Object value : values) {
        statement.setObject(++index, value);
    }

    return statement;
}