is it safe to use java.text.MessageFormat.format() when creating an sql query?

Question

I am using MessageFormat.format() to create the String for my PreparedStatement. I read that using StringBuilder may be a cause of sql injection. Is it the same for MessageFormat?

the code goes like this

String SQL
        = "select CT.SYS_CHANGE_OPERATION, CT.{0} as ID, t.*\n"
        + "from changetable (changes {1}, ?) as CT \n"
        + "left outer join {2} as t \n"
        + "on t.{3} = CT.{4} \n"
        + "order by CT.SYS_CHANGE_VERSION";

String finalSQL = MessageFormat.format(SQL, primaryKey, table, table, primaryKey, primaryKey);
        PreparedStatement pstmt = con.prepareStatement(finalSQL);

Check https://stackoverflow.com/questions/17569608/format-a-message-using-messageformat-format-in-java — Arvind Kumar Avinash, Jan 28 '20 at 12:02
@toastedDeli There is no user input. I am using it in a switch case instead of writing the same script 50 times. — amr soliman, Jan 28 '20 at 12:29
If `primaryKey` and `table` do not include user input, it should be safe. However using a `StringBuilder` would be safe as well. — SteffenJacobs, Jan 28 '20 at 13:03

score 1 · Answer 1 · edited Jan 28 '20 at 12:05

1

This is not safe. MessageFormat.format just replaces the occurrences of {x} in the given string with the given objects without escaping the latter. Therefore it is equivalent to string concatenation.

To build an SQL query that includes data from variables one should always use prepared statements.

edited Jan 28 '20 at 12:05

Robert

39,162
17
99
152

answered Jan 28 '20 at 11:58

SteffenJacobs

402
2
10

score 0 · Accepted Answer · answered Jan 28 '20 at 12:26

Creating an SQL string as you do with filling in table and column names would only be unsafe (SQL injection) when the variable strings stem from some passed input.

Hence StringBuilder might even be more sound (clear) than a MessageFormat. On the other way a format is more readable.

As named placeholders would be even nicer, consider Apache Common StringSubstitutor or such.

In every case code checkers might warn for SQL injection on this.

Sometimes you can use

DatabaseMetaData metaData = connection.getMetaData();

and use table and column names for operation on the meta data.

This is a largely DB vendor independent way of JDBC to query the database.

is it safe to use java.text.MessageFormat.format() when creating an sql query?

2 Answers2